int IEntityOperator.InsertEntities(ArrayList list)
{
if (list.Count <= 0)
{
return(0);
}
var type = list[0].GetType();
var table = TableInfoManager.GetTable(type);
var columns = table.Columns;
var keyColumn = table.Columns.FirstOrDefault(x => x.Value.IsKey).Value;
var count = 0;
var maxIndex = 0;
bool autoIncreament = keyColumn != null && keyColumn.IsAutoIncreament;
//SqlExecutor executor = null;
var getters = ExpressionReflector.GetGetters(type);
var setters = ExpressionReflector.GetSetters(type);
Action <object, object> keySetter = null;
if (keyColumn != null)
{
keySetter = setters.Get(keyColumn.PropertyInfo.Name);
}
if (!autoIncreament)
{
var obj = _sqlExecutor.ExecuteScalar(string.Format("select max(Count) from {0} where Name='{1}'", ConfigManager.SequenceTable, table.Name), new Dictionary <string, object>());
if (obj == DBNull.Value)
{
_sqlExecutor.ExecuteNonQuery(string.Format("insert into {0}(Name,Count) values('{1}',{2})", ConfigManager.SequenceTable, table.Name, 0), new Dictionary <string, object>());
}
else
{
maxIndex = Convert.ToInt32(obj);
}
}
#region 使用Insert语句插入
int page, limit = 10;
page = (int)Math.Ceiling(list.Count / (double)limit);
int pageIndex = 1;
var insertStart = "insert into {0}({1}) values{2}";
var tableName = string.Empty;
if (!string.IsNullOrWhiteSpace(table.DataBase))
{
tableName = string.Format("[{0}].", table.DataBase);
}
tableName = string.Format("[{0}]", table.Name);
var fields = new List <string>();
var autoincreamentColumn = string.Empty;
foreach (var item in table.Columns.Values)
{
if (item.IsAutoIncreament)
{
autoincreamentColumn = item.Name;
continue;
}
fields.Add(item.Name);
}
while (pageIndex <= page)
{
var start = (pageIndex - 1) * limit;
ArrayList entities = null;
if (start + limit > list.Count)
{
entities = list.GetRange(start, list.Count - start);
}
else
{
entities = list.GetRange(start, limit);
}
var values = new List <string>();
var index = 0;
var sqlParameters = new Dictionary <string, object>();
foreach (var entity in entities)
{
var value = new List <string>();
if (!autoIncreament && keySetter != null)
{
keySetter(entity, ++maxIndex);
}
foreach (var key in getters.Keys)
{
if (autoincreamentColumn == key)
{
continue;
}
value.Add(string.Format("@{0}{1}", key, index));
var valueParam = getters.Get(key)(entity);
var dateValue = valueParam as DateTime?;
if (dateValue != null)
{
if (dateValue.Value.Date == dateValue.Value)
{
valueParam = dateValue.Value.ToString("yyyy-MM-dd");
}
else
{
valueParam = dateValue.Value.ToString("yyyy-MM-dd HH:mm:ss");
}
}
sqlParameters.Add(key + index, valueParam);
}
index++;
values.Add(string.Format("({0})", string.Join(",", value)));
}
insertStart = string.Format(insertStart, tableName, string.Join(",", fields), string.Join(",", values));
count += _sqlExecutor.ExecuteNonQuery(insertStart, sqlParameters);
pageIndex++;
}
#endregion
if (!autoIncreament)
{
_sqlExecutor.ExecuteNonQuery(string.Format("update {0} set [Count]={1} where Name='{2}'", ConfigManager.SequenceTable, maxIndex, table.Name), new Dictionary <string, object>());
}
return(count);
}
int IEntityOperator.InsertEntities(ArrayList list)
{
if (list.Count <= 0)
{
return(0);
}
var type = list[0].GetType();
var table = TableInfoManager.GetTable(type);
var columns = table.Columns;
var keyColumn = table.Columns.FirstOrDefault(x => x.Value.IsKey).Value;
var count = 0;
var maxIndex = 0;
bool autoIncreament = keyColumn != null && keyColumn.IsAutoIncreament;
//SqlExecutor executor = null;
var getters = ExpressionReflector.GetGetters(type);
var setters = ExpressionReflector.GetSetters(type);
Action <object, object> keySetter = null;
if (keyColumn != null)
{
keySetter = setters.Get(keyColumn.PropertyInfo.Name);
}
if (!autoIncreament)
{
var obj = _sqlExecutor.ExecuteScalar(string.Format("select max(Count) from {0} where Name='{1}'", ConfigManager.SequenceTable, table.Name), new Dictionary <string, object>());
if (obj == DBNull.Value)
{
_sqlExecutor.ExecuteNonQuery(string.Format("insert into {0}(Name,Count) values('{1}',{2})", ConfigManager.SequenceTable, table.Name, 0), new Dictionary <string, object>());
}
else
{
maxIndex = Convert.ToInt32(obj);
}
}
if (list.Count <= 10)
{
#region 使用Insert语句插入
var insertStart = "insert into {0}({1}) values{2}";
var tableName = string.Empty;
if (!string.IsNullOrWhiteSpace(table.DataBase))
{
tableName = string.Format("[{0}].", table.DataBase);
}
tableName = string.Format("[{0}]", table.Name);
var fields = new List <string>();
var autoincreamentColumn = string.Empty;
foreach (var item in table.Columns.Values)
{
if (item.IsAutoIncreament)
{
autoincreamentColumn = item.Name;
continue;
}
fields.Add(item.Name);
}
var values = new List <string>();
var index = 0;
var sqlParameters = new Dictionary <string, object>();
foreach (var entity in list)
{
var value = new List <string>();
if (!autoIncreament && keySetter != null)
{
keySetter(entity, ++maxIndex);
}
foreach (var key in getters.Keys)
{
if (autoincreamentColumn == key)
{
continue;
}
value.Add(string.Format("@{0}{1}", key, index));
sqlParameters.Add(key + index, getters.Get(key)(entity));
}
index++;
values.Add(string.Format("({0})", string.Join(",", value)));
}
insertStart = string.Format(insertStart, tableName, string.Join(",", fields), string.Join(",", values));
count = _sqlExecutor.ExecuteNonQuery(insertStart, sqlParameters);
#endregion
}
else
{
#region 使用SqlBulkCopy插入
var sqlBulkCopy = new SqlBulkCopy(DataContext.ConnectionString);
sqlBulkCopy.DestinationTableName = "dbo.[" + table.Name + "]";
if (list.Count > 500000)
{
sqlBulkCopy.BatchSize = list.Count / 10;
}
var dataTable = new DataTable();
foreach (var column in table.Columns.Values)
{
var dataColumn = new DataColumn();
dataColumn.ColumnName = column.Name;
dataColumn.DataType = TypeHelper.GetUnderlyingType(column.PropertyInfo.PropertyType);
dataTable.Columns.Add(dataColumn);
sqlBulkCopy.ColumnMappings.Add(column.Name, column.Name);
}
foreach (var item in list)
{
var row = dataTable.NewRow();
if (!autoIncreament && keySetter != null)
{
keySetter(item, ++maxIndex);
}
foreach (var key in getters.Keys)
{
row[columns.Get(key).Name] = getters.Get(key)(item);
}
dataTable.Rows.Add(row);
}
sqlBulkCopy.WriteToServer(dataTable);
sqlBulkCopy.Close();
#endregion
count = list.Count;
}
if (!autoIncreament)
{
_sqlExecutor.ExecuteNonQuery(string.Format("update {0} set [Count]={1} where Name='{2}'", ConfigManager.SequenceTable, maxIndex, table.Name), new Dictionary <string, object>());
}
return(count);
}
