ds更新、插入数据库

方法一：

  // 与SQL Server的连接字符串设置
  private   string  _connString;
  private   string  _strSql;
  private  SqlCommandBuilder sqlCmdBuilder;
  private  DataSet ds  =   new  DataSet();
  private  SqlDataAdapter da;

  public  sqlAccess( string  connString, string  strSql)
  ... {
      this._connString=connString;
 }

  private  SqlConnection GetConn() 
  ... { 
      try 
      ...{ 
           SqlConnection Connection = new SqlConnection(this._connString); 
           Connection.Open(); 
           return Connection; 
      } 
      catch (Exception ex) 
      ...{ 
           MessageBox.Show(ex.Message,"数据库连接失败");
           throw;
      } 
 }

  // 根据输入的SQL语句检索数据库数据
  public  DataSet SelectDb( string  strSql, string  strTableName)
  ... { 
      try
      ...{
          this._strSql = strSql;
          this.da = new SqlDataAdapter(this._strSql,this.GetConn());
          this.ds.Clear();
          this.da.Fill(ds,strTableName);
          return ds;//返回填充了数据的DataSet，其中数据表以strTableName给出的字符串命名
      }
      catch (Exception ex) 
      ...{ 
           MessageBox.Show(ex.Message,"数据库操作失败");
           throw;
      } 
 }

  // 数据库数据更新(传DataSet和DataTable的对象)
  public  DataSet UpdateDs(DataSet changedDs, string  tableName)
  ... {
      try
      ...{
          this.da = new SqlDataAdapter(this._strSql,this.GetConn());
          this.sqlCmdBuilder = new SqlCommandBuilder(da);
          this.da.Update(changedDs,tableName);
          changedDs.AcceptChanges();
          return changedDs;//返回更新了的数据库表
      }
      catch (Exception ex) 
      ...{ 
           MessageBox.Show(ex.Message,"数据库更新失败");
           throw;
      } 
}

 

1. GetConn方法创建一个数据库连接，返回SqlConnection。

2.使用的select命令中必须包含主键，这点大家都知道的!

3. this.da.Fill(ds,strTableName) 填充数据集

4.构造CommandBuilder对象时,将DataAdapter对象作为构造函数参数传入: 

  this.sqlCmdBuilder = new SqlCommandBuilder(da);

5. 在调用UpdateDs()更新数据库前，请检查changedDs是否已经被更新过，用changedDs.[tableName] GetChanges() != null;

6.用this.da.Update(changedDs,tableName)方法更新数据,然后调用changedDs.AcceptChanges()才能真正的更新数据库，调用 changedDs.RejectChanges() 取消更新。

方法二：

该方法可能存在性能问题

             int  tab1Count  =  ds.Tables[ " ruledetail_setup " ].Rows.Count;
             for  ( int  i  =   0 ; i  <  tab1Count; i ++ )
             ... {
                string sql = "select count(*) from ruledetail_setup where type = " + type + " and ID = ";
                    sql += ds.Tables["ruledetail_setup"].Rows[i]["ID"];
                if (Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sql, null)) != 0)
                ...{
                    string sqlUpdata = "updata ruledetail_setup set errorLimit={0},scoreInterval={1},detailRule={2},gradeTime={3} ";
                    sqlUpdata += "where type = " + type + " and ID = " + ds.Tables["ruledetail_setup"].Rows[i]["ID"];
                    String.Format(sqlUpdata,
                        ds.Tables["ruledetail_setup"].Rows[i]["errorLimit"],
                        ds.Tables["ruledetail_setup"].Rows[i]["scoreInterval"],
                        ds.Tables["ruledetail_setup"].Rows[i]["detailRule"],
                        ds.Tables["ruledetail_setup"].Rows[i]["gradeTime"]);
                    SqlHelper.ExecuteNonQuery(CommandType.Text, sqlUpdata, null);
                }
                else
                ...{
                    string sqlIns = "insert into ruledetail_setup (Type,ID,errorLimit,scoreInterval,detailRule,gradeTime) ";
                        sqlIns += "values ("+type+",{0},{1},{2},{3},{4})";
                    String.Format(sqlIns,
                        ds.Tables["ruledetail_setup"].Rows[i]["ID"],
                        ds.Tables["ruledetail_setup"].Rows[i]["errorLimit"],
                        ds.Tables["ruledetail_setup"].Rows[i]["scoreInterval"],
                        ds.Tables["ruledetail_setup"].Rows[i]["detailRule"],
                        ds.Tables["ruledetail_setup"].Rows[i]["gradeTime"]);
                    SqlHelper.ExecuteNonQuery(CommandType.Text, sqlIns, null);
                }
            }