一個支持SqlClient和OleDb的ADO.NET常用操作通用類

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
/*
***************************************
*                                                                           *
*作者:GhostBear                                               *
*博客:http://blog.csdn.net/ghostbear  *   
*                                                                           *
***************************************
*/
namespace AdoNetOmnipotence
{
    /// <summary>
    /// 操縱Ado.net的通用類（sql和oledb）
    /// </summary>
    public class Omnipotence
    {
        bool isSqlClient = true;//判斷調用sqlclient還是oledb
        string connstr;//連接字符串
        SqlConnection sconn = null;
        OleDbConnection oconn = null;
        /// <summary>
        /// 初始化字符串
        /// </summary>
        /// <param name="connectionstring">連接字符串</param>
        public Omnipotence(string connectionstring)
        {
            this.connstr = connectionstring;
        }
        /// <summary>
        /// 指定連接字符串和操縱的目標
        /// </summary>
        /// <param name="connectionstring">連接字符串</param>
        /// <param name="issqlClient">操縱的目標</param>
        public Omnipotence(string connectionstring, bool issqlClient)
        {
            this.connstr = connectionstring;
            this.isSqlClient = issqlClient;
        }
        /// <summary>
        /// 打開連接
        /// </summary>
        private void Open()
        {
            if (isSqlClient)
            {
                if (sconn == null)
                    sconn = new SqlConnection(connstr);
                if (sconn.State == ConnectionState.Closed)
                    sconn.Open();
            }
            else
            {
                if (oconn == null)
                    oconn = new OleDbConnection(connstr);
                if (oconn.State == ConnectionState.Closed)
                    oconn.Open();
            }
        }
        /// <summary>
        /// 關閉連接
        /// </summary>
        private void Close()
        {
            if (isSqlClient)
            {
                if (sconn != null)
                    if (sconn.State == ConnectionState.Open)
                        sconn.Close();
            }
            else
            {
                if (oconn != null)
                    if (oconn.State == ConnectionState.Open)
                        oconn.Close();
            }
        }
            
        /*
***************************************
*                                                                           *
*作者:GhostBear                                               *
*博客:http://blog.csdn.net/ghostbear  *   
*                                                                           *
***************************************
*/
        /// <summary>
        /// 返回command對象，可以是sqlcommand或oledbcommand
        /// </summary>
        /// <param name="sqlcmd">命令</param>
        /// <param name="isStored">是否存儲過程</param>
        /// <param name="param">參數</param>
        /// <returns>一個裝箱的command對象</returns>
        private object command(string sqlcmd, bool isStored, IDataParameter[] param)
        {
            if (isSqlClient)
            {
                SqlCommand cmd = new SqlCommand(sqlcmd);    //初始化command
                cmd.Connection = sconn;                     //賦予連接對象
                if (isStored)
                    cmd.CommandType = CommandType.StoredProcedure;  //賦予命令類型
                else
                    cmd.CommandType = CommandType.Text;
                if (param != null)
                {
                    foreach (SqlParameter t in param)
                    {
                        cmd.Parameters.Add(t);          //參數數據庫不為空則添加參數
                    }
                }
                return cmd;                             //返回sqlcommand
            }
            else
            {
                OleDbCommand cmd = new OleDbCommand(sqlcmd);    //初始化command
                cmd.Connection = oconn;                     //賦予連接對象
                if (isStored)
                    cmd.CommandType = CommandType.StoredProcedure;  //賦予命令類型
                else
                    cmd.CommandType = CommandType.Text;
                if (param != null)
                {
                    foreach (OleDbParameter t in param)
                    {
                        cmd.Parameters.Add(t);          //參數數據庫不為空則添加參數
                    }
                }
                return cmd;                             //返回Oledbcommand
            }
        }
        /// <summary>
        /// 返回dataadapt對象
        /// </summary>
        /// <param name="command">command對象</param>
        /// <returns>一個裝箱的dataadapter對象</returns>
        private object adapter(object command)  //返回DataAdpter類型的對象
        {
            object adpt;
            if (isSqlClient)
            {
                adpt = new SqlDataAdapter((SqlCommand)command);
            }
            else
            {
                adpt = new OleDbDataAdapter((OleDbCommand)command);
            }
            return adpt;
        }
/// <summary>
/// 執行操作。返回影響的行數
/// </summary>
/// <param name="sqlcmd">sql語句或存儲過程名</param>
/// <param name="isStored">是否為存儲過程</param>
/// <returns>語句執行影響的行數</returns>
/*
***************************************
*                                                                           *
*作者:GhostBear                                               *
*博客:http://blog.csdn.net/ghostbear  *   
*                                                                           *
***************************************
*/
        public void RunProc(string sqlcmd, bool isStored,out int infectionRows)
        {
            SqlCommand scmd;
            OleDbCommand ocmd;
            Open();
            if (isSqlClient)
            {
                scmd = (SqlCommand)command(sqlcmd, isStored, null);
                infectionRows = scmd.ExecuteNonQuery();
                Close();
            }
            else
            {
                ocmd = (OleDbCommand)command(sqlcmd, isStored, null);
                infectionRows = ocmd.ExecuteNonQuery();
                Close();
            }
            return;
        }
        /// <summary>
        /// 執行操作。返回影響的行數
        /// </summary>
        /// <param name="sqlcmd">sql語句或存儲過程名</param>
        /// <param name="isStored">是否為存儲過程</param>
        /// <param name="param">執行所需要的參數</param>
        /// <returns>操作影響的行數</returns>
        public void RunProc(string sqlcmd, bool isStored, IDataParameter[] param,out int infectionRows)
        {
            SqlCommand scmd;
            OleDbCommand ocmd;
            Open();
            if (isSqlClient)
            {
                scmd = (SqlCommand)command(sqlcmd, isStored, param);
                 
                infectionRows=scmd.ExecuteNonQuery();
                Close();
            }
            else
            {
                ocmd = (OleDbCommand)command(sqlcmd, isStored, param);
                 
                infectionRows=ocmd.ExecuteNonQuery();
                Close();
            }
            return;
        }
            
