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);
}
