Update and insert a database through DataSet

Method 1: // set 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 Conne Ction;} catch (Exception ex) {MessageBox. show (ex. message, "database connection failed"); throw ;}// retrieve database data 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; // return the DataSet filled with data, where the data table is named by the string given by strTableName} catch (Exception ex) {MessageBox. show (ex. me Ssage, "database operation failed"); throw ;}// database data update (Object for transmitting DataSet and 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; // returns the updated database table} catch (Exception ex) {MessageBox. show (ex. message, "database update failed"); throw ;} 1. The GetConn method creates a database connection and returns SqlConnection. 2. The select command used must contain the primary key. We all know this! 3. this. da. fill (ds, strTableName) fills the dataset 4. when constructing a CommandBuilder object, pass the DataAdapter object as the constructor parameter: this. sqlCmdBuilder = new SqlCommandBuilder (da); 5. before calling UpdateDs () to update a database, check whether changedDs has been updated. [tableName] GetChanges ()! = Null; 6. use this. da. update (changedDs, tableName) method to Update data, and then call changedDs. acceptChanges () can be used to update the database, and changedDs is called. rejectChanges () cancels the update. Method 2: This method may have performance problems 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 (sq1_, 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, sq1_, null );}}