public static DbBase CreateDbBase()
{
var dbBase = new DbBase(ConnectionName);
ConnectionName = "EIP";
return(dbBase);
}
/// <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);
}
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);
}
/// <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);
}
/// <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);
}
/// <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);
}
/// <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);
}
/// <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);
}
/// <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);
}
/// <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);
}
/// <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);
}
