Home > Articles > Programming > Windows Programming

📄 Contents

  1. A Disconnected Database
  2. Building a DataSet from Scratch
  3. Connecting to a Data Source Via the DataAdapter
  4. Conclusion
  • Print
  • + Share This
Like this article? We recommend

Connecting to a Data Source Via the DataAdapter

The examples shown to this point have left out one crucial component: interaction with a data source. The DataAdapter is the class that fulfills this functionality. It's a container for wrapping Command objects that interface with the data source as shown in Figure 3. It has a SelectCommand property for loading via the Fill method, and InsertCommand, UpdateCommand, and DeleteCommand properties for sending updates to the data source via the Update method. The properties all wrap the same type of Command objects we talked about in the previous article on connected operations. The value-add of the DataAdapter is its intelligence in creating and using the RowState and DataRow data versioning facilities to execute the appropriate command.

Figure 3Figure 3 DataAdapter and DataSet with data source.

Listing 7 shows how to build a DataAdapter and use it to fill a DataSet from the SQL Server Northwind database, sending back data modification statements to update it. The code uses SQL statements and the SQL Server .NET Data Provider, so the specific name of the DataAdapter is SqlDataAdapter. The SQL logic locates the top two customers alphabetically from their CustomerID column value in the Northwind Customers table and then pulls all linked orders from the Orders table in the same database. The Fill method normally works with a single table at a time inside the DataSet; notice how we fill the Customers and Orders tables separately. Listing 7 then adds a new row to the Customers table and calls Update on the DataAdapter to push the changes back to the database. The output from Listing 8 shows the result of this effort. Figure 4 shows that the row has made it to the data source. After posing for this picture, the code deletes the row and calls Update on the DataAdapter to delete the row in the data source as well.

Listing 7—Working with DataAdapter and DataSet

using System;
using System.Data;
using System.Data.SqlClient;

namespace DisconnectedOpsDemo
{
  public class UsingDataAdapter
  {

   public static SqlDataAdapter BuildCustomersDataAdapter(SqlConnection conn)
   {
     SqlDataAdapter da = new SqlDataAdapter();

     SqlCommand cmdSelect = new SqlCommand("SELECT TOP 2 CustomerID," +
      " CompanyName, ContactName, ContactTitle, Address, City, Region," +
      " PostalCode, Country, Phone, Fax FROM Customers");
     cmdSelect.Connection = conn;
     da.SelectCommand = cmdSelect;

     SqlCommand cmdInsert = new SqlCommand( @"INSERT INTO Customers(" + 
      "CustomerID, CompanyName, ContactName, ContactTitle, Address, " +
      "City, Region, PostalCode, Country, Phone, Fax) VALUES " + 
      " (@CustomerID, @CompanyName, @ContactName, @ContactTitle, " +
      "@Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)");
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@CustomerID", System.Data.SqlDbType.NVarChar,
      5, "CustomerID"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@CompanyName", System.Data.SqlDbType.NVarChar, 
      40, "CompanyName"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ContactName", System.Data.SqlDbType.NVarChar, 
      30, "ContactName"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ContactTitle", System.Data.SqlDbType.NVarChar,
      30, "ContactTitle"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Address", System.Data.SqlDbType.NVarChar,
      60, "Address"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@City", System.Data.SqlDbType.NVarChar,
      15, "City"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Region", System.Data.SqlDbType.NVarChar,
      15, "Region"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@PostalCode", System.Data.SqlDbType.NVarChar,
      10, "PostalCode"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Country", System.Data.SqlDbType.NVarChar,
      15, "Country"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Phone", System.Data.SqlDbType.NVarChar,
      24, "Phone"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Fax", System.Data.SqlDbType.NVarChar,
      24, "Fax"));
     cmdInsert.Connection = conn;
     da.InsertCommand = cmdInsert;

