/// <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); }