SqlDataAdapter的几种常用方法。主要代码例如下所示：
SelectCommand、InsertCommand、DeleteCommand、UpdateCommand 和 其它 属性，使数据的加载和更新更加方便。 DataSet 增加/更新/插入到数据库
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd;
SqlParameter parm;
// 创建选择命令.
cmd = new SqlCommand("SELECT * FROM Customers WHERE Country = @Country AND City = @City", conn);
cmd.Parameters.Add("@Country", SqlDbType.NVarChar, 15);
cmd.Parameters.Add("@City", SqlDbType.NVarChar, 15);
cmd.SelectCommand.Parameters["@Country"].Value = Country;
cmd.SelectCommand.Parameters["@City"].Value = City;
da.SelectCommand = cmd;
// 创建插入命令.
cmd = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) VALUES (@CustomerID, @CompanyName)", conn);
cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
cmd.SelectCommand.Parameters["@CustomerID"].Value = CustomerID;
cmd.SelectCommand.Parameters["@CompanyName"].Value = CompanyName;
da.InsertCommand = cmd;
// 创建删除命令.
cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", conn);
parm = cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
parm.SourceVersion = DataRowVersion.Original;
cmd.SelectCommand.Parameters["@CustomerID"].Value = CustomerID;
da.DeleteCommand = cmd;
// 创建更新命令.
cmd = new SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName WHERE CustomerID = @oldCustomerID", conn);
cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
cmd.SelectCommand.Parameters["@CustomerID"].Value = CustomerID;
cmd.SelectCommand.Parameters["@CompanyName"].Value = CompanyName;
parm = cmd.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
parm.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = cmd;
return da;
}
// 其它.
private void update()
{
SqlConnection conn = new SqlConnection(str);
SqlDataAdapter da = new SqlDataAdapter("SELECT order_id, contract FROM linhai", conn);
DataSet ds = new DataSet();
da.Fill(ds, "linhai");
da.UpdateCommand = new SqlCommand("UPDATE linhai SET contract = @contract WHERE order_id = @order_id" , conn);
da.UpdateCommand.Parameters.Add("@contract", SqlDbType.NVarChar, 15, "contract");
da.UpdateCommand.Parameters.Add("@order_id", SqlDbType.NVarChar, 15, "order_id");
ds.Tables["linhai"].Rows[0]["contract"] = "PPP";
da.Update(ds.Tables[0]);
}
public DataSet CreateCmdsAndUpdate(DataSet myDataSet,string connstr,string sql,string myTableName)
{
OleDbConnection conn = new OleDbConnection(connstr);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(sql, conn);
// 自动生成用于协调 DataSet 的更改与关联数据库的单表命令
OleDbCommandBuilder ds = new OleDbCommandBuilder(da);
conn.Open();
DataSet ds = new DataSet();
da.Fill(ds);
da.Update(ds);
conn.Close();
return ds;
}
//DataSet 更新数据库
public void Update()
{
string str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+@"f:\a.mdb";
OleDbConnection conn = new OleDbConnection(str);
OleDbDataAdapter da=new OleDbDataAdapter("SELECT * FROM 工资模板",conn);
OleDbCommandBuilder cb=new OleDbCommandBuilder(da);
DataSet ds=new DataSet();
da.Fill(ds);
//DataTable dt=ds.Tables[0];
//dt.PrimaryKey=new DataColumn[]{dt.Columns["编号"]};//设置主键
//DataRow dr=dt.Rows.Find("1");//根据参数查找到需要修改的行
//dr["类型"]="说明项A";//对需要修改的记录赋新值
da.Update(Add(ds).Tables[0]);//用DataAdapter的Update()方法进行数据库的更新
}
public DataSet Add(DataSet ds)
{
//增加
DataRow dr = ds.Tables[0].NewRow();
dr["编号"] = "50";
dr["名称"] = "mmnnvbc";
dr["标识"] = "sdasdasd";
dr["类型"] = "工要";
dr["长度"] = "20";
dr["小数位数"] = "0";
dr["是否唯一"] = "true";
dr["银行项目"] = "false";
ds.Tables[0].Rows.Add(dr);
/*
//记录删除
ds.Tables[0].Rows[2].Delete();
//更新
DataRow dr = ds.Tables[0].Rows[1];
dr.BeginEdit();
dr["CustomerID"] = "********";
dr.EndEdit();
*/
return ds;
}