C#数据库通用连接类

using System;
using System.Data ;
using System.Data.SqlClient ;
using System.Data.SqlTypes ;
using System.Windows.Forms ;
using System.Collections;

namespace Database
{

 /// <summary>
 /// Database 的摘要说明。
 /// </summary>

    public class Database
    {

    /// <summary>
    /// 属性
    /// </summary>
    //  public DataSet dataSet
    //  {
    //      get
    //      {
    //      return m_DataSet;
    //      } 
    //  }

    public Database()
    {

    //
    // TODO: 在此处添加构造函数逻辑
    //

        XmlRead ConStr=new XmlRead();
        if (ConStr.ReadAllConnectNode())
        {
        constr= ConStr.connstring ;

        // try
        // { 
        //  Open();
        // }
        // catch(Exception Ex)
        // {
        //  MessageBox.Show("数据库连接错误"+Ex.ToString () );  
        // }

        }
        else
        {
        constr="-1";
        //throw new SqlErrorCollection();

        } 
    }

    // public bool Open()
    //  {
    //  mcn.ConnectionString = constr;
    //  try
    //  {
    //   mcn.Open();
    //  }
    //  catch( Exception)
    //  {
    //   return  false;
    //  }
    //  return true;
    //  }

    /// <summary>
    /// 默认获取DataSet
    /// </summary>
    /// <param name="pMyTableName"></param>
    /// <param name="tmpMyComputerName"></param>
    /// <returns></returns>

    //  public virtual int getData (string pMyTableName ,string tmpMyComputerName)
    //  {
    //  return -1;
    //  }

    #region ExecuteNonQuery

    /// <summary>
    ///  执行一个SQL Command(使用ConnectString)
    /// </summary>
    ///     <param name="connString">ConnectString（Sql连接字符串）</param>
    ///     <param name="cmdType">Command类型</param>
    ///     <param name="cmdText">Command的语句（SQL语句）</param>
    ///     <param name="cmdParms">Command的参数（SqlParameter[]数组类型）</param>
    ///     <returns>Command的返回值（受影响的行数）</returns>
 
    public  int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        using (SqlConnection conn = new SqlConnection(connString))
       {
        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
        int val = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        return val;
       }
    }

    /// <summary>
    ///     执行一个SQL Command(使用隐含的ConnectString)
    /// </summary>
    /// <param name="cmdType">Command类型</param>
    ///     <param name="cmdText">Command的语句（SQL语句）</param>
    ///     <param name="cmdParms">Command的参数（SqlParameter[]数组类型）</param>
    ///     <returns>Command的返回值（受影响的行数）</returns>
 
