Creating an OleDb Command Object in ADO.NET

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

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
            string ConnectionString = @" provider=Micosoft.Jet.OLEDB.4.0;" + "Data source= c:\\ Northwind.mdb";
            string SQL = "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:

            OleDbCommand cmd = 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
            OleDbCommand cmd = 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

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connection and SQL strings
            string ConnectionString = @"Provider= Microsoft.Jet.OLEDB. 4.0;" + "Data Source =c:/nothwind.mdb";
            string SQL = "SELECT * FROM Orders";
 
            //Create connection object
            OleDbConnection conn = new OleDbConnection(ConnectionString);
            // create command object
            OleDbCommand cmd = new OleDbCommand(SQL);
            cmd.Connection = conn;

            // open connection
            conn.Open();

            // Call command's ExcuteReader
            OleDbDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Console.Write("OrderID :" + reader.GetInt32(0).ToString());
                Console.Write(" ,");
                Console.WriteLine("Customer: " + reader.GetString(1).ToString());
            }

           // close reader and connection
            reader.Close();
            conn.Close();
        }
    }
}

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.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.