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 ? "数据库表对象" : "数据库视图对象"); }