internal static void Add <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel { if (obj.GetDBModel_ChangeList().Count == 0) { throw new Exception("没有修改列"); } var sql = $"INSERT INTO [{DBTool.GetTableName(obj)}]({string.Join(',', obj.GetDBModel_ChangeList())}) VALUES(@{string.Join(",@", obj.GetDBModel_ChangeList())});"; try { if (!string.IsNullOrEmpty(obj.GetDBModel_IncrementCol())) { sql += "select LAST_INSERT_ID() as Id"; var v = conn.ExecuteScalar <int>(sql: sql, param: obj, commandTimeout: commandTimeout); obj.SetIncrementValue(v); } else { conn.Execute(sql: sql, param: obj, commandTimeout: commandTimeout); } obj.GetDBModel_ChangeList().Clear(); } catch (Exception e) { DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e)); throw; } }
internal static int Delete <T>(this DbConnection conn, IQuery <T> where, int?commandTimeout = null) where T : BaseDBModel { var sql = new StringBuilder($"DELETE FROM [{DBTool.GetTableName(where.DBModel)}] WHERE 1=1 "); DynamicParameters dynamicParameters = SetWhereSql(where, sql); try { return(conn.Execute(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout)); } catch (Exception e) { DBTool.DbError(e, sql.ToString() + ToIQueryJSON(dynamicParameters, where) + ExceptionTool.ToString(e)); throw; } }
internal static long Count <T>(this DbConnection conn, IQuery <T> query, int?commandTimeout = null) where T : BaseDBModel { var sql = new StringBuilder($"SELECT COUNT(1) FROM [{DBTool.GetTableName(query.DBModel)}] WHERE 1=1 "); DynamicParameters dynamicParameters = SetWhereSql(query, sql, true); try { return(conn.QuerySingleOrDefault <long>(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout)); } catch (Exception e) { DBTool.DbError(e, sql.ToString() + ToIQueryJSON(dynamicParameters, query) + ExceptionTool.ToString(e)); throw; } }
internal static List <R> GetList <R, T>(this DbConnection conn, IQuery <T> query, int?commandTimeout = null) where T : BaseDBModel { var sql = new StringBuilder($"SELECT * FROM [{DBTool.GetTableName(query.DBModel)}] WHERE 1=1 "); DynamicParameters dynamicParameters = SetWhereSql(query, sql); try { var lst = conn.Query <R>(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout).AsList(); return(lst); } catch (Exception e) { DBTool.DbError(e, sql.ToString() + ToIQueryJSON(dynamicParameters, query) + ExceptionTool.ToString(e)); throw; } }
internal static List <T> GetAll <T>(this DbConnection conn, int?commandTimeout = null) where T : BaseDBModel, new() { T obj = new T(); var sql = $"SELECT * FROM [{DBTool.GetTableName(obj)}] "; try { var lst = conn.Query <T>(sql: sql, param: obj, commandTimeout: commandTimeout).AsList(); lst.ForEach(m => m.GetDBModel_ChangeList().Clear()); return(lst); } catch (Exception e) { DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e)); throw; } }
internal static bool Delete <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel { if (obj.GetDBModel_PKCols().Count == 0) { throw new Exception("没有主键"); } var sql = $"DELETE FROM [{DBTool.GetTableName(obj)}] WHERE {GetSql(obj.GetDBModel_PKCols(), "AND")}"; try { return(conn.Execute(sql: sql, param: obj, commandTimeout: commandTimeout) == 1); } catch (Exception e) { DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e)); throw; } }
internal static T Get <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel { if (obj.GetDBModel_ChangeList().Count == 0) { throw new Exception("没有筛选条件"); } var sql = $"SELECT * FROM [{DBTool.GetTableName(obj)}] WHERE " + GetSql(obj.GetDBModel_ChangeList(), "AND"); try { var res = conn.QueryFirstOrDefault <T>(sql: sql, param: obj, commandTimeout: commandTimeout); res?.GetDBModel_ChangeList().Clear(); return(res); } catch (Exception e) { DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e)); throw; } }
internal static bool Update <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel { if (obj.GetDBModel_ChangeList().Count == 0) { throw new Exception("没有修改列"); } if (obj.GetDBModel_PKCols().Count == 0) { throw new Exception("没有主键"); } var elst = obj.GetDBModel_PKCols().AddRange(obj.GetDBModel_ExcludeColsForUpdate()); var updateList = obj.GetDBModel_ChangeList().Where(m => m != obj.GetDBModel_IncrementCol() && !elst.Contains(m)).ToList(); var sql = $"UPDATE [{DBTool.GetTableName(obj)}] SET {GetSql(updateList, ",")} WHERE {GetSql(obj.GetDBModel_PKCols(), "AND")}"; try { return(conn.Execute(sql: sql, param: obj, commandTimeout: commandTimeout) > 0); } catch (Exception e) { DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e)); throw; } }
internal static int Update <T>(this DbConnection conn, T obj, IQuery <T> where, int?commandTimeout = null) where T : BaseDBModel { if (obj.GetDBModel_ChangeList().Count == 0) { throw new Exception("没有修改列"); } var elst = obj.GetDBModel_PKCols().AddRange(obj.GetDBModel_ExcludeColsForUpdate()); var updateList = obj.GetDBModel_ChangeList().Where(m => m != obj.GetDBModel_IncrementCol() && !elst.Contains(m)).ToList(); var sql = new StringBuilder($"UPDATE [{DBTool.GetTableName(obj)}] SET {GetSql(updateList, ",")} WHERE 1=1"); DynamicParameters dynamicParameters = SetWhereSql(where, sql); dynamicParameters.AddDynamicParams(obj); try { return(conn.Execute(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout)); } catch (Exception e) { DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ToIQueryJSON(dynamicParameters, where) + ExceptionTool.ToString(e)); throw; } }