     SqlCommand cmdUpdate = new SqlCommand(@"UPDATE Customers SET" +
      "CustomerID = @CustomerID, CompanyName = @CompanyName, " +
      "ContactName = @ContactName, ContactTitle = @ContactTitle, " +
      "Address = @Address, City = @City, Region = @Region, " +
      "PostalCode = @PostalCode, Country = @Country, " + 
      "Phone = @Phone, Fax = @Fax WHERE " + 
      "(CustomerID = @Original_CustomerID)");
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@CustomerID", System.Data.SqlDbType.NVarChar,
      5, "CustomerID"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@CompanyName", System.Data.SqlDbType.NVarChar,
      40, "CompanyName"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ContactName", System.Data.SqlDbType.NVarChar,
      30, "ContactName"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ContactTitle", System.Data.SqlDbType.NVarChar,
      30, "ContactTitle"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Address", System.Data.SqlDbType.NVarChar,
      60, "Address"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@City", System.Data.SqlDbType.NVarChar, 
      15, "City"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Region", System.Data.SqlDbType.NVarChar, 
      15, "Region"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@PostalCode", System.Data.SqlDbType.NVarChar, 
      10, "PostalCode"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Country", System.Data.SqlDbType.NVarChar, 
      15, "Country"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Phone", System.Data.SqlDbType.NVarChar, 
      24, "Phone"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Fax", System.Data.SqlDbType.NVarChar, 
      24, "Fax"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Original_CustomerID", System.Data.SqlDbType.NVarChar,
      5, System.Data.ParameterDirection.Input, false, 
      ((System.Byte)(0)), ((System.Byte)(0)), 
      "CustomerID", System.Data.DataRowVersion.Original, null));
     cmdUpdate.Connection = conn;
     da.UpdateCommand = cmdUpdate;

     SqlCommand cmdDelete = new SqlCommand(
      "DELETE FROM Customers WHERE " +
      "(CustomerID = @Original_CustomerID)");
     cmdDelete.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Original_CustomerID", System.Data.SqlDbType.NVarChar, 
      5, System.Data.ParameterDirection.Input, false, 
      ((System.Byte)(0)), ((System.Byte)(0)), "CustomerID", 
      System.Data.DataRowVersion.Original, null));
     cmdDelete.Connection = conn;
     da.DeleteCommand = cmdDelete;

     return da;

   }

   public static SqlDataAdapter BuildOrdersDataAdapter(
     SqlConnection conn)
   {
     SqlDataAdapter da = new SqlDataAdapter();

     SqlCommand cmdSelect = new SqlCommand(
      "SELECT OrderID, CustomerID, EmployeeID, OrderDate, " +
      "RequiredDate, ShippedDate, ShipVia, Freight, " + 
      "ShipName, ShipAddress, ShipCity, ShipRegion, " +
      "ShipPostalCode, ShipCountry FROM Orders WHERE " +
      "CustomerID IN (SELECT TOP 2 CustomerID From Customers)");
     cmdSelect.Connection = conn;
     da.SelectCommand = cmdSelect;

     SqlCommand cmdInsert = new SqlCommand(@"INSERT INTO " +
      "Orders(CustomerID, EmployeeID, OrderDate, " +
      "RequiredDate, ShippedDate, ShipVia, Freight, " +
      "ShipName, ShipAddress, ShipCity, ShipRegion, " + 
      "ShipPostalCode, ShipCountry) VALUES (@CustomerID," +
      "@EmployeeID, @OrderDate, @RequiredDate, @ShippedDate," +
      "@ShipVia, @Freight, @ShipName, @ShipAddress, @ShipCity," +
      "@ShipRegion, @ShipPostalCode, @ShipCountry)");
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@CustomerID", System.Data.SqlDbType.NVarChar, 
      5, "CustomerID"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@EmployeeID", System.Data.SqlDbType.Int, 
      4, "EmployeeID"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@OrderDate", System.Data.SqlDbType.DateTime, 
      8, "OrderDate"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@RequiredDate", System.Data.SqlDbType.DateTime, 
      8, "RequiredDate"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShippedDate", System.Data.SqlDbType.DateTime, 
      8, "ShippedDate"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipVia", System.Data.SqlDbType.Int, 
      4, "ShipVia"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Freight", System.Data.SqlDbType.Money, 
      8, "Freight"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipName", System.Data.SqlDbType.NVarChar, 
      40, "ShipName"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipAddress", System.Data.SqlDbType.NVarChar, 
      60, "ShipAddress"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipCity", System.Data.SqlDbType.NVarChar, 
      15, "ShipCity"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipRegion", System.Data.SqlDbType.NVarChar, 
      15, "ShipRegion"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipPostalCode", System.Data.SqlDbType.NVarChar, 
      10, "ShipPostalCode"));
     cmdInsert.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipCountry", System.Data.SqlDbType.NVarChar, 
      15, "ShipCountry"));
     cmdInsert.Connection = conn;
     da.InsertCommand = cmdInsert;

     SqlCommand cmdUpdate = new SqlCommand(@"UPDATE Orders SET " +
      "CustomerID = @CustomerID, EmployeeID = @EmployeeID, " +
      "OrderDate = @OrderDate, RequiredDate = @RequiredDate, " +
      "ShippedDate = @ShippedDate, ShipVia = @ShipVia, " +
      "Freight = @Freight, ShipName = @ShipName, ShipAddress " +
      "= @ShipAddress, ShipCity = @ShipCity, ShipRegion = " +
      "@ShipRegion, ShipPostalCode = @ShipPostalCode, " +
      "ShipCountry = @ShipCountry WHERE (OrderID = " +
      "@Original_OrderID)");
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@CustomerID", System.Data.SqlDbType.NVarChar, 
      5, "CustomerID"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@EmployeeID", System.Data.SqlDbType.Int, 
      4, "EmployeeID"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@OrderDate", System.Data.SqlDbType.DateTime, 
      8, "OrderDate"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@RequiredDate", System.Data.SqlDbType.DateTime, 
      8, "RequiredDate"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShippedDate", System.Data.SqlDbType.DateTime, 
      8, "ShippedDate"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipVia", System.Data.SqlDbType.Int, 
      4, "ShipVia"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Freight", System.Data.SqlDbType.Money, 
      8, "Freight"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipName", System.Data.SqlDbType.NVarChar, 
      40, "ShipName"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipAddress", System.Data.SqlDbType.NVarChar, 
      60, "ShipAddress"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipCity", System.Data.SqlDbType.NVarChar, 
      15, "ShipCity"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipRegion", System.Data.SqlDbType.NVarChar, 
      15, "ShipRegion"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipPostalCode", System.Data.SqlDbType.NVarChar, 
      10, "ShipPostalCode"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@ShipCountry", System.Data.SqlDbType.NVarChar, 
      15, "ShipCountry"));
     cmdUpdate.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Original_OrderID", System.Data.SqlDbType.Int, 
      4, System.Data.ParameterDirection.Input, false, 
      ((System.Byte)(0)), ((System.Byte)(0)), "OrderID", 
      System.Data.DataRowVersion.Original, null));
     cmdUpdate.Connection = conn;
     da.UpdateCommand = cmdUpdate;

     SqlCommand cmdDelete = new SqlCommand("DELETE FROM Orders" +
      " WHERE (OrderID = @Original_OrderID)");
     cmdDelete.Parameters.Add(
      new System.Data.SqlClient.SqlParameter(
      "@Original_OrderID", System.Data.SqlDbType.Int,
      4, System.Data.ParameterDirection.Input, false,
      ((System.Byte)(0)), ((System.Byte)(0)), 
      "OrderID", System.Data.DataRowVersion.Original, null));
     cmdDelete.Connection = conn;
     da.DeleteCommand = cmdDelete;

     return da;

   }

   public static DataSet LoadDataSet()
   {
     SqlConnection conn =
      new SqlConnection("server=(local);database=Northwind;" + 
      "integrated security=true;");
     conn.Open();

     SqlDataAdapter daCusts = BuildCustomersDataAdapter(conn);
     SqlDataAdapter daOrders = BuildOrdersDataAdapter(conn);

     DataSet ds = new DataSet();
     daCusts.Fill(ds,"Customers");
     daOrders.Fill(ds,"Orders");

     // link the two tables together and autocreate
     // constraints between the two tables
     DataRelation dr = new DataRelation("CustOrderRelation",
      ds.Tables["Customers"].Columns["CustomerID"],
      ds.Tables["Orders"].Columns["CustomerID"],
      true);
     ds.Relations.Add(dr);

     return ds;

   }

   public static void ModifyDataSet(DataSet ds)
   {

     SqlConnection conn =
      new SqlConnection("server=(local);database=Northwind;" + 
      "integrated security=true;");
     conn.Open();

     SqlDataAdapter daCusts = BuildCustomersDataAdapter(conn);
     SqlDataAdapter daOrders = BuildOrdersDataAdapter(conn);

     // Add a Customers row
     DataTable dt = ds.Tables["Customers"];
     DataRow dr = dt.Rows.Add(new object[] { "AAAA", "InformIT",
      "Test User", "Mr.",
      "101 Main Street", "Washington", "DC", "77777", "US",
      "777-5609", "777-5610" });
     daCusts.Update(ds,"Customers");
     ds.AcceptChanges();

     FillingDataSet.DisplayDSData(ds);

     // Delete the same row
     dr.Delete();
     daCusts.Update(ds,"Customers");
     ds.AcceptChanges();

   }

   public static void Main()
   {
     DataSet ds = LoadDataSet();
     ModifyDataSet(ds);

     Console.WriteLine();
   }
  }
}

