[转]强壮的DBHelper(7)

Version7.0:解决了实例化的问题。

    public class DBHelper
    {
        //在配置文件中,连接字符串应该如下编写:
        //server=.;uid=sa;pwd=;database=testDB;min pool size=4;max pool size=40;
        readonly static string cnnString=System.Configuration.ConfigurationManager.ConnectionStrings["SqlServerConntionString"].ConnectionString;
        public static 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 static 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 static 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 static 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;
            }
        }
    }

    基本上已经完事了,能改进的地方在于,把方法内部添加上using块。

    这些就自己加吧。

    土鳖不扛铁牛。

posted @ 2011-03-02 00:11  愤怒的熊猫  阅读(162)  评论(0)    收藏  举报