C # study notes five (ADO.NET)

ADO.net // Overview Data -> DataReader -> Data Provider -> DataSet Data Provider: Connection, Command, DataAdapter DataSet: DataRelationCollection, DataTable collection (including DataTable)) DataTable: DataRowCollection, DataColumnColl, ConstrainColl DataAdapter: retrieve data From DB, Fill Tables in Dataset

// SQL Server .net data provider using System.Data using System.Data.SqlClient; ... string strConnection = "server = allan; uid = sa; pwd =; database = northwind"; string strCommand = "Select productName, unitPrice from Products "; SqlDataAdapter dataAdapter = new SqlDataAdapter (strCommand, strConnection); DataSet dataSet = new DataSet (); dataAdapter.Fill (dataSet," Products "); DataTable dataTable = dataSet.Table [0]; foreach (DataRow row in dataTable .ROWS) {lbproducts.Items.add (Row ["ProductName"] "($" row ["UnitProice") ")");} // oledb data provike system.data.oledb; ... string strConnection = "provider = Microsoft.JET.OLEDB.4.0; data source = c: //nwind.mdb"; OleDbDataAdapter dataAdapter = ... // DataGrids using System.Data.SqlClient public class Form1: System.Windows.Forms .Form {private System.Windows.Forms.DataGrid dgOrders; private System.Data.DataSet dataSet; private System.Data.SqlClient.Sqlconnection connection; private System.Data.SqlClient.SqlCommand; private System.Data.Sql Client.SqlDataAdapter dataAdapter; private void Form1_Load (object sender, System.EventArgs e) {string connectionString = "server = allan; uid = sa; pwd =; database = northWind"; connection = new System.Data.SqlClient.Sqlconnection (connectionString ); connection.Open (); dataSet = new System.Data.DataSet (); dataSet.CaseSensitive = true; command = new System.Data.SqlClient.SqlCommand (); command.Connection = connection; command.CommandText = "Select * from Orders "; DataAdapter = new system.datasqlclient.sqladapter ();

// DataAdapter has SelectCommand, InsertCommand, UpdaterCommand // and DeleteCommand dataAdapter.SelectCommand = command; dataAdapter.TableMappings.Add ( "Table", "Orders"); dataAdapter.Fill (dataSet); ProductDataGrid.DataSource = dataSet.Table [ " Orders ".] DefaultView; // Data Relationships, add code below command2 = new System.Data.SqlClient (); command2.Connection = connection; command2.CommandText =" Select * form [order details] "; dataAdapter2 = new System. Data.SqlClient.SqlDataAdapter (); dataAdapter2.SelectCommand = command2; dataAdapter2.TableMappings.Add ( "Table", "Details"); dataAdatper2.Fill (dataSet); System.Data.DataRelation dataRelation; System.Data.DataColumn datacolumn1; System.Data.DataColumn datacolumn2;. dataColumn1 = dataSet.Table [ "Orders"] Columns [ "OrderID"];. dataColumn2 = dataSet.Table [ "Details"] Columns [ "OrderID"]; dataRelation new System.Data.DataRelation ("ORDERSTODETAILS", Datacolumn1, Datacolumn2; DataSet.ReLations.Add (DataRelation); Product DataGrid.DataSource = Dataset.default.DataManger; ProductDataGrid.DataMember = "Orders"; // Display Order Table, IT HAS mapping to order detail}}

// update data using ado.net string cmd = "Update Products set ..."; ... // Creat Connection, Comand Obj command.connection = connection; command.commandtext = cmd; command.executenonquery (); // Transaction 1.SQL Transaction 2. Connection Transaction // 1 SQL Transaction // creat connection and command obj connnetion.open ();. command.Connection = conntection; command.CommandText = ""; // SP has used transaction command.CommandType = CommandType.StoredProcedure; System.Data.SqlClient.SqlParamenter param; param = command.Parameters.Add ( "@ ProductID", SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = txtProductID.Text .Trim (); ... // pass all parameter need by StoredProcedure command.ExecuteNonQuery ();. // 2 Connection Transaction // create connection and command obj ... System.DataSqlClient.SqlTransaction transaction; transaction = connection.BeginTransaction (); Command.transaction = transaction; command.connection = connection; try {command.commandtext = ""; // this SP has no transaction in it command.CommandType = CommandType.StoredProcedure; System.DataSqlClient.SqlParameter param; ..} catch (Exception ex) {// give Err message transaction.Rollback () ;} // Update DataSet, then update DB at once // create connection, command obj, using command.Transaction ... param = command.Parameters.Add ( "@ QupplierID", SqlDbType.Int); param.Direction = ParameterDirection .Input; param.SourceColumn = "support"; param.SourceVersion = DATAROWVERSION.CURRENT; // Which Version Try {//ado.net will loop Each Row Update DB INT ROWSUPDATED =

dataAdapter.Update (dataSet, "Products"); transaction.Commit ();} catch {transactrion.Rollback ();} // Concurrency Update Database // compare will original data, avoid conflict // Give SQL SP, both Original and Current Data as parameter // SQL will write like this: update ... where ... SupplierID = @ OldSupplierID // original version param = command.Parameters.Add ( "@ OldSupplierID", SqlDbtype.Int); param.Driection = ParameterDiretion.Input; param.SourceColumn = "SupplierID"; param.SourceVersion = DataRowVersion.Original; // current version param = command.Parameters.Add ( "@ SupplierID", SqlDbtype.Int); param.Driection = ParameterDiretion.Input; Param.SourceColumn = "support"; param.sourceversion = DataRowVersion.current;

// SqlCommandBuilder SqlCommandBuilder bldr = new SqlCommandBuilder (dataAdapter); dataAdapter.UpdateCommand = bldr.GetUpdateCommand (); dataAdapter.DeleteCommand = bldr.GetDelteCommand (); dataAdapter.InsertCommand = bldr.GetInsertCommand (); try {// This need not SQL , for bldr has build it for us. int rt rowsupdated = dataadapter.Update (Dataset, "Products");} catch {}