C#學習筆記之五（ADO.net)
2024-07-10 13:00:20
供稿：網友
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 {}
