Exemplo n.º 1
0
        /// <summary>
        /// 根据lambda表达式条件获取实体集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <param name="orderBy"></param>
        /// <param name="isDesc"></param>
        /// <returns></returns>
        public List <T> GetListByQueryExtensions <T>(Expression <Func <T, bool> > predicate, bool isDesc = false, string orderBy = null) where T : class
        {
            DynamicParameters paramsValue = null;
            int paramsLength;

            string where = SqlServerUtilities.GetWhereParameterized <T>(predicate, ref paramsValue, out paramsLength);
            string sql = this.ToParameterizedSqlString <T>(DbOperationType.SELECT, ref paramsValue);

            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            if (!string.IsNullOrEmpty(orderBy))
            {
                sql += " ORDER BY " + orderBy;
                if (isDesc)
                {
                    sql += " DESC";
                }
            }
            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                return(connection.Query <T>(sql, paramsValue).ToList());
            }
        }
Exemplo n.º 2
0
        /// <summary>
        /// 通过operationType将实体对象转换成参数化的Sql语句
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="operationType">数据库操作类型</param>
        /// <param name="entity">实体对象(INSERT,UPDATE时使用)</param>
        /// <param name="paramsStartIndex">参数化Sql开始的索引</param>
        /// <param name="dynamicParameters">参数列表(INSERT,UPDATE,DELETE,SELECT时使用)</param>
        /// <returns></returns>
        private string ToParameterizedSqlString <T>(DbOperationType operationType, T entity, int paramsStartIndex, ref DynamicParameters dynamicParameters)
            where T : class
        {
            Type t = typeof(T);

            MapperPropertyInfo[] mappers = SqlServerUtilities.GetMapperPropertyInfo <T>(entity);
            string[]             fields  = new string[0];

            string[] parameterized = new string[0];
            string   sql           = string.Empty;

            switch (operationType)
            {
            case DbOperationType.INSERT:
                parameterized = SqlServerUtilities.GetParameterized(mappers, paramsStartIndex, ref dynamicParameters, out fields);
                sql           = string.Format("INSERT INTO dbo.[{0}] ({1}) VALUES ({2})",
                                              t.Name,
                                              fields.ToString(p => "[" + p + "]"),
                                              parameterized.ToString(null)
                                              );
                break;

            case DbOperationType.UPDATE:
                parameterized = SqlServerUtilities.GetParameterized(mappers, paramsStartIndex, ref dynamicParameters, out fields);
                sql           = string.Format("UPDATE dbo.[{0}] SET {1}",
                                              t.Name,
                                              fields.Combine(parameterized, (p, q) => "[" + p + "]" + "=" + q).ToString(null)
                                              );
                break;

            case DbOperationType.DELETE:
                sql = string.Format("DELETE FROM dbo.[{0}]", t.Name);
                break;

            case DbOperationType.SELECT:
                fields = SqlServerUtilities.GetFields(mappers);
                sql    = string.Format("SELECT {0} FROM dbo.[{1}] (NOLOCK)",
                                       fields.ToString(p => "[" + p + "]"),
                                       t.Name
                                       );
                break;

            case DbOperationType.COUNT:
                sql = string.Format("SELECT COUNT(*) FROM dbo.[{0}] (NOLOCK)", t.Name);
                break;

            case DbOperationType.EXISTS:
                sql = string.Format("SELECT 1 FROM dbo.[{0}] (NOLOCK)", t.Name);
                break;
            }
            return(sql);
        }
Exemplo n.º 3
0
        public T Insert <T>(T entity) where T : class
        {
            DynamicParameters paramsValue = null;
            string            sql         = this.ToParameterizedSqlString <T>(DbOperationType.INSERT, entity, ref paramsValue) + ";SELECT @@IDENTITY;";

            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                object ret = connection.ExecuteScalar(sql, paramsValue);
                if (ret != null)
                {//如果返回值不是null,设置自增键的值
                    SqlServerUtilities.SetIdentityValue <T>(entity, (IConvertible)ret);
                }
            }
            return(entity);
        }
