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, you’ll create a simple Windows Form, with a single list box
in it called lbCustomers
. You’ll populate
this list box 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";
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.FillDataSet(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 list box:
foreach (DataRow dataRow in dataTable.Rows) { lbCustomers.Items.Add( dataRow["CompanyName"] + " (" + dataRow["ContactName"] + ")" ); }
The list box 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 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 dbstring connectionString =
"server=myServer; uid=sa; pwd=; 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"] + ")" );
}
} public override void Dispose( ) { base.Dispose( ); components.Dispose( ); } 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 list box, as shown in Figure 14-1.
The eight lines of code accomplish the following tasks:
Create the string for the connection:
string connectionString = "server=myServer; uid=sa; pwd=; 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"] + ")" ); }
3.22.74.160