ADO.NET OleDb Command Object in VB.NET

In this article I will explain Creating an OleDb Command Object in ADO.NET.
  • 5360

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.jpg

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.

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.