Exemplo n.º 4
0
        /// <summary>
        /// 根据lambda表达式条件获取总数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public long GetCountByQuery <T>(Expression <Func <T, bool> > condition) where T : class
        {
            DynamicParameters paramsValue = null;
            int paramsLength;

            string where = SqlServerUtilities.GetWhereParameterized <T>(condition, ref paramsValue, out paramsLength);
            string sql = this.ToParameterizedSqlString <T>(DbOperationType.COUNT, ref paramsValue);

            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                return(connection.Query <int>(sql, paramsValue).First());
            }
        }
Exemplo n.º 5
0
        public void Delete <T>(Expression <Func <T, bool> > condition) where T : class
        {
            DynamicParameters paramsValue = null;
            int paramsLength;

            string where = SqlServerUtilities.GetWhereParameterized <T>(condition, ref paramsValue, out paramsLength);
            string sql = this.ToParameterizedSqlString <T>(DbOperationType.DELETE, ref paramsValue);

            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                connection.Execute(sql, paramsValue);
            }
        }
Exemplo n.º 6
0
        public void Delete <T>(T entity) where T : class
        {
            DynamicParameters         paramsValue = null;
            List <MapperPropertyInfo> keys        = SqlServerUtilities.GetPrimaryKeys <T>(entity);

            string where = this.GetPrimaryKeysParameterizedSqlString(keys, out paramsValue);
            string sql = this.ToParameterizedSqlString <T>(DbOperationType.DELETE, ref paramsValue);

            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                connection.Execute(sql, paramsValue);
            }
        }
Exemplo n.º 7
0
        public bool IsExists <T>(Expression <Func <T, bool> > condition) where T : class
        {
            DynamicParameters paramsValue = null;
            int paramsLength;

            string where = SqlServerUtilities.GetWhereParameterized <T>(condition, ref paramsValue, out paramsLength);
            string sql = this.ToParameterizedSqlString <T>(DbOperationType.EXISTS, ref paramsValue);

            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                object ret = connection.ExecuteScalar(sql, paramsValue);
                return(ret != null);
            }
        }
Exemplo n.º 8
0
        /// <summary>
        /// 自定义IEnumerable~T的ToString方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source">源</param>
        /// <param name="func">如果源是string类型func可以为null</param>
        /// <returns></returns>
        public static string ToString <T>(this IEnumerable <T> source, Func <T, string> func)
            where T : class
        {
            SqlServerUtilities.CheckNotEmpty(source, "source");
            string str1 = string.Empty;
            string temp = string.Empty;

            foreach (var t in source)
            {
                temp = (func == null ? (t as string) : func(t));
                if (!string.IsNullOrEmpty(temp))
                {
                    str1 += temp + ",";
                }
            }
            temp = null;
            return(str1.TrimEnd(','));
        }
Exemplo n.º 9
0
        public void Delete <T>(params object[] entityIds) where T : class
        {
            List <MapperPropertyInfo> keys = SqlServerUtilities.GetPrimaryKeys <T>();
            DynamicParameters         paramsValue;

            string where = this.GetPrimaryKeysParameterizedSqlString(keys, out paramsValue);
            for (int i = 0; i < entityIds.Length; i++)
            {
                paramsValue.Add("@p" + i.ToString(), entityIds[i]);
            }
            string sql = this.ToParameterizedSqlString <T>(DbOperationType.DELETE, ref paramsValue);

            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                connection.Execute(sql, paramsValue);
            }
        }
