Example #1
0
		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;
		}
Example #2
0
		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;
		}
Example #3
0
		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;
		}