public static DbTable LoadDb(DbDataReader reader)
{
if (!reader.Read()) {
return null;
}
var tbl = new DbTable((string)reader["TABLE_NAME"]);
var firstColumn = new DbColumn(
(string)reader["COLUMN_NAME"],
(string)reader["DATA_TYPE"]
);
tbl.Columns = new List<DbColumn>() { firstColumn };
while (reader.Read()) {
if (tbl.Name != (string)reader["TABLE_NAME"]) {
break;
}
tbl.Columns.Add(new DbColumn(
(string)reader["COLUMN_NAME"],
(string)reader["DATA_TYPE"]
));
}
return tbl;
}
public DbAuditArgs(ExecTypes action, DbTable table, string sql, object data)
{
Action = action;
Table = table;
Sql = sql;
Data = data;
}
internal DbRow(DbTable table, long rowid)
{
this.table = table;
this.rowid = rowid;
if (IsNew)
values = new Dictionary<string, string>();
}
public Program()
{
using (var i = Prepare())
i.Purge();
db = Prepare();
table = db.Table<MyData>();
}
public void PurgeDb()
{
using (var i = Prepare())
i.Purge();
db = Prepare();
table = db.Table<IData>();
}
/// <summary>
/// The get database collection.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="includeTable">
/// The include table.
/// </param>
/// <param name="includeColumn">
/// The include column.
/// </param>
/// <returns>
/// The <see cref="DatabaseCollection"/>.
/// </returns>
public DatabaseCollection GetDatabaseCollection(
string connectionString,
bool includeTable = false,
bool includeColumn = false)
{
var databaseCollection = new DatabaseCollection();
using (var sqlConnection = new SqlConnection(connectionString))
{
var serverConnection = new ServerConnection(sqlConnection);
var server = new Server(serverConnection);
foreach (
var database in
server.Databases.Cast<Database>().AsQueryable().Where(d => d.IsSystemObject == false))
{
var dbItem = new EntityTemplate.Database { Name = database.Name };
if (includeTable && database.Tables.Count > 0)
{
foreach (var table in database.Tables.Cast<Table>().AsQueryable().Where(t => t.IsSystemObject == false))
{
var dbTableItem = new DbTable { Name = table.Name };
dbTableItem.Description = table.ExtendedProperties.Count > 0 ? table.ExtendedProperties["MS_Description"].Value.ToString() : string.Empty;
if (includeColumn && table.Columns.Count > 0)
{
foreach (var column in table.Columns.Cast<Column>().AsQueryable())
{
var dbColumnItem = new DbColumn();
dbColumnItem.Name = column.Name;
dbColumnItem.Description = column.ExtendedProperties.Count > 0 ? column.ExtendedProperties["MS_Description"].Value.ToString() : string.Empty;
dbColumnItem.IsPrimaryKey = column.InPrimaryKey;
dbColumnItem.IsIdentityColumn = column.Identity;
dbColumnItem.ColumnType = column.DataType.SqlDataType.ToString();
dbColumnItem.AllowEmpty = column.Nullable;
dbColumnItem.DefaultValue = column.Default;
dbTableItem.Columns.Add(dbColumnItem);
}
}
dbItem.Tables.Add(dbTableItem);
}
}
databaseCollection.Add(dbItem);
}
}
return databaseCollection;
}
public BllInterfaceKdCodeFactory(string entityName, string moduleName, DbTable dbTable)
{
EntityName = entityName;
ModuleName = moduleName;
Table = dbTable;
if (string.IsNullOrEmpty(EntityName))
EntityName = Constants.KDCODE_DEFAULT_ENTITY_NAME;
if (string.IsNullOrEmpty(ModuleName))
ModuleName = Constants.KDCODE_DEFAULT_MODULE_NAME;
}
public bool TryGetDbTable(RdbDescriptor db, string dbTableId, out DbTable dbTable)
{
if (!_initialized)
{
Init();
}
if (!_dicById.ContainsKey(db))
{
dbTable = null;
return false;
}
return _dicById[db].TryGetValue(dbTableId, out dbTable);
}
public void PurgeDb()
{
try
{
Extender.RegisterType<Point, PointSerializer>(2000);
}
catch { }
using (var i = Prepare())
i.Purge();
db = Prepare();
table = db.Table<IData>();
}
public bool TryGetDbTableColumns(RdbDescriptor database, DbTable table, out IReadOnlyDictionary<string, DbTableColumn> dbTableColumns)
{
if (!_initialized)
{
Init();
}
if (!_dic.ContainsKey(database))
{
dbTableColumns = new Dictionary<string, DbTableColumn>(StringComparer.OrdinalIgnoreCase);
return false;
}
Dictionary<string, DbTableColumn> outDic;
var r = _dic[database].TryGetValue(table, out outDic);
dbTableColumns = outDic;
return r;
}
public override bool BulkInsert(string schemaName, string tableName, CsvReader reader)
{
using (var cn = _connectionFactory()) {
using (SqlBulkCopy copy = new SqlBulkCopy(cn)) {
var existingTable = GetDbSchema(schemaName, tableName);
var readerTable = new DbTable(tableName, schemaName);
for (var i = 0; i < reader.Columns.Count; i++) {
var column = reader.Columns[i];
readerTable.Columns.Add(column);
}
if (existingTable == null) {
CreateTable(readerTable);
}
var table = GetDbSchema(schemaName, tableName);
// Make sure the mappings are correct
for (var i = 0; i < reader.Columns.Count; i++) {
var column = reader.Columns[i].ColumnName;
var sourceOrdinal = i;
var destinationOrdinal = table.Columns.FindIndex(x => x.ColumnName == column);
if (destinationOrdinal == -1) {
var msg = string.Format("Unable to resolve column mapping, column: {0} was not found in destination table {1}",
column,
table.TableName
);
throw new Exception(msg);
}
copy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(i, destinationOrdinal));
}
copy.DestinationTableName = string.Format("[{0}].[{1}]", table.SchemaName, table.TableName);
copy.BatchSize = 1000;
copy.BulkCopyTimeout = 9999999;
copy.WriteToServer(reader);
}
}
return true;
}
public override bool BulkInsert(string schemaName, string tableName, CsvReader reader)
{
using (var cn = _connectionFactory()) {
var existingTable = GetDbSchema(schemaName, tableName);
var readerTable = new DbTable(tableName, schemaName);
for (var i = 0; i < reader.Columns.Count; i++) {
var column = reader.Columns[i];
readerTable.Columns.Add(column);
}
if (existingTable == null) {
CreateTable(readerTable);
}
var table = GetDbSchema(schemaName, tableName);
// Make a little cache of the pgTypes
var pgTypes = table.Columns
.Select(x => PostgresqlTypeConverter.Get(x.MungType).PostgresqlDbType)
.ToList();
// Not all the columns in the table may be present in the actual reader, so
// we insert null if they are missing.
HashSet<string> actualColumns = new HashSet<string>(reader.Columns.Select(c => c.ColumnName));
using (var writer = cn.BeginBinaryImport(CopyCommand(table))) {
while (reader.Read()) {
writer.StartRow();
for (var i = 0; i < table.Columns.Count; i++) {
var col = table.Columns[i];
if (actualColumns.Contains(col.ColumnName)) {
var val = reader[col.ColumnName];
if (val == DBNull.Value) {
writer.WriteNull();
} else {
writer.Write(reader[col.ColumnName], pgTypes[i]);
}
} else {
writer.WriteNull();
}
}
}
}
}
return true;
}
public static DbTable Create(Db db, string tableName)
{
DbTable t = new DbTable();
t.TableName = tableName;
DataTable dt = db.GetSchemaTable(tableName);
if (dt == null || dt.Rows.Count <= 0)
return t;
t.Fields = DbFieldCollection.Create(dt);
t.PrimaryKeys = new List<DbField>();
foreach (DbField f in t.Fields)
{
if (f.IsKey)
{
t.PrimaryKeys.Add(f);
}
}
t.ForeignKeys = db.SchemaInfo.GetForeignKeys(null, tableName);
#region AuditType
switch (Db.AuditType)
{
case DbAuditTypes.Explicit:
t.DefaultAuditType = TableAuditTypes.None;
break;
case DbAuditTypes.Implicit:
t.DefaultAuditType = TableAuditTypes.Default;
break;
case DbAuditTypes.None:
t.DefaultAuditType = TableAuditTypes.None;
break;
}
#endregion AuditType
return t;
}
public int Delete(DbTable table, Dictionary<string, object> row)
{
int r = 0;
using (DbCommand cm = this.CreateCommand())
{
StringBuilder s = new StringBuilder(String.Format("DELETE FROM {0} WHERE ", table.TableName));
BuildWhere(table, table.PrimaryKeys, row, s, cm);
string sql = s.ToString();
cm.CommandText = sql;
//OnExecuting(ExecTypes.Delete, cm);
r = cm.ExecuteNonQuery();
//OnExecuted(ExecTypes.Delete, cm, r);
DoAudit(new DbAuditArgs(ExecTypes.Delete, table, cm.CommandText, row));
}
return r;
}
public bool Execute()
{
using (var stream = File.OpenText(_filename)) {
using (var reader = new MungedDataReader(stream)) {
var currentSchema = new DbTable(_table, reader);
var oldSchema = GetDbSchema();
// Check / Update the schema in the DB
if (oldSchema == null) {
CreateTable(currentSchema);
} else {
ModifySchema(oldSchema, currentSchema);
}
var newSchema = GetDbSchema();
BulkInsert(newSchema, reader);
}
}
return true;
}
internal DataRow SelectRow(DbTable table, Dictionary<string, object> keys)
{
using(DbCommand cm = this.CreateCommand())
{
StringBuilder s = new StringBuilder(String.Format("SELECT * FROM {0} WHERE ", table.TableName));
BuildWhere(table, table.PrimaryKeys, keys, s, cm);
string sql = s.ToString();
cm.CommandText = sql;
DataTable dt = ExecQuery(cm);
DataRow dr = null;
if (dt.Rows.Count > 0)
dr = dt.Rows[0];
return dr;
}
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="table">The data table to consider.</param>
public static DbField FieldAsNull(
string name, DbTable table)
{
return(DbFluent.Field(name, table).AsNull());
}
static string AppendTableName(DbTable table)
{
return(Utils.QuoteName(table.Name));
}
public override void InsertRange <TEntity>(List <TEntity> entities, bool keepIdentity = false, string table = null)
{
/*
* 将 entities 分批插入数据库
* 每批生成 insert into TableName(...) values(...),(...)...
* 该方法相对循环一条一条插入，速度提升 2/3 这样
*/
PublicHelper.CheckNull(entities);
if (entities.Count == 0)
{
return;
}
int maxParameters = 1000;
int batchSize = 60; /* 每批实体大小，此值通过测试得出相对插入速度比较快的一个值 */
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
var e = typeDescriptor.PropertyDescriptors as IEnumerable <PropertyDescriptor>;
if (keepIdentity == false)
{
e = e.Where(a => a.IsAutoIncrement == false);
}
List <PropertyDescriptor> mappingPropertyDescriptors = e.ToList();
int maxDbParamsCount = maxParameters - mappingPropertyDescriptors.Count; /* 控制一个 sql 的参数个数 */
DbTable dbTable = string.IsNullOrEmpty(table) ? typeDescriptor.Table : new DbTable(table, typeDescriptor.Table.Schema);
string sqlTemplate = AppendInsertRangeSqlTemplate(dbTable, mappingPropertyDescriptors);
Action insertAction = () =>
{
int batchCount = 0;
List <DbParam> dbParams = new List <DbParam>();
StringBuilder sqlBuilder = new StringBuilder();
for (int i = 0; i < entities.Count; i++)
{
var entity = entities[i];
if (batchCount > 0)
{
sqlBuilder.Append(",");
}
sqlBuilder.Append("(");
for (int j = 0; j < mappingPropertyDescriptors.Count; j++)
{
if (j > 0)
{
sqlBuilder.Append(",");
}
PropertyDescriptor mappingPropertyDescriptor = mappingPropertyDescriptors[j];
object val = mappingPropertyDescriptor.GetValue(entity);
if (val == null)
{
sqlBuilder.Append("NULL");
continue;
}
Type valType = val.GetType();
if (valType.IsEnum)
{
val = Convert.ChangeType(val, Enum.GetUnderlyingType(valType));
valType = val.GetType();
}
if (Utils.IsToStringableNumericType(valType))
{
sqlBuilder.Append(val.ToString());
continue;
}
if (val is bool)
{
if ((bool)val == true)
{
sqlBuilder.AppendFormat("1");
}
else
{
sqlBuilder.AppendFormat("0");
}
continue;
}
string paramName = UtilConstants.ParameterNamePrefix + dbParams.Count.ToString();
DbParam dbParam = new DbParam(paramName, val)
{
DbType = mappingPropertyDescriptor.Column.DbType
};
dbParams.Add(dbParam);
sqlBuilder.Append(paramName);
}
sqlBuilder.Append(")");
batchCount++;
if ((batchCount >= 20 && dbParams.Count >= 400 /*参数个数太多也会影响速度*/) || dbParams.Count >= maxDbParamsCount || batchCount >= batchSize || (i + 1) == entities.Count)
{
sqlBuilder.Insert(0, sqlTemplate);
string sql = sqlBuilder.ToString();
this.Session.ExecuteNonQuery(sql, dbParams.ToArray());
sqlBuilder.Clear();
dbParams.Clear();
batchCount = 0;
}
}
};
Action fAction = insertAction;
if (this.Session.IsInTransaction)
{
fAction();
}
else
{
/* 因为分批插入，所以需要开启事务保证数据一致性 */
this.Session.BeginTransaction();
try
{
fAction();
this.Session.CommitTransaction();
}
catch
{
if (this.Session.IsInTransaction)
{
this.Session.RollbackTransaction();
}
throw;
}
}
}
protected virtual void OnFill(DbTable ds, DbDataReader dr)
{
}
protected override async Task <TEntity> Insert <TEntity>(TEntity entity, string table, bool @async)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
List <PrimitivePropertyDescriptor> outputColumns = new List <PrimitivePropertyDescriptor>();
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement)
{
outputColumns.Add(propertyDescriptor);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor, dbTable.Schema);
insertColumns.Add(propertyDescriptor, getNextValueForSequenceExp);
outputColumns.Add(propertyDescriptor);
continue;
}
object val = propertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(propertyDescriptor, val);
DbExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
DbInsertExpression e = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
e.InsertColumns.Add(kv.Key.Column, kv.Value);
}
e.Returns.AddRange(outputColumns.Select(a => a.Column));
DbCommandInfo dbCommandInfo = this.Translate(e);
await this.ExecuteNonQuery(dbCommandInfo, @async);
List <DbParam> outputParams = dbCommandInfo.Parameters.Where(a => a.Direction == ParamDirection.Output).ToList();
for (int i = 0; i < outputColumns.Count; i++)
{
PrimitivePropertyDescriptor propertyDescriptor = outputColumns[i];
string putputColumnName = Utils.GenOutputColumnParameterName(propertyDescriptor.Column.Name);
DbParam outputParam = outputParams.Where(a => a.Name == putputColumnName).First();
var outputValue = PublicHelper.ConvertObjectType(outputParam.Value, propertyDescriptor.PropertyType);
outputColumns[i].SetValue(entity, outputValue);
}
return(entity);
}
private static void AddInsertUpdateDelete(CodeGenerator generator, ClrClass @class, DbTable table)
{
// Add Insert
generator.AddInsert(@class, table);
generator.AddEmptyLine();
// Add Update
generator.AddUpdate(@class, table);
generator.AddEmptyLine();
// Add Delete
generator.AddDelete(@class, table);
}
/// <summary>
/// Reads tables schema from database
/// </summary>
private List <DbTable> ReadTables(List <DbView> viewList)
{
var result = new List <DbTable>();
using (var tables = _dbConnection.GetSchema("Tables"))
{
foreach (DataRow row in tables.Rows)
{
var tableName = row["TABLE_NAME"].ToString();
var ownerName = row["TABLE_SCHEMA"].ToString();
if (String.IsNullOrEmpty(SpecificOwner) || String.Equals(ownerName, SpecificOwner, StringComparison.OrdinalIgnoreCase))
{
if (!IsTableSelected(tableName))
{
continue;
}
var jumpToNext = false;
// search in views about this
for (int i = 0; i < viewList.Count; i++)
{
if (viewList[i].TableName == tableName)
{
jumpToNext = true;
// we ignore view here
break;
}
}
if (jumpToNext)
{
continue;
}
// View columns
var columns = ReadColumns(tableName, ownerName);
// read columns description
if (ReadColumnsDescription)
{
ApplyColumnsDescription(tableName, columns);
}
// new table and table schema
var dbTable = new DbTable(tableName, columns)
{
OwnerName = ownerName
};
// add to results
result.Add(dbTable);
}
}
if (ReadConstraintKeys)
{
// The constraint keys will read here
ApplyTablesConstraintKeys(result);
}
// it is time to read foreign keys!
// foreign keys are requested?
if (ReadTablesForeignKeys)
{
ApplyTablesForeignKeys(result);
}
// Normalize the constraints keys
NormalizeTablesConstraintKeys(result);
if (ReadTablesForeignKeys)
{
ApplyDetectedOneToOneRelation(result);
}
}
return(result);
}
private static Field[] AddFiledsAndContructor(CodeGenerator generator, string className, DbTable[] foreignKeyTables, DbTable detailsTable = null)
{
var fields = Field.GetDictionaryFields(foreignKeyTables, detailsTable);
if (fields.Length > 0)
{
// Add fields
generator.AddDictionaryFields(fields);
generator.AddEmptyLine();
// Add contructor
generator.AddContructor(fields, className);
generator.AddEmptyLine();
}
return(fields);
}
private static string GetAdapterWithCollection(ClrClass @class, DbTable table, DbTable[] foreignKeyTables, DbTable detailsTable)
{
var generator = new CodeGenerator();
var className = AddClassDefinition(table, generator);
generator.BeginBlock();
var fields = AddFiledsAndContructor(generator, className, foreignKeyTables, detailsTable);
// Add Get method
generator.AddGetWithDetailsMethod(@class.Name, table, detailsTable);
generator.AddEmptyLine();
// Add IdReader method
generator.AddIdReaderMethod();
generator.AddEmptyLine();
// Add Attach method
generator.AddAttachMethod(table, detailsTable);
generator.AddEmptyLine();
// Add Creator
generator.AddCreator(@class, fields, detailsTable.Columns.Length - 1);
generator.AddEmptyLine();
AddInsertUpdateDelete(generator, @class, table);
generator.EndBlock();
return(generator.GetFormattedOutput());
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
public static DbField FieldAsNull <T>(
Expression <Func <T, object> > expr,
DbTable table) where T : class
{
return(DbFluent.Field <T>(expr, table).AsNull());
}
public int Insert(DbTable table, Dictionary<string, object> row)
{
int r = 0;
using (DbCommand cm = this.CreateCommand())
{
//InsertStatementParameter isp = null;
//if (useCache && Cache.InsertStatementParameters.TryGetValue(table.TableName, out isp))
//{
// cm.CommandText = isp.Sql;
// foreach (DbField f in isp.Fields)
// {
// DbParameter p = CreateParameter(f, row[f.FieldName]);
// cm.Parameters.Add(p);
// }
//}
//else
{
//if (useCache)
//{
// isp = new InsertStatementParameter();
//}
StringBuilder f = new StringBuilder();
StringBuilder v = new StringBuilder();
if (table.ContainsField(DbFieldNames.CreatedOn))
{
if (row.ContainsKey(DbFieldNames.CreatedOn))
row[DbFieldNames.CreatedOn] = DateTime.Now;
else
row.Add(DbFieldNames.CreatedOn, DateTime.Now);
}
if (UserInfoProvider != null && table.ContainsField(DbFieldNames.CreatedBy))
{
if (row.ContainsKey(DbFieldNames.CreatedBy))
row[DbFieldNames.CreatedBy] = UserInfoProvider.UserName;
else
row.Add(DbFieldNames.CreatedBy, UserInfoProvider.UserName);
}
if (NextIdProvider != null && table.ContainsField(DbFieldNames.Id))
{
if (row.ContainsKey(DbFieldNames.Id))
{
object id = row[DbFieldNames.Id];
if (IsNewId(id))
row[DbFieldNames.Id] = NextIdProvider.Get();
}
else
{
row.Add(DbFieldNames.Id, NextIdProvider.Get());
}
}
int count = table.Fields.Count - 1;
for (int i = 0; i <= count; i++)
{
DbField dbf = table.Fields[i];
if (dbf.IsAutoIncrement) continue;
string fieldName = dbf.FieldName;
if (!row.ContainsKey(fieldName)) continue;
if (fieldName.Equals(DbFieldNames.UpdatedBy, StringComparison.OrdinalIgnoreCase)) continue;
if (fieldName.Equals(DbFieldNames.UpdatedOn, StringComparison.OrdinalIgnoreCase)) continue;
f.Append(fieldName);
f.Append(",");
object o = row[fieldName];
v.Append(GetParameterMarker(fieldName));
DbParameter p = CreateParameter(dbf, o);
cm.Parameters.Add(p);
v.Append(",");
//if (useCache)
//{
// isp.Fields.Add(dbf);
//}
}
if (f.Length > 0)
{
f.Remove(f.Length - 1, 1);
v.Remove(v.Length - 1, 1);
}
string sql = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", table.TableName, f.ToString(), v.ToString());
cm.CommandText = sql;
//if (useCache)
//{
// isp.Sql = sql;
// Cache.InsertStatementParameters.Add(table.TableName, isp);
//}
}
//OnExecuting(ExecTypes.Insert, cm);
r = cm.ExecuteNonQuery();
//OnExecuted(ExecTypes.Insert, cm, r);
DoAudit(new DbAuditArgs(ExecTypes.Insert, table, cm.CommandText, row));
}
return r;
}
/// <summary>从数据流恢复数据</summary>
/// <param name="stream">数据流</param>
/// <param name="table">数据表</param>
/// <param name="progress">进度回调，参数为已处理行数和当前页表</param>
/// <returns></returns>
public Int32 Restore(Stream stream, IDataTable table, Action <Int32, DbTable> progress = null)
{
var writeDb = new WriteDbActor
{
Table = table,
Dal = this,
// 最多同时堆积数页
BoundedCapacity = 4,
};
//writeDb.Start();
var sw = Stopwatch.StartNew();
// 二进制读写器
var bn = new Binary
{
EncodeInt = true,
Stream = stream,
};
var dt = new DbTable();
dt.ReadHeader(bn);
WriteLog("恢复[{0}/{1}]开始，共[{2:n0}]行", table.Name, ConnName, dt.Total);
var row = 0;
var pageSize = 10_000;
var total = 0;
while (true)
{
// 读取数据
var count = dt.ReadData(bn, Math.Min(dt.Total - row, pageSize));
var rs = dt.Rows;
if (rs == null || rs.Count == 0)
{
break;
}
WriteLog("恢复[{0}/{1}]数据 {2:n0} + {3:n0}", table.Name, ConnName, row, rs.Count);
// 进度报告
progress?.Invoke(row, dt);
// 批量写入数据库。克隆对象，避免被修改
writeDb.Tell(dt.Clone());
// 下一页
total += count;
if (count < pageSize)
{
break;
}
row += pageSize;
}
// 通知写入完成
writeDb.Stop(-1);
sw.Stop();
var ms = sw.Elapsed.TotalMilliseconds;
WriteLog("恢复[{0}/{1}]完成，共[{2:n0}]行，耗时{3:n0}ms，速度{4:n0}tps", table.Name, ConnName, total, ms, total * 1000L / ms);
// 返回总行数
return(total);
}
static DbExpression MakeCondition(Dictionary <MappingMemberDescriptor, object> keyValueMap, DbTable dbTable)
{
DbExpression conditionExp = null;
foreach (var kv in keyValueMap)
{
MappingMemberDescriptor keyMemberDescriptor = kv.Key;
object keyVal = kv.Value;
if (keyVal == null)
{
throw new ArgumentException(string.Format("The primary key '{0}' could not be null.", keyMemberDescriptor.MemberInfo.Name));
}
DbExpression left = new DbColumnAccessExpression(dbTable, keyMemberDescriptor.Column);
DbExpression right = DbExpression.Parameter(keyVal, keyMemberDescriptor.MemberInfoType);
DbExpression equalExp = new DbEqualExpression(left, right);
conditionExp = conditionExp == null ? equalExp : DbExpression.And(conditionExp, equalExp);
}
return(conditionExp);
}
public static ValueTask <IActionResult> SynchronizePartitionAsync(this IActionResult result, DbTable dbTable, DbPartition partitionToSave,
DataSynchronizationPeriod period)
{
if (ServiceLocator.SnapshotStorage == null)
{
return(new ValueTask <IActionResult>(result));
}
if (period == DataSynchronizationPeriod.Immediately)
{
var partitionSnapshot = PartitionSnapshot.Create(dbTable, partitionToSave);
return(result.ResponseWithActionAsync(() => ServiceLocator
.SnapshotStorage
.SavePartitionSnapshotAsync(partitionSnapshot)));
}
ServiceLocator.SnapshotSaverScheduler.SynchronizePartition(dbTable, partitionToSave, period);
return(new ValueTask <IActionResult>(result));
}
public static bool Save(IList <NodeExcel> rootList)
{
var list = new List <Excel>();
foreach (var e in rootList)
{
var ex = new Excel()
{
Guid = e.Guid, Name = e.Name, Description = e.Description
};
ex.Nodes = new List <Tab>();
list.Add(ex);
if (e.Nodes == null)
{
continue;
}
foreach (var t in e.Nodes)
{
var tab = new Tab()
{
Guid = e.Guid, Name = t.Name, Description = (t as NodeBase).Description
};
tab.Nodes = new List <DbTable>();
ex.Nodes.Add(tab);
if (t.Nodes == null)
{
continue;
}
foreach (var table in t.Nodes)
{
var dbTable = new DbTable()
{
Guid = e.Guid, Name = table.Name, Description = (table as NodeBase).Description
};
dbTable.Nodes = new List <DbColumn>();
tab.Nodes.Add(dbTable);
if (table.Nodes == null)
{
continue;
}
foreach (var column in table.Nodes)
{
var dbColumn = new DbColumn()
{
Guid = e.Guid, Name = column.Name, Description = (column as NodeBase).Description
};
dbTable.Nodes.Add(dbColumn);
}
}
}
}
var exePath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
var dataPath = string.Format("{0}\\Data", exePath);
var data = string.Format("{0}\\data.json", dataPath);
var json = JsonConvert.SerializeObject(list, Formatting.Indented);
var fi = new FileInfo(data);
using (var stream = fi.CreateText())
{
stream.Write(json);
}
return(true);
}
/// <summary>
/// Search vital drugs by name pattern
/// </summary>
/// <param name="pattern">Name pattern</param>
/// <returns></returns>
public List <VitalDrug> SearchByPattern(string pattern)
{
return(DbTable
.Where(d => d.Name.Contains(pattern))
.ToList());
}
/// <summary>
/// 将查询出来的datatable数据导入到redis
/// </summary>
private void ImportDataTableToRedis(RedisHelper redisHelper, ref int tableCount, DbTable dt, DataTable dataTable, FieldInfo[] fieldInfos)
{
foreach (DataRow dr in dataTable.Rows)
{
Dictionary <string, object> recordDic = new Dictionary <string, object>();
foreach (DataColumn dc in dataTable.Columns)
{
if (dr[dc.ColumnName].ToString() == "")
{
continue; //空值不导入redis
}
if (dc.DataType == System.Type.GetType("System.Byte[]"))
{
continue; //图片不导入redis
}
string columnValue = dr[dc.ColumnName].ToString();
if (dc.DataType == System.Type.GetType("System.Int16") || dc.DataType == System.Type.GetType("System.Decimal"))
{
if (fieldInfos != null)
{
foreach (FieldInfo finfo in fieldInfos)
{
if (finfo.Name.ToUpper().StartsWith("_" + dc.ColumnName) && finfo.FieldType == System.Type.GetType("System.Boolean"))
{
columnValue = dr[dc.ColumnName].ToString() == "1" ? "true" : "false";
break;
}
}
}
}
recordDic.Add(dc.ColumnName, columnValue);
}
string rowsJson = Newtonsoft.Json.JsonConvert.SerializeObject(recordDic);
//RedisHash.SetEntryInHash(dt.Name, dr[0].ToString(), rowsJson);
redisHelper.HashSet(dt.Name, dr[0].ToString(), rowsJson);
}
tableCount++;
if (!isImportAllProjectsData)
{
this.backgroundWorker1.ReportProgress((int)(tableCount * 100 / this.DatabaseTableList.Count));
}
}
public static string CopyCommand(DbTable table)
{
var columns = string.Join(",\n\t", table.Columns.Select(x => $"\"{x.ColumnName}\""));
return $"COPY \"{table.SchemaName}\".\"{table.TableName}\"(\n\t{columns}\n) FROM STDIN BINARY";
}
/*
* insert into stat (siteid,statdate,`count`,cost,createtime,updatetime) values
* (1,'2018-08-11 09:34:00',1,123,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now()),
* (3,'2018-08-11 09:34:00',1,789,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now())
* on duplicate key update
* `count`=`count`+values(`count`),cost=cost+values(cost),
* updatetime=values(updatetime);
*/
private String GetBatchSql(IDataTable table, IDataColumn[] columns, ICollection <String> updateColumns, ICollection <String> addColumns, IEnumerable <IExtend> list)
{
var sb = Pool.StringBuilder.Get();
var db = Database as DbBase;
// 字段列表
//if (columns == null) columns = table.Columns.ToArray();
sb.AppendFormat("Insert Into {0}(", db.FormatName(table));
foreach (var dc in columns)
{
// 取消对主键的过滤，避免列名和值无法一一对应的问题
//if (dc.Identity) continue;
sb.Append(db.FormatName(dc));
sb.Append(',');
}
sb.Length--;
sb.Append(')');
// 值列表
sb.Append(" Values");
// 优化支持DbTable
if (list.FirstOrDefault() is DbRow)
{
// 提前把列名转为索引，然后根据索引找数据
DbTable dt = null;
Int32[] ids = null;
foreach (DbRow dr in list)
{
if (dr.Table != dt)
{
dt = dr.Table;
var cs = new List <Int32>();
foreach (var dc in columns)
{
if (dc.Identity)
{
cs.Add(0);
}
else
{
cs.Add(dt.GetColumn(dc.ColumnName));
}
}
ids = cs.ToArray();
}
sb.Append('(');
var row = dt.Rows[dr.Index];
for (var i = 0; i < columns.Length; i++)
{
var dc = columns[i];
//if (dc.Identity) continue;
var value = row[ids[i]];
sb.Append(db.FormatValue(dc, value));
sb.Append(',');
}
sb.Length--;
sb.Append("),");
}
}
else
{
foreach (var entity in list)
{
sb.Append('(');
foreach (var dc in columns)
{
//if (dc.Identity) continue;
var value = entity[dc.Name];
sb.Append(db.FormatValue(dc, value));
sb.Append(',');
}
sb.Length--;
sb.Append("),");
}
}
sb.Length--;
// 重复键执行update
if ((updateColumns != null && updateColumns.Count > 0) || (addColumns != null && addColumns.Count > 0))
{
sb.Append(" On Duplicate Key Update ");
if (updateColumns != null && updateColumns.Count > 0)
{
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (updateColumns.Contains(dc.Name) && (addColumns == null || !addColumns.Contains(dc.Name)))
{
sb.AppendFormat("{0}=Values({0}),", db.FormatName(dc));
}
}
sb.Length--;
}
if (addColumns != null && addColumns.Count > 0)
{
sb.Append(',');
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (addColumns.Contains(dc.Name))
{
sb.AppendFormat("{0}={0}+Values({0}),", db.FormatName(dc));
}
}
sb.Length--;
}
}
return(sb.Put(true));
}
public override bool BulkInsert(string schemaName, string tableName, DbDataReader reader, Action<long> callback)
{
// Read the first row to get the information on the schema
if (reader.Read()) {
var existingTable = GetDbSchema(schemaName, tableName);
var readerTable = new DbTable(tableName, schemaName);
var pgTypes = new List<NpgsqlDbType>();
for (var i = 0; i < reader.FieldCount; i++) {
var columnName = reader.GetName(i);
var columnType = reader.GetFieldType(i);
var column = new DbColumn(columnName, MungType.Get(columnType));
readerTable.Columns.Add(column);
var type = PostgresqlTypeConverter.Get(column.MungType);
if (type == null) {
throw new Exception($"Unable to load Postgres type for type: {column.MungType.Code}, Column: {column.ColumnName}");
}
pgTypes.Add(type.PostgresqlDbType);
}
if (existingTable == null) {
CreateTable(readerTable);
} else {
ModifySchema(existingTable, readerTable);
}
var table = GetDbSchema(schemaName, tableName);
var rowCount = 0L;
using (var cn = _connectionFactory()) {
using (var writer = cn.BeginBinaryImport(CopyCommand(table))) {
do {
writer.StartRow();
for (var i = 0; i < table.Columns.Count; i++) {
var col = table.Columns[i];
var val = reader[col.ColumnName];
if (val == DBNull.Value) {
writer.WriteNull();
} else {
writer.Write(reader[col.ColumnName], pgTypes[i]);
}
}
if (callback != null) {
callback(rowCount);
}
rowCount++;
} while (reader.Read());
}
}
}
return true;
}
public static ValueTask <IActionResult> SynchronizeDeletePartitionAsync(this IActionResult result, DbTable dbTable,
DbPartition dbPartition,
DataSynchronizationPeriod period)
{
if (ServiceLocator.SnapshotStorage == null)
{
return(new ValueTask <IActionResult>(result));
}
if (period == DataSynchronizationPeriod.Immediately)
{
return(result.ResponseWithActionAsync(() => ServiceLocator
.SnapshotStorage
.DeleteTablePartitionAsync(dbTable.Name, dbPartition.PartitionKey)));
}
ServiceLocator.SnapshotSaverScheduler.SynchronizePartition(dbTable, dbPartition, period);
return(new ValueTask <IActionResult>(result));
}
// As All ---------------------------------
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="table">The data table to consider.</param>
public static DbField FieldAsAll(DbTable table)
{
return(DbFluent.Field(null, table).AsAll());
}
private TResult QueryByCache <T1, T2, T3, TResult>(T1 k1, T2 k2, T3 k3, Func <T1, T2, T3, TResult> callback, String prefix)
{
CheckDatabase();
// 读缓存
var cache = GetCache();
if (cache != null)
{
var sb = Pool.StringBuilder.Get();
if (!prefix.IsNullOrEmpty())
{
sb.Append(prefix);
sb.Append("#");
}
Append(sb, k1);
Append(sb, k2);
Append(sb, k3);
var key = sb.Put(true);
//if (cache.TryGetValue(key, out var value)) return value.ChangeType<TResult>();
return(cache.GetItem(key, k =>
{
// 达到60秒后全表查询使用文件缓存
var dataFile = "";
if ((Expire >= 60 || Db.Readonly) && prefix == nameof(Query))
{
var builder = k1 as SelectBuilder;
var start = (Int64)(Object)k2;
var max = (Int64)(Object)k3;
if (start <= 0 && max <= 0 && builder != null && builder.Where.IsNullOrEmpty())
{
dataFile = NewLife.Setting.Current.DataPath.CombinePath(ConnName, builder.Table.Trim('[', ']', '`', '"') + ".dt");
// 首次缓存加载时采用文件缓存替代，避免读取数据库耗时过长
if (!cache.ContainsKey(k) && File.Exists(dataFile.GetFullPath()))
{
var dt = new DbTable();
dt.LoadFile(dataFile);
return dt;
}
}
}
Interlocked.Increment(ref _QueryTimes);
var rs = Invoke(k1, k2, k3, callback, prefix);
// 达到60秒后全表查询使用文件缓存
if (!dataFile.IsNullOrEmpty())
{
(rs as DbTable).SaveFile(dataFile);
}
return rs;
}).ChangeType <TResult>());
}
Interlocked.Increment(ref _QueryTimes);
return(Invoke(k1, k2, k3, callback, prefix));
}
/// <summary>
/// 获取物理表
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
private List <string> GetTables(string sqlstr)
{
var dbTable = new DbTable(sqlstr);
return(dbTable.QueryTablesName());
}
public static void Write(DbTable dt)
{
string path = Path.Combine(BaseParams.WinFromsPath, "EditDialogs");
if (Directory.Exists(path) == false)
{
Directory.CreateDirectory(path);
}
#region 创建类detail文件
string className = Path.Combine(path, "dlg" + dt.TitleCaseName + "Edit.Designer.cs");
FileStream file = new FileStream(className, FileMode.Create);
StreamWriter sw = new StreamWriter(file, Encoding.UTF8);
CommentsCoder.CreateCsComments(dt.Comments, sw);
sw.WriteLine("namespace " + BaseParams.WinFromsNameSpace + ".EditDialogs");
sw.WriteLine("{");
sw.WriteLine(" partial class dlg" + dt.TitleCaseName + "Edit");
sw.WriteLine(" {");
sw.WriteLine(" /// <summary>");
sw.WriteLine(" /// 必需的设计器变量。");
sw.WriteLine(" /// </summary>");
sw.WriteLine(" private System.ComponentModel.IContainer components = null;");
sw.WriteLine("");
sw.WriteLine(" /// <summary>");
sw.WriteLine(" /// 清理所有正在使用的资源。");
sw.WriteLine(" /// </summary>");
sw.WriteLine(" /// <param name=\"disposing\">如果应释放托管资源,为 true；否则为 false。</param>");
sw.WriteLine(" protected override void Dispose(bool disposing)");
sw.WriteLine(" {");
sw.WriteLine(" if (disposing && (components != null))");
sw.WriteLine(" {");
sw.WriteLine(" components.Dispose();");
sw.WriteLine(" }");
sw.WriteLine(" base.Dispose(disposing);");
sw.WriteLine(" }");
sw.WriteLine("");
sw.WriteLine(" #region Windows 窗体设计器生成的代码");
sw.WriteLine("");
sw.WriteLine(" /// <summary>");
sw.WriteLine(" /// 设计器支持所需的方法 - 不要");
sw.WriteLine(" /// 使用代码编辑器修改此方法的内容。");
sw.WriteLine(" /// </summary>");
sw.WriteLine(" private void InitializeComponent()");
sw.WriteLine(" {");
sw.WriteLine(" this.components = new System.ComponentModel.Container();");
sw.WriteLine(" System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(dlg" + dt.TitleCaseName + "Edit));");
sw.WriteLine(" this.btnSave = new " + BaseParams.ControlsNameSpace + ".MyButton();");
sw.WriteLine(" this.btnCancel = new " + BaseParams.ControlsNameSpace + ".MyButton();");
sw.WriteLine(" this.btnClose = new " + BaseParams.ControlsNameSpace + ".MyButton();");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + " = new System.Windows.Forms.BindingNavigator(this.components);");
sw.WriteLine(" this.bindingNavigatorCountItem = new System.Windows.Forms.ToolStripLabel();");
sw.WriteLine(" this.bindingNavigatorMoveFirstItem = new System.Windows.Forms.ToolStripButton();");
sw.WriteLine(" this.bindingNavigatorMovePreviousItem = new System.Windows.Forms.ToolStripButton();");
sw.WriteLine(" this.bindingNavigatorSeparator = new System.Windows.Forms.ToolStripSeparator();");
sw.WriteLine(" this.bindingNavigatorPositionItem = new System.Windows.Forms.ToolStripTextBox();");
sw.WriteLine(" this.bindingNavigatorSeparator1 = new System.Windows.Forms.ToolStripSeparator();");
sw.WriteLine(" this.bindingNavigatorMoveNextItem = new System.Windows.Forms.ToolStripButton();");
sw.WriteLine(" this.bindingNavigatorMoveLastItem = new System.Windows.Forms.ToolStripButton();");
sw.WriteLine(" this.myPanel1 = new " + BaseParams.ControlsNameSpace + ".MyPanel();");
sw.WriteLine(" this.uc" + dt.TitleCaseName + "Detail1 = new " + BaseParams.UserControlsNameSpace + ".Details.Uc" + dt.TitleCaseName + "Detail();");
sw.WriteLine(" ((System.ComponentModel.ISupportInitialize)(this.bindingNavigator" + dt.TitleCaseName + ")).BeginInit();");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".SuspendLayout();");
sw.WriteLine(" this.myPanel1.SuspendLayout();");
sw.WriteLine(" this.SuspendLayout();");
sw.WriteLine(" // ");
sw.WriteLine(" // btnSave");
sw.WriteLine(" // ");
sw.WriteLine(" this.btnSave.Location = new System.Drawing.Point(249, 3);");
sw.WriteLine(" this.btnSave.Name = \"btnSave\";");
sw.WriteLine(" this.btnSave.Size = new System.Drawing.Size(85, 23);");
sw.WriteLine(" this.btnSave.TabIndex = 0;");
sw.WriteLine(" this.btnSave.Text = \"保存/下一条\";");
sw.WriteLine(" this.btnSave.Click += new System.EventHandler(this.btnSave_Click);");
sw.WriteLine(" // ");
sw.WriteLine(" // btnCancel");
sw.WriteLine(" // ");
sw.WriteLine(" this.btnCancel.CausesValidation = false;");
sw.WriteLine(" this.btnCancel.Location = new System.Drawing.Point(358, 3);");
sw.WriteLine(" this.btnCancel.Name = \"btnCancel\";");
sw.WriteLine(" this.btnCancel.Size = new System.Drawing.Size(85, 23);");
sw.WriteLine(" this.btnCancel.TabIndex = 1;");
sw.WriteLine(" this.btnCancel.TabStop = false;");
sw.WriteLine(" this.btnCancel.Text = \"取消修改\";");
sw.WriteLine(" this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);");
sw.WriteLine(" // ");
sw.WriteLine(" // btnClose");
sw.WriteLine(" // ");
sw.WriteLine(" this.btnClose.CausesValidation = false;");
sw.WriteLine(" this.btnClose.Location = new System.Drawing.Point(467, 3);");
sw.WriteLine(" this.btnClose.Name = \"btnClose\";");
sw.WriteLine(" this.btnClose.Size = new System.Drawing.Size(85, 23);");
sw.WriteLine(" this.btnClose.TabIndex = 2;");
sw.WriteLine(" this.btnClose.TabStop = false;");
sw.WriteLine(" this.btnClose.Text = \"退出\";");
sw.WriteLine(" this.btnClose.Click += new System.EventHandler(this.btnClose_Click);");
sw.WriteLine(" // ");
sw.WriteLine(" // bindingNavigator" + dt.TitleCaseName + "");
sw.WriteLine(" // ");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".AddNewItem = null;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".CountItem = this.bindingNavigatorCountItem;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".DeleteItem = null;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".Dock = System.Windows.Forms.DockStyle.Bottom;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".Items.AddRange(new System.Windows.Forms.ToolStripItem[] {");
sw.WriteLine(" this.bindingNavigatorMoveFirstItem,");
sw.WriteLine(" this.bindingNavigatorMovePreviousItem,");
sw.WriteLine(" this.bindingNavigatorSeparator,");
sw.WriteLine(" this.bindingNavigatorPositionItem,");
sw.WriteLine(" this.bindingNavigatorCountItem,");
sw.WriteLine(" this.bindingNavigatorSeparator1,");
sw.WriteLine(" this.bindingNavigatorMoveNextItem,");
sw.WriteLine(" this.bindingNavigatorMoveLastItem});");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".Location = new System.Drawing.Point(0, 34);");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".MoveFirstItem = this.bindingNavigatorMoveFirstItem;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".MoveLastItem = this.bindingNavigatorMoveLastItem;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".MoveNextItem = this.bindingNavigatorMoveNextItem;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".MovePreviousItem = this.bindingNavigatorMovePreviousItem;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".Name = \"bindingNavigator" + dt.TitleCaseName + "\";");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".PositionItem = this.bindingNavigatorPositionItem;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".Size = new System.Drawing.Size(455, 25);");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".TabIndex = 2;");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".Text = \"bindingNavigator1\";");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".MouseEnter += new System.EventHandler(this.bindingNavigator" + dt.TitleCaseName + "_MouseEnter);");
sw.WriteLine(" // ");
sw.WriteLine(" // bindingNavigatorCountItem");
sw.WriteLine(" // ");
sw.WriteLine(" this.bindingNavigatorCountItem.Name = \"bindingNavigatorCountItem\";");
sw.WriteLine(" this.bindingNavigatorCountItem.Size = new System.Drawing.Size(35, 22);");
sw.WriteLine(" this.bindingNavigatorCountItem.Text = \"/ {0}\";");
sw.WriteLine(" this.bindingNavigatorCountItem.ToolTipText = \"总项数\";");
sw.WriteLine(" // ");
sw.WriteLine(" // bindingNavigatorMoveFirstItem");
sw.WriteLine(" // ");
sw.WriteLine(" this.bindingNavigatorMoveFirstItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;");
sw.WriteLine(" this.bindingNavigatorMoveFirstItem.Image = ((System.Drawing.Image)(resources.GetObject(\"bindingNavigatorMoveFirstItem.Image\")));");
sw.WriteLine(" this.bindingNavigatorMoveFirstItem.Name = \"bindingNavigatorMoveFirstItem\";");
sw.WriteLine(" this.bindingNavigatorMoveFirstItem.RightToLeftAutoMirrorImage = true;");
sw.WriteLine(" this.bindingNavigatorMoveFirstItem.Size = new System.Drawing.Size(23, 22);");
sw.WriteLine(" this.bindingNavigatorMoveFirstItem.Text = \"移到第一条记录\";");
sw.WriteLine(" // ");
sw.WriteLine(" // bindingNavigatorMovePreviousItem");
sw.WriteLine(" // ");
sw.WriteLine(" this.bindingNavigatorMovePreviousItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;");
sw.WriteLine(" this.bindingNavigatorMovePreviousItem.Image = ((System.Drawing.Image)(resources.GetObject(\"bindingNavigatorMovePreviousItem.Image\")));");
sw.WriteLine(" this.bindingNavigatorMovePreviousItem.Name = \"bindingNavigatorMovePreviousItem\";");
sw.WriteLine(" this.bindingNavigatorMovePreviousItem.RightToLeftAutoMirrorImage = true;");
sw.WriteLine(" this.bindingNavigatorMovePreviousItem.Size = new System.Drawing.Size(23, 22);");
sw.WriteLine(" this.bindingNavigatorMovePreviousItem.Text = \"移到上一条记录\";");
sw.WriteLine(" // ");
sw.WriteLine(" // bindingNavigatorSeparator");
sw.WriteLine(" // ");
sw.WriteLine(" this.bindingNavigatorSeparator.Name = \"bindingNavigatorSeparator\";");
sw.WriteLine(" this.bindingNavigatorSeparator.Size = new System.Drawing.Size(6, 25);");
sw.WriteLine(" // ");
sw.WriteLine(" // bindingNavigatorPositionItem");
sw.WriteLine(" // ");
sw.WriteLine(" this.bindingNavigatorPositionItem.AccessibleName = \"位置\";");
sw.WriteLine(" this.bindingNavigatorPositionItem.AutoSize = false;");
sw.WriteLine(" this.bindingNavigatorPositionItem.Name = \"bindingNavigatorPositionItem\";");
sw.WriteLine(" this.bindingNavigatorPositionItem.Size = new System.Drawing.Size(50, 21);");
sw.WriteLine(" this.bindingNavigatorPositionItem.Text = \"0\";");
sw.WriteLine(" this.bindingNavigatorPositionItem.ToolTipText = \"当前位置\";");
sw.WriteLine(" // ");
sw.WriteLine(" // bindingNavigatorSeparator1");
sw.WriteLine(" // ");
sw.WriteLine(" this.bindingNavigatorSeparator1.Name = \"bindingNavigatorSeparator1\";");
sw.WriteLine(" this.bindingNavigatorSeparator1.Size = new System.Drawing.Size(6, 25);");
sw.WriteLine(" // ");
sw.WriteLine(" // bindingNavigatorMoveNextItem");
sw.WriteLine(" // ");
sw.WriteLine(" this.bindingNavigatorMoveNextItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;");
sw.WriteLine(" this.bindingNavigatorMoveNextItem.Image = ((System.Drawing.Image)(resources.GetObject(\"bindingNavigatorMoveNextItem.Image\")));");
sw.WriteLine(" this.bindingNavigatorMoveNextItem.Name = \"bindingNavigatorMoveNextItem\";");
sw.WriteLine(" this.bindingNavigatorMoveNextItem.RightToLeftAutoMirrorImage = true;");
sw.WriteLine(" this.bindingNavigatorMoveNextItem.Size = new System.Drawing.Size(23, 22);");
sw.WriteLine(" this.bindingNavigatorMoveNextItem.Text = \"移到下一条记录\";");
sw.WriteLine(" // ");
sw.WriteLine(" // bindingNavigatorMoveLastItem");
sw.WriteLine(" // ");
sw.WriteLine(" this.bindingNavigatorMoveLastItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;");
sw.WriteLine(" this.bindingNavigatorMoveLastItem.Image = ((System.Drawing.Image)(resources.GetObject(\"bindingNavigatorMoveLastItem.Image\")));");
sw.WriteLine(" this.bindingNavigatorMoveLastItem.Name = \"bindingNavigatorMoveLastItem\";");
sw.WriteLine(" this.bindingNavigatorMoveLastItem.RightToLeftAutoMirrorImage = true;");
sw.WriteLine(" this.bindingNavigatorMoveLastItem.Size = new System.Drawing.Size(23, 22);");
sw.WriteLine(" this.bindingNavigatorMoveLastItem.Text = \"移到最后一条记录\";");
sw.WriteLine(" // ");
sw.WriteLine(" // myPanel1");
sw.WriteLine(" // ");
sw.WriteLine(" this.myPanel1.CausesValidation = false;");
sw.WriteLine(" this.myPanel1.Controls.Add(this.btnSave);");
sw.WriteLine(" this.myPanel1.Controls.Add(this.btnCancel);");
sw.WriteLine(" this.myPanel1.Controls.Add(this.bindingNavigator" + dt.TitleCaseName + ");");
sw.WriteLine(" this.myPanel1.Controls.Add(this.btnClose);");
sw.WriteLine(" this.myPanel1.Dock = System.Windows.Forms.DockStyle.Bottom;");
sw.WriteLine(" this.myPanel1.Location = new System.Drawing.Point(0, 162);");
sw.WriteLine(" this.myPanel1.Name = \"myPanel1\";");
sw.WriteLine(" this.myPanel1.Size = new System.Drawing.Size(455, 59);");
sw.WriteLine(" this.myPanel1.TabIndex = 1;");
sw.WriteLine(" // ");
sw.WriteLine(" // uc" + dt.TitleCaseName + "Detail1");
sw.WriteLine(" // ");
sw.WriteLine(" this.uc" + dt.TitleCaseName + "Detail1.Dock = System.Windows.Forms.DockStyle.Fill;");
sw.WriteLine(" this.uc" + dt.TitleCaseName + "Detail1.Location = new System.Drawing.Point(0, 0);");
sw.WriteLine(" this.uc" + dt.TitleCaseName + "Detail1.Name = \"uc" + dt.TitleCaseName + "Detail1\";");
sw.WriteLine(" this.uc" + dt.TitleCaseName + "Detail1.TabIndex = 0;");
sw.WriteLine(" this.uc" + dt.TitleCaseName + "Detail1." + dt.TitleCaseName + "BindingSource = null;");
sw.WriteLine(" // ");
sw.WriteLine(" // dlg" + dt.TitleCaseName + "Edit");
sw.WriteLine(" // ");
sw.WriteLine(" this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);");
sw.WriteLine(" this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;");
sw.WriteLine(" this.Controls.Add(this.uc" + dt.TitleCaseName + "Detail1);");
sw.WriteLine(" this.Controls.Add(this.myPanel1);");
sw.WriteLine(" this.Name = \"dlg" + dt.TitleCaseName + "Edit\";");
sw.WriteLine(" this.Text = \"编辑对话框\";");
sw.WriteLine(" this.Load += new System.EventHandler(this.dlg" + dt.TitleCaseName + "Edit_Load);");
sw.WriteLine(" this.FormClosing += new System.Windows.Forms.FormClosingEventHandler(this.dlg" + dt.TitleCaseName + "Edit_FormClosing);");
sw.WriteLine(" ((System.ComponentModel.ISupportInitialize)(this.bindingNavigator" + dt.TitleCaseName + ")).EndInit();");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".ResumeLayout(false);");
sw.WriteLine(" this.bindingNavigator" + dt.TitleCaseName + ".PerformLayout();");
sw.WriteLine(" this.myPanel1.ResumeLayout(false);");
sw.WriteLine(" this.myPanel1.PerformLayout();");
sw.WriteLine(" this.ResumeLayout(false);");
sw.WriteLine("");
sw.WriteLine(" }");
sw.WriteLine("");
sw.WriteLine(" #endregion");
sw.WriteLine("");
sw.WriteLine(" public " + BaseParams.UserControlsNameSpace + ".Details.Uc" + dt.TitleCaseName + "Detail uc" + dt.TitleCaseName + "Detail1;");
sw.WriteLine(" private " + BaseParams.ControlsNameSpace + ".MyButton btnSave;");
sw.WriteLine(" private " + BaseParams.ControlsNameSpace + ".MyButton btnCancel;");
sw.WriteLine(" private " + BaseParams.ControlsNameSpace + ".MyButton btnClose;");
sw.WriteLine(" private System.Windows.Forms.BindingNavigator bindingNavigator" + dt.TitleCaseName + ";");
sw.WriteLine(" private System.Windows.Forms.ToolStripLabel bindingNavigatorCountItem;");
sw.WriteLine(" private System.Windows.Forms.ToolStripButton bindingNavigatorMoveFirstItem;");
sw.WriteLine(" private System.Windows.Forms.ToolStripButton bindingNavigatorMovePreviousItem;");
sw.WriteLine(" private System.Windows.Forms.ToolStripSeparator bindingNavigatorSeparator;");
sw.WriteLine(" private System.Windows.Forms.ToolStripTextBox bindingNavigatorPositionItem;");
sw.WriteLine(" private System.Windows.Forms.ToolStripSeparator bindingNavigatorSeparator1;");
sw.WriteLine(" private System.Windows.Forms.ToolStripButton bindingNavigatorMoveNextItem;");
sw.WriteLine(" private System.Windows.Forms.ToolStripButton bindingNavigatorMoveLastItem;");
sw.WriteLine(" private " + BaseParams.ControlsNameSpace + ".MyPanel myPanel1;");
sw.WriteLine("");
sw.WriteLine(" }");
sw.WriteLine("}");
sw.Close();
file.Close();
#endregion
}
static string GetValue(Db db, DbTable table, string fieldName, object value, int maxSize)
{
if (value.IsNull())
return String.Empty;
Type t = value.GetType();
string valueStr;
if (t.IsEnum) valueStr = Convert.ToInt32(value).ToString();
else valueStr = value.ToString();
if (table.ForeignKeys.Count > 0)
{
SchemaForeignKey fk = table.GetForeignKey(fieldName);
if (fk != null && fk.FieldNames.Count == 1)
{
DbTable fTable = db.GetDbTable(fk.ReferencedTableName);
if (fTable.ContainsField(DbFieldNames.Name))
{
string refFieldName = fk.ReferencedFieldNames[0];
string sql = String.Format("SELECT MIN({0}) FROM {1} WHERE {2}={3}",
DbFieldNames.Name,
fk.ReferencedTableName,
refFieldName,
db.GetParameterMarker(refFieldName));
using (DbCommand cm = db.CreateCommand())
{
cm.CommandText = sql;
cm.Parameters.Add(db.CreateParameter(fTable.Fields[refFieldName], value));
string nameStr = db.ExecScalar(cm)
.IfNull(String.Empty);
valueStr = String.Format("{0} ({1})", valueStr, nameStr);
}
}
}
}
if (valueStr.Length > maxSize)
return valueStr.Substring(0, maxSize);
return valueStr;
}
/// <summary>
/// Reads tables schema from database
/// </summary>
private List<DbTable> ReadTables(List<DbView> viewList)
{
List<DbTable> result = new List<DbTable>();
if (_dbConnection.State != ConnectionState.Open)
_dbConnection.Open();
try
{
using (DataTable tables = _dbConnection.GetSchema("Tables"))
{
foreach (DataRow row in tables.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
if (!IsTableSelected(tableName))
continue;
// search in views about this
foreach (var view in viewList)
{
if (view.TableName == tableName)
{
// no view
continue;
}
}
// read the f*****g columns
List<DbColumn> columns = ReadColumns(tableName);
// read columns description
if (ReadColumnsDescription)
ApplyColumnsDescription(tableName, columns);
// new table
var dbTable = new DbTable(tableName, columns);
// table schema
dbTable.OwnerName = row["TABLE_SCHEMA"].ToString();
// add to results
result.Add(dbTable);
}
// it is time to read foreign keys!
// foreign keys are requested?
if (ReadTablesForeignKeys)
{
ApplyTablesForeignKeys(result);
ApplyDetectedOneToOneRelation(result);
}
}
}
finally
{
_dbConnection.Close();
}
return result;
}
protected override async Task <object> Insert <TEntity>(Expression <Func <TEntity> > content, string table, bool @async)
{
PublicHelper.CheckNull(content);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
if (typeDescriptor.PrimaryKeys.Count > 1)
{
/* 对于多主键的实体，暂时不支持调用这个方法进行插入 */
throw new NotSupportedException(string.Format("Can not call this method because entity '{0}' has multiple keys.", typeDescriptor.Definition.Type.FullName));
}
PrimitivePropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys.FirstOrDefault();
Dictionary <MemberInfo, Expression> insertColumns = InitMemberExtractor.Extract(content);
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(dbTable);
DbInsertExpression insertExp = new DbInsertExpression(dbTable);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PrimitivePropertyDescriptor propertyDescriptor = typeDescriptor.GetPrimitivePropertyDescriptor(key);
if (propertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("Could not insert value into the auto increment column '{0}'.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("Can not insert value into the column '{0}', because it's mapping member has define a sequence.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.IsPrimaryKey)
{
object val = ExpressionEvaluator.Evaluate(kv.Value);
if (val == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", propertyDescriptor.Property.Name));
}
else
{
keyVal = val;
insertExp.InsertColumns.Add(propertyDescriptor.Column, DbExpression.Parameter(keyVal, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType));
continue;
}
}
insertExp.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement && propertyDescriptor.IsPrimaryKey)
{
insertExp.Returns.Add(propertyDescriptor.Column);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor, dbTable.Schema);
insertExp.InsertColumns.Add(propertyDescriptor.Column, getNextValueForSequenceExp);
if (propertyDescriptor.IsPrimaryKey)
{
insertExp.Returns.Add(propertyDescriptor.Column);
}
continue;
}
}
if (keyPropertyDescriptor != null)
{
//主键为空并且主键又不是自增列
if (keyVal == null && !keyPropertyDescriptor.IsAutoIncrement && !keyPropertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
}
DbCommandInfo dbCommandInfo = this.Translate(insertExp);
await this.ExecuteNonQuery(dbCommandInfo, @async);
if (keyPropertyDescriptor != null && (keyPropertyDescriptor.IsAutoIncrement || keyPropertyDescriptor.HasSequence()))
{
string outputColumnName = Utils.GenOutputColumnParameterName(keyPropertyDescriptor.Column.Name);
DbParam outputParam = dbCommandInfo.Parameters.Where(a => a.Direction == ParamDirection.Output && a.Name == outputColumnName).First();
keyVal = PublicHelper.ConvertObjectType(outputParam.Value, keyPropertyDescriptor.PropertyType);
}
return(keyVal); /* It will return null if an entity does not define primary key. */
}
public int Update(DbTable table, Dictionary<string, object> row)
{
int r = 0;
using (DbCommand cm = this.CreateCommand())
{
if (table.ContainsField(DbFieldNames.UpdatedOn))
{
if (row.ContainsKey(DbFieldNames.UpdatedOn))
row[DbFieldNames.UpdatedOn] = DateTime.Now;
else
row.Add(DbFieldNames.UpdatedOn, DateTime.Now);
}
if (UserInfoProvider != null && table.ContainsField(DbFieldNames.UpdatedBy))
{
if (row.ContainsKey(DbFieldNames.UpdatedBy))
row[DbFieldNames.UpdatedBy] = UserInfoProvider.UserName;
else
row.Add(DbFieldNames.UpdatedBy, UserInfoProvider.UserName);
}
StringBuilder s = new StringBuilder(String.Format("UPDATE {0} SET ", table.TableName));
int count = table.Fields.Count;
for (int i = 0; i < count; i++)
{
DbField dbf = table.Fields[i];
if (dbf.IsAutoIncrement) continue;
if (dbf.IsKey) continue;
string fieldName = dbf.FieldName;
if (!row.ContainsKey(fieldName)) continue;
if (fieldName.Equals(DbFieldNames.CreatedBy, StringComparison.OrdinalIgnoreCase)) continue;
if (fieldName.Equals(DbFieldNames.CreatedOn, StringComparison.OrdinalIgnoreCase)) continue;
object o = row[fieldName];
s.AppendFormat("{0}={1},", fieldName, GetParameterMarker(fieldName));
DbParameter p = CreateParameter(dbf, o);
cm.Parameters.Add(p);
}
if (s.Length > 0)
{
s.Remove(s.Length - 1, 1);
}
s.Append(" WHERE ");
// Note: if key value changed, update record count will be 0
BuildWhere(table, table.PrimaryKeys, row, s, cm);
string sql = s.ToString();
cm.CommandText = sql;
//OnExecuting(ExecTypes.Update, cm);
DoAudit(new DbAuditArgs(ExecTypes.Update, table, cm.CommandText, row));
r = cm.ExecuteNonQuery();
//OnExecuted(ExecTypes.Update, cm, r);
}
return r;
}
public abstract bool ModifySchema(DbTable oldSchema, DbTable newSchema);
protected void BuildWhere(DbTable table, List<DbField> fields, Dictionary<string, object> values, StringBuilder sql, DbCommand cm)
{
if (fields == null || fields.Count < 1)
{
throw new MissingPrimaryKeyException();
}
int count = fields.Count - 1;
for (int i = 0; i <= count; i++)
{
string fieldName = fields[i].FieldName;
if (!values.ContainsKey(fieldName))
{
throw new MissingPrimaryKeyValueException(fieldName);
}
DbField dbf = table.Fields[fieldName];
object o = values[fieldName];
string whereFieldName = String.Format("wh{0}", fieldName);
sql.AppendFormat("{0}={1}", fieldName, GetParameterMarker(whereFieldName));
DbParameter p = CreateParameter(dbf, o);
p.ParameterName = GetParameterMarker(whereFieldName);
cm.Parameters.Add(p);
if (i < count)
{
sql.Append(" AND ");
}
}// end for
}
public bool TryGetDbTable(string dbTableId, out DbTable dbTable)
{
return _acDomain.Rdbs.DbTables.TryGetDbTable(this, dbTableId, out dbTable);
}
/// <summary>
/// Reads tables schema from database
/// </summary>
private List<DbTable> ReadTables(List<DbView> viewList)
{
List<DbTable> result = new List<DbTable>();
using (DataTable tables = _dbConnection.GetSchema("TABLES"))
{
foreach (DataRow row in tables.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
string ownerName = row["TABLE_SCHEMA"].ToString();
if (!IsTableSelected(tableName))
continue;
bool jumpToNext = false;
// search in views about this
for (int i = 0; i < viewList.Count; i++)
{
if (viewList[i].TableName == tableName)
{
jumpToNext = true;
// we ignore view here
break;
}
}
if (jumpToNext) continue;
// View columns
List<DbColumn> columns = ReadColumns(tableName, ownerName);
// read columns description
if (ReadColumnsDescription)
ApplyColumnsDescription(tableName, columns);
// new table
var dbTable = new DbTable(tableName, columns);
// table schema
dbTable.OwnerName = ownerName;
// add to results
result.Add(dbTable);
}
// detect the sql server version
SQLServerVersions sqlVersion = DetectSqlServerVersion(_dbConnection);
if (ReadConstraintKeys)
// The constraint keys will read here
ApplyTablesConstraintKeys(result, sqlVersion);
// it is time to read foreign keys!
// foreign keys are requested?
if (ReadTablesForeignKeys)
ApplyTablesForeignKeys(result, sqlVersion);
// Normalize the constraints keys
NormalizeTablesConstraintKeys(result, sqlVersion);
if (ReadTablesForeignKeys)
ApplyDetectedOneToOneRelation(result);
}
return result;
}
public Column(Keyword tAs, Identifier tAlias, Identifier t, Identifier tTableAlias, DbTable table, string tableAlias) : base(TokenType.Column, t.startOffset, t.name)
{
parentQuery = t.parentQuery;
asToken = tAs;
columnAliasToken = tAlias;
tableAliasToken = tTableAlias;
columnNameToken = t;
charAfter = t.charAfter;
columnType = ColumnType.Proposed;
proposedTableAlias = tableAlias;
proposedTable = table.name;
UpdateBounds();
}
/*
* insert into stat (siteid,statdate,`count`,cost,createtime,updatetime) values
* (1,'2018-08-11 09:34:00',1,123,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now()),
* (3,'2018-08-11 09:34:00',1,789,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now())
* on duplicate key update
* `count`=`count`+values(`count`),cost=cost+values(cost),
* updatetime=values(updatetime);
*/
private String GetBatchSql(String tableName, IDataColumn[] columns, ICollection <String> updateColumns, ICollection <String> addColumns, IEnumerable <IIndexAccessor> list)
{
var sb = Pool.StringBuilder.Get();
var db = Database as DbBase;
// 字段列表
//if (columns == null) columns = table.Columns.ToArray();
sb.AppendFormat("Insert Into {0}(", db.FormatTableName(tableName));
foreach (var dc in columns)
{
//if (dc.Identity) continue;
sb.Append(db.FormatName(dc.ColumnName));
sb.Append(",");
}
sb.Length--;
sb.Append(")");
// 值列表
sb.Append(" Values");
// 优化支持DbTable
if (list.FirstOrDefault() is DbRow)
{
// 提前把列名转为索引，然后根据索引找数据
DbTable dt = null;
Int32[] ids = null;
foreach (DbRow dr in list)
{
if (dr.Table != dt)
{
dt = dr.Table;
var cs = new List <Int32>();
foreach (var dc in columns)
{
//if (dc.Identity)
// cs.Add(0);
//else
cs.Add(dt.GetColumn(dc.Name));
}
ids = cs.ToArray();
}
sb.Append("(");
var row = dt.Rows[dr.Index];
for (var i = 0; i < columns.Length; i++)
{
var dc = columns[i];
//if (dc.Identity) continue;
var value = row[ids[i]];
sb.Append(db.FormatValue(dc, value));
sb.Append(",");
}
sb.Length--;
sb.Append("),");
}
}
else
{
foreach (var entity in list)
{
sb.Append("(");
foreach (var dc in columns)
{
//if (dc.Identity) continue;
var value = entity[dc.Name];
sb.Append(db.FormatValue(dc, value));
sb.Append(",");
}
sb.Length--;
sb.Append("),");
}
}
sb.Length--;
// 重复键执行update
if (updateColumns != null || addColumns != null)
{
sb.Append(" On Conflict");
// 先找唯一索引，再用主键
var table = columns.FirstOrDefault()?.Table;
var di = table.Indexes?.FirstOrDefault(e => e.Unique);
if (di != null && di.Columns != null && di.Columns.Length > 0)
{
sb.AppendFormat("({0})", di.Columns.Join(",", e => db.FormatName(e)));
}
else
{
var pks = table.PrimaryKeys;
if (pks != null && pks.Length > 0)
{
sb.AppendFormat("({0})", pks.Join(",", e => db.FormatName(e.ColumnName)));
}
}
sb.Append(" Do Update Set ");
if (updateColumns != null)
{
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (updateColumns.Contains(dc.Name) && (addColumns == null || !addColumns.Contains(dc.Name)))
{
sb.AppendFormat("{0}=excluded.{0},", db.FormatName(dc.ColumnName));
}
}
sb.Length--;
}
if (addColumns != null)
{
sb.Append(",");
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (addColumns.Contains(dc.Name))
{
sb.AppendFormat("{0}={0}+excluded.{0},", db.FormatName(dc.ColumnName));
}
}
sb.Length--;
}
}
return(sb.Put(true));
}
protected override async Task InsertRange <TEntity>(List <TEntity> entities, string table, bool @async)
{
/*
* 将 entities 分批插入数据库
* 每批生成 insert into TableName(...) select ... from dual union all select ... from dual...
* 对于 oracle，貌似速度提升不了...- -
* #期待各码友的优化建议#
*/
PublicHelper.CheckNull(entities);
if (entities.Count == 0)
{
return;
}
int maxParameters = 1000;
int batchSize = 40; /* 每批实体大小，此值通过测试得出相对插入速度比较快的一个值 */
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
List <PrimitivePropertyDescriptor> mappingPropertyDescriptors = typeDescriptor.PrimitivePropertyDescriptors.Where(a => a.IsAutoIncrement == false).ToList();
int maxDbParamsCount = maxParameters - mappingPropertyDescriptors.Count; /* 控制一个 sql 的参数个数 */
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
string sqlTemplate = AppendInsertRangeSqlTemplate(dbTable, mappingPropertyDescriptors);
Func <Task> insertAction = async() =>
{
int batchCount = 0;
List <DbParam> dbParams = new List <DbParam>();
StringBuilder sqlBuilder = new StringBuilder();
for (int i = 0; i < entities.Count; i++)
{
var entity = entities[i];
if (batchCount > 0)
{
sqlBuilder.Append(" UNION ALL ");
}
sqlBuilder.Append("SELECT ");
for (int j = 0; j < mappingPropertyDescriptors.Count; j++)
{
if (j > 0)
{
sqlBuilder.Append(",");
}
PrimitivePropertyDescriptor mappingPropertyDescriptor = mappingPropertyDescriptors[j];
object val = mappingPropertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(mappingPropertyDescriptor, val);
if (val == null)
{
sqlBuilder.Append("NULL");
sqlBuilder.Append(" C").Append(j.ToString());
continue;
}
Type valType = val.GetType();
if (valType.IsEnum)
{
val = Convert.ChangeType(val, Enum.GetUnderlyingType(valType));
valType = val.GetType();
}
if (Utils.IsToStringableNumericType(valType))
{
sqlBuilder.Append(val.ToString());
}
else if (val is bool)
{
if ((bool)val == true)
{
sqlBuilder.AppendFormat("1");
}
else
{
sqlBuilder.AppendFormat("0");
}
}
else
{
string paramName = UtilConstants.ParameterNamePrefix + dbParams.Count.ToString();
DbParam dbParam = new DbParam(paramName, val)
{
DbType = mappingPropertyDescriptor.Column.DbType
};
dbParams.Add(dbParam);
sqlBuilder.Append(paramName);
}
sqlBuilder.Append(" C").Append(j.ToString());
}
sqlBuilder.Append(" FROM DUAL");
batchCount++;
if ((batchCount >= 20 && dbParams.Count >= 400 /*参数个数太多也会影响速度*/) || dbParams.Count >= maxDbParamsCount || batchCount >= batchSize || (i + 1) == entities.Count)
{
sqlBuilder.Insert(0, sqlTemplate);
sqlBuilder.Append(") T");
string sql = sqlBuilder.ToString();
await this.Session.ExecuteNonQuery(sql, dbParams.ToArray(), @async);
sqlBuilder.Clear();
dbParams.Clear();
batchCount = 0;
}
}
};
Func <Task> fAction = insertAction;
if (this.Session.IsInTransaction)
{
await fAction();
return;
}
/* 因为分批插入，所以需要开启事务保证数据一致性 */
using (var tran = this.BeginTransaction())
{
await fAction();
tran.Commit();
}
}
public DbTable GetTable(string tableName)
{
CheckValid();
CheckNameValid(tableName);
// Is it in the map?
lock (tableMap) {
DbTable table;
if (tableMap.TryGetValue(tableName, out table))
// It's there, so return it,
return table;
Key k = tableSet.GetItem(tableName);
if (k == null)
// Doesn't exist, so throw an exception
throw new ApplicationException("Table doesn't exist: " + tableName);
long kid = k.Primary;
if (kid > Int64.MaxValue)
// We ran out of keys so can't make any more table items,
// This happens after 2 billion tables created. We need to note this
// as a limitation.
throw new ApplicationException("Id pool exhausted for table item.");
// Turn the key into an SDBTable,
table = new DbTable(this, tableSet.GetItemDataFile(tableName), (int) kid);
tableMap[tableName] = table;
// And return it,
return table;
}
}
protected virtual async Task <TEntity> Insert <TEntity>(TEntity entity, string table, bool @async)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
Dictionary <PrimitivePropertyDescriptor, object> keyValueMap = PrimaryKeyHelper.CreateKeyValueMap(typeDescriptor);
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement)
{
continue;
}
object val = propertyDescriptor.GetValue(entity);
if (propertyDescriptor.IsPrimaryKey)
{
keyValueMap[propertyDescriptor] = val;
}
PublicHelper.NotNullCheck(propertyDescriptor, val);
DbParameterExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
PrimitivePropertyDescriptor nullValueKey = keyValueMap.Where(a => a.Value == null && !a.Key.IsAutoIncrement).Select(a => a.Key).FirstOrDefault();
if (nullValueKey != null)
{
/* 主键为空并且主键又不是自增列 */
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", nullValueKey.Property.Name));
}
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
DbInsertExpression e = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
e.InsertColumns.Add(kv.Key.Column, kv.Value);
}
PrimitivePropertyDescriptor autoIncrementPropertyDescriptor = typeDescriptor.AutoIncrement;
if (autoIncrementPropertyDescriptor == null)
{
await this.ExecuteNonQuery(e, @async);
return(entity);
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(e);
dbCommandInfo.CommandText = string.Concat(dbCommandInfo.CommandText, ";", this.GetSelectLastInsertIdClause());
//SELECT @@IDENTITY 返回的是 decimal 类型
object retIdentity = await this.ExecuteScalar(dbCommandInfo, @async);
if (retIdentity == null || retIdentity == DBNull.Value)
{
throw new ChloeException("Unable to get the identity value.");
}
retIdentity = PublicHelper.ConvertObjectType(retIdentity, autoIncrementPropertyDescriptor.PropertyType);
autoIncrementPropertyDescriptor.SetValue(entity, retIdentity);
return(entity);
}
public abstract bool CreateTable(DbTable tbl);
protected virtual async Task <object> Insert <TEntity>(Expression <Func <TEntity> > content, string table, bool @async)
{
PublicHelper.CheckNull(content);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
if (typeDescriptor.PrimaryKeys.Count > 1)
{
/* 对于多主键的实体，暂时不支持调用这个方法进行插入 */
throw new NotSupportedException(string.Format("Can not call this method because entity '{0}' has multiple keys.", typeDescriptor.Definition.Type.FullName));
}
PrimitivePropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys.FirstOrDefault();
Dictionary <MemberInfo, Expression> insertColumns = InitMemberExtractor.Extract(content);
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(dbTable);
DbInsertExpression e = new DbInsertExpression(dbTable);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PrimitivePropertyDescriptor propertyDescriptor = typeDescriptor.GetPrimitivePropertyDescriptor(key);
if (propertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("Could not insert value into the identity column '{0}'.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.IsPrimaryKey)
{
object val = ExpressionEvaluator.Evaluate(kv.Value);
if (val == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", propertyDescriptor.Property.Name));
}
else
{
keyVal = val;
e.InsertColumns.Add(propertyDescriptor.Column, DbExpression.Parameter(keyVal, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType));
continue;
}
}
e.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
if (keyPropertyDescriptor != null)
{
//主键为空并且主键又不是自增列
if (keyVal == null && !keyPropertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
}
if (keyPropertyDescriptor == null || !keyPropertyDescriptor.IsAutoIncrement)
{
await this.ExecuteNonQuery(e, @async);
return(keyVal); /* It will return null if an entity does not define primary key. */
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(e);
dbCommandInfo.CommandText = string.Concat(dbCommandInfo.CommandText, ";", this.GetSelectLastInsertIdClause());
//SELECT @@IDENTITY 返回的是 decimal 类型
object retIdentity = await this.ExecuteScalar(dbCommandInfo, @async);
if (retIdentity == null || retIdentity == DBNull.Value)
{
throw new ChloeException("Unable to get the identity value.");
}
retIdentity = PublicHelper.ConvertObjectType(retIdentity, typeDescriptor.AutoIncrement.PropertyType);
return(retIdentity);
}
/// <summary>
/// 返回给定的表模式克隆得到的新表
/// </summary>
/// <param name="dbTable"></param>
/// <returns></returns>
public DataTable NewTable(DbTable dbTable)
{
return this.GetTableSchema(dbTable).Clone();
}
protected virtual async Task <int> Update <TEntity>(TEntity entity, string table, bool @async)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
PublicHelper.EnsureHasPrimaryKey(typeDescriptor);
PairList <PrimitivePropertyDescriptor, object> keyValues = new PairList <PrimitivePropertyDescriptor, object>(typeDescriptor.PrimaryKeys.Count);
IEntityState entityState = this.TryGetTrackedEntityState(entity);
Dictionary <PrimitivePropertyDescriptor, DbExpression> updateColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsPrimaryKey)
{
var keyValue = propertyDescriptor.GetValue(entity);
PrimaryKeyHelper.KeyValueNotNull(propertyDescriptor, keyValue);
keyValues.Add(propertyDescriptor, keyValue);
continue;
}
if (propertyDescriptor.CannotUpdate())
{
continue;
}
object val = propertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(propertyDescriptor, val);
if (entityState != null && !entityState.HasChanged(propertyDescriptor, val))
{
continue;
}
DbExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
updateColumns.Add(propertyDescriptor, valExp);
}
object rowVersionNewValue = null;
if (typeDescriptor.HasRowVersion())
{
var rowVersionDescriptor = typeDescriptor.RowVersion;
var rowVersionOldValue = rowVersionDescriptor.GetValue(entity);
rowVersionNewValue = PublicHelper.IncreaseRowVersionNumber(rowVersionOldValue);
updateColumns.Add(rowVersionDescriptor, DbExpression.Parameter(rowVersionNewValue, rowVersionDescriptor.PropertyType, rowVersionDescriptor.Column.DbType));
keyValues.Add(rowVersionDescriptor, rowVersionOldValue);
}
if (updateColumns.Count == 0)
{
return(0);
}
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
DbExpression conditionExp = PublicHelper.MakeCondition(keyValues, dbTable);
DbUpdateExpression e = new DbUpdateExpression(dbTable, conditionExp);
foreach (var item in updateColumns)
{
e.UpdateColumns.Add(item.Key.Column, item.Value);
}
int rowsAffected = await this.ExecuteNonQuery(e, @async);
if (typeDescriptor.HasRowVersion())
{
PublicHelper.CauseErrorIfOptimisticUpdateFailed(rowsAffected);
typeDescriptor.RowVersion.SetValue(entity, rowVersionNewValue);
}
if (entityState != null)
{
entityState.Refresh();
}
return(rowsAffected);
}
/// <summary>
/// 获取给定表的表模式
/// <remarks>表模式是一个ADO.NET表<see cref="DataTable"/></remarks>
/// </summary>
/// <returns></returns>
private DataTable GetTableSchema(DbTable dbTable)
{
if (dbTable == null)
{
throw new ArgumentNullException("dbTable");
}
if (!_tableSchemas.ContainsKey(dbTable.Id))
{
lock (GlobalLocker)
{
if (_tableSchemas.ContainsKey(dbTable.Id)) return _tableSchemas[dbTable.Id];
IReadOnlyDictionary<string, DbTableColumn> dbTableColumns;
if (!_acDomain.Rdbs.DbTableColumns.TryGetDbTableColumns(this, dbTable, out dbTableColumns))
{
throw new AnycmdException("意外的数据库表");
}
var dataTable = new DataTable(dbTable.Name);
foreach (var col in dbTableColumns.Select(a => a.Value).OrderBy(a => a.Ordinal))
{
dataTable.Columns.Add(col.ToDataColumn());
}
_tableSchemas.Add(dbTable.Id, dataTable);
}
}
return _tableSchemas[dbTable.Id];
}
public DbJoinStructure GetJoinStructure(DbTable table)
{
var result = new DbJoinStructure
{
DisplayName = table.DisplayName
};
foreach (var column in table.Columns.Select(c => c as Models.DbColumn))
{
result.Columns.Add(column.Name);
result.ColumnTypes.Add(column.DataType);
result.ShowColumns.Add(column.IsPrimaryKey == false && column.Parent == null && column.Name.EndsWith("id", StringComparison.OrdinalIgnoreCase) == false);
}
Stack <DbTable> tableQueue = new Stack <DbTable>();
tableQueue.Push(table);
Stack <DbJoinStructure> joinQueue = new Stack <DbJoinStructure>();
joinQueue.Push(result);
HashSet <DbTable> visited = new HashSet <DbTable>();
while (tableQueue.Any())
{
var currTable = tableQueue.Pop();
var currJoin = joinQueue.Pop();
if (visited.Contains(currTable) == false)
{
visited.Add(currTable);
foreach (var col in currTable.Columns)
{
if (col.Parent != null)
{
tableQueue.Push(col.Parent.Table);
// add parent child relation columns to this
var parent = new DbJoinStructure
{
ChildJoinColumn = col.Name,
ParentJoinColumn = col.Parent.Name,
DisplayName = col.Parent.Table.DisplayName,
};
foreach (var column in col.Parent.Table.Columns)
{
parent.Columns.Add(column.Name);
parent.ColumnTypes.Add(column.DataType);
parent.ShowColumns.Add(column.IsPrimaryKey == false && column.Parent == null);
}
currJoin.Parents.Add(parent);
joinQueue.Push(parent);
}
}
}
}
return(result);
}