دریافت اطلاعات از پایگاه داده بواسطه Stored Procedure در EF Core 2.0
نویسنده: میثم قیصریان
تاریخ: ۱۳۹۶/۰۹/۰۴ ۱۹:۴۵
آدرس: www.dntips.ir
| مطالب | ۳۶۹۴ |
| نویسندگان | ۲۷۶ |
| گروههای مطالب | ۱۰۲۴ |
| نقشههای راه | ۱۱۹ |
| دورهها | ۱۴ |
| اشتراکها | ۱۷۹۱۴ |
public void OpenConnection()
{
Database.OpenConnection();
}
public DbCommand Command()
{
DbCommand cmd = Database.GetDbConnection().CreateCommand();
return cmd;
} void OpenConnection(); DbCommand Command();
public void ConfigureServices(IServiceCollection services)
{
services.AddScoped<IUnitOfWork, ApplicationDbContext>();
services.AddScoped<ISpReader, SpReader>();
} public List<ViewModel> GetFromSp <ViewModel>(string[,] Parametr, string NameSp) where ViewModel : new() {
_uow.OpenConnection();
DbCommand cmd = _uow.Command();
cmd.CommandText = NameSp;
cmd.CommandType = CommandType.StoredProcedure;
var countParametr = Parametr.GetLength(0);
for (int i = 0; i < countParame tr; i++)
{
cmd.Parameters.Add(new SqlParameter { ParameterName = Parametr[i, 0], Value = Parametr[i, 1] });
}
List<ViewModel> list = new List<ViewModel >();
using (var reader = cmd.ExecuteReader())
{
if (reader != null && reader.HasRows)
{
var entity = typeof(ViewModel);
var propDict = new Dictionary<string, PropertyInfo>();
var props = entity.GetProperties
(BindingFlags.Instance | BindingFlags.Public);
propDict = props.ToDictionary(p => p.Name.ToUpper(), p => p);
while (reader.Read())
{
ViewModel newobject = new ViewModel ();
for (int index = 0; index < reader.FieldCount; index++)
{
if (propDict.ContainsKey(reader.GetName(index).ToUpper()))
{
var info = propDict[reader.GetName(index).ToUpper()];
if ((info != null) && info.CanWrite)
{
var val = reader.GetValue(index);
info.SetValue(newobject, (val == DBNull.Value) ? null : val, null);
}
}
}
list.Add(newobject);
}
}
return list;
} var countParametr = Parametr.GetLength(0);
for (int i = 0; i < countParametr; i++)
{
cmd.Parameters.Add(new SqlParameter { ParameterName = Parametr[i, 0], Value = Parametr[i, 1] });
} CREATE PROCEDURE [dbo].[isRelation]( @TableName as varchar(50), @FieldOfRelation as varchar(70), @ValueOfField as int)
public class EntityServices : IEntityService
{
private ISpreader _Reader;
public EntityServices( ISpreader reader)
{
_Reader = reader;
}
public List<StoreProcedureResultViewModel> IsRelation(string tableName , int keyValue, string keyFieldName)
{
List<StoreProcedureResultViewModel> IsContact;
try
{
string[,] Parametr = new string[3, 2];
Parametr[0, 0] = "@TableName";
Parametr[0, 1] = tableName ;
Parametr[1, 0] = "@ValueOfField";
Parametr[1, 1] = keyValue.ToString().Trim();
Parametr[2, 0] = "@FieldOfRelation";
Parametr[2, 1] = keyFieldName.Trim();
IsContact = _Reader.GetSp<StoreProcedureResultViewModel>(Parametr, "IsRelation");
return IsContact;
}
catch (Exception ex)
{
}
}
} var sprocResults = await _dbContext.Query<MyDTO>()
.FromSql("Get_MySp @Param1 = {0}, @Param2 = {1}", p1, p2)
.AsNoTracking()
.ToListAsync(); var sprocResults = await _dbContext.Set<MyDTO>()
.FromSqlRaw("Get_MySp @Param1 = {0}, @Param2 = {1}", p1, p2)
.AsNoTracking()
.ToListAsync(); 'Incorrect syntax near '@p0'.'
SELECT top 1 * FROM (EXEC MyProc) AS TEMP
context.Products.FromSqlRaw("[dbo].[Ten Most Expensive Products]").AsEnumerable().FirstOrDefault();