C#学习笔记之五（ADO.net)-.NET教程,C#语言

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