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;
}
