/// <summary>
/// 获取插入语法
/// </summary>
/// <param name="obj"></param>
/// <param name="helper"></param>
/// <returns></returns>
public override int InsertObject(IModel obj, CoreHelper.DBHelper helper)
{
Type type = obj.GetType();
string table = TypeCache.GetTableName(type);
var typeArry = TypeCache.GetProperties(type, true).Values;
string sql = string.Format("insert into [{0}](", table);
string sql1 = "";
string sql2 = "";
foreach (Attribute.FieldAttribute info in typeArry)
{
string name = info.Name;
if (info.IsPrimaryKey)
{
continue;
}
if (!string.IsNullOrEmpty(info.VirtualField))
{
continue;
}
object value = info.GetValue(obj);
value = ObjectConvert.SetNullValue(value, info.PropertyType);
sql1 += string.Format("{0},", info.KeyWordName);
sql2 += string.Format("@{0},", name);
helper.AddParam(name, value);
}
sql1 = sql1.Substring(0, sql1.Length - 1);
sql2 = sql2.Substring(0, sql2.Length - 1);
sql += sql1 + ") values( " + sql2 + ") ; SELECT scope_identity() ;";
sql = SqlFormat(sql);
return(Convert.ToInt32(helper.ExecScalar(sql)));
}
/// <summary>
/// 批量插入
/// </summary>
/// <typeparam name="TItem"></typeparam>
/// <param name="helper"></param>
/// <param name="details"></param>
/// <param name="keepIdentity"></param>
public override void BatchInsert <TItem>(CoreHelper.DBHelper helper, List <TItem> details, bool keepIdentity = false)
{
string table = TypeCache.GetTableName(typeof(TItem));
string sql = GetSelectTop("*", " from " + table + " where 1=0", "", 1);
DataTable tempTable = helper.ExecDataTable(sql);
var typeArry = TypeCache.GetProperties(typeof(TItem), true).Values;
foreach (TItem item in details)
{
DataRow dr = tempTable.NewRow();
foreach (Attribute.FieldAttribute info in typeArry)
{
string name = info.Name;
object value = info.GetValue(item);
if (!keepIdentity)
{
if (info.IsPrimaryKey)
{
continue;
}
}
if (!string.IsNullOrEmpty(info.VirtualField))
{
continue;
}
var value2 = ObjectConvert.SetNullValue(value, info.PropertyType);
dr[name] = value2;
}
tempTable.Rows.Add(dr);
}
helper.InsertFromDataTable(tempTable, table, keepIdentity);
}
/// <summary>
/// 提取SQL参数
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="manual"></param>
/// <returns></returns>
public virtual string ReplaceParameter(CoreHelper.DBHelper db, string sql, bool manual = false)
{
if (!SettingConfig.ReplaceSqlParameter && !manual)
{
return(sql);
}
//return sql;
var re = @"((\s|,)*)(\w+)\s*(>|<|=|!=|>=|<=)\s*('(.*?)'|([1-9]\d*.\d*|0.\d*[1-9]\d*))(\s|,|\))";
sql = sql + " ";
if (!Regex.IsMatch(sql, re, RegexOptions.IgnoreCase))
{
return(sql);
}
Regex r = new Regex(re, RegexOptions.IgnoreCase);
List <string> pars = new List <string>();
//int index = 1;
for (var m = r.Match(sql); m.Success; m = m.NextMatch())
{
var name = m.Groups[3];
var op = m.Groups[4];
var value1 = m.Groups[6];
var value2 = m.Groups[7];
var value = string.IsNullOrEmpty(value2.Value) ? value1 : value2;
var p = m.Groups[1];
var p2 = m.Groups[8];
var pName = GetParamName("_p", parIndex);
db.AddParam(pName, value.ToString());
sql = sql.Replace(m.ToString(), string.Format("{0}{1}{4}{2}{3} ", p, name, pName, p2, op));
parIndex += 1;
}
return(sql);
}
/// <summary>
/// 数据访问上下文
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="dbLocation"></param>
public DbContext(CoreHelper.DBHelper dbHelper, DBLocation dbLocation)
{
DBHelper = dbHelper;
DBLocation = dbLocation;
//todo 按数据库类型类型判断
DataBaseArchitecture = dbHelper.CurrentDBType == CoreHelper.DBType.MongoDB ? DataBaseArchitecture.NotRelation : CRL.DataBaseArchitecture.Relation;
}
/// <summary>
/// 批量插入,mysql不支持批量插入
/// </summary>
/// <typeparam name="TItem"></typeparam>
/// <param name="helper"></param>
/// <param name="details"></param>
/// <param name="keepIdentity"></param>
public override void BatchInsert <TItem>(CoreHelper.DBHelper helper, List <TItem> details, bool keepIdentity = false)
{
foreach (var item in details)
{
helper.ClearParams();
InsertObject(item, helper);
}
}
/// <summary>
/// 构造DBExtend
/// </summary>
/// <param name="_helper"></param>
public DBExtend(CoreHelper.DBHelper _helper)
{
if (_helper == null)
{
throw new Exception("数据访问对象未实例化,请实现CRL.SettingConfig.GetDbAccess");
}
GUID = Guid.NewGuid();
helper = _helper;
}
/// <summary>
/// 开始物务
/// </summary>
public override void BeginTran(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
{
if (currentTransStatus != TranStatus.未开始)
{
throw new CRLException("事务开始失败,已有未完成的事务");
}
transDb = GetDBHelper();
transDb.BeginTran(isolationLevel);
currentTransStatus = TranStatus.已开始;
}
/// <summary>
/// 开始物务
/// </summary>
public override void BeginTran()
{
if (currentTransStatus != TranStatus.未开始)
{
throw new CRLException("事务开始失败,已有未完成的事务");
}
transDb = GetDBHelper();
transDb.BeginTran();
currentTransStatus = TranStatus.已开始;
}
internal static int Execute(CoreHelper.DBHelper __DbHelper, string sql)
{
int n = 0;
var el = Run(() =>
{
n = __DbHelper.Execute(sql);
});
Base.SaveSQLRunningtme(sql, el);
return(n);
}
internal static object ExecScalar(CoreHelper.DBHelper __DbHelper, string sql)
{
object obj = null;
var el = Run(() =>
{
obj = __DbHelper.ExecScalar(sql);
});
Base.SaveSQLRunningtme(sql, el);
return(obj);
}
/// <summary>
/// 构造DBExtend
/// </summary>
/// <param name="_dbContext"></param>
public DBExtend(DbContext _dbContext)
{
dbContext = _dbContext;
var _helper = _dbContext.DBHelper;
if (_helper == null)
{
throw new Exception("数据访问对象未实例化,请实现CRL.SettingConfig.GetDbAccess");
}
GUID = Guid.NewGuid();
dbHelper = _helper;
}
/// <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>
/// page
/// </summary>
/// <param name="helper"></param>
/// <param name="query"></param>
/// <param name="fields"></param>
/// <param name="sort"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
internal virtual CallBackDataReader GetPageData(CoreHelper.DBHelper helper, string query, string fields, string sort, int pageSize, int pageIndex)
{
helper.AddParam("query_", query);
helper.AddParam("fields_", fields);
helper.AddParam("sort_", sort);
helper.AddParam("pageSize_", pageSize);
helper.AddParam("pageIndex_", pageIndex);
helper.AddOutParam("count_", 1);
//var reader = helper.RunDataReader("sp_page");
var reader = new CallBackDataReader(helper.RunDataReader("sp_page"), () =>
{
return(Convert.ToInt32(helper.GetOutParam("count_")));
});
return(reader);
}
/// <summary>
/// 检测所有对象
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="baseType"></param>
/// <returns></returns>
public static string CheckAllModel(CoreHelper.DBHelper dbHelper, Type baseType)
{
string msg = "";
var helper = new CRL.DBExtend(dbHelper);
var assembyle = System.Reflection.Assembly.GetAssembly(baseType);
Type[] types = assembyle.GetTypes();
List <Type> findTypes = new List <Type>();
var typeCRL = typeof(CRL.IModel);
foreach (var type in types)
{
if (type.IsClass)
{
var type1 = type.BaseType;
while (type1.BaseType != null)
{
if (type1.BaseType == typeCRL || type1 == typeCRL)
{
findTypes.Add(type);
break;
}
type1 = type1.BaseType;
}
}
}
try
{
foreach (var type in findTypes)
{
try
{
object obj = System.Activator.CreateInstance(type);
CRL.IModel b = obj as CRL.IModel;
msg += b.CreateTable(helper);
}
catch { }
}
}
catch (Exception ero) { }
CoreHelper.EventLog.Log(msg);
return(msg);
}
/// <summary>
/// 获取插入语法
/// </summary>
/// <param name="obj"></param>
/// <param name="helper"></param>
/// <returns></returns>
public override int InsertObject(IModel obj, CoreHelper.DBHelper helper)
{
Type type = obj.GetType();
string table = TypeCache.GetTableName(type);
var typeArry = TypeCache.GetProperties(type, true).Values;
string sql = string.Format("insert into {0}(", table);
string sql1 = "";
string sql2 = "";
string sequenceName = string.Format("{0}_sequence", table);
var sqlGetIndex = string.Format("select {0}.nextval from dual", sequenceName);//oracle不能同时执行多条语句
int id = Convert.ToInt32(helper.ExecScalar(sqlGetIndex));
foreach (Attribute.FieldAttribute info in typeArry)
{
string name = info.Name;
if (info.IsPrimaryKey)
{
//continue;//手动插入ID
}
if (!string.IsNullOrEmpty(info.VirtualField))
{
continue;
}
object value = info.GetValue(obj);
value = ObjectConvert.SetNullValue(value, info.PropertyType);
sql1 += string.Format("{0},", info.KeyWordName);
sql2 += string.Format("@{0},", info.KeyWordName);
helper.AddParam(info.KeyWordName, value);
}
sql1 = sql1.Substring(0, sql1.Length - 1);
sql2 = sql2.Substring(0, sql2.Length - 1);
sql += sql1 + ") values( " + sql2 + ")";
sql = SqlFormat(sql);
var primaryKey = TypeCache.GetTable(obj.GetType()).PrimaryKey;
helper.SetParam(primaryKey.Name, id);
helper.Execute(sql);
//var helper2 = helper as CoreHelper.OracleHelper;
//int id = helper2.Insert(sql,sequenceName);
return(id);
}
public override string GetAllTablesSql(CoreHelper.DBHelper helper)
{
return("select lower(table_name),1 from information_schema.tables where table_schema='" + helper.DatabaseName + "' ");
}
/// <summary>
/// 数据访问上下文
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="dbLocation"></param>
public DbContext(CoreHelper.DBHelper dbHelper, DBLocation dbLocation)
{
DBHelper = dbHelper;
DBLocation = dbLocation;
}
/// <summary>
/// 分页SQL 默认为MSSQL
/// </summary>
/// <param name="db"></param>
/// <param name="fields"></param>
/// <param name="rowOver"></param>
/// <param name="condition"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <param name="sort"></param>
/// <returns></returns>
public virtual string PageSqlFormat(CoreHelper.DBHelper db, string fields, string rowOver, string condition, int start, int end, string sort)
{
string sql = "SELECT * FROM (select {0},ROW_NUMBER() OVER ( Order by {1} ) AS RowNumber {2}) T WHERE T.RowNumber BETWEEN {3} AND {4} order by RowNumber";
return(string.Format(sql, fields, rowOver, condition, start, end));
}
public DBAdapterBase(DbContext _dbContext)
{
dbContext = _dbContext;
helper = dbContext.DBHelper;
}
public override string GetAllTablesSql(CoreHelper.DBHelper helper)
{
return("select Lower(name),id from sysobjects where type='u'");
}
public override string GetAllSPSql(CoreHelper.DBHelper helper)
{
return("select name,id from sysobjects where type='P'");
}
public override string GetAllSPSql(CoreHelper.DBHelper helper)
{
return("select object_name,1 from user_objects where object_type='PROCEDURE'");
}
public override string GetAllTablesSql(CoreHelper.DBHelper helper)
{
return("SELECT lower(table_name),1 FROM user_TABLES");
}
/// <summary> /// 插入对象 /// </summary> /// <param name="obj"></param> /// <param name="helper"></param> /// <returns></returns> public abstract int InsertObject(CRL.IModel obj, CoreHelper.DBHelper helper);
/// <summary> /// 批量插入方法 /// </summary> /// <typeparam name="TItem"></typeparam> /// <param name="helper"></param> /// <param name="details"></param> /// <param name="keepIdentity">否保持自增主键</param> public abstract void BatchInsert <TItem>(CoreHelper.DBHelper helper, List <TItem> details, bool keepIdentity = false) where TItem : IModel, new();
/// <summary> /// 获取所有存储过程 /// </summary> /// <returns></returns> public abstract string GetAllSPSql(CoreHelper.DBHelper helper);
/// <summary> /// 获取所有表,查询需要转为小写 /// </summary> /// <returns></returns> public abstract string GetAllTablesSql(CoreHelper.DBHelper helper);
public DBAdapterBase(DbContext _dbContext)
{
dbContext = _dbContext;
helper = dbContext.DBHelper;
}
public override string GetAllSPSql(CoreHelper.DBHelper helper)
{
return("select `name`,1 from mysql.proc where db = '" + helper.DatabaseName + "' and `type` = 'PROCEDURE' ");
}
/// <summary>
/// SQL语句转换为存储过程
/// </summary>
/// <param name="template"></param>
/// <param name="dbHelper"></param>
/// <param name="sql"></param>
/// <param name="procedureName"></param>
/// <param name="templateParame"></param>
/// <returns></returns>
internal static string SqlToProcedure(string template, CoreHelper.DBHelper dbHelper, string sql, string procedureName, Dictionary <string, string> templateParame = null)
{
var adpater = DBAdapter.DBAdapterBase.GetDBAdapterBase(dbHelper.CurrentDBType);
template = template.Trim();
Regex r = new Regex(@"\@(\w+)", RegexOptions.IgnoreCase);//like @parame
Match m;
List <string> pars = new List <string>();
for (m = r.Match(sql); m.Success; m = m.NextMatch())
{
string par = m.Groups[1].ToString();
if (!pars.Contains(par))
{
pars.Add(par);
}
}
var typeMappint = adpater.GetFieldMapping();
//string template = Properties.Resources.pageTemplate.Trim();
sql = sql.Replace("'", "''");//单引号过滤
template = template.Replace("{name}", procedureName);
template = template.Replace("{sql}", sql);
string parames = "";
//构造参数
foreach (var p in dbHelper.Params)
{
string key = p.Key.Replace("@", "");
var t = p.Value.GetType();
if (!typeMappint.ContainsKey(t))
{
throw new Exception(string.Format("找不到对应的字段类型映射 {0} 在 {1}", t, adpater));
}
var par = typeMappint[t];
if (t == typeof(System.String))
{
par = string.Format(par, 500);
}
parames += adpater.SpParameFormat(key, par, false);
}
foreach (var p in dbHelper.OutParams)
{
string key = p.Key;
var t = p.Value.GetType();
var par = typeMappint[t];
parames += adpater.SpParameFormat(key, par, true);
}
if (parames.Length > 0)
{
parames = parames.Substring(0, parames.Length - 1);
}
template = template.Replace("{parame}", parames);
if (templateParame != null)
{
foreach (var item in templateParame)
{
var value = item.Value;
value = value.Replace("'", "''");//单引号过滤
template = template.Replace("{" + item.Key + "}", value);
}
}
template = adpater.GetCreateSpScript(procedureName, template);
return(template);
}
