- .NET Architecture
- Sample Database
- Connected Data Access
- Disconnected Data Sets
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Connected Data Access
Although much of the design of ADO.NET is geared toward supporting disconnected database applications, there is also support for the connected model. Because connected applications are more familiar, we illustrate database-independent ADO.NET programming with the connected scenario. The code fragments shown below are from the ConnectedSql program, which illustrates performing various database operations on the SimpleBank database. We will look at the complete program and a sample run a little later.
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 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
Our sample program begins by connecting to the database, and it then enters a command loop (see Listing 1):
Listing 1—Connecting to a SqlServer Data Provider
Module ConnectedSql
Private connStr As String = _
"server=localhost;uid=sa;pwd=;database=SimpleBank"
Private conn As IDbConnection
Dim sqlConn As New SqlConnection()
Sub Main()
OpenSql()
CommandLoop()
End Sub
Private Sub OpenSql()
conn = sqlConn
conn.ConnectionString = connStr
Console.WriteLine( _
"Using SQL Server to access SimpleBank")
Console.WriteLine( _
"Database state: " & conn.State.ToString())
conn.Open()
Console.WriteLine(_
"Database state: " & conn.State.ToString())
End Sub
When you run the program, here is how it starts out:
Using SQL Server to access SimpleBank Database state: Closed Database state: Open Enter command, quit to exit >
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. The program illustrates the ConnectionString and State properties of the connection interface, and also the Open method. The key to the relative database independence of this code is using the connection interface IDbConnection rather than the class SqlConnection.
Using Commands
After we have opened a connection to a data source, we can create a command object that will execute a query against a data source. Depending on our data source, we will create either a SqlCommand object or an OleDbCommand object. In either case, we will initialize an interface reference of type IDbCommand, which will be used in the rest of our code—again promoting relative independence from the data source.
The code fragments shown below are from the ConnectedSql program, which illustrates performing various database operations on the SimpleBank database. We will look at the complete program and a sample run a little later.
Creating a Command Object
Listing 2 illustrates creating a command object and returning an IDbCommand interface reference.
Listing 2—Creating a Command Object
Private Function CreateCommand(ByVal query As String) _ As IDbCommand Return New SqlCommand(query, sqlConn) End Function
ExecuteNonQuery
Listing 3 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:
Listing 3—Using ExecuteNonQuery
Private Sub RemoveAccount(ByVal id As Integer)
Dim query As String = _
"delete from Account where AccountId = " & id
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 done with the data reader, you should explicitly close it. Any output parameters or return values of the command object will not be 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 it can be omitted if desired.
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, and otherwise returns false.
Listing 4 is an illustration of code using a data reader to display results of a SELECT query:
Listing 4—Using DataReader
Private Sub ShowList()
Dim query As String = "select * from Account"
Dim command As IDbCommand = CreateCommand(query)
Dim reader As IDataReader = command.ExecuteReader()
While reader.Read()
Console.WriteLine("{0} {1,-10} {2:C}", _
reader("AccountId"), reader("Owner"), _
reader("Balance"))
End While
reader.Close()
End Sub
Sample Database Application Using a Connected Scenario
Our sample application opens up 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 SimpleBank database (ConnectedSql), and one for the Access version (ConnectedJet).
We have examined fragments of the SQL Server version. Listing 5 is the complete Access version (except for the command loop). The specific places where you have to change code to customize for the OleDb data provider are shown in bold:
Listing 5—Database Application Using a Connected Scenario (Access version)
' ConnectedJet.vb
Imports System
Imports System.Data
Imports System.Data.OleDb
Module ConnectedJet
Private connStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\OI\Databases\SimpleBank.mdb"
Private conn As IDbConnection
Dim jetConn As New OleDbConnection()
Sub Main()
OpenJet()
CommandLoop()
End Sub
Private Sub OpenJet()
conn = jetConn
conn.ConnectionString = connStr
Console.WriteLine("Using Access DB SimpleBank.mdb")
Console.WriteLine("Database state: " & _
conn.State.ToString())
conn.Open()
Console.WriteLine("Database state: " & _
conn.State.ToString())
End Sub
Private Sub CommandLoop()
...
Private Sub ShowList()
Dim query As String = "select * from Account"
Dim command As IDbCommand = CreateCommand(query)
Dim reader As IDataReader = command.ExecuteReader()
While reader.Read()
Console.WriteLine("{0} {1,-10} {2:C}", _
reader("AccountId"), reader("Owner"), _
reader("Balance"))
End While
reader.Close()
End Sub
Private Sub AddAccount(ByVal bal As Decimal, _
ByVal owner As String, ByVal id As Integer)
Dim query As String = "insert into Account values(" _
& id & ", '" & owner & "', ' ', " & bal & ")"
Dim command As IDbCommand = CreateCommand(query)
Dim numrow As Integer = command.ExecuteNonQuery()
Console.WriteLine("{0} rows updated", numrow)
End Sub
Private Sub RemoveAccount(ByVal id As Integer)
Dim query As String = _
"delete from Account where AccountId = " & id
Dim command As IDbCommand = CreateCommand(query)
Dim numrow As Integer = command.ExecuteNonQuery()
Console.WriteLine("{0} rows updated", numrow)
End Sub
Private Sub ChangeAccount(ByVal id As Integer, _
ByVal owner As String)
Dim query As String = _
"update Account set Owner = '" _
& owner & "' where AccountId = " & id
Dim command As IDbCommand = CreateCommand(query)
Dim numrow As Integer = command.ExecuteNonQuery()
Console.WriteLine("{0} rows updated", numrow)
End Sub
Private Sub ClearAccounts()
Dim query As String = "delete from Account"
Dim command As IDbCommand = CreateCommand(query)
Dim numrow As Integer = command.ExecuteNonQuery()
Console.WriteLine("{0} rows updated", numrow)
End Sub
Private Function CreateCommand(ByVal query As String) _
As IDbCommand
Return New OleDbCommand(query, jetConn)
End Function
End Module