oledbhelper.cs

会员管理系统

using System;
using System.Data;
using System.Data.OleDb;
using com.sungoal.MemberManage.Common;
using System.Text;
using System.Collections;


namespace com.sungoal.MemberManage.DataAccess
{
	/// <summary>
	/// Summary description for OLEDBExecSQLHelper.
	/// </summary>
	public class OleDBHelper
	{
		private OleDbConnection connection = null;
		private OleDbTransaction trans;
		private int execResult;
		
		
		#region	重载的构造方法
		public OleDBHelper()
		{
			connection = new OleDbConnection(AppConfiguration.GetConnectionString());
		}

		public OleDBHelper(string connString)
		{
			if(connString!=null)
			{
				connection=new OleDbConnection(connString);
			}
			else
			{
				connection = new OleDbConnection(AppConfiguration.GetConnectionString());
			}
		}

		
		public OleDBHelper(OleDbConnection conn)
		{
			if (conn!=null)
			{
				connection=conn;
			}
		}

		#endregion
	
		#region 执行SQL语句

		/// <summary>
		/// 执行SQL语句，获取数据集
		/// </summary>
		/// <param name="sqlString">SQL语句</param>
		/// <param name="ds">数据集</param>
		/// <returns></returns>
		public int ExecuteSQL(string sqlString,DataSet ds)
		{
			OleDbCommand cmd=new OleDbCommand(sqlString,connection);
			cmd.CommandType=CommandType.Text;
			OleDbDataAdapter adapter=new OleDbDataAdapter(cmd);
	
			try
			{
				connection.Open();
				execResult=adapter.Fill(ds);
			}
			catch(OleDbException e)
			{
				execResult=-1;
				throw e;
			}
			finally
			{
				connection.Close();
				cmd.Dispose();
				adapter.Dispose();
			}
			return execResult;
		}


		/// <summary>
		/// 执行SQL语句，获取数据集
		/// </summary>
		/// <param name="sqlString">SQL语句</param>
		/// <param name="ds">数据集</param>
		/// <returns></returns>
		public int ExecuteSQL(string sqlString,DataSet ds,string dataTableName)
		{
			OleDbCommand cmd=new OleDbCommand(sqlString,connection);
			cmd.CommandType=CommandType.Text;
			OleDbDataAdapter adapter=new OleDbDataAdapter(cmd);
	
			try
			{
				connection.Open();
				execResult=adapter.Fill(ds,dataTableName);
			}
			catch(OleDbException e)
			{
				execResult=-1;
				throw e;
			}
			finally
			{
				connection.Close();
				cmd.Dispose();
				adapter.Dispose();
			}
			return execResult;
		}

		
		/// <summary>
		/// 执行SQL语句，获取数据表
		/// </summary>
		/// <param name="sqlString">SQL语句</param>
		/// <param name="dt">数据表</param>
		/// <returns></returns>
		public int ExecuteSQL(string sqlString, DataTable dt)
		{
			OleDbCommand cmd = new OleDbCommand(sqlString,connection);
			cmd.CommandType = CommandType.Text;
			OleDbDataAdapter adapter=new OleDbDataAdapter(cmd);
	
			try
			{
				connection.Open();
				execResult=adapter.Fill(dt);
			}
			catch(OleDbException e)
			{
				execResult=-1;
				throw e;
			}
			finally
			{
				connection.Close();
				cmd.Dispose();
				adapter.Dispose();
			}
			return execResult;
		}

		
		/// <summary>
		/// 执行SQL语句，不需返回结果
		/// </summary>
		/// <param name="sqlString">SQL语句</param>
		/// <returns></returns>
		public int ExecuteSQL(string sqlString)
		{
			OleDbCommand cmd=new OleDbCommand(sqlString,connection);
			cmd.CommandType=CommandType.Text;
	
			try
			{
				connection.Open();
				execResult=cmd.ExecuteNonQuery();
			}
			catch(OleDbException e)
			{
				execResult=-1;
				throw e;
			}
			finally
			{				
				connection.Close();
				cmd.Dispose();
			}
			return execResult;
		}
		
		
		/// <summary>
		/// 执行SQL语句，不需返回结果，调用者控制事务，调用者应及时关闭连接并释放OleDbCommand资源。
		/// </summary>
		/// <param name="sqlString"></param>
		/// <param name="trans"></param>
		/// <returns></returns>
		public static int ExecuteSQL(string sqlString,OleDbTransaction trans)
		{
			int retVal;
			OleDbConnection conn=trans.Connection;
			OleDbCommand cmd=new OleDbCommand(sqlString);
			try
			{
				if (conn.State != ConnectionState.Open)
				{
					conn.Open();
				}
				cmd.Connection = conn;
				if (trans != null)
				{
					cmd.Transaction = trans;
				}			
				cmd.CommandType=CommandType.Text;	
				retVal=cmd.ExecuteNonQuery();
			}
			catch(OleDbException e)
			{
				trans.Rollback();
				retVal=-1;
				throw e;
			}
			finally
			{				
				cmd.Dispose();
			}
			return retVal;
		}


