Asp.net SqlDataAdapter object usage notes

SqlDataAdapter
SqlConnection nwindConn = new SqlConnection ("Data Source = localhost; Integrated Security = SSPI; Initial Catalog = northwind ");
SqlCommand selectCMD = new SqlCommand ("SELECT CustomerID, CompanyName FROM Customers", nwindConn );
SelectCMD. CommandTimeout = 30;
SqlDataAdapter custDA = new SqlDataAdapter ();
CustDA. SelectCommand = selectCMD; // use SqlCommand to set parameters for SqlDataAdapter. You can also use the select statement directly.
NwindConn. Open ();
DataSet custDS = new DataSet ();
CustDA. Fill (custDS, "MERs ");
NwindConn. Close ();
Multiple result sets
If DataAdapter encounters multiple result sets, it creates multiple tables in DataSet. The incremental default name TableN will be provided to these tables, and "Table" indicating Table0 is the first Table name. If you pass the table name as a parameter to the Fill method, the default increasing name TableNameN is provided to these tables. These table names start with "TableName" indicating TableName0.
Fill DataSet from multiple dataadapters
You can use any number of dataadapters with a DataSet. Each DataAdapter can be used to fill in one or more able objects and parse the updates back to the relevant data source. DataRelation and Constraint objects can be locally added to DataSet, so that you can associate data from multiple different data sources. For example, DataSet can contain data from a Microsoft SQL Server database, an IBM DB2 database published through OLE DB, and a data source that processes XML streams. One or more DataAdapter objects can communicate with each data source.
The following code example fills in the customer list from the Northwind database on Microsoft SQL Server 2000, from? In Access 2000, the Northwind database fills the order list. The filled table is associated with DataRelation, and the customer list is displayed with the order of the corresponding customer. For more information about DataRelation objects, see add inter-Table relationships and navigation between tables.
SqlConnection custConn = new SqlConnection ("Data Source = localhost; Integrated Security = SSPI; Initial Catalog = northwind ;");
SqlDataAdapter custDA = new SqlDataAdapter ("SELECT * FROM MERs", custConn );
OleDbConnection orderConn = new OleDbConnection ("Provider = Microsoft. jet. OLEDB.4.0; "+" Data Source = c: \ Program Files \ Microsoft Office \ Samples \ northwind. mdb ;");
OleDbDataAdapter orderDA = new OleDbDataAdapter ("SELECT * FROM Orders", orderConn );
CustConn. Open ();
OrderConn. Open ();
DataSet custDS = new DataSet ();
CustDA. Fill (custDS, "MERs ");
OrderDA. Fill (custDS, "Orders ");
CustConn. Close ();
OrderConn. Close ();
DataRelation custOrderRel =
CustDS. relations. add ("CustOrders", custDS. tables ["MERs"]. columns ["CustomerID"], custDS. tables ["Orders"]. columns ["CustomerID"]);
Foreach (DataRow pRow in custDS. Tables ["MERs"]. Rows)
{
Console. WriteLine (pRow ["mermerid"]);
Foreach (DataRow cRow in pRow. GetChildRows (custOrderRel ))
Console. WriteLine ("\ 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 sources. However, if the data type in the data source is SQL Server decimal, this representation may cause problems .. NET Framework decimal data type can have up to 28 valid bits, while SQL Server decimal data type can have 38 valid bits. If the precision of the SQL Server decimal field is determined to be greater than 28 Characters During the Fill operation, the current row will not be added to the DataTable. The FillError event will occur, which enables you to determine whether precision loss will occur and make appropriate responses. For more information about the FillError event, see use DataAdapter event. To obtain the SQL Server decimal value, you can also use the SqlDataReader object and call the GetSqlDecimal method.
Use SqlCommand during the Update process to change the DataSet record
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 ();
MyDataAdapter. SelectCommand = new OleDbCommand (mySelectQuery, myConn );
OleDbCommandBuilder custCB = new OleDbCommandBuilder (myDataAdapter );
MyConn. Open ();
DataSet custDS = new DataSet ();
MyDataAdapter. Fill (custDS );
// Code to modify data in dataset here
MyDataAdapter. Update (custDS, myTableName );
MyConn. Close ();
Return custDS;
}
The following instance creates a SqlDataAdapter and sets the SelectCommand and InsertCommand attributes. 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 MERs" +
"WHERE Country = @ Country AND City = @ City", conn );
Cmd. Parameters. Add ("@ Country", SqlDbType. NVarChar, 15 );
Cmd. Parameters. Add ("@ City", SqlDbType. NVarChar, 15 );
Da. SelectCommand = cmd;
// Create the InsertCommand.
Cmd = new SqlCommand ("insert into MERs (CustomerID, CompanyName)" +
"VALUES (@ CustomerID, @ CompanyName)", conn );
Cmd. Parameters. Add ("@ CustomerID", SqlDbType. NChar, 5, "CustomerID ");
Cmd. Parameters. Add ("@ CompanyName", SqlDbType. NVarChar, 40, "CompanyName ");
Da. InsertCommand = cmd;
Return da;
}
The following instance creates a SqlDataAdapter and sets the SelectCommand and DeleteCommand attributes. 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 MERs" +
"WHERE Country = @ Country AND City = @ City", conn );
Cmd. Parameters. Add ("@ Country", SqlDbType. NVarChar, 15 );
Cmd. Parameters. Add ("@ City", SqlDbType. NVarChar, 15 );
Da. SelectCommand = cmd;
// Create the DeleteCommand.
Cmd = new SqlCommand ("delete from MERs WHERE CustomerID = @ CustomerID", conn );
Parm = cmd. Parameters. Add ("@ CustomerID", SqlDbType. NChar, 5, "CustomerID ");
Parm. SourceVersion = DataRowVersion. Original;
Da. DeleteCommand = cmd;
Return da;
}
The following instance creates a SqlDataAdapter and sets the SelectCommand and UpdateCommand attributes. 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 MERs" +
"WHERE Country = @ Country AND City = @ City", conn );
Cmd. Parameters. Add ("@ Country", SqlDbType. NVarChar, 15 );
Cmd. Parameters. Add ("@ City", SqlDbType. NVarChar, 15 );
Da. SelectCommand = cmd;
// Create the UpdateCommand.
Cmd = new SqlCommand ("UPDATE MERs SET CustomerID = @ CustomerID, CompanyName = @ CompanyName" +
"WHERE CustomerID = @ oldCustomerID", conn );
Cmd. Parameters. Add ("@ CustomerID", SqlDbType. NChar, 5, "CustomerID ");
Cmd. Parameters. Add ("@ CompanyName", SqlDbType. NVarChar, 40, "CompanyName ");
Parm = cmd. Parameters. Add ("@ oldCustomerID", SqlDbType. NChar, 5, "CustomerID ");
Parm. SourceVersion = DataRowVersion. Original;
Da. UpdateCommand = cmd;
Return da;
}