数据库连接类

 

public class MSSQLDBManger
    {
        private static readonly string conn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString();

        //初始数据库
        #region
        private static void prepareCommand(MSSQLCommand Cmd, MSSQLConnection connStr, MSSQLTransaction Trans, CommandType cmdType, string cmdText, MSSQLParameter[] cmdParms)
        {
            /***************************************************************************************
              * 函数描述:为执行命令准备参数
              * 作者:    无为
              * 输入参数:MSSQLCommand cmd
              *          MSSQLConnection connStr
              *          MSSQLTransaction trans
              *          CommandType cmdType
              *          string cmdText
              *          MSSQLParameter[] cmdParms
              * 输出参数:无
              * 返回值: 无
              * 日期:    2007/04/25
              *
              ***************************************************************************************/

          try
            {             
                if (connStr.State != ConnectionState.Open)
                {
                    connStr.Open();
                }

                Cmd.CommandText = cmdText;

                //看是否要做事务处理
                if (Trans != null)
                {
                    Cmd.Transaction = Trans;
                }

                Cmd.CommandType = cmdType;
                if (cmdParms != null)
                {
                    foreach (MSSQLParameter parm in cmdParms)
                    {
                        Cmd.Parameters.Add(parm);
                    }
                }
           
            }
            catch
            {
                //如果发生错误写入日志处理
            }
        }
        #endregion

        #region 插入 修改 删除 单事务操
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, MSSQLParameter[] commandParameters)
        {
            /***************************************************************************************
             * 函数描述:执行一条不返回结果的SqlCommand，通过一个已经存在的数据库连接
             * 作者:    无为
             * 输入参数:MSSQLConnection cnn
             *          CommandType cmdType
             *          string cmdText
             *          MSSQLParameter[] cmdParms
             * 输出参数:返回一个数值表示此MSSQLCommand命令执行后影响的行数
             * 返回值: 无
             * 日期:    2007/04/25
             *
             ***************************************************************************************/
            int nCount = -1;
            using(MSSQLConnection cnn=new MSSQLConnection())
            {
                cnn.ConnectionString = conn;
                MSSQLCommand cmd=new MSSQLCommand(cmdText, cnn);
                prepareCommand(cmd, cnn, null, cmdType, cmdText, commandParameters);
                try
                {
                    nCount = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                catch(Exception ex)
                {
                    //写入日志
                    throw;
                   
                }
                finally
                {
                    if (cnn.State == ConnectionState.Open)
                    {
                        cnn.Close();
                    }
                }

            }
            return nCount;
        }
        #endregion

        #region 取得SQL的参数
        public static MSSQLParameter[] getParam(Hashtable hashPara)
        {
            MSSQLParameter[] para;
            if ((hashPara != null) && (hashPara.Count > 0))
            {
                int nIndex = 0;
                para = new MSSQLParameter[hashPara.Count];
                foreach (string strKey in hashPara.Keys)
                {
                    para[nIndex] = new MSSQLParameter(strKey, hashPara[strKey]);
                    nIndex++;
                }
                hashPara.Clear();
            }
            else
            {
                para = null;
            }
            return para;
        }
        #endregion
       public static DataTable ExecuteQuery(string cmdText, MSSQLParameter[] commandParameters)
        {
            /***************************************************************************************
            * 函数描述:执行一条返回结果集的MSSQLCommand命令，通过专用的连接字符串。
            * 作者:    无为
            * 输入参数:string cmdText
            *          SqlParameter[] cmdParms
            * 输出参数:返回一个包含结果的数据表
            * 返回值: 无
            * 日期:    2007/04/26
            *
            ****************************************************************************************/
            MSSQLConnection cnn = new MSSQLConnection();
            DataTable ds = new DataTable();
            cnn.ConnectionString = conn;
            MSSQLCommand cmd = new MSSQLCommand(cmdText, cnn);
            MSSQLDataAdapter MyDataAdapter = new MSSQLDataAdapter(cmd);
            prepareCommand(cmd, cnn, null, CommandType.Text, cmdText, commandParameters);
            try
            {
                MyDataAdapter.Fill(ds);

            }
            catch (Exception ex)
            {
                //写入日志;
                throw;
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
                cnn.Dispose();
                MyDataAdapter.Dispose();
            }
            return ds;
        }
        #endregion