Exemplo n.º 10
0
        public List <T> GetPagedListExtensions <T>(int offset, int limit, out int total, bool isDesc = false, Expression <Func <T, bool> > condition = null, string orderBy = null)
            where T : class
        {
            MapperPropertyInfo[] mappers = SqlServerUtilities.GetMapperPropertyInfo <T>(null);
            //fields
            string[] fields = SqlServerUtilities.GetFields(mappers);
            //tableName
            string tableName = "dbo.[" + typeof(T).Name + "]";
            //orderBy
            string order = string.IsNullOrEmpty(orderBy)
                ? string.Format("[{0}] ASC", mappers.First().Name)
                : orderBy.IndexOf("[") > -1 ? string.Format("{0} {1}", orderBy, (isDesc ? "DESC" : "ASC")) : string.Format("[{0}] {1}", orderBy, (isDesc ? "DESC" : "ASC"));
            //分页sql语句
            string sql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY " + order + " ) AS rownumber ,";

            sql += fields.ToString(p => "[" + p + "]");
            sql += " FROM " + tableName;
            //where条件
            string where = condition.ToMSSqlString();
            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            sql += " ) tmp";
            sql += " WHERE tmp.rownumber BETWEEN ( " + (offset + 1).ToString() + " ) AND ( " + (offset + limit).ToString() + " );";
            //查询total
            string sqlTotal = "SELECT COUNT(*) FROM " + tableName;

            if (!string.IsNullOrEmpty(where))
            {
                sqlTotal += " WHERE " + where;
            }
            sqlTotal += ";";
            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                List <T> list = connection.Query <T>(sql).ToList();
                total = connection.ExecuteScalar <int>(sqlTotal);
                return(list);
            }
        }
Exemplo n.º 11
0
        /// <summary>
        /// 将两个集合组合在一起,两个集合的长度必须相等
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="list1">集合1</param>
        /// <param name="list2">集合2</param>
        /// <param name="func">组合的方法</param>
        /// <returns></returns>
        public static IList <TResult> Combine <T1, T2, TResult>(this IList <T1> list1, IList <T2> list2, Func <T1, T2, TResult> func)
            where T1 : class where T2 : class where TResult : class
        {
            SqlServerUtilities.CheckNotEmpty(list1, "list1");
            SqlServerUtilities.CheckNotEmpty(list2, "list2");
            SqlServerUtilities.CheckNotEmpty(func, "func");
            if (list1.Count != list2.Count)
            {
                throw new ArgumentException("list1与list2的数量不相等");
            }
            if (list1.Count == 0)
            {
                return(null);
            }
            IList <TResult> result = new List <TResult>();

            for (int i = 0; i < list1.Count; i++)
            {
                result.Add(func(list1[i], list2[i]));
            }
            return(result);
        }
Exemplo n.º 12
0
        /// <summary>
        /// 获取主键的参数化Sql语句
        /// </summary>
        /// <param name="keys">主键列表</param>
        /// <param name="parms">如果主键有值,返回参数列表</param>
        /// <returns></returns>
        private string GetPrimaryKeysParameterizedSqlString(List <MapperPropertyInfo> keys, out DynamicParameters parms)
        {
            SqlServerUtilities.CheckNotEmpty(keys, "keys");
            parms = new DynamicParameters();
            string sql = string.Empty;

            for (int i = 0; i < keys.Count; i++)
            {
                if (i == (keys.Count - 1))
                {
                    sql += string.Format("{0}=@p{1} ", "[" + keys[i].Name + "]", i);
                }
                else
                {
                    sql += string.Format("{0}=@p{1} AND ", "[" + keys[i].Name + "]", i);
                }
                if (keys[i].Value != null)
                {
                    parms.Add("@p" + i.ToString(), keys[i].Value);
                }
            }
            return(sql);
        }
Exemplo n.º 13
0
 /// <summary>
 /// 批量添加(*注意:批量新增时DataTable中列的顺序一定要和数据库中列的顺序保持一致)
 /// </summary>
 /// <param name="tableName">批量新增的表名</param>
 /// <param name="dt">数据源</param>
 /// <returns></returns>
 public void AddList(string tableName, DataTable dt)
 {
     SqlServerUtilities.AddList(m_connectionString, tableName, dt);
 }