通过DataSet更新、插入数据库
方法一: //与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); } }