示例#1
0
        public static DbBase CreateDbBase()
        {
            var dbBase = new DbBase(ConnectionName);

            ConnectionName = "EIP";
            return(dbBase);
        }
示例#2
0
        /// <summary>
        ///     分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dataCount"></param>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public static IEnumerable <T> Page <T>(this DbBase dbs, int pageIndex, int pageSize, out long dataCount,
                                               SqlQuery sqlQuery = null) where T : class
        {
            var db = dbs.DbConnecttion;

            if (sqlQuery == null)
            {
                sqlQuery = SqlQuery <T> .Builder(dbs);
            }
            sqlQuery = sqlQuery.Page(pageIndex, pageSize);
            var para = sqlQuery.Param;
            var cr   = db.Query(sqlQuery.CountSql, para).SingleOrDefault();

            dataCount = (long)cr.DataCount;
            var result = db.Query <T>(sqlQuery.PageSql, para).ToList();

            return(result);
        }
示例#3
0
        public static DateTime GetCurrentDBTime(this DbBase dbs)
        {
            string sql = string.Empty;

            if (dbs.DbType == DBType.Oracle)
            {
                sql = "SELECT SYSDATE DATENOW FROM DUAL";
            }
            else
            {
                sql = "SELECT GETDATE() DATENOW";
            }

            DataTable dtDate  = dbs.SqlWithParamsDataTable(sql, "DateNow");
            DateTime  dateNow = (DateTime)dtDate.Rows[0][0];

            return(dateNow);
        }
示例#4
0
        /// <summary>
        ///     存储过程查询所有值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="procName">The procName.</param>
        /// <param name="parms">The parms.</param>
        /// <returns></returns>
        public static IEnumerable <T> StoredProcWithParams <T>(this DbBase dbs, string procName, dynamic parms)
        {
            Stopwatch stopwatch = new Stopwatch();

            stopwatch.Start();
            SqlLog log = new SqlLog
            {
                CreateTime = DateTime.Now,
                OperateSql = procName,
                Parameter  = parms == null ? "" : parms.ToString()
            };
            var result = dbs.DbConnecttion.Query <T>(procName, (object)parms, commandType: CommandType.StoredProcedure);

            stopwatch.Stop();
            log.EndDateTime = DateTime.Now;
            log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
            WriteSqlLog(log);
            return(result);
        }
示例#5
0
        /// <summary>
        ///     存储过程增加删除修改
        /// </summary>
        /// <param name="dbs"></param>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parms">参数</param>
        /// <returns>影响条数</returns>
        public static int InsertUpdateOrDeleteStoredProc(this DbBase dbs, string procName, dynamic parms = null)
        {
            Stopwatch stopwatch = new Stopwatch();

            stopwatch.Start();
            SqlLog log = new SqlLog
            {
                CreateTime = DateTime.Now,
                OperateSql = procName,
                Parameter  = parms == null ? "" : parms.ToString()
            };
            var result = dbs.DbConnecttion.Execute(procName, (object)parms, commandType: CommandType.StoredProcedure);

            stopwatch.Stop();
            log.EndDateTime = DateTime.Now;
            log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
            WriteSqlLog(log);
            return(result);
        }
示例#6
0
        /// <summary>
        ///     返回符合要求的第一个
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static T SqlWithParamsSingle <T>(this DbBase dbs, string sql, dynamic parms = null)
        {
            Stopwatch stopwatch = new Stopwatch();

            stopwatch.Start();
            SqlLog log = new SqlLog
            {
                CreateTime = DateTime.Now,
                OperateSql = sql,
                Parameter  = parms == null ? "" : parms.ToString()
            };
            var result = dbs.DbConnecttion.Query <T>(sql, (object)parms).FirstOrDefault();

            stopwatch.Stop();
            log.EndDateTime = DateTime.Now;
            log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
            WriteSqlLog(log);
            return(result);
        }
