␡
- A Disconnected Database
- Building a DataSet from Scratch
- Connecting to a Data Source Via the DataAdapter
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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 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 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