    public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        using (SqlConnection conn = new SqlConnection(constr))
        {
        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
        int val = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        return val;
        }
    }

    // public static int ExecuteNonQuery(string cmdText)
    //  {
    //  }
 
    /// <summary>
    /// 执行一个简单的查询, 只需要输入SQL语句, 一般用于更新或者删除
    /// </summary>
    /// <param name="sqlText"></param>
    /// <returns></returns>

    public int ExecuteNonQuery(string sqlText)
    {
        return ExecuteNonQuery(CommandType.Text,sqlText);
    }

    /// <summary>
    ///     执行一个SQL Command(使用SqlTransaction)
    /// </summary>
    /// <param name="trans">使用的SqlTransaction</param>
    ///     <param name="cmdType">Command类型</param>
    ///     <param name="cmdText">Command的语句（SQL语句）</param>
    ///     <param name="cmdParms">Command的参数（SqlParameter[]数组类型）</param>
    ///     <returns>Command的返回值（受影响的行数）</returns>
 
    public  int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType,string cmdText, params SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
        int val = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        return val;
    }
 
    /// <summary>
    ///     根据指定DsCommandType类型，自动生成cmd执行dataset的更新
    /// </summary>
    /// <param name="connString">ConnectString（Sql连接字符串）</param>
    ///     <param name="cmdType">Command类型</param>
    ///     <param name="dsCommandType">Enum类型</param>
    ///     <param name="cmdText">Command的语句（SQL语句）</param>
    ///     <param name="dataset">dataset</param>
    ///     <param name="tablename">表名</param>
    ///     <param name="cmdParms">Command的参数（SqlParameter[]数组类型）</param>
    ///     <returns>是否更新成功</returns>

    public bool ExecuteNonQuery(string connString,CommandType cmdType,CommandEnum.DsCommandType dsCommandType,string cmdText,DataSet dataset,string tablename,params SqlParameter[] cmdParms)
    {
        SqlDataAdapter dsCommand = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        using (SqlConnection conn = new SqlConnection(connString))
        {
            if (conn.State != ConnectionState.Open)
            conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                 foreach (SqlParameter parm in cmdParms)
                 cmd.Parameters.Add(parm);
            }

            switch(dsCommandType)
            {
                case CommandEnum.DsCommandType.InsertCommand:
                dsCommand.InsertCommand = cmd;
                break;

                case CommandEnum.DsCommandType.UpdateCommand:
                dsCommand.UpdateCommand = cmd;
                break;

                case CommandEnum.DsCommandType.DeleteCommand:
                dsCommand.DeleteCommand = cmd;
                break;

                default:break;
            }

            dsCommand.Update(dataset,tablename);
            if ( dataset.HasErrors )
            {
                 dataset.Tables[tablename].GetErrors()[0].ClearErrors();
                 return false;
            }
            else
            {
                 dataset.AcceptChanges();
                 return true;
            }
        }
    }

    /// <summary>
    ///     更新一个记录集(使用connString)
    /// </summary>
    ///     <param name="connString">ConnectString（Sql连接字符串）</param>
    ///     <param name="cmdInsertType">commandInsert类型</param>
    ///     <param name="cmdInsertText">SQL语句（Insert）</param>
    ///     <param name="cmdUpdateType">commandUpdate类型</param>
    ///     <param name="cmdUpdateText">SQL语句（Update）</param>
    ///     <param name="cmdInsertType">commandDelete类型</param>
    ///     <param name="cmdDeleteText">SQL语句（Delete）</param>
    ///     <param name="cmdInsertParms">InsertCommand参数</param>
    ///     <param name="cmdUpdateParms">UpdateCommand参数</param>
    ///     <param name="cmdDeleteParms">DeleteCommand参数</param>
    ///     <param name="dataset">dataset</param>
    ///     <param name="tablename">表名</param>
    ///     <returns>是否更新成功</returns> 

    public  bool UpdateDataset(string connString,CommandType cmdInsertType,string cmdInsertText,CommandType cmdUpdateType,string cmdUpdateText,CommandType cmdDeleteType,string cmdDeleteText,SqlParameter[] cmdInsertParms,SqlParameter[] cmdUpdateParms,SqlParameter[] cmdDeleteParms,DataSet dataset,string tablename)
    {
        SqlDataAdapter dsCommand = new SqlDataAdapter();
        using (SqlConnection conn = new SqlConnection(connString))
        {
            if (conn.State != ConnectionState.Open)
            conn.Open();

            if(cmdInsertText != String.Empty)
            {
                SqlCommand cmdInsert = new SqlCommand();
                cmdInsert.Connection = conn;
                cmdInsert.CommandText = cmdInsertText;
                cmdInsert.CommandType = cmdInsertType;
                if (cmdInsertParms != null)
                {
                    foreach (SqlParameter parm in cmdInsertParms)
                    cmdInsert.Parameters.Add(parm);
                }
                dsCommand.InsertCommand = cmdInsert;
            }

            if(cmdUpdateText != String.Empty)
            {
                 SqlCommand cmdUpdate = new SqlCommand();
                 cmdUpdate.Connection = conn;
                 cmdUpdate.CommandText = cmdUpdateText;
                 cmdUpdate.CommandType = cmdUpdateType;
                 if (cmdUpdateParms != null)
                 {
                      foreach (SqlParameter parm in cmdUpdateParms)
                      cmdUpdate.Parameters.Add(parm);
                 }
                 dsCommand.UpdateCommand = cmdUpdate;
            }

            if(cmdDeleteText != String.Empty)
            {
                SqlCommand cmdDelete = new SqlCommand();
                cmdDelete.Connection = conn;
                cmdDelete.CommandText = cmdDeleteText;
                cmdDelete.CommandType = cmdDeleteType;
                if (cmdDeleteParms != null)
                {
                    foreach (SqlParameter parm in cmdDeleteParms)
                    cmdDelete.Parameters.Add(parm);
                }
                dsCommand.DeleteCommand = cmdDelete;
            }

            if(cmdInsertText == String.Empty && cmdUpdateText == String.Empty && cmdDeleteText == String.Empty)
            {
                SqlCommandBuilder scb = new SqlCommandBuilder(dsCommand);
                return false;
            }    

            dsCommand.Update(dataset,tablename);

            if ( dataset.HasErrors )
            {
                dataset.Tables[tablename].GetErrors()[0].ClearErrors();
                return false;
            }
            else
            {
                dataset.AcceptChanges();
                return true;
            }
        }
    }

    #endregion
    #region ExecuteReader

    /// <summary>
    ///     获取一个SqlDataReader(使用connString)
    /// </summary>
    ///     <param name="connString">ConnectString</param>
    ///     <param name="cmdType">类型</param>
    ///     <param name="cmdText">Command的语句(select语句)</param>
    ///     <param name="cmdParms">Command的参数</param>
    ///     <returns>所需要的SqlDataReader</returns>

    public SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = new SqlConnection(connString);

        try
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }
        catch
        {
            conn.Close();
            throw;
        }
    }

    /// <summary>
    /// 获取一个SqlDataReader(使用connString), 使用缺省的ConnectionString
    /// </summary>
    ///     <param name="cmdType">类型</param>
    ///     <param name="cmdText">Command的语句(select语句)</param>
    ///     <param name="cmdParms">Command的参数</param>
    ///  <returns>SqlDataReader</returns>

    public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = new SqlConnection(constr);

        try
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }
        catch
        {
            conn.Close();
            throw;
        }
    }

    /// <summary>
    ///  获取一个SqlDataReader, 使用缺省的ConnectionString
    /// </summary>
    /// <param name="cmdtxt">语句命令</param>
    /// <returns></returns>

    public SqlDataReader ExecuteReader(string cmdtxt)
    {
  
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = new SqlConnection(constr);

        try
        {
            cmd=new SqlCommand(cmdtxt,conn);
            conn.Open();
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
   
            return rdr;
        }
        catch
        {
            conn.Close();
            throw;
        }
    }

    #endregion
    #region private函数

    /// <summary>
    /// 准备一个Command(使用SqlParameter[]数组)
    /// </summary>

    private void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
    {
        if (conn.State != ConnectionState.Open)
        {
            try
            {
                conn.Open();
            }
            catch(Exception Ex)
            {
                throw Ex;
                //string a = Ex.ToString();
                //return;
            }  
        }
        cmd.Connection = conn;
        cmd.CommandText = cmdText;

        if (trans != null)
        cmd.Transaction = trans;

        cmd.CommandType = cmdType;

        if (cmdParms != null)
        {
            foreach (SqlParameter parm in cmdParms)
            cmd.Parameters.Add(parm);
        }
    }

    private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameterCollection cmdParms)
    {
        if (conn.State != ConnectionState.Open)
        conn.Open();

        cmd.Connection = conn;
        cmd.CommandText = cmdText;

        if (trans != null)
        cmd.Transaction = trans;

        cmd.CommandType = cmdType;

        if (cmdParms != null)
        {
            foreach (SqlParameter parm in cmdParms)
            cmd.Parameters.Add(parm);
        }
    }

    /// <summary>
    /// 加入一个以字段名为名称的param
    /// </summary>
    /// <param name="fld"></param>
    /// <returns></returns>

    private  SqlParameter NewFieldParam(string fld)
    {
        SqlParameter param = new SqlParameter();
        param.ParameterName = "@" + fld;
        param.SourceColumn = fld;
        return param;
    }

    /// <summary>
    /// 判断字符是否在一个集合中
    /// </summary>
    /// <param name="str"></param>
    /// <param name="ExcludeFields"></param>
    /// <returns></returns>

    private bool InColleciton(string str,IList ExcludeFields)
    {
        foreach(string s in ExcludeFields)
        {
            if(s.ToUpper()==str.ToUpper())
            return true;
        }
        return false;
    }

    #endregion
    #region 填充DataSet

    /// <summary>
    ///     将数据填充到DataSet中(无connString)
    /// </summary>
    ///     <param name="cmdType">类型</param>
    ///     <param name="cmdText">Command的语句</param>
    ///     <param name="tablename">表名</param>
    ///     <param name="cmdParms">Command的参数</param>

    public void FillData(CommandType cmdType,string cmdText,DataSet dataset,string tablename,params SqlParameter[] cmdParms)
    {
        SqlDataAdapter dsCommand = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        dsCommand.SelectCommand = cmd;

        //dsCommand.TableMappings.Add("Table",tablename);
        using (SqlConnection conn = new SqlConnection(constr))
        {
        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
        dsCommand.Fill(dataset,tablename);
        }
    }

    /// <summary>
    ///     将数据填充到DataSet中(使用connString + SqlParameterCollection)
    /// </summary>
    ///     <param name="connString">ConnectString</param>
    ///     <param name="cmdType">类型</param>
    ///     <param name="cmdText">Command的语句</param>
    ///     <param name="tablename">表名</param>
    ///     <param name="cmdParms">Command的参数(SqlParameterCollection)</param>

    public  void FillDataEx(string connString, CommandType cmdType,string cmdText,DataSet dataset,string tablename,SqlParameterCollection cmdParms)
    {
        SqlDataAdapter dsCommand = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        dsCommand.SelectCommand = cmd;
        dsCommand.TableMappings.Add("Table",tablename);
        using (SqlConnection conn = new SqlConnection(connString))
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            dsCommand.Fill(dataset);
        }
    }

    #endregion

    internal  string constr= null; 
    
    //= "Uid =sa ;Pwd=sa ;Server = Drago;Database =Northwind";
    internal SqlConnection mcn = new SqlConnection();
    internal DataSet m_DataSet =new System.Data.DataSet() ;
    }
}