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);
 }
Beispiel #2
0
        /// <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;
        }
Beispiel #3
0
        public virtual string QualifySPName(IStoredProcedure sp)
        {
            const string qualifiedFormat = "[{0}].[{1}]";

            return(String.Format(qualifiedFormat, sp.SchemaName, sp.Name));
        }
Beispiel #4
0
        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);
        }
Beispiel #7
0
        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})");
            }
        }
Beispiel #8
0
 public IList <TRet> ExecuteSPResultList <TRet>(IStoredProcedure sp)
 {
     return(Session.ExecuteSp(sp).SetFetchSize(100).List <TRet>());
 }
Beispiel #9
0
        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);
        }
Beispiel #10
0
        /// <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;
        }
Beispiel #11
0
        /// <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;
        }
Beispiel #12
0
 public IEnumerable <TResult> ExecuteStoredProcedure <TResult>(IStoredProcedure <TResult> procedure, string storeName = null)
 {
     //return Context.Database.ExecuteStoredProcedure(procedure);
     return(Enumerable.Empty <TResult>());
 }
Beispiel #13
0
 public CandidateService(IUnitOfWork unitOfWork, IStoredProcedure storedProcedure)
 {
     this._unitOfWork      = unitOfWork;
     this._storedProcedure = storedProcedure;
 }
Beispiel #14
0
 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);
Beispiel #15
0
        public override string QualifySPName(IStoredProcedure sp)
        {
            string qualifiedFormat = String.IsNullOrEmpty(sp.SchemaName) ? "{1}" : "{0}.{1}";

            return(String.Format(qualifiedFormat, sp.SchemaName, sp.Name));
        }
Beispiel #16
0
 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[] { })));
 }
Beispiel #19
0
        /// <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();
            }
        }
Beispiel #20
0
 public TRet ExecuteSPUniqueResult <TRet>(IStoredProcedure sp)
 {
     return(Session.ExecuteSp(sp).UniqueResult <TRet>());
 }
Beispiel #21
0
        /// <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);
Beispiel #23
0
        /// <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(','));
        }
Beispiel #25
0
        /// <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);
        }
Beispiel #27
0
 public void TearDown()
 {
     _mockSqlParameterFactory = null;
     _storedProcedure         = null;
 }
Beispiel #28
0
        /// <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());
        }
Beispiel #29
0
        /// <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());
        }
Beispiel #30
0
        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;
 }
Beispiel #32
0
 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);
        }