ADO.NET Is Fun (Part III)

Introduction

In parts I and II, I introduced classes in the first part of ADO.NET (DataSet) such as DataTable, DataColumn, DataRow, DataRelation, DataSet and I also mentioned some important methods, properties, or concepts such as serializing and deserializing. In this segment, I will introduce classes in the second part of ADO.NET – Data Provider. You can look at the ADO.NET architecture again:

You can use some .NET programming languages such as C# or VB to write an application; you can also use some DBMSs, such as Access or SQL Server, to create a database. If you have an application and a database, and you want to connect them together then Data Provider classes are a solution.

Data Provider

The Microsoft .NET framework contains the following providers:

• OleDb: you can use this provider to access SQL Server 6.5 or earlier, SyBase, DB2/400, and Microsoft Access.
• Odbc: you can this provider to access to SQL Server 7 or later, SyBase, DB2/400, and Microsoft Access.
• SQL Server: you can this provider to access to SQL Server 7 or later, SyBase, DB2/400, and Microsoft Access.

You can also use third-party providers, such as DB2 and MySQL, which can be downloaded from the web.

The provider classes are subclasses that are labeled by the provider, which replace the Db prefix of base classes with a provider prefix such as SQL, Odbc, or OleDb. In this post, I only mention the SQL Server provider and the following sections will describe these classes in detail.

A Simple Application

Before explaining primary provider classes, I want to introduce a simple application (using VB), which is called ADOApp, and a database (using SQL Express), which is called ngocminhADO.

ngocminhADO database contains a Genre table and a Review table. The relationship among these tables can be shown here:

The schema of a Genre table:

The schema of a Review table:

Data sample for a Genre table:

Data sample for a Review table:

ADOApp has a form that looks like this:

The above form has two DataGridViews. The above DataGridView, which has a Name’s value of genreData, is used to display data from the Genre table. The below DataGridView, which has a Name’s value of reviewData, is used to display data from the Review table.

DbConnection Class

Before operating with data from the database, the application needs to open a connection object. DbConnection class is an abstract class from which the provider inherits to create provider-specific classes. If you are using a SQL Server provider, you will have classes and interfaces as follows:

You need a valid connection string to create a connection. The connection string is created by assigning a string that contains key = value pairs separated by a semicolon to the ConnectionString property of the DbConnection object. The following code sample shows how first to create the connection and then assign the connection string:

Dim connection = new SqlConnection()
connection.ConnectionString =_
"Data Source=.\SQLEXPRESS; Initial catalog = ngocminhADO;Integrated Security=True ;"

The first part of connection string (Data Source) is the name the database server - in my code, I am using SQL Express so I have set the .\SQLEXPRESS   value to Data Source. The second part of connection string (Initial catalog) is the name of the database – in my code it is ngocminhADO. The last part of connection string (IntegratedSecurity) is set to the True value to use a secure connection. A long list that describes the parts of the connection string in detail can be found at the MSDN library.

With a valid connection string, you can open a connection, execute commands, and close the connection if you finish your work. The following code sample shows how to open and close a connection:

connection.Open();
'Do lots of cool work here
connection.Close();

You can store ConnectionString properties in the application configuration file, so the connection strings can be changed without requiring a recompile of the application. You can open this file by choosing the App.config file in the Solution Explorer window, which looks like the below figure:

The content of this file can look like the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,.5" />
</startup>
</configuration>

You place the <connectionStrings> element and its child element, the <add> element, into the <configuration> root element. The code sample looks like the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,.5" />
</startup>
<connectionStrings>
<add name="db" connectionString= 
     "Data Source=.\SQLEXPRESS; Initial catalog = ngocminhADO;
      Integrated Security = True ;" />
</connectionStrings>
</configuration>

I have used the <add> element to add a new connection string setting called db”.

The connection strings can be accessed in the code by using the static ConnectionStrings collection on the ConfigurationManager class as shown in the following code sample:

Dim nw = ConfigurationManager.ConnectionStrings("db")
'name = "db"
Dim name = nw.Name
'provider = "System.Data.SqlClient"
Dim provider = nw.ProviderName
Dim cnString = nw.ConnectionString
MessageBox.Show("From App.Config: " & cnString)

You should also notice that, before using the static ConnectionStrings collection on the ConfigurationManager class, you must refer to System.Configuration.dll by choosing PROJECT > Add Reference…> Assemblies > Framework > System.Configuration as shown in the following figure:

When a connection is open, you can execute commands to the database with the DbCommand object.

DbCommand Class

You use the DbCommand object to send a SQL command to the database. Like DbConnection class, if you are using the SQL Server provider, you will have classes and interfaces as follows:

