SqlDataAdapter
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlCommand selectCMD = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
= 30;
SqlDataAdapter custDA = new SqlDataAdapter();
= selectCMD;//Set parameters for SqlDataAdapter through SqlCommand, or you can use the select statement directly
();
DataSet custDS = new DataSet();
(custDS, "Customers");
();
Multiple result sets
If DataAdapter encounters multiple result sets, it creates multiple tables in the DataSet. These tables are provided with an incremental default name TableN to indicate that "Table" of Table0 is the first table name. If the table name is passed to the Fill method as a parameter, the incrementing default name TableNameN is provided to these tables, which start with "TableName" representing TableName0.
Populate DataSets from multiple DataAdapters
You can use any number of DataAdapters with a DataSet. Each DataAdapter can be used to populate one or more DataTable objects and parse updates back to the relevant data source. DataRelation and Constraint objects can be added locally to the DataSet, so that you can associate data from multiple different data sources. For example, a DataSet can contain data from a Microsoft SQL Server database, an IBM DB2 database exposed through OLE DB, and a data source that streams XML. One or more DataAdapter objects can handle communication with each data source.
The following code example populates the customer list from the Northwind database on Microsoft SQL Server 2000 and the order list from the Northwind database stored in Microsoft? Access 2000. The filled table is associated with DataRelation and the customer list will then be displayed with the corresponding customer's order. For more information about DataRelation objects, see Adding inter-table relationships and navigating inter-table relationships.
SqlConnection custConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");
SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", custConn);
OleDbConnection orderConn = new OleDbConnection("Provider=.4.0;" + "Data Source=c:\\Program Files\\Microsoft Office\\Office\\Samples\\;");
OleDbDataAdapter orderDA = new OleDbDataAdapter("SELECT * FROM Orders", orderConn);
();
();
DataSet custDS = new DataSet();
(custDS, "Customers");
(custDS, "Orders");
();
();
DataRelation custOrderRel =
("CustOrders",["Customers"].Columns["CustomerID"], ["Orders"].Columns["CustomerID"]);
foreach (DataRow pRow in ["Customers"].Rows)
{
(pRow["CustomerID"]);
foreach (DataRow cRow in (custOrderRel))
("\t" + cRow["OrderID"]);
}
SQL Server Decimal Type
DataSet uses the .NET Framework data type to store data. For most applications, these types provide a convenient representation of data source information. However, this representation can cause problems when the data type in the data source is SQL Server decimal. The .NET Framework decimal data type allows up to 28 significant bits, while the SQL Server decimal data type allows 38 significant bits. If the SqlDataAdapter determines that the SQL Server decimal field has an accuracy of more than 28 characters during the Fill operation, the current line will not be added to the DataTable. The FillError event will occur, which allows you to determine if a loss of accuracy will occur and respond appropriately. For more information about the FillError event, see Using DataAdapter Events. To get the SQL Server decimal value, you can also use the SqlDataReader object and call the GetSqlDecimal method.
Use SqlCommand during Update process to change DataSet records
The following example uses the derived class OleDbDataAdapter to update the data source. This example assumes that you have created an OleDbDataAdapter and a DataSet.
The following example uses the derived class OleDbDataAdapter to update the data source. This example assumes that you have created an OleDbDataAdapter and a DataSet.
public DataSet CreateCmdsAndUpdate(DataSet myDataSet,string myConnection,string mySelectQuery,string myTableName)
{
OleDbConnection myConn = new OleDbConnection(myConnection);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
= new OleDbCommand(mySelectQuery, myConn);
OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdapter);
();
DataSet custDS = new DataSet();
(custDS);
//code to modify data in dataset here
(custDS, myTableName);
();
return custDS;
}
The following example will create a SqlDataAdapter and set the SelectCommand and InsertCommand properties. Assume that a SqlConnection object has been created.
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd;
// Create the SelectCommand.
cmd = new SqlCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", conn);
("@Country", , 15);
("@City", , 15);
= cmd;
// Create the InsertCommand.
cmd = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (@CustomerID, @CompanyName)", conn);
("@CustomerID", , 5, "CustomerID");
("@CompanyName", , 40, "CompanyName");
= cmd;
return da;
}
The following example creates a SqlDataAdapter and sets the SelectCommand and DeleteCommand properties. Assume that a SqlConnection object has been created.
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd;
SqlParameter parm;
// Create the SelectCommand.
cmd = new SqlCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", conn);
("@Country", , 15);
("@City", , 15);
= cmd;
// Create the DeleteCommand.
cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", conn);
parm = ("@CustomerID", , 5, "CustomerID");
= ;
= cmd;
return da;
}
The following example will create a SqlDataAdapter and set the SelectCommand and UpdateCommand properties. Assume that a SqlConnection object has been created.
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd;
SqlParameter parm;
// Create the SelectCommand.
cmd = new SqlCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", conn);
("@Country", , 15);
("@City", , 15);
= cmd;
// Create the UpdateCommand.
cmd = new SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", conn);
("@CustomerID", , 5, "CustomerID");
("@CompanyName", , 40, "CompanyName");
parm = ("@oldCustomerID", , 5, "CustomerID");
= ;
= cmd;
return da;
}
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlCommand selectCMD = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
= 30;
SqlDataAdapter custDA = new SqlDataAdapter();
= selectCMD;//Set parameters for SqlDataAdapter through SqlCommand, or you can use the select statement directly
();
DataSet custDS = new DataSet();
(custDS, "Customers");
();
Multiple result sets
If DataAdapter encounters multiple result sets, it creates multiple tables in the DataSet. These tables are provided with an incremental default name TableN to indicate that "Table" of Table0 is the first table name. If the table name is passed to the Fill method as a parameter, the incrementing default name TableNameN is provided to these tables, which start with "TableName" representing TableName0.
Populate DataSets from multiple DataAdapters
You can use any number of DataAdapters with a DataSet. Each DataAdapter can be used to populate one or more DataTable objects and parse updates back to the relevant data source. DataRelation and Constraint objects can be added locally to the DataSet, so that you can associate data from multiple different data sources. For example, a DataSet can contain data from a Microsoft SQL Server database, an IBM DB2 database exposed through OLE DB, and a data source that streams XML. One or more DataAdapter objects can handle communication with each data source.
The following code example populates the customer list from the Northwind database on Microsoft SQL Server 2000 and the order list from the Northwind database stored in Microsoft? Access 2000. The filled table is associated with DataRelation and the customer list will then be displayed with the corresponding customer's order. For more information about DataRelation objects, see Adding inter-table relationships and navigating inter-table relationships.
SqlConnection custConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");
SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", custConn);
OleDbConnection orderConn = new OleDbConnection("Provider=.4.0;" + "Data Source=c:\\Program Files\\Microsoft Office\\Office\\Samples\\;");
OleDbDataAdapter orderDA = new OleDbDataAdapter("SELECT * FROM Orders", orderConn);
();
();
DataSet custDS = new DataSet();
(custDS, "Customers");
(custDS, "Orders");
();
();
DataRelation custOrderRel =
("CustOrders",["Customers"].Columns["CustomerID"], ["Orders"].Columns["CustomerID"]);
foreach (DataRow pRow in ["Customers"].Rows)
{
(pRow["CustomerID"]);
foreach (DataRow cRow in (custOrderRel))
("\t" + cRow["OrderID"]);
}
SQL Server Decimal Type
DataSet uses the .NET Framework data type to store data. For most applications, these types provide a convenient representation of data source information. However, this representation can cause problems when the data type in the data source is SQL Server decimal. The .NET Framework decimal data type allows up to 28 significant bits, while the SQL Server decimal data type allows 38 significant bits. If the SqlDataAdapter determines that the SQL Server decimal field has an accuracy of more than 28 characters during the Fill operation, the current line will not be added to the DataTable. The FillError event will occur, which allows you to determine if a loss of accuracy will occur and respond appropriately. For more information about the FillError event, see Using DataAdapter Events. To get the SQL Server decimal value, you can also use the SqlDataReader object and call the GetSqlDecimal method.
Use SqlCommand during Update process to change DataSet records
The following example uses the derived class OleDbDataAdapter to update the data source. This example assumes that you have created an OleDbDataAdapter and a DataSet.
The following example uses the derived class OleDbDataAdapter to update the data source. This example assumes that you have created an OleDbDataAdapter and a DataSet.
public DataSet CreateCmdsAndUpdate(DataSet myDataSet,string myConnection,string mySelectQuery,string myTableName)
{
OleDbConnection myConn = new OleDbConnection(myConnection);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
= new OleDbCommand(mySelectQuery, myConn);
OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdapter);
();
DataSet custDS = new DataSet();
(custDS);
//code to modify data in dataset here
(custDS, myTableName);
();
return custDS;
}
The following example will create a SqlDataAdapter and set the SelectCommand and InsertCommand properties. Assume that a SqlConnection object has been created.
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd;
// Create the SelectCommand.
cmd = new SqlCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", conn);
("@Country", , 15);
("@City", , 15);
= cmd;
// Create the InsertCommand.
cmd = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (@CustomerID, @CompanyName)", conn);
("@CustomerID", , 5, "CustomerID");
("@CompanyName", , 40, "CompanyName");
= cmd;
return da;
}
The following example creates a SqlDataAdapter and sets the SelectCommand and DeleteCommand properties. Assume that a SqlConnection object has been created.
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd;
SqlParameter parm;
// Create the SelectCommand.
cmd = new SqlCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", conn);
("@Country", , 15);
("@City", , 15);
= cmd;
// Create the DeleteCommand.
cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", conn);
parm = ("@CustomerID", , 5, "CustomerID");
= ;
= cmd;
return da;
}
The following example will create a SqlDataAdapter and set the SelectCommand and UpdateCommand properties. Assume that a SqlConnection object has been created.
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd;
SqlParameter parm;
// Create the SelectCommand.
cmd = new SqlCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", conn);
("@Country", , 15);
("@City", , 15);
= cmd;
// Create the UpdateCommand.
cmd = new SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", conn);
("@CustomerID", , 5, "CustomerID");
("@CompanyName", , 40, "CompanyName");
parm = ("@oldCustomerID", , 5, "CustomerID");
= ;
= cmd;
return da;
}