数据库访问通用类（封装)

例一：
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace Db
{
/**//// <summary>
/// Base 的摘要说明。
/// </summary>
public class Base
{
public Base()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
protected static SqlConnection conn =new SqlConnection(ConfigurationSettings.AppSettings["dsn"]);
protected static SqlCommand cmd = new SqlCommand(strSp,conn);
protected static SqlDataAdapter da = new SqlDataAdapter();
protected static DataSet ds = new DataSet();
protected static DataView dv = new DataView();
protected static SqlDataReader dr;
protected static SqlParameter[] prams;
protected static string strSp;
protected static SqlDataReader drSelectAll(string strSp)
{
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}//返回一个SqlDataReader
protected static DataSet dsSelectAll(string strSp)
{
da.SelectCommand = new SqlCommand(strSp,conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds);
conn.Open();
try
{
da.SelectCommand.ExecuteNonQuery();
return ds;
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Dispose();
conn.Close();
}
}//返回一个SqlDataSet
protected static DataView dvSelectAll(string strSp)
{
cmd.CommandType = CommandType.StoredProcedure;
da.SelectCommand = new SqlCommand(strSp,conn);
da.Fill(ds);

conn.Open();
try
{
da.SelectCommand.ExecuteNonQuery();
dv = ds.Tables[0].DefaultView;
return dv;
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Dispose();
conn.Close();
}
}//返回一个DataView
protected static string strCmd(string strSp,SqlParameter[] prams,SqlDataReader dr)
{
CreateCmd(strSp,prams,dr);
conn.Open();
try
{
cmd.ExecuteNonQuery();
return "1";
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Dispose();
conn.Close();
}
}//返回一个数据库操作
protected static SqlCommand CreateCmd(string strSp, SqlParameter[] prams,SqlDataReader dr)
{
cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return cmd;
}//返回带参数的命令
protected static bool ExecuteSQLs()
{
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
try
{
int i = (int)cmd.ExecuteScalar();
if(i>0)
{
return true;
}
else
{
return false;
}
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Dispose();
conn.Close();
}
}//返回第一行的数据操作
}
}

