using System.Data.sqlClient;
static void Main(string[] args)
{
用dataread读取数据
/***********************************************************
sqlConnection conn = new sqlConnection("server=.;uid=sa;pwd=123456;database=northwind");
conn.open()
sqlCommand com = conn.creatCommadn();
com.CommandText = "select * from customer";
sqlDataRead dr = com.ExecuteRead();
while(dr.read())
{
console.writeLine("{0},{1}", dr[colname1],dr[colname2]);
}
dr.close();
conn.close();
************************************************************/
用dataset读取数据
/************************************************************
sqlconnection conn = new sqlconnection("server=.; uid=sa; pwd=123456; database=northwind");
conn.open();
sqldataadapter da = new sqldataadapter("select customerid, contactname from customers", conn);
dataset ds = new dataset();
da.fill(ds, "customers");
foreach(datarow therow in ds.tables["customers"].rows)
console.writhline(therow["customerid"] + "\t" + therow["contactname"]);
conn.close();
************************************************************/
更新数据库
/************************************************************
sqlconnection con = new sqlconnection("server=.;uid=sa;pwd=123456;database=northwind");
sqldataadapter da = new sqldataadapter("select customid, companyname from customers", con);
sqlcommandbuilder comb = new sqlcommandbuilder(da);
dataset ds = new dataset();
da.fill(ds,"customers");
console.writeline("name before chang:{0}",ds.tables[customers"].rows[9]["companname"]);
ds.tables["customers"].rows[9]["companyname"] = "acme, inc";
da.update(ds, "customers");
console.writeline(name after chang:{0}",ds.tables["customers"].rows[9]["companyname"]);
con.close();
/************************************************************
给数据库添加行
/************************************************************
sqlconnection con = new sqlconnection("server=.;uid=sa;pwd=123456;database=northwind");
sqldataadapter da = new sqldataadapter("select customid, companyName form customers",con);
sqlcommandbuild cmb = new sqlcommandbuild(da);
dataset ds = new dataset();
da.fill(ds, "customers");
console.writeline("#rows before chang:{0}", ds.tables["customers"].rows.count);
datarow dr = ds.tables["customers"].newrow();
dr["customerid"] = "zaczi";
dr["companyname"] = "zachary zithersltd.";
ds.tables["customers"].rows.add(dr);
console.writeline("#rows after chang:{0}", ds.tables["customers"].rows.count);
da.update(ds, "customers");
con.close();
console.readkey();
************************************************************
查找行
************************************************************
sqlconnection con = new sqlconnection("server=.;uid=sa;pwd=123456;database=northwind");
sqldataadapter da = new sqldataadapter(select customerid, companyname from customers", con);
sqlCommandBuild cmb = new sqlcommandbuild(da);
dataset ds = new dataset();
da.fill(ds, "customers");
console.writeline(#rows before chang:{0}", ds.tables["customers"].rows.count);
datacolumn[] keys = new datacolumn[1];
keys[0] = ds.tables["customers"].columns["customerid"];
ds.tables["customers"].primarykey = keys;
datarow = findrow = ds.tables[customers'].rows.find("zaczi");
if(findrow == null)
{
console.writeline("zaczi not found, will andd to customers table");
datarow dr = ds.tables["customers].newrow();
dr["customerid"] = "zaczi";
dr["companyname"] = "zachary zithers ltd.";
ds.tables["customers"].rows.add(dr);
if((findrow = ds.tables["customers"].rows.find("zaczi")) != null)
console.writeline("zaczi successfully added to customers table");
}
else
console.writeline("zaczi alread persent in database");
da.update(ds, "customers);
console.writeline("#rows after change:{0}", ds.tables["customers"].rows.count);
con.close();
console.readkey();
************************************************************
删除行
************************************************************
sqlconnection con = new sqlconnection("server=.;uid=sa;pwd=123456;database=northwin");
sqldataadapter da = new sqldataadapter("select customerid, companyname from customers", con);
sqlcommandbuilder cmb = new sqlcommandbuilder(da);
dataset ds = new dataset();
da.fill(ds, "customers");
console.writeline("#rows before chang:{0}", ds.tables["customers"].rows.count);
datacolumn[] keys = new datacolumn[1];
keys[0] = ds.tables["customers"].columns["customerid"];
ds.tables["customers"].primarykey = keys;
datarow findrow = ds.tables["customers"].rows.find("zaczi");
if(findrow != null)
{
console.writeline("zaczi already in customers table");
console.writeline("removing zaczi...");
findrow.delete();
da.update(ds, "customers");
}
console.writeline("#rows after change: {0}", ds.tables["customers"].rows.count);
conn.close();
console.readkey();
}