| Although much of the design of ADO.NET is geared to supporting disconnected database applications, there is also support for the connected model. Because connected applications are more familiar, we begin our detailed discussion of ADO.NET programming with the connected scenario. Using a Connection The connection class ( OleDbConnection or SqlConnection ) is used to manage the connection to the data source. It has properties for ConnectionString , ConnectionTimeout , and so forth. There are methods for Open , Close , transaction management, and so on. A connection string is used to identify the information the object needs to connect to the database. You can specify the connection string when you construct the connection object, or you may specify it by setting its properties. A connection string contains a series of argument = value statements separated by semicolons. To program in a manner that is independent of the data source, you should obtain an interface reference of type IDbConnection after creating the connection object, and you should program against this interface reference. Connecting to a SQL Server Data Provider Let's begin by writing a small program to connect to the SQL Server database MyAcme that you created earlier in the chapter. If you would like to create this program yourself, follow the instructions. Do your work in the Demos directory for this chapter. If you just want to look at the finished program, examine the project in SqlConnectAcme\Step1 (which connects to the database AcmeCustomer that was created by the setup for this chapter). Follow these steps to create the program yourself: -
Use Visual Studio to create a new VB.NET console application called "SqlConnectAcme." -
Type the code shown in bold. Note that we obtain an interface reference of type IDbConnection so that our code is more independent of the data source.  Imports System.Data.SqlClient Module Module1 Sub Main() Dim connStr As String = _ "server=localhost;uid=sa;pwd=;database=MyAcme" Dim objConn As New SqlConnection() Dim conn As IDbConnection = objConn conn.ConnectionString = connStr Console.WriteLine(_ "Using SQL Server to access MyAcme") Console.WriteLine("Database state: " & _ conn.State.ToString()) conn.Open() Console.WriteLine("Database state: " & _ conn.State.ToString()) conn.Close() Console.WriteLine("Database state: " & _ conn.State.ToString()) End Sub End Module -
Build and run the program. You should get the following output: Using SQL Server to access MyAcme Database state: Closed Database state: Open Database state: Closed This program illustrates the correct connect string for connecting to a SQL Server database. Note the use of the database "localhost." When SQL Server is installed on your system, a SQL Server is created having the name of your computer. You could use either this name or "localhost." If you are on a network and there is a remote SQL Server running, you could connect to that SQL Server by substituting the name of the remote server. If you are running MSDE instead of SQL server, you could use "MMMM\VSDOTNET," where MMMM is the name of your machine. This assumes that you have installed the MSDE that comes with Visual Studio .NET. The program illustrates the ConnectionString and State properties of the connection object and the Open and Close methods. You should close your connections. If the connection is not closed explicitly, the finalizer on the SqlConnection object will eventually get called, and the connection will be closed. Since the garbage collector is not deterministic, there is no way to know when this will happen. So if you do not close your connections, you will use more connections than you need (even with connection pooling), and this could interfere with your applications' scalability. You could also run out of connections. ADO.NET Class Libraries To run a program that uses the ADO.NET classes, you must be sure to set references to the appropriate class libraries. The following libraries should usually be included: -
System.dll -
System.Data.dll -
System.Xml.dll The last one is needed when we are working with datasets; it is not required for the current examples. References to these libraries are set up automatically when you create a console project in Visual Studio. If you create an empty project, you need to specifically add these references. Connecting to an OLE DB Data Provider To connect to an OLE DB data provider instead, you need to change the namespace you are importing and instantiate an object of the OleDbConnection class. You must provide a connection string appropriate to your OLE DB provider. We are going to use the Jet OLE DB provider, which can be used for connecting to an Access database. The program JetConnectAcme\Step1 illustrates connecting to the Access database AcmeCustomer.mdb . Note that database files for this book are installed in the directory C:\OI\NetVb\Chap13\Databases when you install the sample programs. The lines in bold are the only ones that are different from the corresponding SQL Server example. Imports System.Data.OleDb Module Module1 Sub Main() Dim connStr As String = _ "Provider=Microsoft.Jet.OLEDB.4.0;Data" & _ Source=C:\OI\NetVb\Chap13\Databases\AcmeCustomer.mdb" Dim objConn As New OleDbConnection () Dim conn As IDbConnection = objConn conn.ConnectionString = connStr Console.WriteLine(" Using Access DB AcmeCustomer.mdb ") Console.WriteLine("Database state: " & _ conn.State.ToString()) conn.Open() Console.WriteLine("Database state: " & _ conn.State.ToString()) conn.Close() Console.WriteLine("Database state: " & _ conn.State.ToString()) End Sub End Module Using Commands After we have opened a connection to a data source, we can create a command object, which executes a query against a data source. Depending on our data source, we create either a SqlCommand object or an OleDbCommand object. In either case, we initialize an interface reference of type IDbCommand , which is used in the rest of our code, again promoting relative independence from the data source. The code fragments shown below are from the SqlConnectAcme\Step2 program, which illustrates performing various database operations on the AcmeCustomer database. We will look at the complete program and a sample run a little later. Table 13-2 summarizes some of the principle properties and methods of IDbCommand . Table 13-2. Common Properties and Methods of IDbCommand | Property or Method | Description | | CommandText | Text of command to run against the data source | | CommandTimeout | Wait time before terminating command attempt | | CommandType | How CommandText is interpreted (e.g., Text, StoredProcedure) | | Connection | The IDbConnection used by the command | | Parameters | The parameters collection | | Cancel | Cancels the execution of an IDbCommand | | ExecuteReader | Obtains an IDataReader for retrieving data (SELECT) | | ExecuteNonQuery | Executes a SQL command such as INSERT, DELETE, etc. | Creating a Command Object The following code illustrates creating a command object and returning an IDbCommand interface reference. Private Function CreateCommand(ByVal query As String) _ As IDbCommand Return New SqlCommand(query, objConn) End Function ExecutenonQuery The following code illustrates executing a SQL DELETE statement using a command object. We create a query string for the command and obtain a command object for this command. The call to ExecuteNonQuery returns the number of rows that were updated. Private Sub RemoveCustomer(ByVal login As String) Dim query As String = _ "delete from CustomerIds where LoginName = " & _ "'" & login & "'" Dim command As IDbCommand = CreateCommand(query) Dim numrow As Integer = command.ExecuteNonQuery() Console.WriteLine("{0} rows updated", numrow) End Sub Using a Data Reader After we have created a command object, we can call the ExecuteReader method to return an IDataReader . With the data reader, we can obtain a read-only, forward-only stream of data. This method is suitable for reading large amounts of data, because only one row at a time is stored in memory. When you are finished with the data reader, you should explicitly close it. Any output parameters or return values of the command object are not available until after the data reader has been closed. Data readers have an Item property that can be used for accessing the current record. The Item property accepts either an integer (representing a column number) or a string (representing a column name). The Item property is the default property and can be omitted if desired. Data readers have a method IsDBNull , which can be used to check if a column value is NULL. This method takes a column number as a parameter. You can obtain a column number from a column name by the GetOrdinal method of the data reader. The Read method is used to advance the data reader to the next row. When it is created, a data reader is positioned before the first row. You must call Read before accessing any data. Read returns true if there are more rows; otherwise , it returns false. Here is an illustration of code using a data reader to display results of a SELECT query. Private Sub ShowList() Dim query As String = "select * from CustomerIds" Dim command As IDbCommand = CreateCommand(query) Dim reader As IDataReader = command.ExecuteReader() While reader.Read() Dim strHotelCustId As String Dim strAirlineCustId As String If reader.IsDBNull(2) Then strHotelCustId = "NULL" Else strHotelCustId = reader(2) End If If reader.IsDBNull(3) Then strAirlineCustId = "NULL" Else strAirlineCustId = reader(3) End If Console.WriteLine("{0,-20}{1,-20}{2,-8}{3,-8}", _ reader("LoginName"), reader("Password"), _ strHotelCustId, strAirlineCustId) End While reader.Close() End Sub Sample Database Application Using Connected Scenario Our sample application opens a connection, which remains open during the lifetime of the application. Command objects are created to carry out typical database operations, such as retrieving rows from the database, adding rows, deleting rows, and changing rows. There are two versions of the application, one for the SQL Server version of our AcmeCustomer database and one for the Access version. We first look at the SQL Server version and then examine the small amount of code that must be changed for the Access version. Using SQL Server Data Provider The first version of our program uses the SQL Server data provider. The program is provided in the folder SqlConnectAcme\Step2 . Assuming that you have set up the AcmeCustomer database as described earlier in the chapter, you should be able to build and run the program. Alternatively, you can change the connect string and use the MyAcme database you created yourself. The Step 1 version of the program is a short program that you can use to help debug any connection problems you might have. Here is the source code: Imports System.Data.SqlClient Module SqlConnectAcme Dim connStr As String = _ "server=localhost;uid=sa;pwd=;database=AcmeCustomer" Dim objConn As New SqlConnection() Dim conn As IDbConnection = objConn Sub Main() conn.ConnectionString = connStr Console.WriteLine(_ "Using SQL Server to access AcmeCustomer") Console.WriteLine(_ "Database state: " & conn.State.ToString()) conn.Open() Console.WriteLine("Database state: " & _ conn.State.ToString()) ShowList() ClearCustomers() AddCustomer("Tom", "tommy", 101, 201) AddCustomer("Dick", "r2d2", 102, 202) AddCustomer("Harry", "1948", 103, 203) ShowList() RemoveCustomer("Dick") ChangePassword("Harry", "1952") ShowList() End Sub Private Function CreateCommand(ByVal query As String) _ As IDbCommand Return New SqlCommand(query, objConn) End Function Private Sub ClearCustomers() Dim query As String = "delete from CustomerIds" Dim command As IDbCommand = CreateCommand(query) Dim numrow As Integer = command.ExecuteNonQuery() Console.WriteLine("{0} rows updated", numrow) End Sub Private Sub ShowList() Dim query As String = "select * from CustomerIds" Dim command As IDbCommand = CreateCommand(query) Dim reader As IDataReader = command.ExecuteReader() While reader.Read() Dim strHotelCustId As String Dim strAirlineCustId As String = "temp" If reader.IsDBNull(2) Then strHotelCustId = "NULL" Else strHotelCustId = reader(2) End If If reader.IsDBNull(3) Then strAirlineCustId = "NULL" Else strAirlineCustId = reader(3) End If Console.WriteLine("{0,-20}{1,-20}{2,-8}{3,-8}", _ reader("LoginName"), reader("Password"), _ strHotelCustId, strAirlineCustId) End While reader.Close() End Sub Private Sub AddCustomer(ByVal login As String, _ ByVal pwd As String, ByVal hotelid As Integer, _ ByVal airlineid As Integer) Dim query As String = _ "insert into CustomerIds values('" & _ login & "', '" & pwd & "', " & hotelid & _ ", " & airlineid & ")" Dim command As IDbCommand = CreateCommand(query) Dim numrow As Integer = command.ExecuteNonQuery() Console.WriteLine("{0} rows updated", numrow) End Sub Private Sub RemoveCustomer(ByVal login As String) Dim query As String = _ "delete from CustomerIds where LoginName = " & _ "'" & login & "'" Dim command As IDbCommand = CreateCommand(query) Dim numrow As Integer = command.ExecuteNonQuery() Console.WriteLine("{0} rows updated", numrow) End Sub Private Sub ChangePassword(ByVal login As String, _ ByVal pwd As String) Dim query As String = _ "update CustomerIds set Password = '" & pwd & _ "' where LoginName = '" & login & "'" Dim command As IDbCommand = CreateCommand(query) Dim numrow As Integer = command.ExecuteNonQuery() Console.WriteLine("{0} rows updated", numrow) End Sub End Module Here is a sample run: Using SQL Server to access AcmeCustomer Database state: Closed Database state: Open Bullwinkle 1 NULL Rocky 2 NULL 2 rows updated 1 rows updated 1 rows updated 1 rows updated Dick r2d2 102 202 Harry 1948 103 203 Tom tommy 101 201 1 rows updated 1 rows updated Harry 1952 103 203 Tom tommy 101 201 Using OLE DB Data Provider The program JetConnectAcme\Step2 illustrates using the OLE DB data provider for the Jet database engine to talk to an Access database. This program is functionally equivalent to the program SqlConnectAcme\Step2 that we just examined. Here is selected source code. We show in bold all the places where the program was changed: Imports System.Data.OleDb Module JetConnectAcme Dim connStr As String = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\OI\NetVb\Chap13\Databases\AcmeCustomer.mdb" Dim objConn As New OleDbConnection () Dim conn As IDbConnection = objConn Sub Main() conn.ConnectionString = connStr Console.WriteLine(" Using Access DB AcmeCustomer.mdb ") ... End Sub Private Function CreateCommand(_ ByVal query As String) As IDbCommand Return New OleDbCommand (query, objConn) End Function End Module  | 