——————————————————————————————————————————
示例2

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace MyCorporation.DepartMent.DataBase
{
    /// <summary>
    /// 通用数据库类
    /// </summary>
    public class DataBase
    {

        private string ConnStr = null;

        public DataBase()
        {
            ConnStr = ConfigurationSettings.AppSettings["ConnStr"];
        }

        public DataBase(string Str)
        {
            try
            {
                this.ConnStr = Str;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 返回connection对象
        /// </summary>
        /// <returns></returns>
        public SqlConnection ReturnConn()
        {
            SqlConnection Conn = new SqlConnection(ConnStr);
            Conn.Open();
            return Conn;
        }

        public void Dispose(SqlConnection Conn)
        {
            if(Conn!=null)
            {
                Conn.Close();
                Conn.Dispose();
            }
            GC.Collect();
        }

        /// <summary>
        /// 运行SQL语句
        /// </summary>
        /// <param name="SQL"></param>
        public void RunProc(string SQL)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlCommand Cmd ;
            Cmd = CreateCmd(SQL, Conn);
            try
            {
                Cmd.ExecuteNonQuery();
            }
            catch
            {
                throw new Exception(SQL);
            }
            Dispose(Conn);
            return;
        }

        /// <summary>
        /// 运行SQL语句返回DataReader
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns>SqlDataReader对象.</returns>
        public SqlDataReader RunProcGetReader(string SQL)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlCommand Cmd ;
            Cmd = CreateCmd(SQL, Conn);
            SqlDataReader Dr;
            try
            {
                Dr = Cmd.ExecuteReader(CommandBehavior.Default);
            }
            catch
            {
                throw new Exception(SQL);
            }
            //Dispose(Conn);
            return Dr;
        }

        /// <summary>
        /// 生成Command对象
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="Conn"></param>
        /// <returns></returns>
        public SqlCommand CreateCmd(string SQL, SqlConnection Conn)
        {
            SqlCommand Cmd ;
            Cmd = new SqlCommand(SQL, Conn);
            return Cmd;
        }

        /// <summary>
        /// 生成Command对象
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public SqlCommand CreateCmd(string SQL)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlCommand Cmd ;
            Cmd = new SqlCommand(SQL, Conn);
            return Cmd;
        }

        /// <summary>
        /// 返回adapter对象
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="Conn"></param>
        /// <returns></returns>
        public SqlDataAdapter CreateDa(string SQL)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlDataAdapter Da;
            Da = new SqlDataAdapter(SQL, Conn);
            return Da;
        }

        /// <summary>
        /// 运行SQL语句,返回DataSet对象
        /// </summary>
        /// <param name="procName">SQL语句</param>
        /// <param name="prams">DataSet对象</param>
        public DataSet RunProc(string SQL ,DataSet Ds)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlDataAdapter Da;
            //Da = CreateDa(SQL, Conn);
            Da = new SqlDataAdapter(SQL,Conn);
            try
            {
                Da.Fill(Ds);
            }
            catch(Exception Err)
            {
                throw Err;
            }
            Dispose(Conn);
            return Ds;
        }

        /// <summary>
        /// 运行SQL语句,返回DataSet对象
        /// </summary>
        /// <param name="procName">SQL语句</param>
        /// <param name="prams">DataSet对象</param>
        /// <param name="dataReader">表名</param>
        public DataSet RunProc(string SQL ,DataSet Ds,string tablename)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlDataAdapter Da;
            Da = CreateDa(SQL);
            try
            {
                Da.Fill(Ds,tablename);
            }
            catch(Exception Ex)
            {
                throw Ex;
            }
            Dispose(Conn);
            return Ds;
        }

        /// <summary>
        /// 运行SQL语句,返回DataSet对象
        /// </summary>
        /// <param name="procName">SQL语句</param>
        /// <param name="prams">DataSet对象</param>
        /// <param name="dataReader">表名</param>
        public DataSet RunProc(string SQL , DataSet Ds ,int StartIndex ,int PageSize, string tablename )
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlDataAdapter Da ;
            Da = CreateDa(SQL);
            try
            {
                Da.Fill(Ds, StartIndex, PageSize, tablename);
            }
            catch(Exception Ex)
            {
                throw Ex;
            }
            Dispose(Conn);
            return Ds;
        }

        /// <summary>
        /// 检验是否存在数据
        /// </summary>
        /// <returns></returns>
        public bool ExistDate(string SQL)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlDataReader Dr ;
            Dr = CreateCmd(SQL,Conn).ExecuteReader();
            if (Dr.Read())
            {
                Dispose(Conn);
                return true;
            }
            else
            {
                Dispose(Conn);
                return false;
            }
        }

        /// <summary>
        /// 返回SQL语句执行结果的第一行第一列
        /// </summary>
        /// <returns>字符串</returns>
        public string Returnvalue(string SQL)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            string result;
            SqlDataReader Dr ;
            try
            {
                Dr = CreateCmd(SQL,Conn).ExecuteReader();
                if (Dr.Read())
                {
                    result = Dr[0].ToString();
                    Dr.Close();
                }
                else
                {
                    result = "";
                    Dr.Close();
                }
            }
            catch
            {
             throw new Exception(SQL);
            }
            Dispose(Conn);
            return result;
        }

        /// <summary>
        /// 返回SQL语句第一列,第ColumnI列,
        /// </summary>
        /// <returns>字符串</returns>
        public string Returnvalue(string SQL, int ColumnI)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            string result;
            SqlDataReader Dr ;
            try
            {
                Dr = CreateCmd(SQL,Conn).ExecuteReader();
            }
            catch
            {
                throw new Exception(SQL);
            }
            if (Dr.Read())
            {
                result = Dr[ColumnI].ToString();
            }
            else
            {
                result = "";
            }
            Dr.Close();
            Dispose(Conn);
            return result;
        }

        /// <summary>
        /// 生成一个存储过程使用的sqlcommand.
        /// </summary>
        /// <param name="procName">存储过程名.</param>
        /// <param name="prams">存储过程入参数组.</param>
        /// <returns>sqlcommand对象.</returns>
        public SqlCommand CreateCmd(string procName, SqlParameter[] prams)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlCommand Cmd = new SqlCommand(procName, Conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if(parameter != null)
                    {
                        Cmd.Parameters.Add(parameter);
                    }
                }
            }
            return Cmd;
        }

        /// <summary>
        /// 为存储过程生成一个SqlCommand对象
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="prams">存储过程参数</param>
        /// <returns>SqlCommand对象</returns>
        private SqlCommand CreateCmd(string procName, SqlParameter[] prams,SqlDataReader Dr)
        {
            SqlConnection Conn;
            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            SqlCommand Cmd = new SqlCommand(procName, Conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                    Cmd.Parameters.Add(parameter);
            }
            Cmd.Parameters.Add(
            new SqlParameter("Returnvalue", SqlDbType.Int, 4,
            ParameterDirection.Returnvalue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));
            return Cmd;
        }

        /// <summary>
        /// 运行存储过程,返回.
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="prams">存储过程参数</param>
        /// <param name="dataReader">SqlDataReader对象</param>
        public void RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)
        {
            SqlCommand Cmd = CreateCmd(procName, prams, Dr);
            Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            return;
        }

        /// <summary>
        /// 运行存储过程,返回.
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="prams">存储过程参数</param>
        public string RunProc(string procName, SqlParameter[] prams)
        {
            SqlDataReader Dr;
            SqlCommand Cmd = CreateCmd(procName, prams);
            Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            if(Dr.Read())
            {
                return Dr.Getvalue(0).ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 运行存储过程,返回dataset.
        /// </summary>
        /// <param name="procName">存储过程名.</param>
        /// <param name="prams">存储过程入参数组.</param>
        /// <returns>dataset对象.</returns>
        public DataSet RunProc(string procName,SqlParameter[] prams,DataSet Ds)
        {
            SqlCommand Cmd = CreateCmd(procName,prams);
            SqlDataAdapter Da = new SqlDataAdapter(Cmd);
            try
            {
                Da.Fill(Ds);
            }
            catch(Exception Ex)
            {
                throw Ex;
            }
            return Ds;
        }

    }
}  

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
示例3

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Specialized;
using System.Configuration;

