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 {}