SqlDataAdapter的几种常用方法

 

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;
}