Programming C# 3.0, 5th Edition

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, we'll create a console application, and we'll list out bits of information from the Customers table in the Northwind database.

Begin by creating a (SQL Server-specific) 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. Typically, this will be SQL Server Express, which is installed with Visual Studio.

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 table:

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"] ...