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 =""; //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 = "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 {}