/// <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));
}
}
protected string GetInsertSql(DbContext dbContext, Attribute.TableInnerAttribute table, object obj, bool fillParame = true)
{
Type type = obj.GetType();
var key = string.Format("{0}_{1}", type, fillParame);
var helper = dbContext.DBHelper;
var tableName = table.TableName;
//var primaryKey = table.PrimaryKey;
var typeArry = table.Fields;
//var reflect = ReflectionHelper.GetInfo<T>();
string sql;
var cached = insertSqlCache.TryGetValue(key, out sql);
if (!cached)
{
sql = string.Format("insert into {0}(", KeyWordFormat(tableName));
}
string sql1 = "";
string sql2 = "";
foreach (Attribute.FieldInnerAttribute info in typeArry)
{
string name = info.MapingName;
if (info.IsPrimaryKey && !info.KeepIdentity && DBType != DBType.ORACLE)
{
continue;
}
object value = info.GetValue(obj);
value = ObjectConvert.CheckNullValue(value, info.PropertyType);
if (!cached)
{
sql1 += string.Format("{0},", FieldNameFormat(info));
if (fillParame)
{
var par = GetParamName(name, "");
sql2 += string.Format("{0},", par);//@{0}
}
}
if (fillParame)
{
helper.AddParam(name, value);
}
}
if (!cached)
{
sql1 = sql1.Substring(0, sql1.Length - 1) + ") values";
sql += sql1;
if (fillParame)
{
sql2 = sql2.Substring(0, sql2.Length - 1);
sql += "( " + sql2 + ")";
}
//sql = SqlFormat(sql);
insertSqlCache.TryAdd(key, sql);
}
return(sql);
}
/// <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>
/// 批量插入,mysql不支持批量插入
/// </summary>
/// <param name="details"></param>
/// <param name="keepIdentity"></param>
public override void BatchInsert(DbContext dbContext, System.Collections.IList details, bool keepIdentity = false)
{
if (details == null || details.Count == 0)
{
return;
}
var type = details[0].GetType();
var table = TypeCache.GetTable(type);
var typeArry = table.Fields;
var sql = GetInsertSql(dbContext, table, details[0], false);
var helper = dbContext.DBHelper;
var sb = new StringBuilder();
//var reflect = ReflectionHelper.GetInfo<T>();
foreach (var item in details)
{
var line = "(";
foreach (Attribute.FieldAttribute info in typeArry)
{
string name = info.MapingName;
if (info.IsPrimaryKey && !info.KeepIdentity)
{
continue;
}
var value = info.GetValue(item);
//if (info.PropertyType.FullName.StartsWith("System.Nullable"))//Nullable<T>类型为空值不插入
//{
// if (value == null)
// {
// continue;
// }
//}
value = ObjectConvert.CheckNullValue(value, info.PropertyType);
if (value != null && value != DBNull.Value)
{
if (info.PropertyType == typeof(string))
{
value = value.ToString().Replace("'", "\'");
}
line += string.Format("'{0}',", value);
}
else
{
line += "null,";
}
}
line = line.Substring(0, line.Length - 1);
line += "),";
sb.Append(line);
}
sb.Remove(sb.Length - 1, 1);
helper.Execute(sql + sb.ToString());
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="details"></param>
/// <param name="keepIdentity"></param>
public override void BatchInsert(System.Collections.IList details, bool keepIdentity = false)
{
if (details.Count == 0)
{
return;
}
var type = details[0].GetType();
string table = TypeCache.GetTableName(type, dbContext);
table = KeyWordFormat(table);
string sql = GetSelectTop("*", " from " + table + " where 1=0", "", 1);
DataTable tempTable = helper.ExecDataTable(sql);
var typeArry = TypeCache.GetProperties(type, true).Values;
foreach (var item in details)
{
DataRow dr = tempTable.NewRow();
foreach (Attribute.FieldAttribute info in typeArry)
{
if (info.FieldType != Attribute.FieldType.数据库字段)
{
continue;
}
string name = info.MapingName;
object value = info.GetValue(item);
if (!keepIdentity)
{
if (info.IsPrimaryKey)
{
continue;
}
}
if (!string.IsNullOrEmpty(info.VirtualField))
{
continue;
}
var value2 = ObjectConvert.CheckNullValue(value, info.PropertyType);
if (info.PropertyType.FullName.StartsWith("System.Nullable"))//Nullable<T>类型为空值不插入
{
if (value2 == null)
{
continue;
}
}
dr[name] = value2;
}
tempTable.Rows.Add(dr);
}
helper.InsertFromDataTable(tempTable, table, keepIdentity);
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="details"></param>
/// <param name="keepIdentity"></param>
public override void BatchInsert(DbContext dbContext, System.Collections.IList details, bool keepIdentity = false)
{
if (details.Count == 0)
{
return;
}
var type = details[0].GetType();
var table = TypeCache.GetTable(type);
var tableName = KeyWordFormat(table.TableName);
var helper = dbContext.DBHelper;
var sb = new StringBuilder();
GetSelectTop(sb, "*", b =>
{
b.Append(" from " + tableName + " where 1=0");
}, "", 1);
var sql = sb.ToString();
var tempTable = helper.ExecDataTable(sql);
var typeArry = table.Fields;
foreach (var item in details)
{
var dr = tempTable.NewRow();
foreach (Attribute.FieldInnerAttribute info in typeArry)
{
string name = info.MapingName;
object value = info.GetValue(item);
if (!keepIdentity)
{
if (info.IsPrimaryKey)
{
continue;
}
}
var value2 = ObjectConvert.CheckNullValue(value, info.PropertyType);
dr[name] = value2;
}
tempTable.Rows.Add(dr);
}
helper.InsertFromDataTable(tempTable, tableName, keepIdentity);
}
/// <summary>
/// 设置参数
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public override void SetParam(string name, object value)
{
value = ObjectConvert.CheckNullValue(value);
dbHelper.SetParam(name, value);
}
//static System.Collections.Concurrent.ConcurrentDictionary <string, DataTable> cacheTables = new System.Collections.Concurrent.ConcurrentDictionary<string, DataTable>();
/// <summary>
/// 批量插入
/// </summary>
/// <param name="details"></param>
/// <param name="keepIdentity"></param>
public override void BatchInsert(DbContext dbContext, System.Collections.IList details, bool keepIdentity = false)
{
if (details.Count == 0)
{
return;
}
var type = details[0].GetType();
var table = TypeCache.GetTable(type);
var tableName = KeyWordFormat(table.TableName);
var helper = dbContext.DBHelper;
//DataTable tempTable;
//var key = string.Format("{0}_{1}", dbContext.DBHelper.DatabaseName, type.Name);
//if (!cacheTables.ContainsKey(key))
//{
// var sb = new StringBuilder();
// GetSelectTop(sb,"*", b=>
// {
// b.Append(" from " + tableName + " where 1=0");
// }, "", 1);
// var sql = sb.ToString();
// var tempTable2 = helper.ExecDataTable(sql);
// //cacheTables.TryAdd(key, tempTable2);//暂每次动态查询
// tempTable = tempTable2.Clone();//创建一个副本
//}
//else
//{
// tempTable = cacheTables[key].Clone();
//}
var sb = new StringBuilder();
GetSelectTop(sb, "*", b =>
{
b.Append(" from " + tableName + " where 1=0");
}, "", 1);
var sql = sb.ToString();
var tempTable = helper.ExecDataTable(sql);
////字段顺序得和表一至,不然插入出错
//DataTable tempTable = new DataTable() { TableName = tableName };
//foreach (var f in table.Fields)
//{
// var column = new DataColumn() { ColumnName = f.MapingName, DataType = f.PropertyType, AllowDBNull = true };
// tempTable.Columns.Add(column);
//}
var typeArry = table.Fields;
foreach (var item in details)
{
DataRow dr = tempTable.NewRow();
foreach (Attribute.FieldInnerAttribute info in typeArry)
{
string name = info.MapingName;
object value = info.GetValue(item);
if (!keepIdentity)
{
if (info.IsPrimaryKey)
{
continue;
}
}
var value2 = ObjectConvert.CheckNullValue(value, info.PropertyType);
dr[name] = value2;
}
tempTable.Rows.Add(dr);
}
helper.InsertFromDataTable(tempTable, tableName, keepIdentity);
}
/// <summary>
/// 格式化为更新值查询
/// </summary>
/// <param name="setValue"></param>
/// <param name="joinType"></param>
/// <returns></returns>
string ForamtSetValue <T>(ParameCollection setValue, Type joinType = null) where T : IModel
{
//string tableName = TypeCache.GetTableName(typeof(T), dbContext);
string setString = "";
var fields = TypeCache.GetProperties(typeof(T), true);
foreach (var pair in setValue)
{
string name = pair.Key;
object value = pair.Value;
value = ObjectConvert.CheckNullValue(value);
if (name.StartsWith("$"))//直接按值拼接 c2["$SoldCount"] = "SoldCount+" + num;
{
name = name.Substring(1, name.Length - 1);
if (!fields.ContainsKey(name))
{
throw new CRLException("找不到对应的字段,在" + typeof(T) + ",名称" + name);
}
var field = fields[name];
string value1 = value.ToString();
//未处理空格
value1 = System.Text.RegularExpressions.Regex.Replace(value1, name + @"([\+\-])", field.MapingName + "$1", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
name = field.MapingName;
value = value1;
setString += string.Format(" {0}={1},", _DBAdapter.KeyWordFormat(name), value);
}
else
{
if (joinType != null && value.ToString().Contains("$"))//当是关联更新
{
if (!fields.ContainsKey(name))
{
throw new CRLException("找不到对应的字段,在" + typeof(T) + ",名称" + name);
}
var field = fields[name];
name = field.MapingName;//转换映射名
var fields2 = TypeCache.GetProperties(joinType, true);
var value1 = System.Text.RegularExpressions.Regex.Match(value.ToString(), @"\$(\w+)", System.Text.RegularExpressions.RegexOptions.IgnoreCase).Groups[1].Value;
if (!fields2.ContainsKey(value1))
{
throw new CRLException("找不到对应的字段,在" + joinType + ",名称" + value1);
}
var field2 = fields2[value1];
value = value.ToString().Replace("$" + value1, "t2." + field2.MapingName);//右边字段需加前辍
name = string.Format("t1.{0}", name);
}
else
{
if (!fields.ContainsKey(name))
{
throw new CRLException("找不到对应的字段,在" + typeof(T) + ",名称" + name);
}
var field = fields[name];
name = field.MapingName;//转换映射名
string parame = string.Format("@{0}", name, dbContext.parIndex);
AddParam(name, value);
dbContext.parIndex += 1;
value = parame;
}
setString += string.Format(" {0}={1},", name, value);
}
}
setString = setString.Substring(0, setString.Length - 1);
return(setString);
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="details"></param>
/// <param name="keepIdentity"></param>
public override void BatchInsert(DbContext dbContext, System.Collections.IList details, bool keepIdentity = false)
{
if (details.Count == 0)
{
return;
}
var type = details[0].GetType();
var table = TypeCache.GetTable(type);
var tableName = KeyWordFormat(table.TableName);
var helper = dbContext.DBHelper;
DataTable tempTable;
if (!cacheTables.ContainsKey(type))
{
var sb = new StringBuilder();
GetSelectTop(sb, "*", b =>
{
b.Append(" from " + tableName + " where 1=0");
}, "", 1);
var sql = sb.ToString();
var tempTable2 = helper.ExecDataTable(sql);
cacheTables.Add(type, tempTable2);
tempTable = tempTable2.Clone();//创建一个副本
}
else
{
tempTable = cacheTables[type].Clone();
}
////字段顺序得和表一至,不然插入出错
//DataTable tempTable = new DataTable() { TableName = tableName };
//foreach (var f in table.Fields)
//{
// var column = new DataColumn() { ColumnName = f.MapingName, DataType = f.PropertyType, AllowDBNull = true };
// tempTable.Columns.Add(column);
//}
var typeArry = table.Fields;
foreach (var item in details)
{
DataRow dr = tempTable.NewRow();
foreach (Attribute.FieldAttribute info in typeArry)
{
//if (info.FieldType != Attribute.FieldType.数据库字段)
//{
// continue;
//}
string name = info.MapingName;
object value = info.GetValue(item);
if (!keepIdentity)
{
if (info.IsPrimaryKey)
{
continue;
}
}
var value2 = ObjectConvert.CheckNullValue(value, info.PropertyType);
if (info.PropertyType.FullName.StartsWith("System.Nullable"))//Nullable<T>类型为空值不插入
{
if (value2 == null)
{
continue;
}
}
dr[name] = value2;
}
tempTable.Rows.Add(dr);
}
helper.InsertFromDataTable(tempTable, tableName, keepIdentity);
}
/// <summary>
/// 增加参数
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public override void AddParam(string name, object value)
{
value = ObjectConvert.CheckNullValue(value);
__DbHelper.AddParam(name, value);
}