c#连接sql数据库

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();

}