csqlbuilder.cs

C#数据访问组件

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
/*
作者：何林波
时间：2004年6月2日
作用：实现通用数据存贮。 
*/
namespace ENet.BCL
{
	
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	public class CSqlBuilder
	{		
		private string m_TableName="";
		private string m_ConnectionString="";
		private OleDbCommand m_OleCmd; 
		private OleDbConnection m_OleConn;
		private OleDbTransaction m_OleTrans;

		public CSqlBuilder()
		{
			//
			// TODO: Add constructor logic here
			//
		}
		public string ConnectionString
		{
			get
			{
				return this.m_ConnectionString;				
			}
			set
			{
				this.m_ConnectionString=value;
				this.m_OleConn=new OleDbConnection(this.m_ConnectionString);
			}
		}
		public bool CreateSQL(string TableName,string ActiveConnection)
		{
			try
			{
				this.m_TableName=TableName;
				this.m_ConnectionString=ActiveConnection;
				this.m_OleConn=new OleDbConnection(this.m_ConnectionString);				
				return true;
			}
			catch
			{
				return false;
			}	
		}
		private void ExecuteSql(OleDbCommand OleCmd,string StrSql)
		{			
			OleCmd.CommandText=StrSql;
			OleCmd.CommandType=CommandType.Text;
			OleCmd.ExecuteNonQuery();			
		}
		
		public void ExecuteSql(string StrSql)
		{
			if(this.m_OleConn.State!=System.Data.ConnectionState.Open)
				this.m_OleConn.Open();		
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.CommandType=System.Data.CommandType.Text;
			this.m_OleCmd.CommandText=StrSql;
			this.m_OleCmd.ExecuteNonQuery();			
			this.m_OleConn.Close();	
		}
		private void AppendFields(OleDbCommand OleCmd,Hashtable hsValue)
		{
			string FieldSql="";
			string ValueSql="";
			OleCmd.Parameters.Clear();
			foreach(DictionaryEntry entry in hsValue)
			{							
				FieldSql+=entry.Key.ToString()+",";
				ValueSql+="?,";
				OleCmd.Parameters.Add(entry.Key.ToString(),entry.Value);
			}		
			FieldSql=FieldSql.Substring(0,FieldSql.Length-1);
			ValueSql=ValueSql.Substring(0,ValueSql.Length-1);
			string StrSql="";
			StrSql="Insert into "+this.m_TableName+"("+FieldSql+")";
			StrSql+=" Values("+ValueSql+")";
			OleCmd.CommandText=StrSql;
			OleCmd.CommandType=CommandType.Text;
			OleCmd.ExecuteNonQuery();

		}
		
		public bool SQLInserSingleRow(Hashtable hsData)
		{
			if(hsData.Count==0)
			{
				return false;
			}
			this.m_OleConn.Open();
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleTrans=this.m_OleConn.BeginTransaction();
			this.m_OleCmd.Transaction=this.m_OleTrans;
			try
			{				
				AppendFields(m_OleCmd,hsData);			

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return true;

			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return false;
			}
		}

		public bool SQLInserSingleRow(Hashtable hsData,string exeSql,string SeedField,int SeedCount)
		{
			if(hsData.Count==0)
			{
				return false;
			}
			this.m_OleConn.Open();
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleTrans=this.m_OleConn.BeginTransaction();
			this.m_OleCmd.Transaction=this.m_OleTrans;
			try
			{			
				if(exeSql!="")
					this.ExecuteSql(this.m_OleCmd,exeSql);
				if(SeedField!="")
				{
					string StrSql="";
					StrSql="select Max("+SeedField+") as "+SeedField+
						" from "+this.m_TableName;
					DataTable Dt=this.GetDataTable(this.m_OleCmd,StrSql);
					if(Dt.Rows.Count!=0)
					{
						hsData[SeedField]=(int)Dt.Rows[0][0]+SeedCount;
					}
				}
				
				AppendFields(m_OleCmd,hsData);			

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return true;

			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return false;
			}

		}

		public bool SQLInserSingleRow(Hashtable hsData,string exeSql,string SeedField)
		{
			if(hsData.Count==0)
			{
				return false;
			}
			this.m_OleConn.Open();
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleTrans=this.m_OleConn.BeginTransaction();
			this.m_OleCmd.Transaction=this.m_OleTrans;
			try
			{			
				if(exeSql!="")
					this.ExecuteSql(this.m_OleCmd,exeSql);
				if(SeedField!="")
				{
					string StrSql="";
					StrSql="select Max("+SeedField+") as "+SeedField+
						" from "+this.m_TableName;
					DataTable Dt=this.GetDataTable(this.m_OleCmd,StrSql);
					if(Dt.Rows.Count!=0)
					{
						if(Dt.Rows[0][0].ToString()=="")
							hsData[SeedField]=0;
						else
							hsData[SeedField]=(int)Dt.Rows[0][0]+1;
					}
				}
				
				AppendFields(m_OleCmd,hsData);			

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return true;

			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return false;
			}

		}

		public bool SQLInserSingleRow(Hashtable hsData,string exeSql,string SeedField,ref int ID)
		{
			if(hsData.Count==0)
			{
				return false;
			}
			this.m_OleConn.Open();
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleTrans=this.m_OleConn.BeginTransaction();
			this.m_OleCmd.Transaction=this.m_OleTrans;
			try
			{			
				if(exeSql!="")
					this.ExecuteSql(this.m_OleCmd,exeSql);
				if(SeedField!="")
				{
					string StrSql="";
					StrSql="select Max("+SeedField+") as "+SeedField+
						" from "+this.m_TableName;
					DataTable Dt=this.GetDataTable(this.m_OleCmd,StrSql);
					if(Dt.Rows.Count!=0)
					{
						if(Dt.Rows[0][0].ToString()=="")
							hsData[SeedField]=0;
						else
							hsData[SeedField]=(int)Dt.Rows[0][0]+1;
					}
					ID=(int)hsData[SeedField];
				}
				
				AppendFields(m_OleCmd,hsData);			

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return true;

			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return false;
			}

		}

		public bool SQLInserSingleRow(Hashtable hsData,string exeSql)
		{
			if(hsData.Count==0)
			{
				return false;
			}
			this.m_OleConn.Open();
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleTrans=this.m_OleConn.BeginTransaction();
			this.m_OleCmd.Transaction=this.m_OleTrans;
			try
			{			
				if(exeSql!="")
					this.ExecuteSql(this.m_OleCmd,exeSql);

				AppendFields(m_OleCmd,hsData);			

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return true;

			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return false;
			}
		}
		public DataSet GetDataSet(string StrSql)
		{		
			try
			{
				if(this.m_OleConn.State!=System.Data.ConnectionState.Open)
					this.m_OleConn.Open();
			}
			catch
			{
				return null;
			}
			try
			{
			
				this.m_OleCmd=new OleDbCommand();
				this.m_OleCmd.CommandTimeout=0;
				this.m_OleCmd.Connection=this.m_OleConn;		
				this.m_OleCmd.CommandType=System.Data.CommandType.Text;
				this.m_OleCmd.CommandText=StrSql;
				System.Data.OleDb.OleDbDataAdapter oda=new OleDbDataAdapter(this.m_OleCmd);			
				DataSet ds=new DataSet();
				oda.Fill(ds);	
				this.m_OleConn.Close();
				return ds;
			}
			catch
			{
				this.m_OleConn.Close();
				return null;
			}
		}
		public DataTable GetDataTable(string StrSql,string BeforeSQL)
		{
			try
			{
				if(this.m_OleConn.State!=System.Data.ConnectionState.Open)
					this.m_OleConn.Open();
			}
			catch
			{
				return null;
			}
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleTrans=this.m_OleConn.BeginTransaction();
			this.m_OleCmd.Transaction=this.m_OleTrans;
			try
			{			
				if(BeforeSQL!="")
					this.ExecuteSql(this.m_OleCmd,BeforeSQL);
				DataTable dt=this.GetDataTable(this.m_OleCmd,StrSql);				

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return dt;

			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return null;
			}
		}
		public DataTable GetDataTable(string StrSql,string BeforeSQL,string EndSQL)
		{
			try
			{
				if(this.m_OleConn.State!=System.Data.ConnectionState.Open)
					this.m_OleConn.Open();
			}
			catch
			{
				return null;
			}
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleTrans=this.m_OleConn.BeginTransaction();
			this.m_OleCmd.Transaction=this.m_OleTrans;
			try
			{			
				if(BeforeSQL!="")
					this.ExecuteSql(this.m_OleCmd,BeforeSQL);
				DataTable dt=this.GetDataTable(this.m_OleCmd,StrSql);
				try
				{
					if(EndSQL!="")
						this.ExecuteSql(this.m_OleCmd,EndSQL);
				}
				catch
				{}				

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return dt;

			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return null;
			}
		}
		public DataTable GetDataTable(string StrSql)
		{
			try
			{
				if(this.m_OleConn.State!=System.Data.ConnectionState.Open)
					this.m_OleConn.Open();
			}
			catch
			{
				return null;
			}
			try
			{			
				this.m_OleCmd=new OleDbCommand();
				this.m_OleCmd.CommandTimeout=0;
				this.m_OleCmd.Connection=this.m_OleConn;		
				this.m_OleTrans=this.m_OleConn.BeginTransaction();
				this.m_OleCmd.Transaction=this.m_OleTrans;

				this.m_OleCmd.CommandType=System.Data.CommandType.Text;
				
				if(StrSql.IndexOf(";")!=-1)
				{
					string[] Sql=StrSql.Split(';');
					for(int i=0;i<Sql.Length;i++)
					{
						if(i!=Sql.Length-1)
							this.ExecuteSql(this.m_OleCmd,Sql[i]);
						else
							StrSql=Sql[Sql.Length-1];
					}
				}
				this.m_OleCmd.CommandText=StrSql;
				System.Data.OleDb.OleDbDataAdapter oda=new OleDbDataAdapter(this.m_OleCmd);			
				DataTable Dt=new DataTable();
				oda.Fill(Dt);	

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return Dt;
			}
			catch(System.Exception ex)
			{

				string str=ex.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return null;
			}		
			
		}
		private DataTable GetDataTable(OleDbCommand OleCmd,string StrSql)
		{

			if(StrSql.IndexOf(";")!=-1)
			{
				string[] Sql=StrSql.Split(';');
				for(int i=0;i<Sql.Length;i++)
				{
					if(i!=Sql.Length-1)
						this.ExecuteSql(this.m_OleCmd,Sql[i]);
					else
						StrSql=Sql[Sql.Length-1];
				}
			}

			OleCmd.CommandText=StrSql;
			System.Data.OleDb.OleDbDataAdapter oda=new OleDbDataAdapter(OleCmd);			
			DataTable Dt=new DataTable();
			oda.Fill(Dt);				
			return Dt;
		}
		public bool SQLInsertMutiRow(Hashtable hsData)
		{
			if(hsData.Count==0)
			{
				return false;
			}
			this.m_OleConn.Open();
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleTrans=this.m_OleConn.BeginTransaction();
			this.m_OleCmd.Transaction=this.m_OleTrans;
			try
			{
				foreach(DictionaryEntry entry in hsData)
				{
					AppendFields(m_OleCmd,(Hashtable)entry.Value);
				}

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return true;

			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return false;
			}
		}
		public bool SQLInsertMutiRow(Hashtable hsData,string exeSql)
		{
			if(hsData.Count==0)
			{
				return false;
			}
			this.m_OleConn.Open();
			this.m_OleCmd=new OleDbCommand();
			this.m_OleCmd.CommandTimeout=0;
			this.m_OleCmd.Connection=this.m_OleConn;
			this.m_OleTrans=this.m_OleConn.BeginTransaction();
			this.m_OleCmd.Transaction=this.m_OleTrans;
			try
			{
				if(exeSql!="")
					this.ExecuteSql(this.m_OleCmd,exeSql);

				foreach(DictionaryEntry entry in hsData)
				{
					AppendFields(m_OleCmd,(Hashtable)entry.Value);
				}

				this.m_OleTrans.Commit();
				this.m_OleConn.Close();
				return true;

			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				this.m_OleTrans.Rollback();
				this.m_OleConn.Close();
				return false;
			}
		}
	}
}