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