/// <summary>
/// 创建表
/// </summary>
/// <param name="helper"></param>
/// <returns></returns>
public string CreateTable(DBExtend helper)
{
string msg;
CreateTable(helper, out msg);
return(msg);
}
/// <summary>
/// 对GROUP进行分页
/// </summary>
/// <param name="query">包含了group的查询</param>
/// <param name="count"></param>
/// <returns></returns>
public List <dynamic> AutoSpGroupPage(LambdaQuery <TModel> query, out int count)
{
count = 0;
DBExtend helper = dbHelper;
return(helper.AutoSpGroupPage(query, out count));
}
internal static string CreateColumn(DBExtend helper, Attribute.FieldAttribute item)
{
var dbAdapter = helper._DBAdapter;
string result = "";
if (string.IsNullOrEmpty(item.ColumnType))
{
throw new Exception("ColumnType is null");
}
string str = dbAdapter.GetCreateColumnScript(item);
string indexScript = "";
if (item.FieldIndexType != Attribute.FieldIndexType.无)
{
indexScript = dbAdapter.GetColumnIndexScript(item);
}
try
{
helper.Execute(str);
if (!string.IsNullOrEmpty(indexScript))
{
helper.Execute(indexScript);
}
result += string.Format("创建字段:{0}\r\n", item.Name);
}
catch (Exception ero)
{
//CoreHelper.EventLog.Log("创建字段时发生错误:" + ero.Message);
result += string.Format("创建字段:{0} 发生错误:{1}\r\n", item.Name, ero.Message);
}
return(result);
}
/// <summary>
/// 按条件删除[基本方法]
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public int Delete(Expression <Func <TModel, bool> > expression)
{
DBExtend helper = dbHelper;
int n = helper.Delete <TModel>(expression);
return(n);
}
/// <summary>
/// 创建TABLE[基本方法]
/// </summary>
/// <returns></returns>
public string CreateTable()
{
DBExtend helper = dbHelper;
TModel obj1 = new TModel();
return(obj1.CreateTable(helper));
}
/// <summary>
/// 按ID整体更新[基本方法]
/// </summary>
/// <typeparam name="TItem"></typeparam>
/// <param name="item"></param>
public static void UpdateById <TItem>(TItem item) where TItem : TModel, new()
{
DBExtend helper = dbHelper;
helper.UpdateById <TItem>(item);
//_CleanDataCache(CommandType.UPDATE, item);
}
/// <summary>
/// 指定条件和参数进行更新[基本方法]
/// </summary>
/// <typeparam name="TItem"></typeparam>
/// <param name="expression">条件</param>
/// <param name="setValue">值</param>
/// <returns></returns>
public static int Update <TItem>(Expression <Func <TItem, bool> > expression, ParameCollection setValue) where TItem : TModel, new()
{
DBExtend helper = dbHelper;
int n = helper.Update <TItem>(expression, setValue);
return(n);
}
/// <summary>
/// 创建表索引
/// </summary>
/// <typeparam name="TItem"></typeparam>
public static void CreateTableIndex <TItem>() where TItem : TModel, new()
{
DBExtend helper = dbHelper;
TItem obj1 = new TItem();
obj1.CheckIndexExists(helper);
}
/// <summary>
/// 按条件删除[基本方法]
/// </summary>
/// <typeparam name="TItem"></typeparam>
/// <param name="expression"></param>
/// <returns></returns>
public static int Delete <TItem>(Expression <Func <TItem, bool> > expression) where TItem : TModel, new()
{
DBExtend helper = dbHelper;
int n = helper.Delete <TItem>(expression);
return(n);
}
/// <summary>
/// 创建TABLE[基本方法]
/// </summary>
/// <typeparam name="TItem"></typeparam>
/// <param name="is2000"></param>
/// <returns></returns>
public static string CreateTable <TItem>(bool is2000) where TItem : TModel, new()
{
DBExtend helper = dbHelper;
TItem obj1 = new TItem();
return(obj1.CreateTable(helper, is2000));
}
/// <summary>
/// 指定条件和参数进行更新[基本方法]
/// </summary>
/// <param name="expression">条件</param>
/// <param name="setValue">值</param>
/// <returns></returns>
public int Update(Expression <Func <TModel, bool> > expression, ParameCollection setValue)
{
DBExtend helper = dbHelper;
int n = helper.Update <TModel>(expression, setValue);
return(n);
}
/// <summary>
/// 指定条件并按对象差异更新
/// </summary>
/// <param name="expression"></param>
/// <param name="model"></param>
/// <returns></returns>
public int Update(Expression <Func <TModel, bool> > expression, TModel model)
{
DBExtend helper = dbHelper;
int n = helper.Update <TModel>(expression, model);
return(n);
}
/// <summary>
/// 创建表索引
/// </summary>
public void CreateTableIndex()
{
DBExtend helper = dbHelper;
TModel obj1 = new TModel();
obj1.CheckIndexExists(helper);
}
/// <summary>
/// 填充参数
/// </summary>
/// <param name="db"></param>
internal void FillParames(DBExtend db)
{
//db.ClearParams();
foreach (var n in QueryParames)
{
db.SetParam(n.Key, n.Value);
}
}
/// <summary>
/// 添加一条记录[基本方法]
/// </summary>
/// <typeparam name="TItem"></typeparam>
/// <param name="p"></param>
/// <returns></returns>
public static int Add <TItem>(TItem p) where TItem : TModel, new()
{
DBExtend helper = dbHelper;
int id = helper.InsertFromObj(p);
p.Id = id;
return(id);
}
/// <summary>
/// 创建TABLE[基本方法]
/// </summary>
/// <returns></returns>
public virtual string CreateTable()
{
DBExtend helper = DBExtend;
TModel obj1 = new TModel();
var str = obj1.CreateTable(helper);
return(str);
}
/// <summary>
/// 添加一条记录[基本方法]
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
public int Add(TModel p)
{
DBExtend helper = dbHelper;
int id = helper.InsertFromObj(p);
var field = TypeCache.GetTable(p.GetType()).PrimaryKey;
field.SetValue(p, id);
return(id);
}
/// <summary>
/// 指定格式化更新[基本方法]
/// </summary>
/// <param name="sql"></param>
/// <param name="parame"></param>
/// <param name="types"></param>
/// <returns></returns>
protected static int ExecuteWithFormat(string sql, ParameCollection parame, params Type[] types)
{
DBExtend helper = dbHelper;
foreach (var p in parame)
{
helper.AddParam(p.Key, p.Value);
}
return(helper.Execute(sql, types));
}
/// <summary>
/// 设置当前数据访问上下文,不能跨库
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="baseProvider"></param>
protected void SetContext <T>(BaseProvider <T> baseProvider) where T : IModel, new()
{
var source = baseProvider.GetCurrentDBExtend();
if (source.DatabaseName != dbHelper.DatabaseName)
{
throw new Exception(string.Format("不能跨库设置数据上下文访问,当前:{0} 设置为:{1}", this, baseProvider));
}
dbHelper = source;
}
/// <summary>
/// 指定格式化返回单个结果[基本方法]
/// </summary>
/// <param name="sql"></param>
/// <param name="parame"></param>
/// <param name="types"></param>
/// <returns></returns>
protected T ExecScalarWithFormat <T>(string sql, ParameCollection parame, params Type[] types)
{
DBExtend helper = dbHelper;
foreach (var p in parame)
{
helper.AddParam(p.Key, p.Value);
}
return(helper.ExecScalar <T>(sql, types));
}
/// <summary>
/// lambda查询
/// </summary>
/// <param name="_dbExtend"></param>
/// <param name="_useTableAliasesName">查询是否生成别名</param>
internal LambdaQuery(DBExtend _dbExtend, bool _useTableAliasesName = true)
{
dBExtend = _dbExtend;
dBAdapter = _dbExtend._DBAdapter;
useTableAliasesName = _useTableAliasesName;
TypeCache.SetDBAdapterCache(typeof(T), dBAdapter);
GetPrefix(typeof(T));
QueryTableName = TypeCache.GetTableName(typeof(T));
startTime = DateTime.Now;
}
/// <summary>
/// 指定格式化查询列表[基本方法]
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="parame"></param>
/// <param name="types"></param>
/// <returns></returns>
protected List <T> ExecListWithFormat <T>(string sql, ParameCollection parame, params Type[] types) where T : class, new()
{
DBExtend helper = dbHelper;
foreach (var p in parame)
{
helper.AddParam(p.Key, p.Value);
}
return(helper.ExecList <T>(sql, types));
}
/// <summary>
/// 仅用来检查表结构
/// </summary>
/// <returns></returns>
DBExtend GetBackgroundDBExtend()
{
if (backgroundDBExtend == null)
{
var helper = SettingConfig.GetDbAccess(dbContext.DBLocation);
var dbContext2 = new DbContext(helper, dbContext.DBLocation);
dbContext2.ShardingMainDataIndex = dbContext.ShardingMainDataIndex;
dbContext2.UseSharding = dbContext.UseSharding;
backgroundDBExtend = new DBExtend(dbContext2);
}
return(backgroundDBExtend);
}
/// <summary>
/// 数据访问对象[基本方法]
/// 按指定的类型
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
protected DBExtend GetDbHelper(Type type)
{
if (SettingConfig.GetDbAccess == null)
{
throw new Exception("请配置CRL数据访问对象,实现CRL.SettingConfig.GetDbAccess");
}
CoreHelper.DBHelper helper = SettingConfig.GetDbAccess(type);
var db = new DBExtend(helper);
db.OnUpdateNotifyCacheServer = OnUpdateNotifyCacheServer;
TypeCache.SetDBAdapterCache(typeof(TModel), db._DBAdapter);
return(db);
}
/// <summary>
/// 数据访问对象[基本方法]
/// 按指定的类型
/// </summary>
/// <returns></returns>
protected DBExtend GetDbHelper(Type type = null)
{
var dbContext2 = GetDbContext();
if (type != null)
{
dbLocation.ManageType = type;
}
var db = new DBExtend(dbContext2);
if (dbLocation.ShardingDataBase == null)
{
db.OnUpdateNotifyCacheServer = OnUpdateNotifyCacheServer;
}
return(db);
}
/// <summary>
/// 检查索引
/// </summary>
/// <param name="helper"></param>
/// <returns></returns>
public void CheckIndexExists(DBExtend helper)
{
var list = GetIndexScript(helper);
foreach (var item in list)
{
try
{
helper.Execute(item);
}
catch (Exception ero)//出错,
{
CoreHelper.EventLog.Log(string.Format("创建索引失败:{0}\r\n{1}", ero.Message, item));
}
}
}
//Expression<Func<TModel, bool>> GetQueryId(int id)
//{
// var parameter = Expression.Parameter(typeof(TModel), "b");
// //创建常数
// var constant = Expression.Constant(id);
// var table = TypeCache.GetTable(typeof(TModel));
// MemberExpression member = Expression.PropertyOrField(parameter, table.PrimaryKey.Name);
// var body = Expression.Equal(member, constant);
// //获取Lambda表达式
// var lambda = Expression.Lambda<Func<TModel, Boolean>>(body, parameter);
// return lambda;
//}
/// <summary>
/// 从对象缓存中进行查询一项
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public TModel QueryItemFromCache(int id)
{
if (QueryCacheFromRemote)
{
var expression = DBExtend.GetQueryIdExpression <TModel>(id);
return(QueryItemFromCache(expression));
}
else
{
var all = GetCache(CacheQuery());
if (all.ContainsKey(id))
{
return(all[id]);
}
return(null);
}
}
internal List <string> GetIndexScript(DBExtend helper)
{
var dbAdapter = helper._DBAdapter;
List <string> list2 = new List <string>();
List <Attribute.FieldAttribute> columns = GetColumns(dbAdapter);
foreach (Attribute.FieldAttribute item in columns)
{
if (item.FieldIndexType != Attribute.FieldIndexType.无)
{
//string indexScript = string.Format("CREATE {2} NONCLUSTERED INDEX IX_INDEX_{0}_{1} ON dbo.[{0}]({1})", tableName, item.Name, item.FieldIndexType == Attribute.FieldIndexType.非聚集唯一 ? "UNIQUE" : "");
string indexScript = dbAdapter.GetColumnIndexScript(item);
list2.Add(indexScript);
}
}
return(list2);
}
/// <summary>
/// 对GROUP进行分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query">查询 如:ProductReceiptDetail with (nolock) group by styleid</param>
/// <param name="fields">查询字段 如:styleid,sum(num) as total</param>
/// <param name="rowOver">行排序 如:sum(num) desc</param>
/// <param name="sortfield">排序字段 如:total desc</param>
/// <param name="pageSize">每页大小</param>
/// <param name="pageIndex">页索引</param>
/// <param name="count"></param>
/// <returns></returns>
protected static List <T> GroupByPage <T>(string query, string fields, string rowOver, string sortfield, int pageSize, int pageIndex, out int count) where T : class, new()
{
count = 0;
DBExtend helper = dbHelper;
helper.AddParam("query", query);
helper.AddParam("fields", fields);
helper.AddParam("rowOver", rowOver);
helper.AddParam("sortfield", sortfield);
helper.AddParam("pageSize", pageSize);
helper.AddParam("pageIndex", pageIndex);
helper.AddOutParam("Counts");
var list = helper.RunList <T>("sp_GroupPage");
count = Convert.ToInt32(helper.GetOutParam("Counts"));
return(list);
}
/// <summary>
/// 创建表
/// 会检查表是否存在,如果存在则检查字段
/// </summary>
/// <param name="helper"></param>
/// <param name="message"></param>
/// <returns></returns>
public bool CreateTable(DBExtend helper, out string message)
{
var dbAdapter = helper._DBAdapter;
message = "";
TypeCache.SetDBAdapterCache(GetType(), dbAdapter);
string tableName = TypeCache.GetTableName(GetType());
string sql = dbAdapter.GetSelectTop("0", "from " + tableName, "", 1);
bool needCreate = false;
try
{
//检查表是否存在
helper.Execute(sql);
}
catch
{
needCreate = true;
}
if (needCreate)
{
List <string> list = new List <string>();
try
{
List <Attribute.FieldAttribute> columns = GetColumns(dbAdapter);
dbAdapter.CreateTable(helper, columns, tableName);
message = string.Format("创建表:{0}\r\n", TypeCache.GetTableName(GetType()));
CheckIndexExists(helper);
return(true);
}
catch (Exception ero)
{
message = "创建表时发生错误 类型{0} {1}\r\n";
message = string.Format(message, GetType(), ero.Message);
throw new Exception(message);
return(false);
}
CoreHelper.EventLog.Log(message, "", false);
}
else
{
message = CheckColumnExists(helper);
}
return(true);
}