14.3 Getting Started with ADO.NET

Enough theory! Let's write some code and see how this works. Working with ADO.NET can be complex, but for many queries, the model is surprisingly simple.

In this example, create a simple Windows Form, with a single listbox in it called lbCustomers. Populate this listbox with bits of information from the Customers table in the Northwind database.

Begin by creating a DataAdapter object:

SqlDataAdapter DataAdapter = 
new SqlDataAdapter(
commandString, connectionString);

The two parameters are commandString and connectionString. The commandString is the SQL statement that will generate the data you want in your DataSet:

string commandString = 
    "Select CompanyName, ContactName from Customers";

The connectionString is whatever string is needed to connect to the database. In my case, I'm running SQL Server on my development machine where I have left the system administrator (sa) password blank (I know, I know, not a good idea. I'll fix it by the time this book is released. Honest.):

string connectionString = 
    "server=localhost; uid=sa; pwd=; database=northwind";

If you do not have SQL Server installed, select Samples and Quickstart Tutorials from the Microsoft .NET Framework SDK program group. A web page appears, giving you the option to install the .NET Framework Samples Database, which includes an installation of SQL Server. After you install the Samples Database, set up the QuickStarts (this will create the Northwind sample database). To use this database, you need this connection string:

"server=(local)\\NetSDK; Trusted_Connection=yes; database=northwind"

With the DataAdapter in hand, you're ready to create the DataSet and fill it with the data that you obtain from the SQL select statement:

DataSet DataSet = new DataSet( );
DataAdapter.Fill(DataSet,"Customers");

That's it. You now have a DataSet, and you can query, manipulate, and otherwise manage the data. The DataSet has a collection of tables; you care only about the first one because you've retrieved only a single record:

DataTable dataTable = DataSet.Tables[0];

You can extract the rows you've retrieved with the SQL statement and add the data to the listbox:

foreach (DataRow dataRow in dataTable.Rows)
{
    lbCustomers.Items.Add(
        dataRow["CompanyName"] + 
        " (" + dataRow["ContactName"] + ")" );
}

The listbox is filled with the company name and contact name from the table in the database, according to the SQL statement we passed in. Example 14-1 contains the complete source code for this example.

Example 14-1. Working with ADO.NET

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace ProgrammingCSharpWinForm
{

   public class ADOForm1 : System.Windows.Forms.Form
   {
      private System.ComponentModel.Container components;
      private System.Windows.Forms.ListBox lbCustomers;

      public ADOForm1( )
      {
         InitializeComponent( );

         // connect to my local server, northwind db
         
         string connectionString = "server=(local)\\NetSDK;" +
         "Trusted_Connection=yes; database=northwind";

         // get records from the customers table
         string commandString = 
         "Select CompanyName, ContactName from Customers";

         // create the data set command object 
         // and the DataSet
         SqlDataAdapter DataAdapter = 
         new SqlDataAdapter(
         commandString, connectionString);

         DataSet DataSet = new DataSet( );

         // fill the data set object
         DataAdapter.Fill(DataSet,"Customers");

         // Get the one table from the DataSet
         DataTable dataTable = DataSet.Tables[0];
            
         // for each row in the table, display the info
         foreach (DataRow dataRow in dataTable.Rows)
         {
            lbCustomers.Items.Add(
               dataRow["CompanyName"] + 
               " (" + dataRow["ContactName"] + ")" );
         }


      }

      protected override void Dispose(bool disposing)
      {
         if (disposing)
         {
             if (components != null)
             {
                 components.Dispose( );
             }
         }
         base.Dispose(disposing);
      }

      private void InitializeComponent( )
      {
         this.components = 
            new System.ComponentModel.Container ( );
         this.lbCustomers = new System.Windows.Forms.ListBox ( );
         lbCustomers.Location = new System.Drawing.Point (48, 24);
         lbCustomers.Size = new System.Drawing.Size (368, 160);
         lbCustomers.TabIndex = 0;
         this.Text = "ADOFrm1";
         this.AutoScaleBaseSize = new System.Drawing.Size (5, 13);
         this.ClientSize = new System.Drawing.Size (464, 273);
         this.Controls.Add (this.lbCustomers);
      }

 
      public static void Main(string[] args) 
      {
         Application.Run(new ADOForm1( ));
      }
   }
}

With just a few lines of code, you have extracted a set of data from the database and displayed it in the listbox, as shown in Figure 14-1.

Figure 14-1. Output from Example 14-1

The eight lines of code accomplish the following tasks:

• Create the string for the connection:

  string connectionString = "server=(local)\\NetSDK;" +
  "Trusted_Connection=yes; database=northwind";

• Create the string for the select statement:

  string commandString = 
  "Select CompanyName, ContactName from Customers";

• Create the DataAdapter and pass in the selection and connection strings:

  SqlDataAdapter DataAdapter = 
  new SqlDataAdapter(
  commandString, connectionString);

• Create a new DataSet object:

  DataSet DataSet = new DataSet( );

• Fill the DataSet from the Customers table using the DataAdapter:

  DataAdapter.Fill(DataSet,"Customers");

• Extract the DataTable from the DataSet:

  DataTable dataTable = DataSet.Tables[0];

• Use the DataTable to fill the list box:

  foreach (DataRow dataRow in dataTable.Rows)
  {
     lbCustomers.Items.Add(
        dataRow["CompanyName"] + 
        " (" + dataRow["ContactName"] + ")" );
  }