/// <summary>
/// 临时数据刷进数据库，仅支持sqlserver
/// </summary>
/// <param name="database"></param>
/// <returns></returns>
public bool FlushToTable(string database = "DefaultDB", string insertDuplicate = null)
{
if (Count() == 0)
{
return(true);
}
if (string.IsNullOrEmpty(TabName))
{
throw new Exception("未定义表名");
}
DataContextMoudle <T> dataContext = DataContextMoudelFactory <T> .GetDataContext(database);
if (dataContext is MSSqlDataContextMoudle <T> )
{
return(SqlServerFlush(dataContext));
}
else if (dataContext is MySqlDataContextMoudle <T> )
{
return(MysqlServerFlush(dataContext, insertDuplicate));
}
throw new NotImplementedException();
}
private bool SqlServerFlush(DataContextMoudle <T> dataContext)
{
Dictionary <string, object> retdic = new Dictionary <string, object>();
lock (innerCodeLock)
{
if (needReColumnWhenFlushTB && setFlushTBColumnsOrderDic == null)
{
needReColumnWhenFlushTB = false;
setFlushTBColumnsOrderDic = new Dictionary <string, int>();
string sql = string.Format("declare @t Mulitinsertmidtable${0};select top 0 * from @t;", TabName);
DataTable dttable = dataContext.ExecuteSQLTable(sql, null);
for (int i = 0; i < dttable.Columns.Count; i++)
{
setFlushTBColumnsOrderDic.Add(dttable.Columns[i].ColumnName.ToLower(), i);
if (!needReColumnWhenFlushTB && !string.Equals(cachTable.Columns[i].ColumnName, dttable.Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase))
{
needReColumnWhenFlushTB = true;
}
}
}
}
var param = new System.Data.SqlClient.SqlParameter("@dt", System.Data.SqlDbType.Structured, -1);
if (needReColumnWhenFlushTB)
{
param.Value = this.cachTable.DefaultView.ToTable(false, setFlushTBColumnsOrderDic.Keys.ToArray());
}
else
{
param.Value = this.cachTable;
}
dataContext.ExecuteProc(string.Format("{0}_mulitinsert", TabName)
,
new System.Data.Common.DbParameter[]
{
param
}
, ref retdic);
cachTable.Clear();
return(true);
}
private bool MysqlServerFlush(DataContextMoudle <T> dataContext, string insertDuplicate = null)
{
try
{
readWriteLock.EnterWriteLock();
List <Action <StringBuilder, object> > acts = new List <Action <StringBuilder, object> >();
List <int> columnIndex = new List <int>();
List <string> sqlColumns = new List <string>();
int idx = -1;
foreach (DataColumn column in this.cachTable.Columns)
{
idx++;
var r = rpAttrList.Find(p => p.Column.Equals(column.ColumnName, StringComparison.OrdinalIgnoreCase));
if (r == null || r.isKey)
{
continue;
}
sqlColumns.Add(r.Column);
columnIndex.Add(idx);
if (column.DataType.Name.Equals("int32", StringComparison.OrdinalIgnoreCase) ||
column.DataType.Name.Equals("int64", StringComparison.OrdinalIgnoreCase) ||
column.DataType.Name.Equals("long", StringComparison.OrdinalIgnoreCase) ||
column.DataType.Name.Equals("bit", StringComparison.OrdinalIgnoreCase) ||
column.DataType.Name.IndexOf("float", StringComparison.OrdinalIgnoreCase) > -1 ||
column.DataType.Name.IndexOf("real", StringComparison.OrdinalIgnoreCase) > -1 ||
column.DataType.Name.IndexOf("money", StringComparison.OrdinalIgnoreCase) > -1 ||
column.DataType.Name.IndexOf("timestamp", StringComparison.OrdinalIgnoreCase) > -1 ||
column.DataType.Name.IndexOf("money", StringComparison.OrdinalIgnoreCase) > -1
)
{
acts.Add(Append);
}
else if (column.DataType.Name.Equals("boolean", StringComparison.OrdinalIgnoreCase) ||
column.DataType.Name.Equals("bool", StringComparison.OrdinalIgnoreCase))
{
acts.Add(AppendBool);
}
else if (column.DataType.Name.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
acts.Add(AppendTime);
}
else
{
acts.Add(AppendStr);
}
}
int page = 0;
int maxcount = 5000;
while (true)
{
var y = TableAttr.IsSplitTable ? (from x in cachTable.AsEnumerable().Skip(page * maxcount).Take(maxcount)
group x by x[TablenameColumnName] into m
select m) : (
from x in cachTable.AsEnumerable().Skip(page * maxcount).Take(maxcount)
group x by TableAttr.TableName into m
select m
);
if (y.Count() == 0)
{
break;
}
page++;
foreach (var rowgp in y)
{
StringBuilder sbaddsql = new StringBuilder();
//sbaddsql.AppendFormat("SET SQL_SAFE_UPDATES = 0;Insert into {0} ({1}) values", string.Concat("`", rowgp.Key, "`"), string.Join(",", sqlColumns.Select(p => string.Concat("`", p, "`"))));
sbaddsql.AppendFormat("SET SQL_SAFE_UPDATES = 0;Insert {0}into {1} ({2}) values", "IGNORE".Equals(insertDuplicate, StringComparison.OrdinalIgnoreCase) ? "IGNORE " : string.Empty, string.Concat("`", rowgp.Key, "`"), string.Join(",", sqlColumns.Select(p => string.Concat("`", p, "`"))));
var sbaddsqlolen = sbaddsql.Length;
StringBuilder sbinsertsql = new StringBuilder();
sbinsertsql.AppendFormat("SET SQL_SAFE_UPDATES = 0;update {0} a inner join (", rowgp.Key);
bool isInstFirstRow = true;
var sbinsertsqlolen = sbinsertsql.Length;
foreach (var row in rowgp)
{
if (GetRowState(row) == DataTableRowState.Add)
{
sbaddsql.Append("(");
for (int i = 0; i < acts.Count; i++)
{
if (row[columnIndex[i]] == DBNull.Value)
{
sbaddsql.Append("NULL,");
}
else
{
acts[i](sbaddsql, row[columnIndex[i]]);
}
}
sbaddsql.Remove(sbaddsql.Length - 1, 1);
sbaddsql.Append("),");
SetRowState(row, DataTableRowState.Del);
}
else if (GetRowState(row) == DataTableRowState.Update)
{
if (isInstFirstRow)
{
sbinsertsql.Append("select ");
sbinsertsql.AppendFormat("{0} as {1},", row[keyColumn.Column], keyColumn.Column);
}
else
{
sbinsertsql.Append(" union all select ");
sbinsertsql.AppendFormat("{0},", row[keyColumn.Column]);
}
for (int i = 0; i < acts.Count; i++)
{
if (row[columnIndex[i]] == DBNull.Value)
{
sbinsertsql.AppendFormat("NULL ");
if (isInstFirstRow)
{
sbinsertsql.Append(" as ");
sbinsertsql.Append(sqlColumns[i]);
}
sbinsertsql.Append(",");
}
else
{
acts[i](sbinsertsql, row[columnIndex[i]]);
if (isInstFirstRow)
{
sbinsertsql.Remove(sbinsertsql.Length - 1, 1);
sbinsertsql.Append(" as ");
sbinsertsql.Append(sqlColumns[i]);
sbinsertsql.Append(",");
}
}
}
sbinsertsql.Remove(sbinsertsql.Length - 1, 1);
SetRowState(row, DataTableRowState.Del);
if (isInstFirstRow)
{
isInstFirstRow = false;
}
}
}
try
{
if (sbaddsql.Length > sbaddsqlolen)
{
sbaddsql.Remove(sbaddsql.Length - 1, 1);
if (!string.IsNullOrWhiteSpace(insertDuplicate) && !"IGNORE".Equals(insertDuplicate, StringComparison.OrdinalIgnoreCase))
{
sbaddsql.AppendFormat(" ON DUPLICATE KEY {0}", insertDuplicate);
}
dataContext.ExecuteNonQuery(sbaddsql.ToString());
}
if (sbinsertsql.Length > sbinsertsqlolen)
{
sbinsertsql.Append(") b ");
sbinsertsql.AppendFormat(" on a.{0}=b.{0} set ", keyColumn.Column);
foreach (var col in sqlColumns)
{
sbinsertsql.AppendFormat("a.{0}=b.{0},", col);
}
sbinsertsql.Remove(sbinsertsql.Length - 1, 1);
sbinsertsql.Append(" where 1=1;");
dataContext.ExecuteNonQuery(sbinsertsql.ToString());
}
}
catch (Exception ex)
{
if (Regex.IsMatch(ex.Message, string.Format(@"table '[^']*\.?{0}' doesn't exist", rowgp.Key), RegexOptions.IgnoreCase))
{
dataContext.ExecuteNonQuery(string.Format("CREATE TABLE IF NOT EXISTS `{0}` select * from {1} limit 0,0;alter table {0} change {2} {2} bigint not null auto_increment primary key;", rowgp.Key, this.TabName, keyColumn.Column));
dataContext.ExecuteNonQuery(sbaddsql.ToString());
}
else
{
if (ex.Data != null)
{
ex.Data.Add("addsql", sbaddsql.ToString());
ex.Data.Add("insertsql", sbinsertsql);
}
throw ex;
}
}
}
}
cachTable.Clear();
return(true);
}
finally
{
readWriteLock.ExitWriteLock();
}
}