示例#7
0
        /// <summary>
        ///     执行增加删除修改语句
        /// </summary>
        /// <param name="dbs"></param>
        /// <param name="sql">Sql语句</param>
        /// <param name="parms">参数信息</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns>影响数</returns>
        public static int InsertUpdateOrDeleteSql(this DbBase dbs, string sql, dynamic parms = null,
                                                  bool isSetConnectionStr = true)
        {
            Stopwatch stopwatch = new Stopwatch();

            stopwatch.Start();
            SqlLog log = new SqlLog
            {
                CreateTime = DateTime.Now,
                OperateSql = sql,
                Parameter  = parms == null ? "" : parms.ToString(),
            };
            var result = dbs.DbConnecttion.Execute(sql, (object)parms);

            stopwatch.Stop();
            log.EndDateTime = DateTime.Now;
            log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
            WriteSqlLog(log);
            return(result);
        }
示例#8
0
        /// <summary>
        ///     执行语句返回bool
        /// </summary>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static bool SqlWithParamsBool(this DbBase dbs, string sql, dynamic parms = null)
        {
            Stopwatch stopwatch = new Stopwatch();

            stopwatch.Start();
            sql = sql.Replace("$ParamPrefix", dbs.ParamPrefix);
            SqlLog log = new SqlLog
            {
                CreateTime = DateTime.Now,
                OperateSql = sql,
                Parameter  = parms == null ? "" : parms.ToString()
            };
            var result = dbs.DbConnecttion.Query(sql, (object)parms).Any();

            stopwatch.Stop();
            log.EndDateTime = DateTime.Now;
            log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
            WriteSqlLog(log);
            return(result);
        }
示例#9
0
        /// <summary>
        ///     插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="t"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int Insert <T>(this DbBase dbs, T t, IDbTransaction transaction = null, int?commandTimeout = null)
            where T : class
        {
            Stopwatch stopwatch = new Stopwatch();

            stopwatch.Start();
            var db  = dbs.DbConnecttion;
            var sql = SqlQuery <T> .Builder(dbs);

            SqlLog log = new SqlLog
            {
                CreateTime = DateTime.Now,
                OperateSql = sql.InsertSql
            };
            var result = db.Execute(sql.InsertSql, t, transaction, commandTimeout);

            stopwatch.Stop();
            log.EndDateTime = DateTime.Now;
            log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
            WriteSqlLog(log);
            return(result);
        }
示例#10
0
        /// <summary>
        ///     使用SqlBulkCopy批量进行插入数据
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="dbs"></param>
        /// <param name="entitys">实体对象集合</param>
        public static int InsertWithBulkCopy <T>(this DbBase dbs, List <T> entitys) where T : new()
        {
            Stopwatch stopwatch = new Stopwatch();

            stopwatch.Start();
            int result = 1;

            using (var destinationConnection = (SqlConnection)dbs.DbConnecttion)
            {
                using (var bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    Type   type      = entitys[0].GetType();
                    object classAttr = type.GetCustomAttributes(false)[0];
                    if (classAttr is TableAttribute)
                    {
                        TableAttribute tableAttr = classAttr as TableAttribute;
                        bulkCopy.DestinationTableName = tableAttr.Name; //要插入的表的表明
                    }
                    ModelHandler <T> mh = new ModelHandler <T>();
                    DataTable        dt = mh.FillDataTable(entitys);
                    if (dt != null && dt.Rows.Count != 0)
                    {
                        bulkCopy.WriteToServer(dt);
                    }
                }
            }
            SqlLog log = new SqlLog
            {
                CreateTime = DateTime.Now,
                OperateSql = "BulkCopy批量插入"
            };

            stopwatch.Stop();
            log.EndDateTime = DateTime.Now;
            log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
            WriteSqlLog(log);
            return(result);
        }
示例#11
0
        /// <summary>
        ///     执行Sql语句带参数
        /// </summary>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static DataTable SqlWithParamsDataTable(this DbBase dbs, string sql, string tableName, dynamic parms = null)
        {
            Stopwatch stopwatch = new Stopwatch();

            stopwatch.Start();
            sql = sql.Replace("$ParamPrefix", dbs.ParamPrefix);
            SqlLog log = new SqlLog
            {
                CreateTime = DateTime.Now,
                OperateSql = sql,
                Parameter  = parms == null ? "" : parms.ToString()
            };
            var       resultReader = dbs.DbConnecttion.ExecuteReader(sql, (object)parms);
            DataTable result       = new DataTable(tableName);

            result.Load(resultReader);

            stopwatch.Stop();
            log.EndDateTime = DateTime.Now;
            log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
            WriteSqlLog(log);
            return(result);
        }