数据库操作基本方法实现

一、获取数据库连接和基本的信息

1定义接口

namespace Com.Mycompany.Data
{

     public interface IDbSetting
       {
	string ConnectionString
	{
		get;
	}
	DbServerType DbServerType
	{
		get;
	}

        string COID
        {
            get;
        }
        string UserNO
        {
            get;
        }…………

    }
}

2继承接口

namespace Com.Mycompany.Web.WebUI
{

    public class MyDBSetting : IDbSetting
    {

        public MyDBSetting(string language, UserInformation user)
        {

        }

        /// <summary>
        /// 数据连接串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                if (HttpContext.Current.Application["ConnectionString"] == null)
                {
                    if (ConfigurationManager.ConnectionStrings["Conncetion"] == null)
                    {
                        throw new Exception(KDCommon.DataConnectionInfo);
                    }
                    else
                    {
                        HttpContext.Current.Application["ConnectionString"] = ConfigurationManager.ConnectionStrings["Conncetion"].ToString();
                    }
                }
                return HttpContext.Current.Application["ConnectionString"].ToString();
            }
        }

        /// <summary>
        /// 数据库类型
        /// </summary>
        public DbServerType DbServerType
        {
            get
            {
                return DbServerType.SqlServer;
            }
        }

        /// <summary>
        /// 当前公司coid
        /// </summary>
        public string COID
        {
            get
            {
                SessionTimeOut("coid");
                return HttpContext.Current.Session["coid"].ToString(); 
            }
        }
        public string UserNO
        {
            get
            {
                SessionTimeOut("loginuserno");
                return HttpContext.Current.Session["loginuserno"].ToString(); 
            }
        }
…………
    }
}

 3封装接口

namespace Com.Mycompany.Common
{

	public class BaseInfo
	{
		private static IDbSetting s_dbSetting = null;
		public static IDbSetting DbSetting
		{
			get
			{
				return s_dbSetting;
			}
                                     set
                                    {
                                                 BaseInfo.s_dbSetting = value;
                                     }
		}

		public BaseInfo()
		{

		}
               }
…………
}

 

二、数据库基本操作

 

 /// <summary>
 /// 访问数据库对象（辅助工具）
 /// </summary>
 /// <remarks></remarks>
 public sealed class DbUtils

 

1、创建连接

 

