Esempio n. 1
0
        /// <summary>
        /// 根据主键批量删除数据
        /// </summary>
        public static int DeleteByIds <T>(this IDbConnection conn, object ids, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            if (DapperExtCommon.ObjectIsEmpty(ids))
            {
                return(0);
            }

            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            if (sqls.HasKey)
            {
                DynamicParameters dpar = new DynamicParameters();
                dpar.Add("@ids", ids);
                return(conn.Execute(sqls.DeleteByIdsSql, dpar, transaction, commandTimeout));
            }
            else
            {
                throw new ArgumentException("表" + sqls.TableName + "没有主键,无法DeleteById。");
            }
        }
Esempio n. 2
0
        public static IEnumerable <dynamic> GetByInDynamic <T>(this IDbConnection conn, string field, object ids, string returnFields = null, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            if (DapperExtCommon.ObjectIsEmpty(ids))
            {
                return(new List <dynamic>());
            }

            DapperExtSqls     sqls = GetDapperExtSqls(typeof(T));
            DynamicParameters dpar = new DynamicParameters();

            dpar.Add("@ids", ids);
            if (returnFields == null)
            {
                returnFields = sqls.AllFields;
            }

            string sql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}] IN @ids", returnFields, sqls.TableName, field);

            return(conn.Query(sql, dpar, transaction, true, commandTimeout));
        }
Esempio n. 3
0
        /// <summary>
        /// 获取dynamic数据,使用skip 和take
        /// </summary>
        /// <returns></returns>
        public static IEnumerable <dynamic> GetBySkipDynamic <T>(this IDbConnection conn, int skip, int take, string returnFields = null, string where = null, object param = null, string orderBy = null, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            if (returnFields == null)
            {
                returnFields = sqls.AllFields;
            }

            if (orderBy == null)
            {
                if (sqls.HasKey)
                {
                    orderBy = string.Format("ORDER BY [{0}] DESC", sqls.KeyName);
                }
                else
                {
                    orderBy = string.Format("ORDER BY [{0}]", sqls.AllFieldList.First());
                }
            }

            StringBuilder sb = new StringBuilder();

            if (skip == 0) //第一页,使用Top语句
            {
                sb.AppendFormat("SELECT TOP ({0}) {1} FROM [{2}] WITH(NOLOCK) {3} {4}", take, returnFields, sqls.TableName, where, orderBy);
            }
            else //使用ROW_NUMBER()
            {
                sb.AppendFormat("WITH cte AS(SELECT ROW_NUMBER() OVER({0}) AS rownum,{1} FROM [{2}] WITH(NOLOCK) {3})", orderBy, returnFields, sqls.TableName, where);
                if (returnFields.Contains(" AS") || returnFields.Contains(" as"))
                {
                    sb.AppendFormat("SELECT * FROM cte WHERE cte.rownum BETWEEN {1} AND {2}", returnFields, skip + 1, skip + take);
                }
                else
                {
                    sb.AppendFormat("SELECT {0} FROM cte WHERE cte.rownum BETWEEN {1} AND {2}", returnFields, skip + 1, skip + take);
                }
            }
            return(conn.Query(sb.ToString(), param, transaction, true, commandTimeout));
        }
Esempio n. 4
0
        public static int UpdateByIdBatch <T>(this IDbConnection conn, IEnumerable <T> entitys, string updateFields = null, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            if (sqls.HasKey)
            {
                if (updateFields == null)
                {
                    return(conn.Execute(sqls.UpdateByIdSql, entitys, transaction, commandTimeout));
                }
                else
                {
                    string updateList = DapperExtCommon.GetFieldsEqStr(updateFields.Split(',').ToList(), "[", "]");
                    string sql        = string.Format("UPDATE [{0}] SET {1} WHERE [{2}]=@{2}", sqls.TableName, updateList, sqls.KeyName);
                    return(conn.Execute(sql, entitys, transaction, commandTimeout));
                }
            }
            else
            {
                throw new ArgumentException("表" + sqls.TableName + "没有主键,无法UpdateById。");
            }
        }
