也学DBHelper

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