Figure 4Figure 4 Result of adding a new row to Northwind database Customers table.

Listing 8—Output from Working with DataAdapter and DataSet

Navigating by DataTable

Table: Customers
Row: 0 CustomerID=ALFKI CompanyName=Alfreds Futterkiste ContactName=Maria And
ers ContactTitle=Sales Representative Address=Obere Str. 57 City=Berlin Regi
on= PostalCode=12209 Country=Germany Phone=030-0074321 Fax=030-0076545
Row: 1 CustomerID=ANATR CompanyName=Ana Trujillo Emparedados y helados Contac
tName=Ana Trujillo ContactTitle=Owner Address=Avda. de la Constitución 2222 C
ity=México D.F. Region= PostalCode=05021 Country=Mexico Phone=(5) 555-4729
Fax=(5) 555-3745
Row: 2 CustomerID=AAAA CompanyName=InformIT ContactName=Test User ContactTit
le=Mr. Address=101 Main Street City=Washington Region=DC PostalCode=77777 C
ountry=US Phone=777-5609 Fax=777-5610

Table: Orders
Row: 0 OrderID=10308 CustomerID=ANATR EmployeeID=7 OrderDate=9/18/1996 12:00
:00 AM RequiredDate=10/16/1996 12:00:00 AM ShippedDate=9/24/1996 12:00:00 AM
ShipVia=3 Freight=1.61 ShipName=Ana Trujillo Emparedados y helados ShipAddres
s=Avda. de la Constitución 2222 ShipCity=México D.F. ShipRegion= ShipPostalCo
de=05021 ShipCountry=Mexico
Row: 1 OrderID=10625 CustomerID=ANATR EmployeeID=3 OrderDate=8/8/1997 12:00:
00 AM RequiredDate=9/5/1997 12:00:00 AM ShippedDate=8/14/1997 12:00:00 AM Shi
pVia=1 Freight=43.9 ShipName=Ana Trujillo Emparedados y helados ShipAddress=A
vda. de la Constitución 2222 ShipCity=México D.F. ShipRegion= ShipPostalCode=
05021 ShipCountry=Mexico
Row: 2 OrderID=10643 CustomerID=ALFKI EmployeeID=6 OrderDate=8/25/1997 12:00
:00 AM RequiredDate=9/22/1997 12:00:00 AM ShippedDate=9/2/1997 12:00:00 AM Sh
ipVia=1 Freight=29.46 ShipName=Alfreds Futterkiste ShipAddress=Obere Str. 57
 ShipCity=Berlin ShipRegion= ShipPostalCode=12209 ShipCountry=Germany
