ASP.net SqlDataAdapter Object Use notes _ practical skills

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;//Sets the parameters to SqlDataAdapter by SqlCommand, and can//directly use the SELECT statement
Nwindconn.open ();
DataSet custDS = new DataSet ();
Custda.fill (custDS, "Customers");
Nwindconn.close ();
Multiple result Sets
If DataAdapter encounters multiple result sets, it creates multiple tables in the DataSet. The tables are provided with an incremented default name Tablen to indicate that the TABLE0 "table" is the first table name. If the table name is passed as a parameter to the Fill method, the tables are provided with an incremented default name Tablenamen, which starts with the expression TABLENAME0 "tablename".
Populating a DataSet from multiple DataAdapter
You can use any number of DataAdapter with a DataSet. Each DataAdapter can be used to populate one or more DataTable objects and resolve the update back to the relevant data source. DataRelation and Constraint objects can be added locally to the dataset, allowing you to associate data from multiple different data sources. For example, a dataset can contain data from a Microsoft SQL Server database, an IBM DB2 database that is exposed through OLE DB, and a data source for streaming 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, from the store in Microsoft? The Northwind database in Access 2000 populates the list of orders. The populated table is associated with DataRelation, and the customer list is displayed with the corresponding customer's order. For more information about the DataRelation object, see Adding relationships between tables and navigating tables.
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=Microsoft.Jet.OLEDB.4.0;" + "Data source=c:\\program Files\\ Microsoft office\\office\\samples\\northwind.mdb; ");
OleDbDataAdapter Orderda = new OleDbDataAdapter ("SELECT * from Orders", orderconn);
Custconn.open ();
Orderconn.open ();
DataSet custDS = new DataSet ();
Custda.fill (custDS, "Customers");
Orderda.fill (custDS, "Orders");
Custconn.close ();
Orderconn.close ();
DataRelation Custorderrel =
CUSTDS.RELATIONS.ADD ("CustOrders", custds.tables["Customers"). columns["CustomerID"], custds.tables["Orders"]. columns["CustomerID"]);
foreach (DataRow prow in custds.tables["Customers"). Rows)
{
Console.WriteLine (prow["CustomerID"]);
foreach (DataRow cRow in Prow.getchildrows (Custorderrel))
Console.WriteLine ("T" + crow["OrderID"]);
}
SQL Server Decimal Type
The dataset uses the. NET Framework data types to store data. For most applications, these types provide a convenient representation of the 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 digits, while the SQL Server decimal data type allows 38 A valid bit. If SqlDataAdapter determines that the precision of the SQL Server decimal field is greater than 28 characters during the Fill operation, the current row will not be added to the DataTable. The FillError event occurs, which allows you to determine whether the loss of precision will occur and respond appropriately. For more information about the FillError event, see Using the DataAdapter event. To get the SQL Server decimal value, you can also use the SqlDataReader object and invoke the GetSqlDecimal method.
Use SqlCommand in 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 a OleDbDataAdapter and a DataSet.
The following example uses the derived class OleDbDataAdapter to Update the data source. This example assumes that you have created a 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
Mydataadapter.update (custDS, mytablename);
Myconn.close ();
return custDS;
}
The following instance creates a SqlDataAdapter and sets the SelectCommand and InsertCommand properties. Suppose you have created a SqlConnection object.
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);
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 Customers (CustomerID, CompanyName)" +
"VALUES (@CustomerID, @CompanyName)", conn);
Cmd. Parameters.Add ("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
Cmd. Parameters.Add ("@CompanyName", SqlDbType.NVarChar, "CompanyName");
Da. InsertCommand = cmd;
Return da;
}
The following instance creates a SqlDataAdapter and sets the SelectCommand and DeleteCommand properties. Suppose you have created a SqlConnection object.
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);
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 Customers 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 properties. Suppose you have created a SqlConnection object.
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);
Cmd. Parameters.Add ("@Country", SqlDbType.NVarChar, 15);
Cmd. Parameters.Add ("@City", SqlDbType.NVarChar, 15);
Da. SelectCommand = cmd;
Create the UpdateCommand.
cmd = new SqlCommand ("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName" +
"WHERE CustomerID = @oldCustomerID", conn);
Cmd. Parameters.Add ("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
Cmd. Parameters.Add ("@CompanyName", SqlDbType.NVarChar, "CompanyName");
Parm = cmd. Parameters.Add ("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
Parm. SourceVersion = datarowversion.original;
Da. UpdateCommand = cmd;
Return da;
}