C#学习笔记之五（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 provider
 using 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.SqlClient.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);
  productDataGrid.dataSource = dataset.DefaultViewManger;
  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 ="<storedProcedureName>"; //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="<SP>"; //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 = "SupplierID";
 param.SourceVersion = DataRowVersion.Current; //which version
 try
 { //ADO.net will loop each row to 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 ="SupplierID";
 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 rowsUpdated = dataAdapter.Update(dataSet, "Products");
 }   
 catch {}