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

}