ADOHelper一些ADO.NET相关的通用扩展

之前的博客里面已经有这个ADOHelper的一个简易版本（注：此扩展不是SqlHelper），然后这两天看到有位同志在博客园贴了个简易的拼接Update Sql方法，想想自己以前也写过一个版本，但因为上份工作离职后，未将此部分代码保留，所以又将这些代码重写了一遍，并做了一些相关扩展，该部分扩展不适用于所有关系型数据库(ACCESS就不适用，可以进行参数扩展使之适用，只是个人不打算这么做)，但至少适用于MSSQL，Oracle，MySql，以下是相关代码

    using System.Data;
    using System.Data.Common;
    /// <summary>
    /// 数据相关的帮助类
    /// </summary>
    public static class ADOHelper
    {
        /// <summary>
        /// 从DataReader中读取可空对象
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="dr">IDataReader</param>
        /// <param name="key">Key</param>
        /// <returns></returns>
        public static Nullable<T> GetNullable<T>(this IDataRecord dr, string key)
            where T : struct
        {
            return dr[key] == null || dr[key] == DBNull.Value ? (Nullable<T>)null : (T)dr[key];
        }
        /// <summary>
        /// 当DataReader读取对象为空时，返回defaultValue
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="dr">IDataReader</param>
        /// <param name="key">Key</param>
        /// <param name="defaultValue">当取到的数据为DBNull时，应当返回的默认值</param>
        /// <returns></returns>
        public static T GetDefaultWhileNullable<T>(this IDataRecord dr, string key, T defaultValue = default(T))
        {
            return dr[key] == null || dr[key] == DBNull.Value ? defaultValue : (T)dr[key];
        }
        /// <summary>
        /// 从DataReader中读取字符串并消除前后空白字符
        /// </summary>
        /// <param name="dr">IDataReader</param>
        /// <param name="key">Key</param>
        /// <returns></returns>
        public static string GetTrimedString(this IDataRecord dr, string key)
        {
            return dr[key].ToString().Trim();
        }
        /// <summary>
        /// 将数组转换为IN对应的SQL（返回值不包含小括号）,返回格式@P1,@P2,@P3
        /// </summary>
        /// <typeparam name="T1">数据类型</typeparam>
        /// <typeparam name="T2">Sql参数类型</typeparam>
        /// <param name="items"></param>
        /// <param name="parameters"></param>
        /// <param name="key"></param>
        /// <param name="dbType"></param>
        /// <param name="paramterPrefix"></param>
        /// <returns></returns>
        public static string GetInSql<T1, T2>(this IEnumerable<T1> items, out IList<T2> parameters, string key = "p", DbType? dbType = null, char paramterPrefix = '@')
            where T2 : DbParameter, new()
        {
            if (items == null || !items.Any())
            {
                throw new ArgumentNullException();
            }
            StringBuilder tmp = new StringBuilder();
            parameters = new List<T2>();
            int i = 0;
            foreach (T1 item in items)
            {
                string k = string.Format("{0}{1}{2}", paramterPrefix, key, i);
                tmp.Append(',');
                tmp.Append(k);

                T2 param = new T2();
                param.ParameterName = k;
                param.Value = item;
                if (dbType.HasValue)
                {
                    param.DbType = dbType.Value;
                }
                parameters.Add(new T2() { ParameterName = k, Value = item });
                i++;
            }
            tmp.Remove(0, 1);
            return tmp.ToString();
        }
        /// <summary>
        /// 如果当前值为null，则返回DbNull，以防止Sql参数不被传递的问题
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static object GetDbNullWhileNull(this object obj)
        {
            if (obj == null)
            {
                return DBNull.Value;
            }
            return obj;
        }
        /// <summary>
        /// 获取简单的更新Sql
        /// </summary>
        /// <typeparam name="T">Sql参数类型</typeparam>
        /// <param name="tableName">关系型数据库表名</param>
        /// <param name="updateDic">要更新的字段集合</param>
        /// <param name="keyDic">where条件集合，可传null值,此时表示更新所有数据</param>
        /// <param name="parameters">与返回Sql对应的DbParameter集合</param>
        /// <param name="paramterPrefix">Sql参数前缀</param>
        /// <returns></returns>
        public static string GetUpdateSql<T>(this string tableName, IDictionary<string, object> updateDic, IDictionary<string, object> keyDic, out IList<T> parameters, char paramterPrefix = '@')
            where T : DbParameter, new()
        {
            if (string.IsNullOrWhiteSpace(tableName) || updateDic == null || updateDic.Count == 0)
            {
                throw new ArgumentNullException();
            }
            StringBuilder sql = new StringBuilder();
            parameters = new List<T>();
            sql.Append("UPDATE ");
            sql.AppendLine(tableName);
            sql.AppendLine("SET");
            Action<IDictionary<string, object>, IList<T>, string> act = (dic, list, joinStr) =>
            {
                foreach (var kv in dic)
                {
                    sql.Append(kv.Key);
                    sql.Append('=');
                    sql.Append(paramterPrefix);
                    sql.Append(kv.Key);
                    sql.Append(joinStr);

                    T param = new T();
                    param.ParameterName = string.Format("{0}{1}", paramterPrefix, kv.Key);
                    param.Value = kv.Value.GetDbNullWhileNull();
                    //param.DbType可以在方法执行结束后根据索引获取再进行设置
                    list.Add(param);//在匿名方法、lambda 表达式或查询表达式的内部不能使用 ref 或 out 参数,所以要声明list
                }
            };
            act(updateDic, parameters, ",");
            sql.Remove(sql.Length - 1, 1);
            if (keyDic != null && keyDic.Count > 0)
            {
                sql.AppendLine();
                sql.AppendLine("WHERE");
                act(keyDic, parameters, " AND ");
                sql.Remove(sql.Length - 5, 5);
            }
            return sql.ToString();
        }
    }

拼接Sql扩展对应的测试代码

            IDictionary<string, object> updateDic = new Dictionary<string, object>()
            {
                {"FieldInt",1},
                {"FieldString","test"},
                {"FieldDateTime",DateTime.Now}
            };
            IDictionary<string, object> keyDic = null;
            IList<SqlParameter> parameter;
            string tableName = "TableName";
            string sql = tableName.GetUpdateSql(updateDic, keyDic, out parameter);
            Console.WriteLine(sql);
            keyDic = new Dictionary<string, object>()
            {
                {"KeyInt",99},
                {"KeyString","55"}
            };
            Console.WriteLine("###################################");
            sql = tableName.GetUpdateSql(updateDic, keyDic, out parameter, ':');
            Console.WriteLine(sql);

            Console.WriteLine("--------------------------------");
            IEnumerable<int> arr = Enumerable.Range(1, 10);
            sql = arr.GetInSql(out parameter, paramterPrefix: ':');
            Console.WriteLine(sql);