整理了一个多数据库通用操作类

DataBase.cs

 

using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

public enum MyDbType
{
    MSSQL,
    ORACLE,
    MYSQL,
    ACCESS
}
public class DataBase
{
    private DbConnection cnn;//抽象类型
    private DbCommand cmd;//抽象类型
    private DbProviderFactory provider;
    private string providerName;
    private string connectionString;
    public DataBase() : this(MyDbType.MSSQL)
    {
    }
    public DataBase(MyDbType dbType)
    {
        providerName = ConfigurationManager.ConnectionStrings[dbType.ToString()].ProviderName;
        provider = DbProviderFactories.GetFactory(providerName);
        cnn = provider.CreateConnection();
        cnn.ConnectionString = ConfigurationManager.ConnectionStrings[dbType.ToString()].ConnectionString;
        cmd = provider.CreateCommand();
        cmd.Connection = cnn;
    }
    #region 执行不带参数的SQL语句
    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// </summary>
    public int ExecuteSQL(string sql)
    {
        return ExecuteSQL(sql, null);
    }
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    public int ExecuteSqlTran(List<string> sqlList)
    {
        int count = -1;
        cnn.Open();
        DbTransaction tx = cnn.BeginTransaction();
        try
        {
            cmd.Transaction = tx;
            for (int n = 0; n < sqlList.Count; n++)
            {
                string strsql = sqlList[n].ToString();
                if (strsql.Trim().Length > 1)
                {
                    cmd.CommandText = strsql;
                    count = cmd.ExecuteNonQuery();
                }
            }
            tx.Commit();
        }
        catch (SqlException e)
        {
            tx.Rollback();
            cnn.Close();
            throw new Exception(e.Message);
        }
        return count;
    }

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    public int ExecuteScalar(string sql)
    {
        return ExecuteScalar(sql, null);
    }
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    public DataSet GetDataSet(string sql)
    {
        return GetDataSet(sql, null);
    }
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    public DataTable GetDataTable(string sql)
    {
        return GetDataSet(sql).Tables[0];
    }
    /// <summary>
    /// 执行查询语句，返回DataReader(使用该方法切记要手工关闭DataReader和连接)
    /// </summary>
    public DbDataReader ExecuteReader(string sql)
    {
        return ExecuteReader(sql, null);
    }
    #endregion
    

    #region 执行带参数的SQL语句
    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// </summary>
    public int ExecuteSQL(string sql, params DbParameter[] cmdParms)
    {
        try
        {
            CreateCommand(sql, cmdParms);
            int rows = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return rows;
        }
        catch (SqlException e)
        {
            cnn.Close();
            throw new Exception(e.Message);
        }
    }
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    public int ExecuteSqlTran(Hashtable sqlList)
    {
        int count = -1;
        cnn.Open(); 
        DbTransaction tx = cnn.BeginTransaction();
        try
        {
            cmd.Transaction = tx;
            foreach (DictionaryEntry myDE in sqlList)
            {
                string cmdText = myDE.Key.ToString();
                DbParameter[] cmdParms = (DbParameter[])myDE.Value;
                CreateCommand(cmdText, cmdParms);
                count = cmd.ExecuteNonQuery();                
            }
            tx.Commit();
        }
        catch (SqlException e)
        {
            tx.Rollback();
            cnn.Close();
            throw new Exception(e.Message);
        }
        return count;
    }

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    public int ExecuteScalar(string sql, params DbParameter[] cmdParms)
    {
        try
        {
            CreateCommand(sql, cmdParms);
            object o = cmd.ExecuteScalar();
            return int.Parse(o.ToString());
        }
        catch (SqlException e)
        {
            cnn.Close();
            throw new Exception(e.Message);
        }
    }
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    public DataSet GetDataSet(string sql, params DbParameter[] cmdParms)
    {
        DataSet ds = new DataSet();
        try
        {
            CreateCommand(sql, cmdParms);
            DbDataAdapter adapter = provider.CreateDataAdapter();
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);
        }
        catch (SqlException e)
        {
            cnn.Close();
            throw new Exception(e.Message);
        }
        return ds;
    }
    /// <summary>
    /// 执行查询语句，返回DataTable
    /// </summary>
    public DataTable GetDataTable(string sql, params DbParameter[] cmdParms)
    {
        return GetDataSet(sql, cmdParms).Tables[0];
    }
    /// <summary>
    /// 执行查询语句，返回DataReader(使用该方法切记要手工关闭DataReader和连接)
    /// </summary>
    public DbDataReader ExecuteReader(string sql, params DbParameter[] cmdParms)
    {
        try
        {
            CreateCommand(sql, cmdParms);
            DbDataReader myReader = cmd.ExecuteReader();
            return myReader;
        }
        catch (SqlException e)
        {
            cnn.Close();
            throw new Exception(e.Message);
        }
    }
    public DbParameter MakeParam(string ParamName, DbType DbType, Int32 Size, object Value)
    {
        DbParameter Param = cmd.CreateParameter();
        Param.ParameterName = ParamName;
        Param.DbType = DbType;
        if(Size > 0)
            Param.Size = Size;
        if (Value != null)
            Param.Value = Value;
        return Param;
    }
    private DbCommand CreateCommand(string cmdText, DbParameter[] Prams)
    {
        return CreateCommand(CommandType.Text, cmdText, Prams);
    }
    private DbCommand CreateCommand(CommandType cmdType, string cmdText, DbParameter[] Prams)
    {
        if (cnn.State != ConnectionState.Open)
            cnn.Open();
        cmd.CommandType = cmdType;
        cmd.CommandText = cmdText;
        if (Prams != null)
        {
            cmd.Parameters.Clear();
            foreach (DbParameter Parameter in Prams)
                cmd.Parameters.Add(Parameter);
        }
        return cmd;
    }
    public DataSet GetDataSetByProc(string ProcName, DbParameter[] Params)
    {
        cnn.Open();
        DbCommand cmd = CreateCommand(CommandType.StoredProcedure, ProcName, Params);
        DbDataAdapter adapter = provider.CreateDataAdapter();
        adapter.SelectCommand = cmd;
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        cnn.Close();
        return ds;
    }
    #endregion

}

 

Web.config

<appSettings/>
    <connectionStrings>
        <add name="MSSQL" connectionString="Data Source=localhost;Initial Catalog=forum;Integrated Security=True" providerName="System.Data.SqlClient"/>
        <add name="ACCESS" connectionString="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|db1.mdb" providerName="System.Data.OleDb"/>
        <add name="MYSQL" connectionString="Data Source=192.168.0.2;Initial Catalog=test;Persist Security Info=True;User ID=user1;Password=pass1" providerName="MySql.Data.MySqlClient"/>
    </connectionStrings>
    <system.data>
        <DbProviderFactories>
            <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
        </DbProviderFactories>
    </system.data>
</appSettings/>