ADO.NET OleDb Command Object in VB.NET
In this article I will explain Creating an OleDb Command Object in ADO.NET.
There are a number of ways to construct a command object. You can pass the command a SQL query string. You can pass it a string and a connection, or you can pass it a string, a connection, and a transaction. The following code shows you three different ways to create a command object. This code constructs a connection and SQL string:
' connection and SQL strings
Dim ConnectionString As String = " provider=Micosoft.Jet.OLEDB.4.0;" & "Data source= c:\ Northwind.mdb"
Dim SQL As String = "SELECT OrderID, CustomerID FROM Orders"
Now create OleDbCommand object using a constructor with no arguments. Later you set OleDbCommand's Connection and CommandText properties to connect to a connection and set SQL statement, which this command will be executing:
Dim cmd As New OleDbCommand()
cmd.Connection = conn
cmd.CommandText = Sql
In the second form, you create an OleDbCommand object by directly passing a SQL query and the OleDbConnection object as the first and second arguments:
' Create command object
OleDb command cmd = New OleDbCommand (SQL, conn)
The third way is to create a command by just passing a SQL query as the argument and setting its Connection property later:
' Create command object
Dim cmd As New OleDbCommand(Sql)
cmd.Connection = conn
Listing 5-30 shows you how to connect to the North Wind Access 2000 database, read all the records from the Order table, and display the first and second column data to the console output. The new things you'll notice in this code are ExcuteReader and OleDbDataReader. An OleDbDataReader is data reader class, and ExecuteReader fills data from a data source to the data reader based on the SQL query. (I'll discuss data reader classes in the next section.)
Listing 5-30. Using OleDbCommand to read data from database
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.OleDb
Namespace ConsoleApplication1
Class Program
Private Shared Sub Main(ByVal args As String())
' Connection and SQL strings
Dim ConnectionString As String = "Provider= Microsoft.Jet.OLEDB. 4.0;" & "Data Source =c:/nothwind.mdb"
Dim SQL As String = "SELECT * FROM Orders"
'Create connection object
Dim conn As New OleDbConnection(ConnectionString)
' create command object
Dim cmd As New OleDbCommand(SQL)
cmd.Connection = conn
' open connection
conn.Open()
' Call command's ExcuteReader
Dim reader As OleDbDataReader = cmd.ExecuteReader()
While reader.Read()
Console.Write("OrderID :" & reader.GetInt32(0).ToString())
Console.Write(" ,")
Console.WriteLine("Customer: " & reader.GetString(1).ToString())
End While
' close reader and connection
reader.Close()
conn.Close()
End Sub
End Class
End Namespace
The output listing 5-30 looks like figure 5-30.
Figure 5-30. Output of Listing 5-30
Conclusion
Hope this article would have helped you in understanding Creating an OleDb Command Object in ADO.NET. See my other articles on the website on ADO.NET.