[转]强壮的DBHelper（5）

Version5.0：这次将连接放入每个方法中，现场产生，现场使用。

    public class DBHelper
    {
        string cnnString=@"server=.;uid=sa;pwd=;database=testDB;";
        public int ExecuteNonQuery(string procName, SqlParameter[] ps)
        {
            SqlConnection cnn = new SqlConnection(cnnString);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Parameters.AddRange(ps);

            cnn.Open();
            int x = cmd.ExecuteNonQuery();
            cnn.Close();
            return x;
        }

        public DataTable GetTable(string procName, SqlParameter[] ps)
        {
            SqlConnection cnn = new SqlConnection(cnnString);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Parameters.AddRange(ps);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public object GetScalar(string procName, SqlParameter[] ps)
        {
            SqlConnection cnn = new SqlConnection(cnnString);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Parameters.AddRange(ps);

            cnn.Open();
            object obj = cmd.ExecuteScalar();
            cnn.Close();
            return obj;
        }

        public int ExecuteNonQuery(string[] procNames, SqlParameter[][] ps)
        {
            SqlConnection cnn = new SqlConnection(cnnString);
            SqlTransaction tran = cnn.BeginTransaction();
            SqlCommand[] cmds=new SqlCommand[procNames.Length];
            for (int i = 0; i < procNames.Length; i++)
            {
                cmds[i] = cnn.CreateCommand();
                cmds[i].CommandType = CommandType.StoredProcedure;
                cmds[i].CommandText = procNames[i];
                cmds[i].Parameters.AddRange(ps[i]);
                cmds[i].Transaction = tran;
            }

            try
            {
                for (int i = 0; i < cmds.Length; i++)
                {
                    cmds[i].ExecuteNonQuery();
                }
                tran.Commit();
                return 0;
            }
            catch (Exception)
            {
                tran.Rollback();
                return -1;
            }
        }
    }

    这个版本的问题在于：当调用方法的频率稍高的时候，就会报“连接池已满”的错误。这个问题比较好处理，可以在连接字符串中添加连接池的支持。

    还有连接字符串的问题，基本上是写死到程序中了，可以考虑写入配置文件中。

    土鳖扛铁牛。