		/// <summary>
		/// 执行SQL语句集合，不需返回结果，调用者控制事务，调用者应及时关闭连接并释放OleDbCommand资源。
		/// </summary>
		/// <param name="sqlString">SQL语句集合</param>
		/// <param name="cmd">OleDbCommand</param>
		/// <returns></returns>
		public static int ExecuteSQL(string[] sqlString,OleDbTransaction trans)
		{
			int retVal=0;
			OleDbCommand cmd = new OleDbCommand();
			OleDbConnection conn=trans.Connection;
			if (conn.State != ConnectionState.Open)
			{
				conn.Open();
			}
			if (trans != null)
			{
				cmd.Transaction = trans;
			}
			cmd.Connection = conn;

			try
			{
				foreach (string sql in sqlString)
				{
					cmd.CommandText=sql;
					retVal=cmd.ExecuteNonQuery();					
				}
			}
			catch(OleDbException e)
			{
				trans.Rollback();
				retVal=-1;
				throw e;
			}
			finally
			{				
				cmd.Dispose();
			}
			return retVal;
		}


		/// <summary>
		/// 执行SQL语句集合，不需返回结果，Helper类的实例控制事务。
		/// </summary>
		/// <param name="sqlString">SQL语句集合</param>
		/// <returns></returns>
		public int ExecuteSQL(string[] sqlString)
		{
			OleDbCommand cmd=new OleDbCommand();
			cmd.CommandType=CommandType.Text;
			cmd.Connection=connection;
					
			try
			{
				connection.Open();
				trans=connection.BeginTransaction();

				foreach (string sql in sqlString)
				{
					cmd.Transaction=trans;
					cmd.CommandText=sql;
					execResult=cmd.ExecuteNonQuery();					
				}

				trans.Commit();
			}
			catch(OleDbException e)
			{
				string error=
					"Source: " + e.Source + "\n" +
					"Message: " + e.Message ;

				trans.Rollback();
				execResult=-1;
				throw new Exception(error);
			}
			finally
			{				
				connection.Close();
				cmd.Dispose();
			}
			return execResult;
		}
		#endregion

		#region 得到需要执行Insert操作的Sql语句（集）

		/// <summary>
		/// 得到添加数据库记录的sql语句集，需要保证内存表的字段与数据库表的字段拼写一致。
		/// </summary>
		/// <param name="dt">内存表。</param>
		/// <param name="tableName">数据库表名。</param>
		/// <returns>成功返回sql语句集合，否则返回null。</returns>		
		public static string[] GetInsertSql(DataTable dt,string tableName )
		{
			if(dt==null)
			{
				return null;
			}

			int rowCount=dt.Rows.Count;
			if(rowCount < 1)
			{
				return null;
			}
			
			int columnCount=dt.Columns.Count;
			string[] sqlString=new string[rowCount];
			DataRow row;

			for(int i= 0 ;i< rowCount;i++)													
			{
				row=dt.Rows[i];
				string insString=GetInsertSql(row,tableName);			
				sqlString.SetValue(insString.ToString(),i);
			}
			return sqlString;
		}

		
		/// <summary>
		/// 得到添加数据库记录的sql语句，需要保证内存表的字段与数据库表的字段拼写一致。
		/// </summary>
		/// <param name="row">内存表中的一条记录。</param>
		/// <param name="tableName">对应的数据库表名。</param>
		/// <returns>sql语句。</returns>