        /*
***************************************
*                                                                           *
*作者:GhostBear                                               *
*博客:http://blog.csdn.net/ghostbear  *   
*                                                                           *
***************************************
*/
        /// <summary>
        /// 返回數據集合中第一行第一列中的值
        /// </summary>
        /// <param name="sqlcmd">sql語句或存儲過程名</param>
        /// <param name="isStored">是否存儲過程</param>
        /// <param name="isReturnContetn">返回值</param>
        /// <returns></returns>
        public void RunProc(string sqlcmd, bool isStored,out object isReturnContent)
        {
            SqlCommand scmd;
            OleDbCommand ocmd;
            Open();
            if (isSqlClient)
            {
                scmd = (SqlCommand)command(sqlcmd, isStored, null);
                 
                isReturnContent = scmd.ExecuteScalar();
                Close();
            }
            else
            {
                ocmd = (OleDbCommand)command(sqlcmd, isStored, null);
                  
                isReturnContent = ocmd.ExecuteScalar();
                Close();
            }
            return;
        }
        /// <summary>
        /// 返回數據集合中第一行第一列中的值
        /// </summary>
        /// <param name="sqlcmd">sql語句或存儲過程名</param>
        /// <param name="isStored">是否存儲過程</param>
        /// <param name="param">參數集合</param>
        /// <param name="isReturnContent">返回值</param>
        /// <returns></returns>
        public void RunProc(string sqlcmd, bool isStored, IDataParameter[] param, out object 

isReturnContent)
        {
            SqlCommand scmd;
            OleDbCommand ocmd;
            Open();
            if (isSqlClient)
            {
                scmd = (SqlCommand)command(sqlcmd,isStored,param);
                   
                isReturnContent = scmd.ExecuteScalar();
                Close();
            }
            else
            {
                ocmd = (OleDbCommand)command(sqlcmd, isStored, param);
                 
                isReturnContent = ocmd.ExecuteScalar();
                Close();
            }
            return;
        }
        /*
***************************************
*                                                                           *
*作者:GhostBear                                               *
*博客:http://blog.csdn.net/ghostbear  *   
*                                                                           *
***************************************
*/