Row: 3 OrderID=10692 CustomerID=ALFKI EmployeeID=4 OrderDate=10/3/1997 12:00
:00 AM RequiredDate=10/31/1997 12:00:00 AM ShippedDate=10/13/1997 12:00:00 AM
 ShipVia=2 Freight=61.02 ShipName=Alfred's Futterkiste ShipAddress=Obere Str.
 57 ShipCity=Berlin ShipRegion= ShipPostalCode=12209 ShipCountry=Germany
Row: 4 OrderID=10702 CustomerID=ALFKI EmployeeID=4 OrderDate=10/13/1997 12:0
0:00 AM RequiredDate=11/24/1997 12:00:00 AM ShippedDate=10/21/1997 12:00:00 AM
 ShipVia=1 Freight=23.94 ShipName=Alfred's Futterkiste ShipAddress=Obere Str
. 57 ShipCity=Berlin ShipRegion= ShipPostalCode=12209 ShipCountry=Germany
Row: 5 OrderID=10759 CustomerID=ANATR EmployeeID=3 OrderDate=11/28/1997 12:0
0:00 AM RequiredDate=12/26/1997 12:00:00 AM ShippedDate=12/12/1997 12:00:00 AM
 ShipVia=3 Freight=11.99 ShipName=Ana Trujillo Emparedados y helados ShipAdd