The DbCommand object requires a valid open connection to issue the command to the database. A DbConnection object can be passed into the DbCommand object 's constructor or attached to the DbCommand object's Connection property after DbCommand is created or using the CreateCommand method on the DbConnection object. The following code sample will demonstrate how a DbCommand object is created to execute a SQL command to select all data from the Genre table of the database:

Dim nw = ConfigurationManager.ConnectionStrings("db")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
'****You can use one of the following three ways*******************  
'the DbConnection object can be passed into the DbCommand object 's constructor
Dim command = New SqlCommand(“Select * from Genre”, connection)
'or attached to the DbCommand object’s Connection property after DbCommand is created
Dim command = New SqlCommand()
command.CommandText = “Select * from Genre”
command.Connection = connection
'using CreateCommand method on the DbConnection object
Dim command = connection.CreateCommand()
command.CommandType = CommandType.Text
command.CommandText = " Select * from Genre "

DbParameter Objects

The DbParameter object is used when you want to pass parameter values to SQL command (stored procedure or text) to execute. You can create a DbParameter object by using the  CreateParameter()  and the  Parameter.Add() method on the DbCommand object. The following code sample will demonstrate how to display all the rows, which have GenreId = 101, in the Review table by using DbParameter:

Dim nw = ConfigurationManager.ConnectionStrings("db")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = connection.CreateCommand()
connection.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = "select * from Review where GenreId=@GenreId"
Dim parm = cmd.CreateParameter()
parm.ParameterName = "@GenreId"
parm.Value = "101"
cmd.Parameters.Add(parm)

ExecuteNonQuery() Method

The  ExecuteNonQuery() method returns an integer that represents the number of rows affected by the operation. The following code sample will demonstrate how to use the  ExecuteNonQuery() method:

' updating value of the Title column of the row whose ID is 3 in the Review table
cmd.CommandType = CommandType.Text
cmd.CommandText = "UPDATE Review SET WHERE ID = 3"
' return the number of rows that were updated
Dim count As Integer
count = cmd.ExecuteNonQuery()
MsgBox(count.ToString) ' count = 1

ExecuteScalar() Method

The  ExecuteScalar() method returns a result that consists of a single row with a single column. The following code sample will demonstrate how to use the  ExecuteScalar ()  method:

cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT COUNT(*) FROM Review"
Dim count As Integer
count = cmd.ExecuteScalar()
connection.Close()
MsgBox(count.ToString) ' count = 3

DbDataReader Class

So far, you have opened a connection by using the DbConnection object, you have also executed SQL commands by using the DbCommand object, and you want to retrieve data from the database. A DbDataReader object provides a high-performance method of retrieving data from the database. If you are using a SQL Server provider, you will have classes and interfaces as follows:

You use the  ExecuteReader() method on the DbCommand object to return a DbDataReader instance and you use the  Read() method on the DbDataReader object to retrieve data. The following code sample will demonstrate how to use the DbDataReader object:

Dim cmd = connection.CreateCommand()
connection.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT ID, Title FROM Review"
Dim rdr = cmd.ExecuteReader()
While (rdr.Read())
    MessageBox.Show(rdr("ID") & ": " & rdr("Title"))
End While
connection.Close()

You can also use the  Load() method to retrieve data as follows:

Dim cmd = connection.CreateCommand()
connection.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT ID, Title FROM Review"
Dim rdr = cmd.ExecuteReader()
Dim subReview As New DataTable()
subReview.Load(rdr, LoadOption.Upsert)
connection.Close()
reviewData.DataSource = subReview

The second parameter of the Load method is a LoadOption enumeration member. The LoadOption enumeration members are described as follows:

DbDataAdapter Class

You use the DbDataAdapter object to retrieve and update data between a data table and a database. If you are using SQL Server provider, you will have classes and interfaces as follows:

The DbDataAdpter object has a SelectCommand property you use when retrieving the data. DbDataAdpter also has InsertCommand, UpdateCommand, and DeleteCommand properties, which might contain DbCommand objects. You use these properties if you want to save DataTable changes back to the database.

The DbDataAdapter object has the Fill method, which is used to move data from the database to the DataTable object you pass into this method, and the Update method, which is used to save changes to the database.

The following code sample will demonstrate how to use the DbDataAdapter, properties, and methods:

Dim nw = ConfigurationManager.ConnectionStrings("db")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = connection.CreateCommand()
connection.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM Review"
Dim da As New SqlDataAdapter()
Dim daset As New DataSet
da.SelectCommand = cmd
Dim bldr As New SqlCommandBuilder(da)
da.Fill(daset, "Review")
Dim newTable = daset.Tables("Review")
'Add new row
newTable.Rows.Add(4, "Title 04", "Summary 04", 100)
'send changes to database
da.Update(daset, "Review")
reviewData.DataSource = daset
reviewData.DataMember = "Review"

Conclusion

In this article, I have introduced primary Provider Classes in ADO.NET. I hope you find my article helpful!