/// <summary>
/// 获取插入语法
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public override object InsertObject <T>(DbContext dbContext, T obj)
{
Type type = obj.GetType();
var helper = dbContext.DBHelper;
var table = TypeCache.GetTable(type);
var primaryKey = table.PrimaryKey;
object id;
if (primaryKey.KeepIdentity)
{
id = primaryKey.GetValue(obj);
}
else
{
string sequenceName = string.Format("{0}_sequence", table.TableName);
var sqlGetIndex = string.Format("select {0}.nextval from dual", sequenceName);//oracle不能同时执行多条语句
id = SqlStopWatch.ExecScalar(helper, sqlGetIndex);
primaryKey.SetValue(obj, Convert.ChangeType(id, primaryKey.PropertyType));
}
var sql = GetInsertSql(dbContext, table, obj);
//helper.SetParam(primaryKey.MapingName, id);
SqlStopWatch.Execute(helper, sql);
//var helper2 = helper as OracleHelper;
//int id = helper2.Insert(sql,sequenceName);
return(id);
}
/// <summary>
/// 获取插入语法
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public override object InsertObject(DbContext dbContext, IModel obj)
{
var helper = dbContext.DBHelper;
Type type = obj.GetType();
string table = TypeCache.GetTableName(type, dbContext);
var typeArry = TypeCache.GetProperties(type, true).Values;
Attribute.FieldAttribute primaryKey = null;
string sql = string.Format("insert into `{0}`(", table);
string sql1 = "";
string sql2 = "";
foreach (Attribute.FieldAttribute info in typeArry)
{
//if (info.FieldType != Attribute.FieldType.数据库字段)
//{
// continue;
//}
string name = info.MapingName;
if (info.IsPrimaryKey)
{
primaryKey = info;
}
if (info.IsPrimaryKey && !info.KeepIdentity)
{
continue;
}
//if (!string.IsNullOrEmpty(info.VirtualField))
//{
// continue;
//}
object value = info.GetValue(obj);
if (info.PropertyType.FullName.StartsWith("System.Nullable"))//Nullable<T>类型为空值不插入
{
if (value == null)
{
continue;
}
}
value = ObjectConvert.CheckNullValue(value, info.PropertyType);
sql1 += string.Format("{0},", FieldNameFormat(info));
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 + ") ; ";
sql = SqlFormat(sql);
if (primaryKey.KeepIdentity)
{
helper.Execute(sql);
return(primaryKey.GetValue(obj));
}
else
{
sql += "SELECT LAST_INSERT_ID();";
return(SqlStopWatch.ExecScalar(helper, sql));
}
}
/// <summary>
/// 指定替换对象返回单个结果
/// </summary>
/// <param name="sql"></param>
/// <param name="types">格式化SQL语句的关键类型</param>
/// <returns></returns>
public override object ExecScalar(string sql, params Type[] types)
{
sql = AutoFormat(sql, types);
sql = _DBAdapter.SqlFormat(sql);
var db = GetDBHelper(AccessType.Read);
object obj = SqlStopWatch.ExecScalar(db, sql);
ClearParame();
return(obj);
}
/// <summary>
/// 获取插入语法
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public override object InsertObject(DbContext dbContext, IModel obj)
{
var helper = dbContext.DBHelper;
Type type = obj.GetType();
string table = TypeCache.GetTableName(type, dbContext);
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(SqlStopWatch.ExecScalar(helper, sqlGetIndex));
foreach (Attribute.FieldAttribute info in typeArry)
{
//if (info.FieldType != Attribute.FieldType.数据库字段)
//{
// continue;
//}
string name = info.MapingName;
if (info.IsPrimaryKey && !info.KeepIdentity)
{
//continue;//手动插入ID
}
//if (!string.IsNullOrEmpty(info.VirtualField))
//{
// continue;
//}
object value = info.GetValue(obj);
if (info.PropertyType.FullName.StartsWith("System.Nullable"))//Nullable<T>类型为空值不插入
{
if (value == null)
{
continue;
}
}
value = ObjectConvert.CheckNullValue(value, info.PropertyType);
sql1 += string.Format("{0},", info.MapingName);
sql2 += string.Format("@{0},", info.MapingName);
helper.AddParam(info.MapingName, 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.MapingName, id);
helper.Execute(sql);
//var helper2 = helper as CoreHelper.OracleHelper;
//int id = helper2.Insert(sql,sequenceName);
return(id);
}
/// <summary>
/// 指定替换对象返回单个结果
/// </summary>
/// <param name="sql"></param>
/// <param name="types">格式化SQL语句的关键类型</param>
/// <returns></returns>
public override object ExecScalar(string sql)
{
sql = _DBAdapter.SqlFormat(sql);
var db = GetDBHelper(DataAccessType.Read);
sql = _DBAdapter.ReplaceParameter(db, sql);
object obj = SqlStopWatch.ExecScalar(db, sql);
ClearParame();
return(obj);
}
/// <summary>
/// 获取插入语法
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public override object InsertObject <T>(DbContext dbContext, T obj)
{
Type type = obj.GetType();
var helper = dbContext.DBHelper;
var table = TypeCache.GetTable(type);
var primaryKey = table.PrimaryKey;
var sql = GetInsertSql(dbContext, table, obj);
if (primaryKey.KeepIdentity)
{
SqlStopWatch.Execute(helper, sql);
return(primaryKey.GetValue(obj));
}
else
{
sql += ";SELECT LAST_INSERT_ID();";
return(SqlStopWatch.ExecScalar(helper, sql));
}
}
/// <summary>
/// GROUP和是否编译判断
/// </summary>
/// <param name="query1"></param>
/// <returns></returns>
internal CallBackDataReader GetPageReader(LambdaQueryBase query1)
{
if (query1.__GroupFields != null)
{
return(GetGroupPageReader(query1));
}
if (_DBAdapter.CanCompileSP && query1.__CompileSp)
{
return(GetSpPageReader(query1));
}
CheckTableCreated(query1.__MainType);
//var fields = query.GetQueryFieldString(b => b.Length > 500 || b.PropertyType == typeof(byte[]));
var fields = query1.GetQueryFieldString();
var rowOver = query1.GetOrder();
if (string.IsNullOrEmpty(rowOver))
{
var table = TypeCache.GetTable(query1.__MainType);
rowOver = string.Format("t1.{0} desc", table.PrimaryKey.MapingName);
}
var orderBy = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "t.");
var sb = new StringBuilder();
query1.GetQueryConditions(sb);
var condition = sb.ToString();
condition = _DBAdapter.SqlFormat(condition);
query1.FillParames(this);
var pageIndex = query1.SkipPage;
var pageSize = query1.TakeNum;
pageIndex = pageIndex == 0 ? 1 : pageIndex;
pageSize = pageSize == 0 ? 15 : pageSize;
string countSql = string.Format("select count(*) from {0}", condition);
var db = GetDBHelper(AccessType.Read);
int count = Convert.ToInt32(SqlStopWatch.ExecScalar(db, countSql));
query1.ExecuteTime += db.ExecuteTime;
query1.RowCount = count;
//if (count == 0)
//{
// return null;
//}
int pageCount = (count + pageSize - 1) / pageSize;
if (pageIndex > pageCount)
{
pageIndex = pageCount;
}
var start = pageSize * (pageIndex - 1) + 1;
var end = start + pageSize - 1;
string sql = _DBAdapter.PageSqlFormat(fields, rowOver, condition, start, end, orderBy);
var reader = new CallBackDataReader(db.ExecDataReader(sql), () =>
{
return(count);
}, sql);
query1.ExecuteTime += db.ExecuteTime;
ClearParame();
return(reader);
}
/// <summary>
/// 按是否能编译
/// </summary>
/// <param name="query1"></param>
/// <returns></returns>
CallBackDataReader GetGroupPageReader(LambdaQueryBase query1)
{
//var query1 = query as RelationLambdaQuery<TModel>;
if (_DBAdapter.CanCompileSP && query1.__CompileSp)
{
return(GetSpGroupPageReader(query1));
}
CheckTableCreated(query1.__MainType);
var sb = new StringBuilder();
query1.GetQueryConditions(sb);
var condition = sb.ToString();
var fields = query1.GetQueryFieldString();
if (!condition.Contains("group"))
{
throw new CRLException("缺少group语法");
}
var rowOver = query1.GetOrder();
if (string.IsNullOrEmpty(rowOver))
{
throw new CRLException("Group分页需指定Group排序字段");
//var table = TypeCache.GetTable(typeof(T));
//rowOver = string.Format("t1.{0} desc", table.PrimaryKey.Name);
}
var sort1 = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "");
condition = _DBAdapter.SqlFormat(condition);
query1.FillParames(this);
var pageIndex = query1.SkipPage;
var pageSize = query1.TakeNum;
pageIndex = pageIndex == 0 ? 1 : pageIndex;
pageSize = pageSize == 0 ? 15 : pageSize;
string countSql = string.Format("select count(*) from (select count(*) as a from {0}) t", condition);
var db = GetDBHelper(AccessType.Read);
int count = Convert.ToInt32(SqlStopWatch.ExecScalar(db, countSql));
query1.ExecuteTime += db.ExecuteTime;
query1.RowCount = count;
//if (count == 0)
//{
// return null;
//}
int pageCount = (count + pageSize - 1) / pageSize;
if (pageIndex > pageCount)
{
pageIndex = pageCount;
}
var start = pageSize * (pageIndex - 1) + 1;
var end = start + pageSize - 1;
string sql = _DBAdapter.PageSqlFormat(fields, rowOver, condition, start, end, "");
//System.Data.Common.DbDataReader reader;
//reader = dbHelper.ExecDataReader(sql);
var reader = new CallBackDataReader(db.ExecDataReader(sql), () =>
{
return(count);
}, sql);
query1.ExecuteTime += db.ExecuteTime;
ClearParame();
return(reader);
}
/// <summary>
/// 插入对象,并返回主键
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public override object InsertObject <T>(DbContext dbContext, T obj)
{
Type type = obj.GetType();
var helper = dbContext.DBHelper;
var table = TypeCache.GetTable(type);
var primaryKey = table.PrimaryKey;
//string table = TypeCache.GetTableName(type, dbContext);
//var typeArry = TypeCache.GetProperties(type, true).Values;
//Attribute.FieldAttribute primaryKey = null;
//string sql = string.Format("insert into [{0}](", table);
//string sql1 = "";
//string sql2 = "";
//foreach (Attribute.FieldAttribute info in typeArry)
//{
// //if (info.FieldType != Attribute.FieldType.数据库字段)
// //{
// // continue;
// //}
// string name = info.MapingName;
// if (info.IsPrimaryKey)
// {
// primaryKey = info;
// }
// if (info.IsPrimaryKey && !info.KeepIdentity)
// {
// continue;
// }
// //if (!string.IsNullOrEmpty(info.VirtualField))
// //{
// // continue;
// //}
// object value = info.GetValue(obj);
// if (info.PropertyType.FullName.StartsWith("System.Nullable"))//Nullable<T>类型为空值不插入
// {
// if (value == null)
// {
// continue;
// }
// }
// value = ObjectConvert.CheckNullValue(value, info.PropertyType);
// sql1 += string.Format("{0},", FieldNameFormat(info));
// 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 + ") ; ";
//sql = SqlFormat(sql);
var sql = GetInsertSql(dbContext, table, obj);
if (primaryKey.KeepIdentity)
{
SqlStopWatch.Execute(helper, sql);
return(primaryKey.GetValue(obj));
}
else
{
sql += ";SELECT scope_identity() ;";
return(SqlStopWatch.ExecScalar(helper, sql));
}
}