Section 14.3. Getting Started with ADO.NET


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 a trusted connection to the database:

string connectionString =      "server=localhost; trusted_connection=true; database=northwind";

If you don't have SQL Server installed, select Quickstart Tutorials from the Microsoft .NET Framework SDK program group (you must have selected this option when you installed Visual Studio or the .NET Framework SDK). A web page appears, giving you the option to install the Microsoft SQL Server Desktop Engine (MSDE). After you install MSDE, 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 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"] +          " (" + 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
#region Using directives using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Windows.Forms; #endregion namespace WorkingWithADONET {    partial class ADONetForm1 : Form    {       public ADONetForm1( )       {          InitializeComponent( );          // connect to my local server, northwind db          string connectionString = "server=localhost;" +             "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"] + ")" );          }       }    } }

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=localhost;" + "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 select 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 listbox:

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



Programming C#(c) Building. NET Applications with C#
Programming C#: Building .NET Applications with C#
ISBN: 0596006993
EAN: 2147483647
Year: 2003
Pages: 180
Authors: Jesse Liberty

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net