[转]强壮的DBHelper（4）

Version4.0：解决了事务的问题。

public class DBHelper
    {
        SqlConnection cnn = new SqlConnection(@"server=.;uid=sa;pwd=;database=testDB;");
        public int ExecuteNonQuery(string procName, SqlParameter[] ps)
        {
            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)
        {
            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)
        {
            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)
        {

        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;
            }
        }
    }

但是，这些方法一旦同时执行，很有可能报“连接未关闭。”的错误，原因在于多个方法使用了同一连接对象，当A客户调用ExecuteNoQuery方法进行到cnn.Open之后的时候，B客户开始调用cnn.Open，麻烦大了，这里不能使用同一个连接对象，只能使用同一个连接字符串。

土鳖扛铁牛。