ress=Avda. de la Constitución 2222 ShipCity=México D.F. ShipRegion= ShipPosta
lCode=05021 ShipCountry=Mexico
Row: 6 OrderID=10835 CustomerID=ALFKI EmployeeID=1 OrderDate=1/15/1998 12:00
:00 AM RequiredDate=2/12/1998 12:00:00 AM ShippedDate=1/21/1998 12:00:00 AM S
hipVia=3 Freight=69.53 ShipName=Alfred's Futterkiste ShipAddress=Obere Str. 5
7 ShipCity=Berlin ShipRegion= ShipPostalCode=12209 ShipCountry=Germany
Row: 7 OrderID=10926 CustomerID=ANATR EmployeeID=4 OrderDate=3/4/1998 12:00:
00 AM RequiredDate=4/1/1998 12:00:00 AM ShippedDate=3/11/1998 12:00:00 AM Shi
pVia=3 Freight=39.92 ShipName=Ana Trujillo Emparedados y helados ShipAddress=
Avda. de la Constitución 2222 ShipCity=México D.F. ShipRegion= ShipPostalCode
=05021 ShipCountry=Mexico
Row: 8 OrderID=10952 CustomerID=ALFKI EmployeeID=1 OrderDate=3/16/1998 12:00
:00 AM RequiredDate=4/27/1998 12:00:00 AM ShippedDate=3/24/1998 12:00:00 AM S
hipVia=1 Freight=40.42 ShipName=Alfred's Futterkiste ShipAddress=Obere Str. 5
7 ShipCity=Berlin ShipRegion= ShipPostalCode=12209 ShipCountry=Germany
Row: 9 OrderID=11011 CustomerID=ALFKI EmployeeID=3 OrderDate=4/9/1998 12:00:
00 AM RequiredDate=5/7/1998 12:00:00 AM ShippedDate=4/13/1998 12:00:00 AM Shi
pVia=1 Freight=1.21 ShipName=Alfred's Futterkiste ShipAddress=Obere Str. 57
ShipCity=Berlin ShipRegion= ShipPostalCode=12209 ShipCountry=Germany

Press any key to continue
  • + Share This
  • 🔖 Save To Your Account