也学DBHelper

来源：互联网发布：表单制作软件编辑：程序博客网时间：2024/05/18 00:59
SQLHelper、DBHelper是ADO.NET学习的必经之路，虽然以后更多的是由EF来帮我们写SQL语句。
下面是学的时候练习把常规版改造成的接口版。以后可以把ConnectionString放到数据库或Config中；并且现在在CreateConnection时还留有new MySqlConnection这个耦合，parameters为了减少耦合也绕了个弯用KVP来传递，感觉最好是用IOC改造一下，但以我目前的水平还没有思路。
    class DBHelper//MySql版    {        private static readonly string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;        private static void AddKvpToCmd(IDbCommand cmd, params KeyValuePair<string, object>[] parameters)        {            foreach (KeyValuePair<string, object> kvp in parameters)            {                IDbDataParameter p = cmd.CreateParameter();                p.ParameterName = kvp.Key;                p.Value = kvp.Value;                cmd.Parameters.Add(p);            }        }        public static IDbConnection CreateConnection(string connstr)        {            IDbConnection conn = new MySqlConnection(connstr);            conn.Open();            return conn;        }        public static IDbConnection CreateConnection()        {            IDbConnection conn = new MySqlConnection(connstr);            conn.Open();            return conn;        }        public static int ExecuteNonQuery(IDbConnection conn, string sql, params KeyValuePair<string, object>[] parameters)        {            using (IDbCommand cmd = conn.CreateCommand())            {                cmd.CommandText = sql;                AddKvpToCmd(cmd, parameters);                return cmd.ExecuteNonQuery();            }        }        /// <summary>        /// 在SQLServer中必须把BeginTransaction返回的对象赋值给SqlCommand的Transaction属性；MySQLServer中可以省略这步。        /// </summary>        /// <param name="conn"></param>        /// <param name="sql"></param>        /// <param name="tx"></param>        /// <param name="parameters"></param>        /// <returns></returns>        public static int ExecuteNonQuery(IDbConnection conn, string sql, IDbTransaction tx, params KeyValuePair<string, object>[] parameters)        {            using (IDbCommand cmd = conn.CreateCommand())            {                cmd.CommandText = sql;                cmd.Transaction = tx;                AddKvpToCmd(cmd, parameters);                return cmd.ExecuteNonQuery();            }        }        public static int ExecuteNonQuery(string sql, params KeyValuePair<string, object>[] parameters)        {            using (IDbConnection conn = CreateConnection())            {                return ExecuteNonQuery(conn, sql, parameters);            }        }        /// <summary>        /// 在SQLServer中必须把BeginTransaction返回的对象赋值给SqlCommand的Transaction属性；MySQLServer中可以省略这步。        /// </summary>        /// <param name="sql"></param>        /// <param name="tx"></param>        /// <param name="parameters"></param>        /// <returns></returns>        public static int ExecuteNonQuery(string sql, IDbTransaction tx, params KeyValuePair<string, object>[] parameters)        {            using (IDbConnection conn = CreateConnection())            {                return ExecuteNonQuery(conn, sql, tx, parameters);            }        }        public static object ExecuteScalar(IDbConnection conn, string sql, params KeyValuePair<string, object>[] parameters)        {            using (IDbCommand cmd = conn.CreateCommand())            {                cmd.CommandText = sql;                AddKvpToCmd(cmd, parameters);                return cmd.ExecuteScalar();            }        }        /// <summary>        /// 在SQLServer中必须把BeginTransaction返回的对象赋值给SqlCommand的Transaction属性；MySQLServer中可以省略这步。        /// </summary>        /// <param name="conn"></param>        /// <param name="sql"></param>        /// <param name="tx"></param>        /// <param name="parameters"></param>        /// <returns></returns>        public static object ExecuteScalar(IDbConnection conn, string sql, IDbTransaction tx, params KeyValuePair<string, object>[] parameters)        {            using (IDbCommand cmd = conn.CreateCommand())            {                cmd.CommandText = sql;                cmd.Transaction = tx;                AddKvpToCmd(cmd, parameters);                return cmd.ExecuteScalar();            }        }        public static object ExecuteScalar(string sql, params KeyValuePair<string, object>[] parameters)        {            using (IDbConnection conn = CreateConnection())            {                return ExecuteScalar(conn, sql, parameters);            }        }        /// <summary>        /// 在SQLServer中必须把BeginTransaction返回的对象赋值给SqlCommand的Transaction属性；MySQLServer中可以省略这步。        /// </summary>        /// <param name="sql"></param>        /// <param name="tx"></param>        /// <param name="parameters"></param>        /// <returns></returns>        public static object ExecuteScalar(string sql, IDbTransaction tx, params KeyValuePair<string, object>[] parameters)        {            using (IDbConnection conn = CreateConnection())            {                return ExecuteScalar(conn, sql, tx, parameters);            }        }        public static DataTable ExecuteQuery(IDbConnection conn, string sql, params KeyValuePair<string, object>[] parameters)        {            DataTable table = new DataTable();            using (IDbCommand cmd = conn.CreateCommand())            {                cmd.CommandText = sql;                AddKvpToCmd(cmd, parameters);                using (IDataReader reader = cmd.ExecuteReader())                {                    table.Load(reader);                }            }            return table;        }        public static DataTable ExecuteQuery(string sql, params KeyValuePair<string, object>[] parameters)        {            using (IDbConnection conn = CreateConnection())            {                return ExecuteQuery(conn, sql, parameters);            }        }        /// <summary>        /// 批量插入数据        /// </summary>        /// <param name="conn"></param>        /// <param name="sql">Insert into... Values后面留空</param>        /// <param name="paraValues">object[有几组][组内排列序号，必须与SQL语句中字段顺序一致]</param>        /// <returns></returns>        public static int BatchInsert(IDbConnection conn, string sql, object[][] paraValues)        {            StringBuilder placeholder = new StringBuilder('a');            StringBuilder sqlbuilder = new StringBuilder(sql);            List<KeyValuePair<string, object>> kvplist = new List<KeyValuePair<string, object>>();            foreach (object[] group in paraValues)            {                sqlbuilder.Append('(');                foreach (object p in group)                {                    kvplist.Add(new KeyValuePair<string, object>(placeholder.ToString(), p));                    sqlbuilder.Append('@').Append(placeholder.ToString());                    if (p != group[group.Length - 1])                    {                        sqlbuilder.Append(',');                    }                    AlphabetDecimalCarrier(placeholder);                }                sqlbuilder.Append(')');                if (group != paraValues[paraValues.Length - 1])                {                    sqlbuilder.Append(',');                }            }            return ExecuteNonQuery(conn, sqlbuilder.ToString(), kvplist.ToArray());        }        /// <summary>        /// 谜之简短参数名生成方法        /// </summary>        /// <param name="sb"></param>        /// <returns></returns>        public static StringBuilder AlphabetDecimalCarrier(StringBuilder sb)        {            int carry = sb.Length - 1;            while (true)            {                if (sb[carry] == 'z')                {                    sb[carry] = 'a';                    if (carry == 0)                    {                        sb.Append('a');                        return sb;                    }                    carry--;                }                else                {                    sb[carry]++;                    return sb;                }            }        }    }
阅读全文
0 0