        /// <summary>
        /// 返回sqldatareader對象
        /// </summary>
        /// <param name="sqlcmd">sql語句或存儲過程</param>
        /// <param name="isStored">是否存儲過程</param>
        /// <param name="reader">輸出sqldatareader的 out參數</param>
        public void RunProc(string sqlcmd, bool isStored,out SqlDataReader reader)
        {
            SqlCommand scmd;

           Open();
          scmd = (SqlCommand)command(sqlcmd, isStored, null);
          
           reader = scmd.ExecuteReader(CommandBehavior.CloseConnection);
            return;
        }
        /// <summary>
        /// 返回oledbdatareader對象
        /// </summary>
        /// <param name="sqlcmd">sql語句或存儲過程</param>
        /// <param name="isStored">是否存儲過程</param>
        /// <param name="reader">輸出oledbdatareader的 out參數</param>
    public void RunProc(string sqlcmd, bool isStored, out OleDbDataReader reader)
        {
          
            OleDbCommand ocmd;
            Open();
        
                ocmd = (OleDbCommand)command(sqlcmd, isStored, null);
            
                reader = ocmd.ExecuteReader(CommandBehavior.CloseConnection);
            
            return;
        }
        /// <summary>
        /// 返回oledbdatareader對象
        /// </summary>
        /// <param name="sqlcmd">sql語句或存儲過程</param>
        /// <param name="isStored">是否存儲過程</param>
        /// <param name="param">命令的參數</param>
        /// <param name="reader">輸出oledbdatareader的 out參數</param>
        public void RunProc(string sqlcmd, bool isStored, IDataParameter[] param, out SqlDataReader reader)

        {
            SqlCommand scmd;
            Open();
            scmd = (SqlCommand)command(sqlcmd, isStored,param);
            reader = scmd.ExecuteReader(CommandBehavior.CloseConnection);
            return;
        }
        /// <summary>
        /// 返回oledbdatareader對象
        /// </summary>
        /// <param name="sqlcmd">sql語句或存儲過程名</param>
        /// <param name="isStored">是否存儲過程</param>
        /// <param name="param">命令參數</param>
        /// <param name="reader">輸出參數</param>
        public void RunProc(string sqlcmd, bool isStored, IDataParameter[] param, out OleDbDataReader 

reader)
        {
            OleDbCommand ocmd;
            Open();
            ocmd = (OleDbCommand)command(sqlcmd, isStored, param);
            reader = ocmd.ExecuteReader(CommandBehavior.CloseConnection);
            return;
        }
            
        /*
***************************************
*                                                                           *
*作者:GhostBear                                               *
*博客:http://blog.csdn.net/ghostbear  *   
*                                                                           *
***************************************
*/
          
        /// <summary>
        /// 返回dataset對象
        /// </summary>
        /// <param name="sqlcmd">sql語句或命令</param>
        /// <param name="isStored">是否存儲過程</param>
        /// <param name="ds">輸出參數</param>
        public void RunProc(string sqlcmd, bool isStored, out DataSet ds)
        {
            SqlDataAdapter sadpt = null;
            OleDbDataAdapter oadpt = null;
            DataSet myds = new DataSet();
            if (isSqlClient)
            {
                Open();
                sadpt = (SqlDataAdapter)adapter(command(sqlcmd, isStored, null));
                    
                sadpt.Fill(myds);
                Close();
            }
            else
            {
                Open();
                oadpt = (OleDbDataAdapter)adapter(command(sqlcmd, isStored, null));
                   
                oadpt.Fill(myds);
                Close();
            }
            ds = myds;
            return;
            
        }
            
        /*
***************************************
*                                                                           *
*作者:GhostBear                                               *
*博客:http://blog.csdn.net/ghostbear  *   
*                                                                           *
***************************************
*/
        /// <summary>
        /// 返回dataset對象
        /// </summary>
        /// <param name="sqlcmd">sql語句或命令</param>
        /// <param name="isStored">是否存儲過程</param>
        /// <param name="param">參數</param>
        ///  <param name="ds">輸出dataset</param>
        public void RunProc(string sqlcmd, bool isStored, IDataParameter[] param, out DataSet ds)
        {
            SqlDataAdapter sadpt = null;
            OleDbDataAdapter oadpt = null;
            DataSet myds = new DataSet();
            if (isSqlClient)
            {
                Open();
                sadpt = (SqlDataAdapter)adapter(command(sqlcmd, isStored,param));
                sadpt.Fill(myds);
                Close();
            }
            else
            {
                Open();
                oadpt = (OleDbDataAdapter)adapter(command(sqlcmd, isStored,param));
                oadpt.Fill(myds);
                Close();
            }
            ds = myds;
            return;
        }
        
    }
}