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 ="

 1<storedprocedurename>"; //SP has used transaction   
 2command.CommandType= CommandType.StoredProcedure;   
 3System.Data.SqlClient.SqlParamenter param;   
 4param = command.Parameters.Add("@ProductID", SqlDbType.Int);   
 5param.Direction = ParameterDirection.Input;   
 6param.Value = txtProductID.Text.Trim();   
 7... //pass all parameter need by StoredProcedure   
 8command.ExecuteNonQuery();   
 9  
10//2. Connection Transaction   
11//create connection and command obj   
12...   
13System.DataSqlClient.SqlTransaction transaction;   
14transaction = connection.BeginTransaction();   
15command.Transaction = transaction;   
16command.Connection = connection;   
17try   
18{   
19command.CommandText="<sp>"; //this SP has no transaction in it   
20command.CommandType = CommandType.StoredProcedure;   
21System.DataSqlClient.SqlParameter param;   
22..   
23}   
24catch (Exception ex)   
25{   
26//give Err message   
27transaction.Rollback();   
28}   
29  
30//Update DataSet, then update DB at once   
31//create connection, command obj, using command.Transaction   
32...   
33param = command.Parameters.Add("@QupplierID", SqlDbType.Int);   
34param.Direction = ParameterDirection.Input;   
35param.SourceColumn = "SupplierID";   
36param.SourceVersion = DataRowVersion.Current; //which version   
37try   
38{ //ADO.net will loop each row to update DB   
39int rowsUpdated = dataAdapter.Update(dataSet, "Products");   
40transaction.Commit();   
41}   
42catch   
43{   
44transactrion.Rollback();   
45}   
46  
47  
48// Concurrency Update Database   
49//compare will original data, avoid conflict   
50//Give SQL SP, both Original and Current Data as parameter   
51//SQL will write like this: update ... where ... SupplierID = @OldSupplierID 
52
53//original version   
54param = command.Parameters.Add("@OldSupplierID", SqlDbtype.Int);   
55param.Driection = ParameterDiretion.Input;   
56param.SourceColumn ="SupplierID";   
57param.SourceVersion = DataRowVersion.Original;   
58//current version   
59param = command.Parameters.Add("@SupplierID", SqlDbtype.Int);   
60param.Driection = ParameterDiretion.Input;   
61param.SourceColumn ="SupplierID";   
62param.SourceVersion = DataRowVersion.Current; 
63
64//SqlCommandBuilder   
65SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);   
66dataAdapter.UpdateCommand = bldr.GetUpdateCommand();   
67dataAdapter.DeleteCommand = bldr.GetDelteCommand();   
68dataAdapter.InsertCommand = bldr.GetInsertCommand();   
69try   
70{   
71//This need not SQL, for bldr has build it for us.   
72int rowsUpdated = dataAdapter.Update(dataSet, "Products");   
73}   
74catch {}</sp></storedprocedurename>