多种数据库操作


using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;using System.Data.OleDb;
using System.Data.SqlClient;
//using System.Data.OracleClient;   //由于Oracle没有安装，在此舍去Oracle的调试namespace MyWeb
{
    /// <summary>
    /// 用于判断数据库类型的名称枚举
    /// </summary>
    public enum DataBaseType
    {
        SqlServer,
        Access,
        Oracle
    }    /// <summary>
    /// 访问数据库
    /// </summary>
    public class MyDBHelper
    {
        #region 创建Connection，Command，DataAdapter实例对象        /// <summary>
        /// 创建并初始化Connection连接对象实例
        /// </summary>
        /// <param name="dbType">数据提供程序类型</param>
        /// <returns>返回一个已经创建好的数据库连接对象实例</returns>
        public static IDbConnection CreateConnection(DataBaseType dbType)
        {
            //声明一个IDbConnection对象，用以返回不同的连接对象实例
            IDbConnection con;
            //根据枚举值来判断创建何种类型的数据库连接实例
            switch (dbType)
            {
                case DataBaseType.Access:
                    con = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString);
                    break;
                //case DataBaseType.Oracle:
                //    con = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString);
                //    break;
                default:
                    con = new SqlConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString);
                    break;
            }
            return con;
        }        /// <summary>
        /// 创建并初始化Command对象实例
        /// </summary>
        /// <param name="cmdText">SQL查询字符串或存储过程名臣</param>
        /// <param name="dbType">数据提供程序类型</param>
        /// <param name="con">数据库连接实例</param>
        /// <returns>返回一个已经创建好的Command实例对象</returns>
        public static IDbCommand CreateCommand(string cmdText, DataBaseType dbType, IDbConnection con)
        {
            //声明一个IDbCommand对象，用以返回不同的连接对象实例
            IDbCommand cmd;
            //根据枚举值来判断创建何种类型的数据库命令对象实例
            switch (dbType)
            {
                case DataBaseType.Access:
                    cmd = new OleDbCommand(cmdText, (OleDbConnection)con);
                    break;
                //case DataBaseType.Oracle:
                //    cmd = new OracleConnection(cmdText, (OracleConnection)con);
                //    break;
                default:
                    cmd = new SqlCommand(cmdText, (SqlConnection)con);
                    break;
            }
            return cmd;
        }        /// <summary>
        /// 创建并初始化一个DataAdapter对象实例
        /// </summary>
        /// <param name="cmd">Command实例对象</param>
        /// <param name="dbType">数据提供程序类型</param>
        /// <returns>返回一个已经创建好的IDbDataAdapter对象</returns>
        public static IDbDataAdapter CreateDataAdapter(IDbCommand cmd, DataBaseType dbType)
        {
            //声明一个IDbDataAdapter对象，用以返回不同的DataAdapter对象实例
            IDbDataAdapter Adapter;
            //根据枚举值来判断创建何种类型的DataAdapter对象实例
            switch (dbType)
            {
                case DataBaseType.Access:
                    Adapter = new OleDbDataAdapter((OleDbCommand)cmd);
                    break;
                //case DataBaseType.Oracle:
                //    Adapter = new OracleDataAdapter((OracleCommand)cmd);
                //    break;
                default:
                    Adapter = new SqlDataAdapter((SqlCommand)cmd);
                    break;
            }
            return Adapter;
        }
        #endregion        #region 私有成员变量
        private IDbConnection con;
        private IDbCommand cmd;
        private DataSet ds;
        private IDbDataAdapter Adapter;
        private DataBaseType dbType = (DataBaseType)Enum.Parse(typeof(DataBaseType), ConfigurationManager.AppSettings["dbType"].ToString());
        #endregion        #region 执行数据库操作语句        public MyDBHelper()
        {
            con = CreateConnection(dbType); 
            ds = new DataSet();
        }        /// <summary>
        /// 执行增删改操作，UID是CUID中的
        /// </summary>
        /// <param name="cmdText">执行操作的SQL字符串</param>
        public void ExecuteUID(string cmdText)
        {
            try
            {
                cmd = CreateCommand(cmdText, dbType, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }        /// <summary>
        /// 执行增删改操作，UID是CUID中的
        /// </summary>
        /// <param name="cmdText">执行操作的SQL字符串</param>
        /// <param name="parameter">参数</param>
        public void ExecuteUID(string cmdText,IDbDataParameter[] parameter)
        {
            try
            {
                cmd = CreateCommand(cmdText, dbType, con);
                foreach (IDbDataParameter p in parameter)
                {
                    cmd.Parameters.Add(p);
                }
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }        /// <summary>
        /// 执行查询操作，C代表CUID中的C
        /// </summary>
        /// <param name="cmdText">查询SQL语句</param>
        /// <returns>返回DataSet对象</returns>
        public DataSet ExecuteC(string cmdText)
        {
            cmd = CreateCommand(cmdText, dbType, con);
            Adapter = CreateDataAdapter(cmd, dbType);
            Adapter.Fill(ds);
            return ds;
        }        #endregion
    }
}
注：Web.config文件：
<appSettings>
   <add key="dbType" value="SqlServer"/>
    <!--这里可选值必须是SqlServer或者Access在或者是Oracle，大小写不能改变-->
</appSettings>
<connectionStrings>     <add name="AccessConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=你的Access数据库文件路径; User Id=用户名;Password=用户密码"/>
    <add name="SqlServerConnectionString" connectionString="Server=IP地址;DataBase=数据库文件名称;UID=数据库登录名;Pwd=数据库登录密码" />
</connectionStrings>