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