Esempio n. 5
0
 public static DapperExtSqls GetDapperExtSqls(Type t)
 {
     if (dapperExtsqlsDict.Keys.Contains(t.TypeHandle))
     {
         return(dapperExtsqlsDict[t.TypeHandle]);
     }
     else
     {
         DapperExtSqls sqls           = DapperExtCommon.GetDapperExtSqls(t);
         string        Fields         = DapperExtCommon.GetFieldsStr(sqls.AllFieldList, "[", "]");
         string        FieldsAt       = DapperExtCommon.GetFieldsAtStr(sqls.AllFieldList);
         string        FieldsEq       = DapperExtCommon.GetFieldsEqStr(sqls.AllFieldList, "[", "]");
         string        FieldsExtKey   = DapperExtCommon.GetFieldsStr(sqls.ExceptKeyFieldList, "[", "]");
         string        FieldsAtExtKey = DapperExtCommon.GetFieldsAtStr(sqls.ExceptKeyFieldList);
         string        FieldsEqExtKey = DapperExtCommon.GetFieldsEqStr(sqls.ExceptKeyFieldList, "[", "]");
         sqls.AllFields = Fields;
         if (sqls.HasKey && sqls.IsIdentity) //有主键并且是自增
         {
             sqls.InsertSql         = string.Format("INSERT INTO [{0}]({1})VALUES({2})", sqls.TableName, FieldsExtKey, FieldsAtExtKey);
             sqls.InsertIdentitySql = string.Format("SET IDENTITY_INSERT [{0}] ON;INSERT INTO [{0}]({1})VALUES({2});SET IDENTITY_INSERT [{0}] OFF", sqls.TableName, Fields, FieldsAt);
         }
         else
         {
             sqls.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", sqls.TableName, Fields, FieldsAt);
         }
         if (sqls.HasKey) //含有主键
         {
             sqls.DeleteByIdSql  = string.Format("DELETE FROM [{0}] WHERE [{1}]=@id", sqls.TableName, sqls.KeyName);
             sqls.DeleteByIdsSql = string.Format("DELETE FROM [{0}] WHERE [{1}] IN @ids", sqls.TableName, sqls.KeyName);
             sqls.GetByIdSql     = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}]=@id", Fields, sqls.TableName, sqls.KeyName);
             sqls.GetByIdsSql    = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}] IN @ids", Fields, sqls.TableName, sqls.KeyName);
             sqls.UpdateByIdSql  = string.Format("UPDATE [{0}] SET {1} WHERE [{2}]=@{2}", sqls.TableName, FieldsEqExtKey, sqls.KeyName);
         }
         sqls.DeleteAllSql = string.Format("DELETE FROM [{0}]", sqls.TableName);
         sqls.GetAllSql    = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK)", Fields, sqls.TableName);
         dapperExtsqlsDict[t.TypeHandle] = sqls;
         return(sqls);
     }
 }
Esempio n. 6
0
        public static dynamic Insert <T>(this IDbConnection conn, T entity, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            if (sqls.HasKey && sqls.IsIdentity)
            {
                switch (sqls.KeyType)
                {
                case "Int32": return(conn.ExecuteScalar <int>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));       //int

                case "Int64": return(conn.ExecuteScalar <long>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));      //long

                case "Decimal": return(conn.ExecuteScalar <decimal>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //decimal

                case "UInt32": return(conn.ExecuteScalar <uint>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));     //uint

                case "UInt64": return(conn.ExecuteScalar <ulong>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));    //ulong

                case "Double": return(conn.ExecuteScalar <double>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));   //double

                case "Single": return(conn.ExecuteScalar <float>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));    //float

                case "Byte": return(conn.ExecuteScalar <byte>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));       //byte

                case "SByte": return(conn.ExecuteScalar <sbyte>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));     //sbyte

                case "Int16": return(conn.ExecuteScalar <short>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));     //short

                case "UInt16": return(conn.ExecuteScalar <ushort>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));   //ushort

                default: return(conn.ExecuteScalar <dynamic>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout));        //dynamic
                }
            }
            else
            {
                return(conn.Execute(sqls.InsertSql, entity, transaction, commandTimeout));
            }
        }
Esempio n. 7
0
        /// <summary>
        /// 获取分页数据
        /// </summary>
        public static IEnumerable <dynamic> GetByPageDynamic <T>(this IDbConnection conn, int pageIndex, int pageSize, out long total, string returnFields = null, string where = null, object param = null, string orderBy = null, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            if (returnFields == null)
            {
                returnFields = sqls.AllFields;
            }

            if (orderBy == null)
            {
                if (sqls.HasKey)
                {
                    orderBy = string.Format("ORDER BY `{0}` DESC", sqls.KeyName);
                }
                else
                {
                    orderBy = string.Format("ORDER BY `{0}`", sqls.AllFieldList.First());
                }
            }

            int skip = 0;

            if (pageIndex > 0)
            {
                skip = (pageIndex - 1) * pageSize;
            }

            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM `{0}` {1};", sqls.TableName, where);
            sb.AppendFormat("SELECT {0} FROM `{1}` {2} {3} LIMIT {4},{5}", returnFields, sqls.TableName, where, orderBy, skip, pageSize);
            using (var reader = conn.QueryMultiple(sb.ToString(), param, transaction, commandTimeout))
            {
                total = reader.ReadFirst <long>();
                return(reader.Read());
            }
        }
Esempio n. 8
0
        public static dynamic GetByIdDynamic <T>(this IDbConnection conn, dynamic id, string returnFields = null, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            if (sqls.HasKey)
            {
                DynamicParameters dpar = new DynamicParameters();
                dpar.Add("@id", id);
                if (returnFields == null)
                {
                    return(conn.QueryFirstOrDefault(sqls.GetByIdSql, dpar, transaction, commandTimeout));
                }
                else
                {
                    string sql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}]=@id", returnFields, sqls.TableName, sqls.KeyName);
                    return(conn.QueryFirstOrDefault(sql, dpar, transaction, commandTimeout));
                }
            }
            else
            {
                throw new ArgumentException("表" + sqls.TableName + "没有主键,无法GetByIdDynamic。");
            }
        }