/// <summary>
/// 数据连接串
/// </summary>
	public static string ConnectionString
	{
		get
		{
			if( _connectionString == null || _connectionString.Length == 0)
			{
				return BaseInfo.DbSetting.ConnectionString;
			}
			else
			{
				return _connectionString;
			}
		}

		set
		{
			_connectionString =value;
		}
	}

	/// <summary>
	/// 数据服务器类型
	/// </summary>
	public static DbServerType DbServerType
	{
		get
		{
			return BaseInfo.DbSetting.DbServerType;
		}
	}

        /// <summary>
        /// 产品id号        /// </summary>
        public static string COID
        {
            get
            {
                return BaseInfo.DbSetting.COID;
            }
        }

        /// <summary>
        /// 用户编号
        /// </summary>
        public static string UserNO
        {
            get
            {
                return BaseInfo.DbSetting.UserNO;
            }
        }…………

 3、执行select查询语句

		/// <summary>
		/// 执行查询命令
		/// </summary>
		/// <param name="commandString">Sql命令</param>
		/// <param name="datareader">输出DataReader</param>
		public static void RunSelectCommand(
			string connectionString,
			string commandString,
			out IDataReader datareader
			) 
		{
			IDbCommand __command;
			OleDbConnection conn = GetConnection(connectionString);		
			__command = new OleDbCommand(commandString, conn);
			__command.CommandTimeout = 0; //设置等待命令执行的时间为无限期


			if(__command.Connection.State != ConnectionState.Open)
			{
				__command.Connection.Open();
			}
			datareader = __command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

			__command.Dispose();//释放__command
			
		}     
        /// <summary>
        /// 传递连接对象，用于建立临时表的操作，不能每次新建连接对象（否则临时表每次SQL执行完后会自动删除）
        /// </summary>
        /// <param name="commandString">Sql命令</param>
        /// <param name="dataTable">输出数据表</param>
        /// <param name="prams">查询命令参数列表</param>
        public static void RunSelectCommand(
            OleDbConnection conn,
            string commandString,
            out DataTable dataTable,
            params IDbDataParameter[] prams
            )
        {
            IDbDataAdapter __dataadapter;

            DataSet __dataSet;

            __dataadapter = new OleDbDataAdapter(commandString, conn);
            __dataadapter.SelectCommand.CommandTimeout = 0;//设置等待命令执行的时间为无限期

            if (prams != null)
            {
                foreach (IDbDataParameter pram in prams)
                {
                    //if (pram.DbType == DbType.String)
                    //{
                    //    pram.Value = ReplaceSQLEscape(pram.Value);
                    //}
                    __dataadapter.SelectCommand.Parameters.Add(pram);
                }
            }

            __dataSet = new DataSet();
            try
            {
                __dataadapter.Fill(__dataSet);

                dataTable = __dataSet.Tables[0];
            }
            catch (Exception e)
            {
                throw new Exception(commandString, e);
            }

        }

 4、执行存储过程

		#region RunStoredProcedure without out datatable

		public static void RunStoredProcedure(
			string connString,
			string procName
			)
		{
			using(OleDbConnection conn = GetConnection(connString))
			{
				try
				{
					IDbCommand command = conn.CreateCommand();
					command.CommandText = procName;
					command.CommandType = CommandType.StoredProcedure;
					command.CommandTimeout = 0;//设置等待命令执行的时间为无限期

					command.ExecuteNonQuery();
					command.Dispose();
				}
				catch(Exception ex)
				{
					throw new Exception(procName + ":" + ex.Message);
				}
				finally
				{
					conn.Dispose();
				}
			}
		}
		public static void RunStoredProcedure(
			string connString,
			string procName,			
			params IDbDataParameter[] paramsArray)
		{
			using(OleDbConnection conn = GetConnection(connString))
			{
				try
				{
					IDbCommand command = conn.CreateCommand();
					command.CommandText = procName;
					command.CommandType = CommandType.StoredProcedure;
					command.CommandTimeout = 0;//设置等待命令执行的时间为无限期

					foreach(IDbDataParameter param in paramsArray)
					{
						command.Parameters.Add(param);
					}

					command.ExecuteNonQuery();
					command.Dispose();
				}
				catch(Exception ex)
				{
					throw new Exception(procName + ":" + ex.Message);
				}
				finally
				{
					conn.Dispose();
				}
			}
		}
		#endregion

		#region RunStoredProcedure with out datatable
		public static void RunStoredProcedure(
			string connString,
			string procName,			
			out DataTable dataTable)
		{
			OleDbDataAdapter adapter;
			DataSet dataSet;

			using(OleDbConnection conn = GetConnection(connString))
			{
				try
				{
					dataSet = new DataSet();
					IDbCommand command = conn.CreateCommand();
					command.CommandText = procName;
					command.CommandType = CommandType.StoredProcedure;
					command.CommandTimeout = 0;//设置等待命令执行的时间为无限期

					adapter = new OleDbDataAdapter((OleDbCommand)command);
					adapter.Fill(dataSet);
					adapter.Dispose();
					command.Dispose();

					dataTable = dataSet.Tables[0];
				}
				catch(Exception ex)
				{
					throw new Exception(procName + ":" + ex.Message);
				}
				finally
				{
					conn.Dispose();
				}
			}
		}
		public static void RunStoredProcedure(
			string connString,
			string procName,			
			out DataTable dataTable,
			params IDbDataParameter[] paramsArray)
		{
			OleDbDataAdapter adapter;
			DataSet dataSet;
            dataTable = null;
			using(OleDbConnection conn = GetConnection(connString))
			{
				try
				{
					dataSet = new DataSet();
					IDbCommand command = conn.CreateCommand();
					command.CommandText = procName;
					command.CommandType = CommandType.StoredProcedure;
					command.CommandTimeout = 0;//设置等待命令执行的时间为无限期

					foreach(IDbDataParameter param in paramsArray)
					{
						command.Parameters.Add(param);
					}

					adapter = new OleDbDataAdapter((OleDbCommand)command);
					adapter.Fill(dataSet);
					adapter.Dispose();
					command.Dispose();

                    if (dataSet != null && dataSet.Tables.Count > 0)
                    {
                        dataTable = dataSet.Tables[0];
                    }
				}
				catch(Exception ex)
				{
					throw new Exception(procName + ":" + ex.Message);
				}
				finally
				{
					conn.Dispose();
				}
			}
		}
		#endregion

 5、执行sql语句，返回影响数据行

  //返回值表示操作影响的记录数
		public static int RunCommand(
			string connectionString,
			string commandString,			
			params IDbDataParameter[] prams
			) 
		{
			IDbCommand __command;
            int __rtnRowCount = -1;
			using(OleDbConnection conn = GetConnection(connectionString))
			{
				__command = new OleDbCommand(commandString,conn);
				__command.CommandTimeout = 0; //设置等待命令执行的时间为无限期

				if(prams != null)
				{
					foreach(IDbDataParameter pram in prams)
					{
						__command.Parameters.Add(pram);
					}
				}
				
				if(__command.Connection.State != ConnectionState.Open)
				{
					__command.Connection.Open();
				}
                __rtnRowCount = __command.ExecuteNonQuery();

				__command.Dispose();
			}

            return __rtnRowCount;

		}


        /// <summary>
        /// 传递连接对象，用于建立临时表的操作，不能每次新建连接对象（否则临时表每次SQL执行完后会自动删除）
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="commandString"></param>
        /// <param name="prams"></param>
        public static int RunCommand(
            OleDbConnection conn,
            string commandString,
            params IDbDataParameter[] prams
            )
        {
            IDbCommand __command;
            int __rtnRowCount = -1;
            __command = new OleDbCommand(commandString, conn);
            __command.CommandTimeout = 0; //设置等待命令执行的时间为无限期

            if (prams != null)
            {
                foreach (IDbDataParameter pram in prams)
                {
                    __command.Parameters.Add(pram);
                }
            }

            if (__command.Connection.State != ConnectionState.Open)
            {
                __command.Connection.Open();
            }
            __rtnRowCount=__command.ExecuteNonQuery();

            __command.Parameters.Clear();

            return __rtnRowCount;

        }