//InsertCommand
using (SqlConnection conn = new SqlConnection(connectionstring))
{
conn.Open();
DataTable dtInsert = new DataTable();
dtInsert.Columns.Add("DeptID", typeof(int));
dtInsert.Columns.Add("DeptName", typeof(string));
dtInsert.Rows.Add(new object[] { "1", "技术部" });
dtInsert.Rows.Add(new object[] { "2", "招商部" });
dtInsert.Rows.Add(new object[] { "3", "信息部" });
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand("insert into Dept(DeptID,DeptName) values(@DeptID,@DeptName)", conn);
adapter.SelectCommand = new SqlCommand("select * from Dept where 1=0", conn);//仅为了获得框架
adapter.InsertCommand.Parameters.Add("DeptID", SqlDbType.Int);
adapter.InsertCommand.Parameters.Add("DeptName", SqlDbType.VarChar);
adapter.InsertCommand.Parameters["DeptID"].SourceColumn = "DeptID";
adapter.InsertCommand.Parameters["DeptName"].SourceColumn = "DeptName";
//adapter.InsertCommand.Parameters["AddDate"].Value = DateTime.Now;//直接赋值
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
adapter.Fill(dtInsert);
if (dtInsert.Rows.Count > 0)
{
adapter.Update(dtInsert);
}
}
//UpdateCommand
using (SqlConnection conn = new SqlConnection(connectionstring))
{
conn.Open();
DataSet ds = new DataSet();
string sql = "select * from Dept";
SqlDataAdapter adapate = new SqlDataAdapter(sql, conn);
adapate.Fill(ds, "table1");
foreach (DataRow datarow in ds.Tables[0].Rows)
{
datarow["DeptName"] = "sc";//ai->sc
}
SqlCommandBuilder builder = new SqlCommandBuilder(adapate);
adapate.Update(ds, "table1");
}
//DeleteCommand
using (SqlConnection conn = new SqlConnection(connectionstring))
{
conn.Open();
DataSet ds = new DataSet();
string sql = "select * from Dept";
//string sql = "select * from Dept where deptid=1";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(ds, "table1");
adapter.DeleteCommand = new SqlCommand("delete from Dept", conn);
//adapter.DeleteCommand = new SqlCommand("delete from Dept where deptid=@deptid", conn);
//adapter.DeleteCommand.Parameters.Add("deptid", SqlDbType.Int);
//adapter.DeleteCommand.Parameters["deptid"].SourceColumn = "deptid";
ds.Tables["table1"].Rows[ds.Tables["table1"].Rows.Count - 1].Delete();
adapter.Update(ds, "table1");
}