/// <summary>
/// 分页获取数据
/// </summary>
/// <param name="innerQuery">分页前的内部子查询</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="conditions">条件子句(不用包含WHERE,如："AND status = 1 ...")</param>
/// <param name="projection">外层SELECT子句</param>
/// <param name="param">查询参数对象</param>
/// <typeparam name="T">结果集类型</typeparam>
/// <returns></returns>
public PagedList <T> GetPaging <T>(string innerQuery = null, int pageIndex = 1, int pageSize = int.MaxValue, string conditions = "", string projection = "*", object param = null)
where T : class
{
if (string.IsNullOrEmpty(innerQuery))
{
var tableName = SqlMapperExtensions.GetTableName(typeof(TEntity));
innerQuery = $"SELECT * FROM {tableName}";
}
StringBuilder builder = new StringBuilder();
builder.Append($"SELECT COUNT(1) as totalCount FROM ({innerQuery}) as temp WHERE 1 = 1 {conditions};");
int start = (pageIndex - 1) * pageSize;
builder.Append($"SELECT {projection} FROM ({innerQuery}) as temp WHERE 1 = 1 {conditions} LIMIT {start},{pageSize};");
return(Run(conn =>
{
string sql = builder.ToString();
using (var multi = conn.QueryMultiple(sql, param))
{
int totalCount = multi.Read <int>().Single();
var list = multi.Read <T>().ToList();
return new PagedList <T>()
{
PageIndex = pageIndex,
PageSize = pageSize,
TotalCount = totalCount,
List = list
};
}
}));
}
public virtual void Update(TEntity entity)
{
using (var connection = this.GetDbConnection())
{
SqlMapperExtensions.Update(connection, entity);
}
}
public IEnumerable <Category> GetCategoryBooks(int category)
{
CategoryType categoryType = (CategoryType)category;
IEnumerable <Category> res = SqlMapperExtensions.GetAll <Category>(_connectionString).Where(x => x.CategoryType == categoryType);
return(res);
}
public void Update(TaskItem user)
{
using (IDbConnection db = new SqlConnection(connectionString))
{
SqlMapperExtensions.Update(db, user);
}
}
public void Update(Task task)
{
using (var cnn = GetDbConnection())
{
SqlMapperExtensions.Update(cnn, task);
}
}
/// <summary>
/// 根据Id获取单个实体对象
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public TEntity Get(dynamic id)
{
return(Run(conn =>
{
return SqlMapperExtensions.Get <TEntity>(conn, id);
}));
}
private static string GetTableName(Type type)
{
if (TypeTableName.TryGetValue(type.TypeHandle, out string name))
{
return(name);
}
if (SqlMapperExtensions.TableNameMapper != null)
{
name = SqlMapperExtensions.TableNameMapper(type);
}
else
{
var info = type;
//NOTE: This as dynamic trick falls back to handle both our own Table-attribute as well as the one in EntityFramework
var tableAttrName =
info.GetCustomAttribute <TableAttribute>(false)?.Name
?? (info.GetCustomAttributes(false).FirstOrDefault(attr => attr.GetType().Name == "TableAttribute") as dynamic)?.Name;
if (tableAttrName != null)
{
name = tableAttrName;
}
else
{
name = type.Name + "s";
if (type.IsInterface && name.StartsWith("I"))
{
name = name.Substring(1);
}
}
}
TypeTableName[type.TypeHandle] = name;
return(name);
}
public void Update(Board board)
{
using (var cnn = GetDbConnection())
{
SqlMapperExtensions.Update(cnn, board);
}
}
public IEnumerable <TaskItem> GetItem(string id)
{
using (IDbConnection db = new SqlConnection(connectionString))
{
var res = SqlMapperExtensions.GetAll <TaskItem>(db).Where(x => x.UserId == id);
return(res);
}
}
public TaskItem GetById(object id)
{
using (IDbConnection db = new SqlConnection(connectionString))
{
var res = SqlMapperExtensions.Get <TaskItem>(db, id);
return(res);
}
}
public IEnumerable <TaskItem> GetAll()
{
using (IDbConnection db = new SqlConnection(connectionString))
{
var res = SqlMapperExtensions.GetAll <TaskItem>(db);
return(res);
}
}
/// <summary>
/// 删除|
/// Delete data in table "Ts".
/// </summary>
/// <param name="tableNameFormat">Table Name Format placeholder</param>
/// <param name="whereString">parameterized sql of "where",(example:whereString:name like @name)</param>
/// <param name="param">whereString's param，(example:new { name = "google%" })</param>
/// <returns>受影响的行数|The number of rows affected.</returns>
public async Task <bool> DeleteAsync(string tableNameFormat, string whereString, object param)
{
SqlBuilder sb = new SqlBuilder();
sb.Append("DELETE FROM " + SqlMapperExtensions.GetTableName(TableName, tableNameFormat));
sb.Where(whereString, param);
return(await ExecuteAsync(sb.ESQL, sb.Arguments) > 0);
}
/// <summary>
/// Tìm đối tượng theo khóa chính đầu tiên
/// </summary>
/// <param name="pk">Giá trị khóa chính</param>
/// <returns>Entity</returns>
public virtual async Task <Entity> FindByKeyAsync(dynamic pk)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
return(await SqlMapperExtensions.GetAsync <Entity>(connection, pk).ConfigureAwait(false));
}
}
public async Task <int> EasyInsertAsync <T>(T data) where T : class
{
await EnsureOpenAsync();
EnsureDapperTypeMapping(typeof(T));
return(await SqlMapperExtensions.InsertAsync <T>(Connection, data, Transaction));
}
public static void Update <T>(this T entity) where T : IDbTable
{
using (var db = ConFactory.GetConnection())
{
db.Open();
SqlMapperExtensions.Update(db, entity);
}
}
/// <summary>
/// Tìm đối tượng theo khóa chính đầu tiên
/// </summary>
/// <param name="pk">Giá trị khóa chính</param>
/// <returns>Entity</returns>
public virtual Entity FindByKey(dynamic pk)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
return(SqlMapperExtensions.Get <Entity>(connection, pk));
}
}
public IEnumerable <Category> GetAutorAndCategoryBook(string avtor, int category)
{
List <Category> bookList = new List <Category>();
CategoryType categoryType = (CategoryType)category;
bookList.AddRange(SqlMapperExtensions.GetAll <Category>(_connectionString).Where(x => x.CategoryType == categoryType).ToList());
return(bookList);
}
public void Delete(int id)
{
T entity = SqlMapperExtensions.Get <T>(_connection, id);
if (entity != null)
{
SqlMapperExtensions.Delete(_connection, entity);
}
}
public void CreateAndGetAllComments(string UserName, string Comment)
{
Comment comment = new Comment()
{
UserName = UserName, Message = Comment, createTime = DateTime.Now
};
SqlMapperExtensions.Insert(_connectionString, comment);
}
/// <summary>
/// 扩展新增操作，并返回自增列值
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="conn">连接对象</param>
/// <param name="entity">实体对象</param>
/// <param name="identityId"></param>
/// <param name="transaction">是否事务</param>
/// <returns></returns>
public static long Insert <T>(this IDbConnection conn, T entity, out long identityId, bool isTransaction = true) where T : class
{
identityId = 0;
var type = typeof(T);
if (type.IsArray)
{
type = type.GetElementType();
}
else if (type.IsGenericType)
{
type = type.GetGenericArguments()[0];
}
var allProperties = TypePropertiesCache(type);
var tableMapper = type.GetCustomAttribute <TableAttribute>();
var name = SqlMapperExtensions.TableNameMapper != null?SqlMapperExtensions.TableNameMapper(type) : (tableMapper == null ? type.Name : tableMapper.Name);
var sqlStr = new StringBuilder();
var valColumns = new StringBuilder();
sqlStr.AppendFormat("INSERT INTO {0} (", name);
for (var i = 0; i < allProperties.Count; i++)
{
sqlStr.AppendFormat("`{0}`,", allProperties[i].Name);
valColumns.AppendFormat("@{0},", allProperties[i].Name);
}
sqlStr.Length = sqlStr.Length - 1;
valColumns.Length = valColumns.Length - 1;
sqlStr.AppendFormat(") VALUES ({0});", valColumns.ToString());
sqlStr.Append("SELECT ROW_COUNT() AS ROW,LAST_INSERT_ID() AS IDENTITY");
IDbTransaction trans = null;
if (isTransaction)
{
trans = conn.BeginTransaction();
conn = trans.Connection;
}
using (IDataReader reader = conn.ExecuteReader(sqlStr.ToString(), entity, transaction: trans))
{
if (reader.Read())
{
//最新id
identityId = reader.GetInt64(1);
//影响行数
return(reader.GetInt64(0));
}
else
{
return(0);
}
}
}
public IEnumerable <Notification> GetNotificationsForMessage(string uniquecustomerid, string messageid)
{
List <Notification> notifications = null;
using (var conn = Connection)
{
notifications = SqlMapperExtensions.GetAll <Notification>(conn, Transaction).Where(cuid => cuid.CustomerUniqueId == uniquecustomerid && cuid.MessageName == messageid).Take <Notification>(1000).ToList <Notification>();
}
return(notifications);
}
public void Delete(int id)
{
using (IDbConnection db = new SqlConnection(connectionString))
{
var res = SqlMapperExtensions.Get <TaskItem>(db, id);
if (res != null)
{
SqlMapperExtensions.Delete(db, res);
}
}
}
/// <summary>
/// 异步查询总数|
/// Returns the number of rows
/// </summary>
/// <param name="tableNameFormat">Table Name Format placeholder</param>///
/// <param name="whereString">parameterized sql of "where",(example:whereString:name like @name)</param>
/// <param name="param">whereString's param，(example:new { name = "google%" })</param>
/// <returns>总数|Returns the number of rows</returns>
public async Task <int> QueryCountAsync(string tableNameFormat, string whereString = null, object param = null)
{
SqlBuilder sb = new SqlBuilder();
sb.Select(args: "Count(*)");
sb.From(SqlMapperExtensions.GetTableName(TableName, tableNameFormat));
if (!string.IsNullOrEmpty(whereString))
{
sb.Where(whereString, param);
}
return(await DBConnection.QueryFirstAsync <int>(sb.ESQL, sb.Arguments, transaction : _dbTransaction));
}
public void AddCategory(Category category)
{
using (DbConnection connection = new SqlConnection(DataConfig.GetPrimaryConnectionString()))
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
//TODO: SqlMapperExtensions 这里还没有测试通过
SqlMapperExtensions.Insert <Category>(connection, category);
}
}
public override async Task <TEntity> GetAsync(dynamic id)
{
if (id == null)
{
throw new ArgumentNullException(nameof(id));
}
ValidateConnection();
var conn = OpenDbConnection();
var value = await SqlMapperExtensions.GetAsync <TEntity>(conn, id, DbTransaction);
CloseConnection(conn);
return(value);
}
private void Save()
{
if (Authorizer.Instance.Guest != 1)
{
if (Ev.Id == null)
{
SqlMapperExtensions.Insert(AnomalEventConnection.Connection, Ev);
}
else
{
SqlMapperExtensions.Update(AnomalEventConnection.Connection, Ev);
}
}
}
private void ProcessQueue()
{
while (!DataService.DataQueue.IsEmpty)
{
if (!DataService.DataQueue.TryDequeue(out DatabaseAction request))
{
Console.WriteLine("DATABASE WORKER: Was unable to process an object. Will try again...");
return;
}
try
{
if (request.Action == DatabaseActionType.Insert)
{
foreach (var record in request.Data)
{
SqlMapperExtensions.Insert(_connection, record);
}
}
else if (request.Action == DatabaseActionType.Update)
{
foreach (var record in request.Data)
{
SqlMapperExtensions.Update(_connection, record);
}
}
else if (request.Action == DatabaseActionType.Delete)
{
foreach (var record in request.Data)
{
SqlMapperExtensions.Delete(_connection, record);
}
}
}
catch (MySqlException ex)
{
Console.WriteLine("****EXCEPTION ON DATABASE BACKGROUND THREAD****");
Console.WriteLine("Data Type: " + request.DataType);
Console.WriteLine("Action: " + request.Action);
LoggingService.LogError(ex, request.Action.ToString());
}
catch (Exception ex)
{
Console.WriteLine("****EXCEPTION ON DATABASE BACKGROUND THREAD****");
Console.WriteLine("Data Type: " + request.DataType);
Console.WriteLine("Action: " + request.Action);
LoggingService.LogError(ex, request.Action.ToString());
}
}
}
public async Task <T> EasyGetAsync <T>(dynamic id, bool throwErrorIfNotFound = false) where T : class
{
await EnsureOpenAsync();
EnsureDapperTypeMapping(typeof(T));
var ret = await SqlMapperExtensions.GetAsync <T>(Connection, id, Transaction);
if (throwErrorIfNotFound && ret == null)
{
throw new KeyNotFoundException();
}
return(ret);
}
public async Task <bool> EasyDeleteAsync <T>(T data, bool throwErrorIfNotFound = false) where T : class
{
await EnsureOpenAsync();
EnsureDapperTypeMapping(typeof(T));
bool ret = await SqlMapperExtensions.DeleteAsync <T>(Connection, data, Transaction);
if (throwErrorIfNotFound && ret == false)
{
throw new KeyNotFoundException();
}
return(ret);
}
public async Task <IEnumerable <T> > EasyGetAllAsync <T>(bool throwErrorIfNotFound = false) where T : class
{
await EnsureOpenAsync();
EnsureDapperTypeMapping(typeof(T));
var ret = await SqlMapperExtensions.GetAllAsync <T>(Connection, Transaction);
if (throwErrorIfNotFound && ret.Count() == 0)
{
throw new KeyNotFoundException();
}
return(ret);
}
