public void Setup() { Database.SetUp(); Database.CreateTables(); Database.ExecuteNonQuery("CREATE PROCEDURE ExistingProcedure AS BEGIN RETURN 0 END", Database.FirstTable.Name); Database.ExecuteNonQuery("CREATE PROCEDURE ReturnValue AS BEGIN RETURN 42 END", Database.FirstTable.Name); Database.ExecuteNonQuery("CREATE PROCEDURE GetAll AS BEGIN SELECT * FROM {0} END", Database.FirstTable.Name); Database.ExecuteNonQuery("CREATE PROCEDURE GetOne @Id int AS BEGIN SELECT TOP 1 * FROM {0} WHERE Id=@Id END", Database.FirstTable.Name); Database.ExecuteNonQuery("CREATE PROCEDURE GetCount AS BEGIN SELECT COUNT(*) FROM {0} END", Database.FirstTable.Name); Database.ExecuteNonQuery("CREATE PROCEDURE DeleteOne @Id int AS BEGIN DELETE FROM {0} WHERE Id=@Id END", Database.FirstTable.Name); Database.ExecuteNonQuery("CREATE PROCEDURE Echo @Value int AS BEGIN SELECT @Value END", Database.FirstTable.Name); StoredProcedure = Gribble.StoredProcedure.Create(Database.Connection, profiler: Profiler); }
/// <summary> /// Query Database with IStoredProcedure /// </summary> /// <param name="proc">Procedure</param> /// <returns>Data Set</returns> public virtual async Task<DataSet> Query(IStoredProcedure sproc) { if (null == sproc) { throw new ArgumentNullException("sproc"); } DataSet ds = null; using (var command = sproc.Build(this.connection)) { ds = await this.Query(command); } return ds; }
public virtual string QualifySPName(IStoredProcedure sp) { const string qualifiedFormat = "[{0}].[{1}]"; return(String.Format(qualifiedFormat, sp.SchemaName, sp.Name)); }
public static IEnumerable <TResult> ExecuteStoredProcedure <TResult>(this Database database, IProcTools procTool, string procCmd, IStoredProcedure procedure) { var parameters = procTool.CreateDbParametersFromProperties(procedure); var format = procTool.CreateProcCommand <TResult>(procCmd, parameters); return(database.SqlQuery <TResult>(format, parameters.ToArray())); }
public static IEnumerable <TResult> ExecuteStoredProcedure <TResult>(this Database database, IStoredProcedure <TResult> procedure, string storeName = null) { var parameters = CreateSqlParametersFromProperties(procedure); var format = CreateSpCommand <TResult>(parameters, storeName); return(database.SqlQuery <TResult>(format, parameters.Cast <object>().ToArray())); }
private static List <SqlParameter> CreateSqlParametersFromProperties <TResult>(IStoredProcedure <TResult> procedure) { var procedureType = procedure.GetType(); var propertiesOfProcedure = procedureType.GetProperties(BindingFlags.Public | BindingFlags.Instance); var parameters = new List <SqlParameter>(); // ReSharper disable once LoopCanBeConvertedToQuery foreach (var prop in propertiesOfProcedure) { var paramAttributes = prop.GetCustomAttribute <DbProcedureParameter>(); var paramName = paramAttributes != null ? paramAttributes.ParameterName : prop.Name; parameters.Add(new SqlParameter(string.Format("@{0}", paramName), prop.GetValue(procedure, new object[] { }))); } return(parameters); }
private static async Task PrintProceduresAsync(ILogging logger, string database, ISchemaProvider schemaProvider, IStoredProcedure procedure) { logger.Log($"TABLE {procedure.Name}:", LogType.Information); var parameters = await schemaProvider.GetParametersAsync(database, procedure.Name); foreach (var parameter in parameters) { logger.Log($" {(parameter.IsInput ? "IN" : "OUT")} PARAMETER {parameter.Name} ({parameter.DataType})"); } }
public IList <TRet> ExecuteSPResultList <TRet>(IStoredProcedure sp) { return(Session.ExecuteSp(sp).SetFetchSize(100).List <TRet>()); }
public List <IDbDataParameter> CreateParameters(IStoredProcedure procedureIn, IStoredProcedure procedureOut, bool hasCursor = true) { List <IDbDataParameter> reParameters = new List <IDbDataParameter>(); CreateParametersFromProperties(procedureIn, ParameterDirection.Input, reParameters); CreateParametersFromProperties(procedureOut, ParameterDirection.Output, reParameters); return(reParameters); }
/// <summary> /// Non-Query /// </summary> /// <param name="sproc">Procedure To Execute</param> /// <returns>rows affected</returns> public virtual async Task<int> NonQuery(IStoredProcedure sproc) { if (null == sproc) { throw new ArgumentNullException("sproc"); } var rowsAffected = 0; using (var command = sproc.Build(this.connection)) { rowsAffected = await this.NonQuery(command); } return rowsAffected; }
/// <summary> /// Query for Reader with IStoredProcecure /// </summary> /// <param name="sproc">Stored Procedure</param> /// <returns>Data Reader</returns> public virtual async Task<IDataReader> DataReader(IStoredProcedure sproc) { if (null == sproc) { throw new ArgumentNullException("sproc"); } IDataReader reader; using (var command = sproc.Build(this.connection)) { if (this.connection.State != ConnectionState.Open) { await this.connection.OpenAsync(); } reader = await command.ExecuteReaderAsync(); } return reader; }
public IEnumerable <TResult> ExecuteStoredProcedure <TResult>(IStoredProcedure <TResult> procedure, string storeName = null) { //return Context.Database.ExecuteStoredProcedure(procedure); return(Enumerable.Empty <TResult>()); }
public CandidateService(IUnitOfWork unitOfWork, IStoredProcedure storedProcedure) { this._unitOfWork = unitOfWork; this._storedProcedure = storedProcedure; }
public async Task <IEnumerable <TEntity> > QueryProcedure <TEntity>(IStoredProcedure procedure) where TEntity : class => await Connection.QueryAsync <TEntity>( sql : procedure.Name, param : procedure.Parameter, commandType : CommandType.StoredProcedure);
public override string QualifySPName(IStoredProcedure sp) { string qualifiedFormat = String.IsNullOrEmpty(sp.SchemaName) ? "{1}" : "{0}.{1}"; return(String.Format(qualifiedFormat, sp.SchemaName, sp.Name)); }
public static TEntity ExecuteSingle <TEntity>( this IStoredProcedure storedProcedure, string name, object parameters) where TEntity : class { return(storedProcedure.ExecuteSingle <TEntity>(name, parameters.AsDictionary())); }
public Task <IQueryable <TEntity> > ExecStoredProcedureWithReturn <TEntity>(IStoredProcedure <TEntity> sp) where TEntity : class { throw new NotImplementedException(); }
private static SqlParameter ConvertPropertyToSpParameter <TResult>(IStoredProcedure <TResult> procedure, PropertyInfo propertyInfo) { return(new SqlParameter(string.Format("@{0}", (object)propertyInfo.Name), propertyInfo.GetValue(procedure, new object[] { }))); }
/// <summary> /// 执行非查询的存储过程 /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="procedureIn">输入对象</param> /// <param name="procedureOut">输出对象</param> /// <param name="commandType">类型</param> public void ExcuteProceNo(DbContext db, string procName, IStoredProcedure procedureIn, IStoredProcedure procedureOut, CommandType commandType = CommandType.StoredProcedure) { if (string.IsNullOrEmpty(procName)) { return; } var cmd = db.Database.Connection.CreateCommand(); cmd.CommandText = procName; cmd.CommandType = commandType; var parameters = _CreateParametersFromProperties.CreateParameters(procedureIn, procedureOut, false); if (parameters != null) { foreach (IDbDataParameter parm in parameters) { cmd.Parameters.Add(parm); } } try { ((IObjectContextAdapter)db).ObjectContext.Connection.Open(); cmd.ExecuteNonQuery(); //获取返回参数值 if (procedureOut != null) { Set(procedureOut, parameters); } } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); //((IObjectContextAdapter)db).ObjectContext.Connection.Dispose(); } }
public TRet ExecuteSPUniqueResult <TRet>(IStoredProcedure sp) { return(Session.ExecuteSp(sp).UniqueResult <TRet>()); }
/// <summary> /// 调用sql语句返回结果集合 /// </summary> /// <typeparam name="TResult"></typeparam> /// <param name="sqlStr"></param> /// <returns></returns> public List <TResult> ExcuteSql <TResult>(DbContext db, string sqlStr) { IStoredProcedure procedureOut = null; return(ExcuteProce <TResult>(db, sqlStr, null, ref procedureOut, CommandType.Text)); }
public async Task <IEnumerable <TEntity> > QueryProcedure <TEntity>(IStoredProcedure procedure) where TEntity : class => await Query.QueryProcedure <TEntity>(procedure);
/// <summary> /// 执行存储过程,返回结果集。不带返回参数 /// </summary> /// <typeparam name="TResult"></typeparam> /// <param name="procName"></param> /// <param name="procedureIn"></param> /// <returns></returns> public List <TResult> ExcuteProce <TResult>(DbContext db, string procName, IStoredProcedure procedureIn) { IStoredProcedure procedureOut = null; return(ExcuteProce <TResult>(db, procName, procedureIn, ref procedureOut)); }
private static string CreateSpCommand <TResult>(List <SqlParameter> parameters, IStoredProcedure <TResult> procedure) { var procType = procedure.GetType(); var procAttributes = procType.GetCustomAttribute <DbProcedure>(); string name = procAttributes != null ? procAttributes.ProcName : procType.Name; string queryString = string.Format("sp_{0}", name); parameters.ForEach(x => queryString = string.Format("{0} {1},", queryString, x.ParameterName)); return(queryString.TrimEnd(',')); }
/// <summary> /// 执行存储过程,返回结果集,带返回参数 /// </summary> /// <typeparam name="TResult">对象类型</typeparam> /// <param name="commanText">过程名称</param> /// <param name="procedure">参数</param> /// <param name="commandType">设置类型</param> /// <returns></returns> public List <TResult> ExcuteProce <TResult>(DbContext db, string procName, IStoredProcedure procedureIn, ref IStoredProcedure procedureOut, CommandType commandType = CommandType.StoredProcedure) { if (string.IsNullOrEmpty(procName)) { return(null); } List <TResult> ResultInfo = null; var cmd = db.Database.Connection.CreateCommand(); cmd.CommandText = procName; cmd.CommandType = commandType; var parameters = _CreateParametersFromProperties.CreateParameters(procedureIn, procedureOut); if (parameters != null) { foreach (IDbDataParameter parm in parameters) { cmd.Parameters.Add(parm); } } try { ((IObjectContextAdapter)db).ObjectContext.Connection.Open(); var reader = cmd.ExecuteReader(); ResultInfo = ((IObjectContextAdapter)db).ObjectContext.Translate <TResult>(reader).ToList(); reader.Dispose(); //获取返回参数值 if (procedureOut != null) { Set(procedureOut, parameters); } } catch (Exception ex) { //TODO: throw ex; } finally { cmd.Dispose(); //((IObjectContextAdapter)db).ObjectContext.Connection.Dispose(); } return(ResultInfo); }
private static List <SqlParameter> CreateSqlParametersFromProperties <TResult>(IStoredProcedure <TResult> procedure) { var procedureType = procedure.GetType(); var propertiesOfProcedure = procedureType.GetProperties(BindingFlags.Public | BindingFlags.Instance); var parameters = propertiesOfProcedure.Select(propertyInfo => new SqlParameter(string.Format("@{0}", propertyInfo.Name), propertyInfo.GetValue(procedure, new object[] { }))) .ToList(); return(parameters); }
public void TearDown() { _mockSqlParameterFactory = null; _storedProcedure = null; }
/// <summary> /// Creates a list of sql parameters based on the public properties of an object /// </summary> /// <param name="procedure">The procedure object that contains the public properties to build the sql parameters from</param> /// <returns></returns> /// <remarks>All public properties except the 'ProcedureName' property will be used to build up the sql parameter list</remarks> private static List <SqlParameter> CreateSqlParametersFromProperties <TResult>(IStoredProcedure <TResult> procedure) { var procedureType = procedure.GetType(); var propertiesOfProcedure = procedureType.GetProperties(BindingFlags.Public | BindingFlags.Instance); var parameters = propertiesOfProcedure.Select(propertyInfo => new SqlParameter(string.Format("@{0}", propertyInfo.Name), propertyInfo.GetValue(procedure, new object[] {}) ?? DBNull.Value)) .ToList(); return (parameters.Where( p => !p.ParameterName.Equals("@ProcedureName", StringComparison.InvariantCultureIgnoreCase)). ToList()); }
/// <summary> /// Execute the stored procedure represented by the provided parameter and return an instance of the expected type /// </summary> /// <param cref="Socratic.DataAccess.Abstractions.IStoredProcedure{T}" name="storedProc"></param> /// <typeparam name="T"></typeparam> /// <returns>T</returns> public async Task <T> QuerySingleAsync <T>(IStoredProcedure <T> storedProc) { var results = await QueryListAsync(storedProc); return(results.SingleOrDefault()); }
public async Task Query() { IQuery <string> query = _database.Products.Query().Join(e => e.Owner).Where(e => e.IsAvailable).OrderBy(e => e.Name).Offset(10).Limit(20).Select(e => e.Name); IDelete batchDelete = _database.Products.Delete().Where(e => e.IsAvailable); IInsert batchInsert = _database.Products.Insert(new Product(), new Product()); IUpdate batchUpdate = _database.Products.Update().Where(e => e.IsAvailable).Set(e => new Product { IsAvailable = false }); IUpdate singleUpdate = _database.Products.Update().Set(e => e.NetPrice, e => e.NetPrice * 1.1m).Set(e => e.DateAdded, DateTime.UtcNow); var(queryResult, deleteAffected, updateAffected, insertAffected) = _database.Execute(query, batchDelete, batchUpdate, batchInsert); (queryResult, deleteAffected, updateAffected, insertAffected) = await _database.ExecuteAsync(query, batchDelete, batchUpdate, batchInsert); string querySql = query.ToSqlString(); string deleteSql = batchDelete.ToSqlString(); string updateSql = batchUpdate.ToSqlString(); string insertSql = batchInsert.ToSqlString(); var product1 = await _database.Products.Query().Where(e => e.Id == 1).Single().ExecuteAsync(); var product2 = await _database.Products.Query().Where(e => e.Id == 2).Single().ExecuteAsync(); var updatedExplicitly = await _database.Products.Update(product1, product2).ExecuteAsync(); var product3 = await _database.Products.Query().Where(e => e.Id == 3).Single().ExecuteAsync(); var product4 = await _database.Products.Query().Where(e => e.Id == 4).Single().ExecuteAsync(); var deletedExplicitly = await _database.Products.Delete(product3, product4).ExecuteAsync(); var copyInsert = _database.ArchivedProducts.InsertFrom(_database.Products.Query().Where(p => !p.IsAvailable && p.DateAdded < DateTime.UtcNow.AddYears(-1)).Select(e => new ArchivedProduct { /* ... */ })); var copiedRows = await copyInsert.ExecuteAsync(); queryResult = _database.ExecuteSql <List <string> >(querySql, new { x = 1 }); queryResult = await _database.ExecuteSqlAsync <List <string> >(querySql, new { x = 1 }); IStoredProcedure <List <ProductDto> > searchedProductsProcedure = _database.SearchProducts("name"); var searchedProducts = searchedProductsProcedure.Execute(); searchedProducts = await searchedProductsProcedure.ExecuteAsync(); searchedProducts = _database.Execute(searchedProductsProcedure); searchedProducts = await _database.ExecuteAsync(searchedProductsProcedure); IFunction <decimal> priceFunction = _database.CalculatePrice(123); var price = priceFunction.Execute(); price = await priceFunction.ExecuteAsync(); price = _database.Execute(priceFunction); price = await _database.ExecuteAsync(priceFunction); _database.Insert(new Product()).Execute(); var product5 = _database.Query <Product>().Where(e => e.Id == 5).Execute(); _database.Update(product5).Execute(); _database.Delete(product5).Execute(); var q1 = _database.Products.Query().Where(e => e.OwnerId == 5); var q2 = _database.Products.Query().Where(e => e.OwnerId == 6); var q3 = _database.Products.Query().Where(e => e.OwnerId == 7); List <Product> unionList123 = q1.Union(q2).UnionAll(q3).Execute(); List <Product> unionList = _database.Products .Query().Where(e => e.OwnerId == 5) .UnionAll() .Query().Where(e => e.OwnerId == 6) .Execute(); var product7 = _database.Query <Product>(options => options.AsEditable().WithComment("my query tag").WithHint("NOLOCK")) .Where(e => e.Id == 7).Execute(); // WITH CTE explicit var cte1 = _database.Query <Product>(); var cte2 = _database.Query(cte1).Where(e => e.IsAvailable); var withQuery = _database.Query(cte2).Where(e => e.NetPrice > 0); // Recursive CTE var managerQuery = _database.Query <Employee>().Where(e => e.ManagerId == null) .Select(e => new EmployeeDto { Id = e.Id, Name = e.Name, ManagerId = e.ManagerId, Level = 1 }); var employeeQuery = _database.Query <Employee>().Join(managerQuery, (l, r) => l.ManagerId == r.Id) .Where((e, mq) => e.Id == 234) .Select((e, mq) => new EmployeeDto { Id = e.Id, Name = e.Name, ManagerId = e.ManagerId, Level = mq.Level + 1 }); var hierarchyUnion = managerQuery.UnionAll(employeeQuery); var employeeHierarchy = _database.Query(hierarchyUnion) .OrderBy(e => e.Level) .ThenOrderByDescending(e => e.ManagerId) .Select(e => new EmployeeHierarchy { Employee = e.Name, Level = e.Level, Manager = _database.Query <Employee>().Where(m => m.Id == e.ManagerId).Select(m => m.Name).First().Execute() }) .Distinct() .Execute(); // Subquery _database.Products.Query().Select(e => new ProductDto()).OrderBy(e => e.Price).Execute(); // Multiquery _database.Query <Product, Owner, Employee>(); _database.Query(_database.Products, _database.AvailableProducts, hierarchyUnion) .Join(managerQuery, (p, _, _, m) => p.Id == m.Id); // Group by var groupedProducts = _database.Query <Product>() .GroupBy(p => new { p.Id, p.Name }) .Having(p => p.Key.Id > 4 && p.Count() > 5 && p.Max(e => e.NetPrice) > 50) .Select(p => new { p.Key.Id, p.Key.Name, Count = p.Count(), Max = p.Max(e => e.NetPrice), CountAvailable = p.Where(e => e.IsAvailable).Count(), MaxAvailable = p.Where(e => e.IsAvailable).Max(e => e.NetPrice), AllAvailable = p.Where(e => e.IsAvailable).Select(e => new { e.Name, e.NetPrice }).AsList() }) .Execute(); // Mapping Product[] productArray = _database.Query <Product>().AsArray().Execute(); Dictionary <int, Product> productByOwnerDict = _database.Query <Product>().AsDictionary(e => e.OwnerId).Execute(); }
public ExaminerService(IUnitOfWork unitOfWork, IStoredProcedure storedProcedure) { this._unitOfWork = unitOfWork; this._storedProcedure = storedProcedure; }
public static T ExecuteScalar <T>(this IStoredProcedure storedProcedure, string name, object parameters) { return(storedProcedure.ExecuteScalar <T>(name, parameters.AsDictionary())); }
public static IEnumerable <TResult> ExecuteStoredProcedure <TResult>(this Database database, IStoredProcedure <TResult> procedure) { var parameters = CreateSqlParametersFromProperties(procedure); var sqlCommand = CreateSPCommand <TResult>("MCS." + procedure.GetType().Name, parameters); return(database.SqlQuery <TResult>(sqlCommand, parameters.Cast <object>().ToArray())); }
public override string QualifySPName(IStoredProcedure sp) { string qualifiedFormat = String.IsNullOrEmpty(sp.SchemaName) ? "{1}" : "{0}.{1}"; return String.Format(qualifiedFormat, sp.SchemaName, sp.Name); }
private static List <SqlParameter> CreateSqlParametersFromProperties <TResult>(IStoredProcedure <TResult> procedure) { var procedureType = procedure.GetType(); var propertiesOfProcedure = procedureType.GetProperties(BindingFlags.Public | BindingFlags.Instance); var parameters = propertiesOfProcedure.Select(propertyInfo => ConvertPropertyToSpParameter(procedure, propertyInfo)).ToList(); return(parameters); }