ADO.NET(下)



1:查找

stringconnString ="server=(local);database=dbs;uid=uid;password=123456";

SqlConnection conn = new SqlConnection(connString);

string  sql ="select classid as cid,classnameas cname  from classes";

SqlCommand Cmd =new SqlCommand(sql);

insertCmd.Connection= conn

SqlDataAdapter sda =new SqlDataAdapter();

sda.SelectCommand(insertCmd);

DataTabledt =new DataTable();

conn.Open();

sda.Fill(dt);

conn.Close();

 

cid	cname
1	A
2	B

 

2:更新,删除

create proc Update_Proc(@cid int,@cname varchar(10))

as

updateclasses set classname =@cname where classid=@cid

go

 

stringconnString ="server=(local);database=dbs;uid=uid;password=123456";

SqlConnection conn = new SqlConnection(connString);

SqlCommand updateCmd =new SqlCommand();

updateCmd .CommandType =CommandType.StoredProcedure；

updateCmd .Parameters.Add(@cid,SqlDbType.int).SourceColumn="cid";

updateCmd.Parameters.Add(@cname,SqlDbType.Varchar,10).SourceColumn="cname";

updateCmd.Connection = conn

 

string  sql ="delete from classes where classid=@"cid";

SqlCommand deleteCmd = new  SqlCommand();

deleteCmd.Parameters.Add(@cid,SqlDbType.Int).SourceColumn="cid";

deleteCmd.Connection= conn;

 

SqlDataAdapter sda =new SqlDataAdapter();

 sda.UpdateCommand(updateCmd );

 sda.DeleteCommand(deleteCmd);

Transaction trans = null;

try

{

   conn.Open();

   trans = conn.BeginTransaction();

   sda.UpdateCommand.Transaction  = trans;

  sda.DeleteCommand.Transaction = trans;

  sda.Update(dt);

   trans.Commit();

catch(SqlExceptionex)

{

  if(trans!=null)

  {

    trans.Rollback();

  }

 MessageBox.Show(ex.Message);

}

finally

{

 conn.Close();

}

3：插入

DataTable dt = new DataTable();

dt.Columns.Add("ClassId",typeof(int));

dt.Columns.Add("ClassName",typeof(string));

DataRow dr = dt.NewRow();

dr["ClassId"]=1;dr["ClassName"]="A";

dt.Rows.Add(dr);

dt.Rows.Add(2,"B");

stringconnString ="server=(local);database=dbs;uid=uid;password=123456";

SqlConnection conn = new SqlConnection(connString);

string  sql ="insert  into classes values(@cid,@cname)";

SqlCommand cmd =new SqlCommand(sql);

cmd.Connection= conn;

cmd.Parameters.Add(@cid,SqlDataType.int).SourceColumn="cid";

cmd.Parameters.Add(@cname,SqlDataType.nvarchar,50).SourceColumn="cname";

SqlDataAdapter sda =new SqlDataAdapter();

sda.InsertCommand(cmd);

conn.Open();

sda.Update(dt);

conn.Close();