Esempio n. 9
0
        public static IEnumerable <dynamic> GetBySkipDynamic <T>(this IDbConnection conn, int skip, int take, string returnFields = null, string where = null, object param = null, string orderBy = null, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            if (returnFields == null)
            {
                returnFields = sqls.AllFields;
            }
            if (orderBy == null)
            {
                if (sqls.HasKey)
                {
                    orderBy = string.Format("ORDER BY `{0}` DESC", sqls.KeyName);
                }
                else
                {
                    orderBy = string.Format("ORDER BY `{0}`", sqls.AllFieldList.First());
                }
            }
            string sql = string.Format("SELECT {0} FROM `{1}` {2} {3} LIMIT {4},{5}", returnFields, sqls.TableName, where, orderBy, skip, take);

            return(conn.Query(sql, param, transaction, true, commandTimeout));
        }
Esempio n. 10
0
        private static T GetByIdBase <T>(this IDbConnection conn, Type t, dynamic id, string returnFields = null, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(t);

            if (sqls.HasKey)
            {
                DynamicParameters dpar = new DynamicParameters();
                dpar.Add("@id", id);
                if (returnFields == null)
                {
                    return(conn.QueryFirstOrDefault <T>(sqls.GetByIdSql, dpar, transaction, commandTimeout));
                }
                else
                {
                    string sql = string.Format("SELECT {0} FROM `{1}` WHERE `{2}`=@id", returnFields, sqls.TableName, sqls.KeyName);
                    return(conn.QueryFirstOrDefault <T>(sql, dpar, transaction, commandTimeout));
                }
            }
            else
            {
                throw new ArgumentException("表" + sqls.TableName + "没有主键,无法GetById。");
            }
        }
Esempio n. 11
0
        public static int InsertBatch <T>(this IDbConnection conn, IEnumerable <T> entitys, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            return(conn.Execute(sqls.InsertSql, entitys, transaction, commandTimeout));
        }
Esempio n. 12
0
        public static int DeleteAll <T>(this IDbConnection conn, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            return(conn.Execute(sqls.DeleteAllSql, null, transaction, commandTimeout));
        }
Esempio n. 13
0
        /// <summary>
        /// 获取分页数据
        /// </summary>
        public static IEnumerable <dynamic> GetByPageDynamic <T>(this IDbConnection conn, int pageIndex, int pageSize, out int total, string returnFields = null, string where = null, object param = null, string orderBy = null, IDbTransaction transaction = null, int?commandTimeout = null)
        {
            DapperExtSqls sqls = GetDapperExtSqls(typeof(T));

            if (returnFields == null)
            {
                returnFields = sqls.AllFields;
            }

            if (orderBy == null)
            {
                if (sqls.HasKey)
                {
                    orderBy = string.Format("ORDER BY [{0}] DESC", sqls.KeyName);
                }
                else
                {
                    orderBy = string.Format("ORDER BY [{0}]", sqls.AllFieldList.First());
                }
            }

            int skip = 0;

            if (pageIndex > 0)
            {
                skip = (pageIndex - 1) * pageSize;
            }

            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("DECLARE @total INT;SELECT @total = COUNT(1) FROM [{0}] WITH(NOLOCK) {1};SELECT @total;", sqls.TableName, where);
            sb.Append("IF(@total>0) BEGIN ");
            if (pageIndex == 1)
            {
                sb.AppendFormat("SELECT TOP ({0}) {1} FROM [{2}] WITH(NOLOCK) {3} {4}", pageSize, returnFields, sqls.TableName, where, orderBy);
            }
            else
            {
                sb.AppendFormat("WITH cte AS (SELECT ROW_NUMBER() OVER({0}) AS rownum,{1} FROM [{2}] WITH(NOLOCK) {3})", orderBy, returnFields, sqls.TableName, where);
                if (returnFields.Contains(" AS") || returnFields.Contains(" as"))
                {
                    sb.AppendFormat("SELECT * FROM cte WHERE cte.rownum BETWEEN {0} AND {1}", skip + 1, skip + pageSize);
                }
                else
                {
                    sb.AppendFormat("SELECT {0} FROM cte WHERE cte.rownum BETWEEN {1} AND {2}", returnFields, skip + 1, skip + pageSize);
                }
            }
            sb.Append(" END");

            using (var reader = conn.QueryMultiple(sb.ToString(), param, transaction, commandTimeout))
            {
                total = reader.ReadFirst <int>();
                if (total > 0)
                {
                    return(reader.Read());
                }
                else
                {
                    return(new List <dynamic>());
                }
            }
        }