利用DataAdapter更新数据库

本文主要研究如何利用DataAdapter来更新数据库内容（包括增、删、改）

 下面以Oracle数据库为例，代码如下：

 一、首先新建一个类DataInfo：

/// <summary>
///DataInfo 的摘要说明
/// </summary>
public class DataInfo
{
    public DataInfo()
    {
      
    }
    public DataInfo(string tblname, DataSet ds, OracleDataAdapter oda, DataTable dt)
    {
        this.tblname = tblname;
        this.ds = ds;
        this.oda = oda;
        this.dt = dt;      
    }
    private string tblname;

    public string TblName
    {
        get { return tblname; }
        set { tblname = value; }
    }
    private DataSet ds;

    public DataSet Ds
    {
        get { return ds; }
        set { ds = value; }
    }
    private OracleDataAdapter oda;

    public OracleDataAdapter Oda
    {
        get { return oda; }
        set { oda = value; }
    }
    private DataTable dt;

    public DataTable Dt
    {
        get { return dt; }
        set { dt = value; }
    }

} 

 二、然后编写底层类DataSetChange

  /// <summary>
  ///
  /// </summary>
  /// <param name="safeSql">SQL语句</param>
  /// <param name="tblname">填充的表名</param>
  /// <param name="primarykey">主键</param>
  /// <returns></returns>
    public DataInfo GetDataSet(string safeSql, string tblname, string primarykey)
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        OracleCommand cmd = new OracleCommand(safeSql, Connection);
        OracleDataAdapter da = new OracleDataAdapter(cmd);
        OracleCommandBuilder ocb = new OracleCommandBuilder(da);
        da.Fill(ds, tblname);
        dt = ds.Tables[tblname];
        dt.PrimaryKey = new DataColumn[] { dt.Columns[primarykey] };
        DataInfo dif = new DataInfo(tblname, ds, da, dt);
        return dif;
    } 

三、数据库操作

  private static DataInfo dif = new DataInfo();

  DataSetChange dsc = new DataSetChange(); 

    /// <summary>
    /// 获取数据
    /// </summary>
    protected void GetData()
    {
        dif = dsc.GetDataSet("select STATUTEID as \"编号\",sort as \"税种\",area as \"地区\" from statute1 where rownum<20 order by statuteid", "tbl1", "编号");
    }
    /// <summary>
    /// 删除行
    /// </summary>
    /// <param name="keys"></param>
    protected void Delete(string keys)
    {
        DataRow dr = dif.Dt.Rows.Find(keys);
        if (dr != null)
        {
            dr.Delete();
            dif.Oda.Update(dif.Ds, "tbl1");
        }
    }
    /// <summary>
    /// 插入行
    /// </summary>
    /// <param name="strs"></param>
    protected void Insert(string strs)
    {
        DataTable dt = dif.Dt;
        DataRow dr = dt.NewRow();
        dr["编号"] = 1;//dt.Columns["编号"].AutoIncrement = true;如果是SQL Server的自增字段 则可这样处理
        dr["税种"] = strs;
        dt.Rows.Add(dr);
        dif.Oda.Update(dif.Ds, "tbl1");
    }
    /// <summary>
    /// 更新行
    /// </summary>
    /// <param name="keys"></param>
    protected void Update(string keys)
    {
        string[] strs = keys.Split(',');
        DataTable dt = dif.Dt;
        DataRow dr = dt.Rows.Find(strs[0]);
        if (dr != null)
        {
            dr["地区"] = strs[1];
            dif.Oda.Update(dif.Ds, "tbl1");
        }
    }

 

结语：

本方法只可用于单表数据操作，如果是多表查询，则还需用SQL语句实现。否则将会报错。此方法仅供参考，如有疏漏敬请指正。

转载于:https://www.cnblogs.com/zxh0208/archive/2010/07/07/1772853.html