public string GenerateCode(IDbEngine engine, SettingInfo setting)
{
var columnInfos = engine.Execute(@"
SELECT
a.Name,
t.name as DbType,
a.max_length as MaxLength,
IsPrimaryKey=case when exists(SELECT 1 FROM sys.objects where type='PK' and name in (
SELECT name FROM sys.indexes WHERE index_id in(
SELECT indid FROM sys.sysindexkeys WHERE id = a.object_id AND colid=a.column_id
))) then 1 else 0 end,
IsNullable=a.is_nullable,
Comments=isnull(g.[value],'')
FROM sys.columns a
inner join sys.objects d on a.object_id=d.object_id and d.name<>'dtproperties'
inner join sys.types t on a.user_type_id=t.user_type_id
left join sys.extended_properties g on a.object_id=g.major_id and a.column_id=g.minor_id
WHERE d.Name=@tableName
ORDER BY a.column_id", "@tableName", this.Name)
.ToEntities <ColumnInfo>();
//var table = engine.Execute("SELECT * FROM " + this.Name + " WHERE 1=2").ToTable();
//var columns = table.Columns;
//foreach(var item in columnInfos)
//{
// item.SetType(columns[item.Name].DataType);
//}
return(GenerateCode(columnInfos, setting, this.Type == ObjectType.Table ? "数据库表对象" : "数据库视图对象"));
}
public string GenerateCode(IDbEngine engine, SettingInfo setting, string sql)
{
List <ColumnInfo> columns = new List <ColumnInfo>();
engine.Execute(sql).ToReader(reader =>
{
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
var column = new ColumnInfo()
{
Name = reader.GetName(i),
};
column.SetType(reader.GetFieldType(i));
columns.Add(column);
}
});
var code = this.GenerateCode(columns, setting, "自定义语句");
code = "/*********** SQL **********\r\n\r\n"
+ sql
+ "\r\n\r\n********** SQL ***********/\r\n\r\n"
+ code;
return(code);
}
public string GenerateCode(IDbEngine engine, SettingInfo setting, string sql)
{
List<ColumnInfo> columns = new List<ColumnInfo>();
engine.Execute(sql).ToReader(reader =>
{
reader.Read();
for(int i = 0; i < reader.FieldCount; i++)
{
var column = new ColumnInfo()
{
Name = reader.GetName(i),
};
column.SetType(reader.GetFieldType(i));
columns.Add(column);
}
});
var code = this.GenerateCode(columns, setting, "自定义语句");
code = "/*********** SQL **********\r\n\r\n"
+ sql
+ "\r\n\r\n********** SQL ***********/\r\n\r\n"
+ code;
return code;
}
/// <summary>
/// 获取指定 Id 键值的数据源对象。
/// </summary>
/// <typeparam name="TEntity">实体的数据类型。</typeparam>
/// <typeparam name="TView">视图的数据类型。</typeparam>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="keyName">主键的列名。</param>
/// <param name="keyValue">主键的列值。</param>
public static DbResult <TView> FindOne <TEntity, TView>(this IDbEngine engine, string keyName, object keyValue)
{
if (engine == null)
{
throw new ArgumentNullException("engine");
}
if (keyValue == null)
{
throw new ArgumentNullException("keyValue");
}
var parSetting = engine.Owner.Injector.ParameterSettings;
var m1 = TypeMapper.Instance <TEntity> .Mapper;
if (string.IsNullOrEmpty(keyName))
{
var prop = m1.Properties.FirstOrDefault(p => p.IsKey);
if (prop == null)
{
keyName = DefaultKeyName;
}
else
{
keyName = prop.Name;
}
}
var fields = CreateFields <TEntity, TView>(engine);
return(engine.Execute("SELECT " + fields + " FROM " + parSetting.EscapeName(m1.Name)
+ " WHERE " + keyName + "=" + parSetting.PrefixWithText + "pk"
, parSetting.PrefixWithCollection + "pk", keyValue).ToEntity <TView>());
}
private void InsertCountTest(IDbEngine engine)
{
Assert.Equal(10000
, engine
.Execute("SELECT COUNT(*) FROM TestTable")
.ToScalar <int>()
);
}
private void InsertPerformanceTest(IDbEngine engine)
{
for (int i = 0; i < 10000; i++)
{
engine.Execute("INSERT INTO TestTable(UserName) VALUES (@username)", "@username", "user" + i)
.ToNonQuery();
}
}
/// <summary>
/// 获取最后递增序列值。
/// </summary>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <returns>返回一个结果。</returns>
public static Result <long> GetLastIdentity <TEntity>(this IDbEngine engine)
{
if (engine == null)
{
throw new ArgumentNullException("engine");
}
var command = engine.Owner.Injector.CreateLastIdentityCommand(engine, TypeMapper.Instance <TEntity> .Mapper);
return(engine.Execute(command).ToScalar <long>());
}
/// <summary>
/// 获取最后递增序列值。
/// </summary>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="tunnel">用于个性化表名和命令的暗道，可以为 null 值。</param>
/// <returns>递增序列值。</returns>
public static long GetLastIdentity <TEntity>(this IDbEngine engine, ICommandTunnel tunnel = null)
{
if (engine == null)
{
throw new ArgumentNullException(nameof(engine));
}
var command = engine.Provider.SqlFactory.CreateLastIdentityCommand(TypeMapper.Instance <TEntity> .Mapper, tunnel);
return(engine.Execute(command).ToScalar <long>());
}
public Book Find(string id)
{
if (_cashForUpdate.ContainsKey(id))
{
return(_cashForUpdate[id]);
}
var resString = _dbEngine.Execute($"get Id={id};");
if (resString == ";")
{
return(null);
}
var resBook = StringToBookParser(resString);
_cashForUpdateDB[id] = resBook.Copy();
_cashForUpdate[id] = resBook;
return(_cashForUpdate[id]);
}
/// <summary>
/// execute command
/// </summary>
/// <param name="cmds">commands</param>
/// <returns>date numbers </returns>
public int Execute(params ICommand[] cmds)
{
if (cmds.IsNullOrEmpty())
{
return(0);
}
Dictionary <string, List <ICommand> > commandGroup = new Dictionary <string, List <ICommand> >();
Dictionary <string, ServerInfo> serverInfos = new Dictionary <string, ServerInfo>();
#region get database servers
foreach (var cmd in cmds)
{
var servers = GetServer(cmd);
foreach (var server in servers)
{
string serverKey = server.Key;
if (serverInfos.ContainsKey(serverKey))
{
commandGroup[serverKey].Add(cmd);
}
else
{
commandGroup.Add(serverKey, new List <ICommand>()
{
cmd
});
serverInfos.Add(serverKey, server);
}
}
}
#endregion
#region verify database server engine
IEnumerable <ServerType> serverTypeList = serverInfos.Values.Select(c => c.ServerType).Distinct();
VerifyServerEngine(serverTypeList.ToArray());
#endregion
#region execute commands
int totalVal = 0;
foreach (var cmdGroup in commandGroup)
{
ServerInfo serverInfo = serverInfos[cmdGroup.Key];
IDbEngine engine = DBConfig.DbEngines[serverInfo.ServerType];
totalVal += engine.Execute(serverInfo, cmdGroup.Value.ToArray());
}
return(totalVal);
#endregion
}
internal static DbResult <int> ExecuteAED <TEntity>(this IDbEngine engine, Func <TypeMapper, Result <ExecuteCommand> > callback)
{
var commandResult = callback(TypeMapper.Instance <TEntity> .Mapper);
if (commandResult.IsSucceed)
{
return(engine.Execute(commandResult.Value).ToNonQuery());
}
return(new DbResult <int>().ToFailded(commandResult.Exception));
}
/// <summary>
/// 提供匹配条件，执行一个更新的命令，可以是匿名对象的部分成员（<paramref name="entity"/> 属性成员和 <typeparamref name="TEntity"/> 属性成员必须一致）。
/// </summary>
/// <typeparam name="TEntity">实体的数据类型。</typeparam>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="entity">实体的实例对象，可以是匿名对象的部分成员（<paramref name="entity"/> 属性成员和 <typeparamref name="TEntity"/> 属性成员必须一致）。</param>
/// <param name="where">条件表达式。</param>
/// <param name="ps">参数集合实例。</param>
public static DbResult <int> ModifyWhere <TEntity>(this IDbEngine engine, object entity, string where, ExecuteParameterCollection ps = null)
{
if (engine == null)
{
throw new ArgumentNullException("engine");
}
if (entity == null)
{
throw new ArgumentNullException("entity");
}
var mapper1 = TypeMapper.Instance <TEntity> .Mapper;
var mapper2 = TypeMapper.Create(entity.GetType());
var parSettings = engine.Owner.Injector.ParameterSettings;
var setBuilder = new StringBuilder("UPDATE ")
.Append(parSettings.EscapeName(mapper1.Name))
.Append(" SET ");
int index = 0;
if (ps == null)
{
ps = new ExecuteParameterCollection();
}
foreach (var property2 in mapper2.Properties)
{
if (property2.IsIgnore || property2.IsKey)
{
continue;
}
if (!mapper1.Contains(property2.Name))
{
throw new NotSupportedException("表 {0} 没有属性 {1}。".Fmt(mapper1.Name, property2.Name));
}
if (index > 0)
{
setBuilder.Append(',');
}
setBuilder.Append(parSettings.EscapeName(property2.Name))
.Append('=')
.Append(parSettings.PrefixWithText)
.Append("_value_")
.Append(index);
ps.Add(parSettings.PrefixWithCollection + "_value_" + index, property2.GetValue(entity));
index++;
}
if (where != null)
{
setBuilder.Append(" WHERE ").Append(where);
}
return(engine.Execute(setBuilder.ToString(), ps).ToNonQuery());
}
public Book Find(string id)
{
if (updBuffer.ContainsKey(id))
{
return(updBuffer[id] as Book);
}
var getRequest = GetGetRequest(id);
var response = dbEngine.Execute(getRequest);
if (response == ";")
{
return(null);
}
var dbEntity = CreateEntityByFields <Book>(response);
var sameDbEntity = GetSameEntityWithOtherLink(dbEntity);
updBuffer[id] = dbEntity;
immutableBuffer[id] = sameDbEntity;
return(dbEntity);
}
/// <summary>
/// 执行一个插入的命令，可以是匿名对象的部分成员（<paramref name="entity"/> 属性成员和 <typeparamref name="TEntity"/> 属性成员必须一致）。
/// </summary>
/// <typeparam name="TEntity">实体的数据类型。</typeparam>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="entity">实体的实例对象，可以是匿名对象的部分成员（<paramref name="entity"/> 属性成员和 <typeparamref name="TEntity"/> 属性成员必须一致）。</param>
/// <param name="tunnel">用于个性化表名和命令的暗道，可以为 null 值。</param>
/// <returns>受影响的行。</returns>
public static int AddAnonymous <TEntity>(this IDbEngine engine, object entity, ICommandTunnel tunnel = null)
{
if (engine == null)
{
throw new ArgumentNullException(nameof(engine));
}
if (entity == null)
{
throw new ArgumentNullException(nameof(entity));
}
var command = engine.Provider.SqlFactory.CreateInsertCommand(TypeMapper.Instance <TEntity> .Mapper, entity, tunnel);
return(engine.Execute(command).ToNonQuery());
}
public T Find <T>(string id) where T : DbEntity
{
Console.WriteLine("FIND ENTRY:" + id);
if (_updCache.ItemContains(id))
{
return(_updCache.GetItem <T>(id));
}
else
{
string answer = dbEngine.Execute($"get Id={id};");
if (answer == ";")
{
return(null);
}
else
{
answer = DeleteScreening(answer);
T obj = Serializator.Deserialize <T>(answer);
_updCache.CreateItem(obj);
Console.WriteLine(answer);
return(obj);
}
}
}
/// <summary>
/// 获取数据表的总行数。
/// </summary>
/// <typeparam name="TEntity">实体的数据类型。</typeparam>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="where">筛选条件，不含 WHERE 关键字。</param>
/// <param name="ps">参数集合。</param>
/// <returns>返回一个查询结果。</returns>
public static DbResult <long> RowCount <TEntity>(this IDbEngine engine, string where = null, ExecuteParameterCollection ps = null)
{
if (engine == null)
{
throw new ArgumentNullException("engine");
}
var commandText = "SELECT COUNT(*) FROM " + engine.Owner.Injector.ParameterSettings.EscapeName(TypeMapper.Instance <TEntity> .Mapper.Name);
if (!string.IsNullOrEmpty(where))
{
commandText += " WHERE " + where;
}
return(engine.Execute(commandText, ps).ToScalar <long>());
}
/// <summary>
/// 提供匹配条件，执行一个删除的命令。
/// </summary>
/// <typeparam name="TEntity">实体的数据类型。</typeparam>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="where">条件表达式。</param>
/// <param name="ps">参数集合实例。</param>
public static DbResult <int> RemoveWhere <TEntity>(this IDbEngine engine, string where, ExecuteParameterCollection ps = null)
{
if (engine == null)
{
throw new ArgumentNullException("engine");
}
var mapper1 = TypeMapper.Instance <TEntity> .Mapper;
var parSettings = engine.Owner.Injector.ParameterSettings;
var setBuilder = new StringBuilder("DELETE ")
.Append(parSettings.EscapeName(mapper1.Name));
if (where != null)
{
setBuilder.Append(" WHERE ").Append(where);
}
return(engine.Execute(setBuilder.ToString(), ps).ToNonQuery());
}
/// <summary>
/// 提供匹配条件，获取对象的列表。
/// </summary>
/// <typeparam name="TEntity">实体的数据类型。</typeparam>
/// <typeparam name="TView">视图的数据类型。</typeparam>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="where">条件表达式。</param>
/// <param name="ps">参数集合实例。</param>
public static DbResult <List <TView> > FindAllWhere <TEntity, TView>(this IDbEngine engine, string where, ExecuteParameterCollection ps = null)
{
if (engine == null)
{
throw new ArgumentNullException("engine");
}
var parSetting = engine.Owner.Injector.ParameterSettings;
var m1 = TypeMapper.Instance <TEntity> .Mapper;
var fields = CreateFields <TEntity, TView>(engine);
var commandText = "SELECT " + fields + " FROM " + parSetting.EscapeName(TypeMapper.Instance <TEntity> .Mapper.Name);
if (!string.IsNullOrEmpty(where))
{
commandText += " WHERE " + where;
}
return(engine.Execute(commandText, ps).ToEntities <TView>());
}
/// <summary>
/// 判断指定的条件的数据是否已存在。
/// </summary>
/// <typeparam name="TEntity">实体的数据类型。</typeparam>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="where">条件表达式。</param>
/// <param name="ps">参数集合实例。</param>
public static Result <bool> ExistsWhere <TEntity>(this IDbEngine engine, string where, ExecuteParameterCollection ps = null)
{
if (engine == null)
{
throw new ArgumentNullException("engine");
}
var commandText = "SELECT 1 FROM "
+ engine.Owner.Injector.ParameterSettings.EscapeName(TypeMapper.Instance <TEntity> .Mapper.Name);
if (!string.IsNullOrEmpty(where))
{
commandText += " WHERE " + where;
}
var r = engine.Execute(commandText, ps).ToScalar();
if (r.IsFailed)
{
return(r.Exception);
}
return(r.Value != null);
}
/// <summary>
/// 生成执行数据源查询与交互的执行器。
/// </summary>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="commandText">Transact-SQL 语句。第一个字符为“>”时，表示一个存储过程。</param>
/// <param name="objectInstance">任意类型的实例。</param>
/// <returns>执行数据源查询与交互的执行器。</returns>
public static IDbExecutor Execute(this IDbEngine engine, string commandText, object objectInstance)
{
return(engine.Execute(new ExecuteCommand(commandText, new ExecuteParameterCollection(objectInstance))));
}
/// <summary>
/// 生成执行数据源查询与交互的执行器。
/// </summary>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="commandText">Transact-SQL 语句。第一个字符为“>”时，表示一个存储过程。</param>
/// <param name="parameters">参数集合。</param>
/// <returns>执行数据源查询与交互的执行器。</returns>
public static IDbExecutor Execute(this IDbEngine engine, string commandText, params ExecuteParameter[] parameters)
{
return(engine.Execute(new ExecuteCommand(commandText, parameters)));
}
/// <summary>
/// 生成执行数据源查询与交互的执行器。
/// </summary>
/// <param name="engine">数据源查询与交互引擎的实例。</param>
/// <param name="commandText">Transact-SQL 语句。第一个字符为“>”时，表示一个存储过程。</param>
/// <returns>执行数据源查询与交互的执行器。</returns>
public static IDbExecutor Execute(this IDbEngine engine, string commandText)
{
return(engine.Execute(new ExecuteCommand(commandText)));
}
public void UnknownCommand_SyntaxError(string query, string expected)
{
Assert.AreEqual(expected, sut.Execute(query));
}
private void InsertCountTest(IDbEngine engine)
{
Assert.Equal(10000
, engine
.Execute("SELECT COUNT(*) FROM TestTable")
.ToScalar<int>()
);
}
private void InsertPerformanceTest(IDbEngine engine)
{
for(int i = 0; i < 10000; i++)
{
engine.Execute("INSERT INTO TestTable(UserName) VALUES (@username)", "@username", "user" + i)
.ToNonQuery();
}
}
public string GenerateCode(IDbEngine engine, SettingInfo setting)
{
var columnInfos = engine.Execute(@"
SELECT
a.Name,
t.name as DbType,
a.max_length as MaxLength,
IsPrimaryKey=case when exists(SELECT 1 FROM sys.objects where type='PK' and name in (
SELECT name FROM sys.indexes WHERE index_id in(
SELECT indid FROM sys.sysindexkeys WHERE id = a.object_id AND colid=a.column_id
))) then 1 else 0 end,
IsNullable=a.is_nullable,
Comments=isnull(g.[value],'')
FROM sys.columns a
inner join sys.objects d on a.object_id=d.object_id and d.name<>'dtproperties'
inner join sys.types t on a.user_type_id=t.user_type_id
left join sys.extended_properties g on a.object_id=g.major_id and a.column_id=g.minor_id
WHERE d.Name=@tableName
ORDER BY a.column_id", "@tableName", this.Name)
.ToEntities<ColumnInfo>();
//var table = engine.Execute("SELECT * FROM " + this.Name + " WHERE 1=2").ToTable();
//var columns = table.Columns;
//foreach(var item in columnInfos)
//{
// item.SetType(columns[item.Name].DataType);
//}
return GenerateCode(columnInfos, setting, this.Type == ObjectType.Table ? "数据库表对象" : "数据库视图对象");
}
