public static int UpdateTableByDataSet(DataSet ds,string sql,string tablename,string key,string DirectDel) { int ret=0; string constr=AccessDB.GetConnStr(); System.Data.OleDb.OleDbConnection myConn=new OleDbConnection(constr); try { System.Data.OleDb.OleDbDataAdapter myDataAdapter=new OleDbDataAdapter(sql,myConn); System.Data.OleDb.OleDbCommandBuilder cb = new OleDbCommandBuilder(myDataAdapter); if(myConn.State==ConnectionState.Open) { myConn.Close(); } myConn.Open(); bool selfadd=false; int currvalue=0; if(ds.Tables[0].Rows[0][key].ToString()==""||ds.Tables[0].Rows[0][key]==null) { selfadd=true; currvalue=int.Parse(AccessDB.GetDataSet("select currvalue from count_table where tablename='"+tablename+"'").Tables[0].Rows[0][0].ToString()); ds.Tables[0].Rows[0][key]=currvalue; ret=currvalue; } else { if(Comm.IsNumber(ds.Tables[0].Rows[0][key].ToString())) { ret=int.Parse(ds.Tables[0].Rows[0][key].ToString()); } else { ret=1; } } myDataAdapter.Fill(ds,tablename); //code to modify data in DataSet here //Without the SqlCommandBuilder this line would fail if(selfadd||ds.Tables[1].Rows.Count==0) { myDataAdapter.InsertCommand=cb.GetInsertCommand(); ds.Tables.RemoveAt(1); ds.Tables[0].TableName=tablename; myDataAdapter.Update(ds,tablename); } else { myDataAdapter.UpdateCommand=cb.GetUpdateCommand(); ////// //ds.Tables[1].Rows[0]=ds.Tables[0].Rows[0]; ////// foreach(DataColumn cls in ds.Tables[0].Columns) ////// { ////// if(!ds.Tables[1].Columns.Contains(cls.ColumnName)) continue; ////// ds.Tables[1].Rows[0][cls.ColumnName]=ds.Tables[0].Rows[0][cls.ColumnName].ToString(); ////// } ds.Tables.RemoveAt(1); ds.Tables[0].TableName=tablename; myDataAdapter.Update(ds,tablename); } if(selfadd) { ++currvalue; string cntsql="update count_table set currvalue="+currvalue.ToString()+" where tablename='"+tablename+"'"; System.Data.OleDb.OleDbCommand olc=new OleDbCommand(cntsql,myConn); olc.ExecuteNonQuery(); } } catch(Exception e) { return -1; } finally { myConn.Close(); } return ret; }
public static int UpdateTableByDataSet(DataSet ds,string sql,string tablename,string key,bool mutil) { int ret=0; string constr=AccessDB.GetConnStr(); System.Data.OleDb.OleDbConnection myConn=new OleDbConnection(constr); try { System.Data.OleDb.OleDbDataAdapter myDataAdapter=new OleDbDataAdapter(sql,myConn); System.Data.OleDb.OleDbCommandBuilder cb = new OleDbCommandBuilder(myDataAdapter); myConn.Open(); bool selfadd=false; int currvalue=0; if(ds.Tables[0].Rows[0][key].ToString()==""||ds.Tables[0].Rows[0][key]==null) { selfadd=true; currvalue=int.Parse(AccessDB.GetDataSet("select currvalue from count_table where tablename='"+tablename+"'").Tables[0].Rows[0][0].ToString()); ds.Tables[0].Rows[0][key]=currvalue; ret=currvalue; } else { if(Comm.IsNumber(ds.Tables[0].Rows[0][key].ToString())) { ret=int.Parse(ds.Tables[0].Rows[0][key].ToString()); } else { ret=1; } } myDataAdapter.Fill(ds,tablename); //code to modify data in DataSet here //Without the SqlCommandBuilder this line would fail if(selfadd||ds.Tables[1].Rows.Count==0) { myDataAdapter.InsertCommand=cb.GetInsertCommand(); for(int i=0;i<ds.Tables[0].Rows.Count;i++) { DataRow dr=ds.Tables[1].NewRow(); foreach(DataColumn cls in ds.Tables[0].Columns) { if(!ds.Tables[1].Columns.Contains(cls.ColumnName)) continue; string val=ds.Tables[0].Rows[i][cls.ColumnName].ToString(); switch(cls.DataType.ToString()) { case "System.Int32": dr[cls.ColumnName]=Comm.IsNumber(val)==true?int.Parse(val):0; break; case "System.Double": dr[cls.ColumnName]=Comm.IsNumber(val)==true?Double.Parse(val):0; break; default: dr[cls.ColumnName]=val; break; } } ds.Tables[1].Rows.Add(dr); } myDataAdapter.Update(ds.Tables[1]); } else { myDataAdapter.UpdateCommand=cb.GetUpdateCommand(); //ds.Tables[1].Rows[0]=ds.Tables[0].Rows[0]; foreach(DataColumn cls in ds.Tables[0].Columns) { if(ds.Tables[1].Columns.Contains(cls.ColumnName)) ds.Tables[1].Rows[0][cls.ColumnName]=ds.Tables[0].Rows[0][cls.ColumnName].ToString(); } myDataAdapter.Update(ds,tablename); } if(selfadd) { ++currvalue; string cntsql="update count_table set currvalue="+currvalue.ToString()+" where tablename='"+tablename+"'"; System.Data.OleDb.OleDbCommand olc=new OleDbCommand(cntsql,myConn); olc.ExecuteNonQuery(); } } catch(Exception e) { return -1; } finally { myConn.Close(); } return ret; }
public static int UpdateTableByDataSet(DataTable dt,string sql,string key) { int ret=0; string constr=AccessDB.GetConnStr(); System.Data.OleDb.OleDbConnection myConn=new OleDbConnection(); myConn.ConnectionString =constr; DataSet ds=new DataSet(); try { if(myConn.State ==ConnectionState.Closed) { myConn.Open(); } System.Data.OleDb.OleDbDataAdapter myDataAdapter=new OleDbDataAdapter(sql,myConn); System.Data.OleDb.OleDbCommandBuilder cb = new OleDbCommandBuilder(myDataAdapter); if(myConn.State==ConnectionState.Open) { myConn.Close(); } myConn.Open(); bool selfadd=false; int currvalue=0; myDataAdapter.Fill(ds,dt.TableName); if(ds==null) return -1; int reccnt=ds.Tables[0].Rows.Count; for(int i=0;i<dt.Rows.Count;i++) { if(!dt.Columns.Contains(key)) { dt.Columns.Add(key); break; } if(dt.Rows[i][key].ToString()==""||dt.Rows[i][key]==null) { selfadd=true; currvalue=int.Parse(AccessDB.GetDataSet("select currvalue from count_table where tablename='"+dt.TableName+"'").Tables[0].Rows[0][0].ToString()); dt.Rows[i][key]=currvalue+i; ret=currvalue; } else { if(Comm.IsNumber(dt.Rows[i][key].ToString())) { ret=int.Parse(dt.Rows[i][key].ToString()); } else { ret=1; } } } //code to modify data in DataSet here //Without the SqlCommandBuilder this line would fail ds.Tables.Clear(); ds.Tables.Add(dt); ds.Tables[0].TableName=dt.TableName; if(selfadd||reccnt==0) { myDataAdapter.InsertCommand=cb.GetInsertCommand(); myDataAdapter.Update(ds,dt.TableName); } else { myDataAdapter.UpdateCommand=cb.GetUpdateCommand(); myDataAdapter.Update(ds,dt.TableName); } if(selfadd) { ++currvalue; string cntsql="update count_table set currvalue=currvalue"+dt.Rows.Count+" where tablename='"+dt.TableName+"'"; System.Data.OleDb.OleDbCommand olc=new OleDbCommand(cntsql,myConn); olc.ExecuteNonQuery(); } } catch(Exception e) { return -1; } finally { myConn.Close(); } return ret; }