namespace IRain.Rheleven.Components
{
 /// <summary>
 /// SqlDabaProvider 的摘要说明。
 /// </summary>
 public class SqlDataProvider : IDisposable
 {
  public SqlDataProvider()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }

  #region Sql 连接
  private string connectionString = ConfigurationSettings.AppSettings["ConnectionString"] ;

  public string ConnectionString
  {
   get { return connectionString; }
   set { connectionString = value; }
  }

  private SqlConnection GetSqlConnection()
  {
   try
   { return new SqlConnection(connectionString); }
   catch
   {
    throw new ArgumentNullException( "The SqlServer Is Not Valid" );
   }
  }

  #endregion

  #region  运行存储过程
  
  /// <summary>
  ///  运行存储过程 返回存储过程返回值
  /// </summary>
  /// <param name="procedureName">存储过程名</param>
  /// <returns>存储过程返回值</returns>
  public int RunProcNonQueryReturn(string procedureName)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateCommand(sqlConnection, procedureName, null);
    cmd.ExecuteNonQuery() ;
    sqlConnection.Close();
    return (int)cmd.Parameters["ReturnValue"].Value;
   }
  }

  /// <summary>
  /// 运行存储过程 返回存储过程返回值
  /// </summary>
  /// <param name="procedureName">存储过程名</param>
  /// <param name="parameter">存储过程参数</param>
  /// <returns>存储过程返回值</returns>
  public int RunProcNonQueryReturn(string procedureName, SqlParameter[] parameter)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateCommand(sqlConnection, procedureName, parameter);
    cmd.ExecuteNonQuery() ;
    sqlConnection.Close();
    return (int)cmd.Parameters["ReturnValue"].Value;
   }
  }

  public void RunProcNonQuery(string procedureName)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateCommand(sqlConnection, procedureName, null);
    cmd.ExecuteNonQuery() ;
    sqlConnection.Close();
   }
  }
  
  /// <summary>
  /// 运行存储过程 获取存储过程输出参数值
  /// </summary>
  /// <param name="procedureName">存储过程名</param>
  /// <param name="parameter">存储过程参数</param>
  public void RunProcNonQuery(string procedureName, SqlParameter[] parameter)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateCommand(sqlConnection, procedureName, parameter);
    cmd.ExecuteNonQuery() ;
    sqlConnection.Close();
   }
  }

  /// <summary>
  /// 运行存储过程
  /// </summary>
  /// <param name="procedureName">存储过程名</param>
  /// <returns>返回第一条记录第一个</returns>
  public int RunProcScalar(string procedureName)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateCommand(sqlConnection,procedureName,null);
    int tmp;
    tmp = (int)cmd.ExecuteScalar();
    sqlConnection.Close();
    return tmp;
   }
  }

  /// <summary>
  /// 运行存储过程
  /// </summary>
  /// <param name="procedureName">存储过程名</param>
  /// <param name="parameter">存储过程参数</param>
  /// <returns>返回第一条记录第一个</returns>
  public int RunProcScalar(string procedureName, SqlParameter[] parameter)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateCommand(sqlConnection,procedureName,parameter);
    int tmp;
    tmp = (int)cmd.ExecuteScalar();
    sqlConnection.Close();
    return tmp;
   }
  }

  /// <summary>
  /// 运行存储过程并返回 DataReader
  /// </summary>
  /// <param name="procedureName">存储过程名</param>
  /// <returns>返回一个新的 SqlDataReader 对象</returns>
  public SqlDataReader RunProcReader(string procedureName)
  {
   //using ( SqlConnection sqlConnection = GetSqlConnection() )
   //{
   SqlConnection sqlConnection = GetSqlConnection();
   SqlCommand cmd = CreateCommand(sqlConnection, procedureName, null);
   return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
   //}
  }

  /// <summary>
  /// 运行存储过程并返回 DataReader
  /// </summary>
  /// <param name="procedureName">存储过程名</param>
  /// <param name="parameter">存储过程参数</param>
  /// <returns>返回一个新的 SqlDataReader 对象</returns>
  public SqlDataReader RunProcReader(string procedureName, SqlParameter[] parameter)
  {
   //using ( SqlConnection sqlConnection = GetSqlConnection() )
   //{
   SqlConnection sqlConnection = GetSqlConnection();
   SqlCommand cmd = CreateCommand(sqlConnection, procedureName, parameter);
   return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
   //}
  }
  
  /// <summary>
  /// 运行存储过程返回DataSet
  /// </summary>
  /// <param name="procedureName">存储过程名</param>
  /// <returns>返回DataSet</returns>
  public DataSet RunProcDataSet(string procedureName)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateCommand(sqlConnection,procedureName,null);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet dataSet = new DataSet();
    da.Fill(dataSet);
    sqlConnection.Close();
    return dataSet;
   }
  }

  /// <summary>
  /// 运行存储过程返回DataSet
  /// </summary>
  /// <param name="procedureName">存储过程名称</param>
  /// <param name="parameter">存储过程参数</param>
  /// <returns>返回DataSet</returns>
  public DataSet RunProcDataSet(string procedureName, SqlParameter[] parameter)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateCommand(sqlConnection,procedureName,parameter);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet dataSet = new DataSet();
    da.Fill(dataSet);
    sqlConnection.Close();
    return dataSet;
   }
  }

  /// <summary>
  /// 创建一个SqlCommand对象以此来执行存储过程
  /// </summary>
  /// <param name="sqlConnection">sql链接</param>
  /// <param name="procedureName">存储过程名称</param>
  /// <param name="parameter">存储过程参数</param>
  /// <returns>返回SqlCommand对象</returns>
  private SqlCommand CreateCommand(SqlConnection sqlConnection, string procedureName, SqlParameter[] parameter)
  {
   if ( sqlConnection.State == System.Data.ConnectionState.Closed )
    sqlConnection.Open();
   SqlCommand command = new SqlCommand(procedureName,sqlConnection);
   command.CommandType = CommandType.StoredProcedure;

   if ( parameter != null )
   {
    foreach( SqlParameter param in parameter)
    {
     command.Parameters.Add( param );
    }
   }

   /// 加入返回参数
   command.Parameters.Add( new SqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,String.Empty,DataRowVersion.Default,null) );

   return command ;
  }

  #endregion

  #region  生成存储过程参数
  ///  示例
  ///  SqlDataProvider myData = new SqlDataProvider();
  ///  SqlParameter[] prams = { myData.CreateInParam("@ID",SqlDbType.Int,4,1),
  ///         myData.CreateOutParam("@OutParam",SqlDbType.Int,4)
  ///        }

  /// <summary>
  /// 生成存储过程参数
  /// </summary>
  /// <param name="parameterName">存储过程名称</param>
  /// <param name="dataType">参数类型</param>
  /// <param name="size">参数大小</param>
  /// <param name="parameterDirection">参数方向</param>
  /// <param name="parameterValue">参数值</param>
  /// <returns>新的 parameter 对象</returns>
  public SqlParameter CreateParam(string parameterName, SqlDbType dataType, Int32 size, ParameterDirection parameterDirection, object parameterValue)
  {
   SqlParameter parameter ;

   if ( size > 0 )
    parameter = new SqlParameter(parameterName, dataType, size);
   else
    parameter = new SqlParameter(parameterName, dataType);

   parameter.Direction = parameterDirection ;
   if ( !( parameterDirection == ParameterDirection.Output && parameterValue == null ) )
    parameter.Value = parameterValue ;

   return parameter ;
  }

  /// <summary>
  /// 传入输入参数
  /// </summary>
  /// <param name="parameterName">存储过程名称</param>
  /// <param name="dataType">参数类型</param>
  /// <param name="size">参数大小</param>
  /// <param name="parameterValue">参数值</param>
  /// <returns>新的 parameter 对象</returns>
  public SqlParameter CreateInParam(string parameterName, SqlDbType dataType, Int32 size, object parameterValue)
  {
   return CreateParam(parameterName, dataType, size, ParameterDirection.Input, parameterValue);
  }

  /// <summary>
  /// 传入返回值参数
  /// </summary>
  /// <param name="parameterName">存储过程名称</param>
  /// <param name="dataType">参数类型</param>
  /// <param name="size">参数大小</param>
  /// <param name="parameterValue">参数值</param>
  /// <returns>新的 parameter 对象</returns>
  public SqlParameter CreateOutParam(string parameterName, SqlDbType dataType, Int32 size)
  {
   return CreateParam(parameterName, dataType, size, ParameterDirection.Output, null);
  }

  
  #endregion

  #region  运行 SQL 语句
  
  /// <summary>
  /// 运行 SQL 语句 无返回值
  /// </summary>
  /// <param name="strSql">SQL语句</param>
  public void RunSqlNonQuery(string strSql)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateSqlCommand(sqlConnection, strSql, null);
    cmd.ExecuteNonQuery();
    sqlConnection.Close();
   }
  }

  /// <summary>
  /// 运行 SQL 语句 无返回值
  /// </summary>
  /// <param name="strSql">SQL语句</param>
  /// <param name="parameter">构建SQL语句参数</param>
  public void RunSqlNonQuery(string strSql, SqlParameter[] parameter)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateSqlCommand(sqlConnection, strSql, parameter);
    cmd.ExecuteNonQuery();
    sqlConnection.Close();
   }
  }

  /// <summary>
  /// 运行 SQL 语句
  /// </summary>
  /// <param name="strSql">SQL语句</param>
  /// <returns>返回int</returns>
  public int RunSqlScalar(string strSql)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,null);
    int tmp = (int)cmd.ExecuteScalar();
    sqlConnection.Close();
    return tmp;
   }
  }

  /// <summary>
  ///  运行 SQL 语句
  /// </summary>
  /// <param name="strSql">SQL语句</param>
  /// <param name="parameter">构建SQL语句参数</param>
  /// <returns>返回int</returns>
  public int RunSqlScalar(string strSql, SqlParameter[] parameter)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,parameter);
    int tmp = (int)cmd.ExecuteScalar();
    sqlConnection.Close();
    return tmp;
   }
  }

  /// <summary>
  /// 运行 SQL 语句 返回DataReader
  /// </summary>
  /// <param name="strSql">SQL语句</param>
  /// <returns>返回一个SqlDataReader 对象</returns>
  public SqlDataReader RunSqlReader(string strSql)
  {
   //using ( SqlConnection sqlConnection = GetSqlConnection() )
   //{
    SqlConnection sqlConnection = GetSqlConnection();
    SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,null);
    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
   //}
  }

  /// <summary>
  /// 运行 SQL 语句 返回DataReader
  /// </summary>
  /// <param name="strSql">SQL语句</param>
  /// <param name="parameter">构建SQL语句参数</param>
  /// <returns>返回一个SqlDataReader 对象</returns>
  public SqlDataReader RunSqlReader(string strSql,  SqlParameter[] parameter)
  {
   //using ( SqlConnection sqlConnection = GetSqlConnection() )
   //{
   SqlConnection sqlConnection = GetSqlConnection();
   SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,parameter);
   return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
   //}
  }

  /// <summary>
  /// 运行 SQL 语句 返回DataSet
  /// </summary>
  /// <param name="strSql">SQL语句</param>
  /// <returns>返回DataSet</returns>
  public DataSet RunSqlDataSet(string strSql)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,null);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet dataSet = new DataSet();
    da.Fill(dataSet);
    sqlConnection.Close();
    return dataSet;
   }
  }

  /// <summary>
  /// 运行 SQL 语句 返回DataSet
  /// </summary>
  /// <param name="strSql">SQL语句</param>
  /// <param name="parameter">构建SQL语句参数</param>
  /// <returns>返回DataSet</returns>
  public DataSet RunSqlDataSet(string strSql, SqlParameter[] parameter)
  {
   using ( SqlConnection sqlConnection = GetSqlConnection() )
   {
    SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,parameter);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet dataSet = new DataSet();
    da.Fill(dataSet);
    sqlConnection.Close();
    return dataSet;
   }
  }

  /// <summary>
  /// 根据SQL语句生成 SqlCommand 对象
  /// </summary>
  /// <param name="sqlConnection">SqlConnection 连接</param>
  /// <param name="strSql">SQL语句</param>
  /// <param name="parameter">构造SQL语句参数</param>
  /// <returns>返回一个新的 SqlCommand 对象</returns>
  public SqlCommand CreateSqlCommand(SqlConnection sqlConnection, string strSql, SqlParameter[] parameter)
  {
   if ( sqlConnection.State == System.Data.ConnectionState.Closed )
    sqlConnection.Open();
   SqlCommand command = new SqlCommand(strSql,sqlConnection);

   if ( parameter != null )
   {
    foreach( SqlParameter param in parameter)
    {
     command.Parameters.Add( param );
    }
   }

   return command ;
  }
  #endregion

  #region  生成 构建SQL语句 参数

  /// <summary>
  /// 根据SQL语句生成SqlParameter对象
  /// </summary>
  /// <param name="parameterName">SqlParameter名</param>
  /// <param name="dbType">SqlParameter DbType</param>
  /// <param name="size">SqlParameter 大小</param>
  /// <param name="parameterValue">SqlParameter 值</param>
  /// <returns>返回SqlParameter对象</returns>
  public SqlParameter CreateSqlParam(string parameterName, SqlDbType dbType, Int32 size, object parameterValue)
  {
   SqlParameter parameter ;
   parameter = new SqlParameter(parameterName,dbType,size);
   parameter.Value = parameterValue ;
   return parameter ;
  }
  #endregion

  #region IDisposable 成员

  public void Dispose()
  {
   // TODO:  添加 SqlDabaProvider.Dispose 实现
  }

  #endregion
 }
}