DOTNET POINT: Use of strote procedure in EDM in code first approch.

using Nop.Core;

using Nop.Core.Data;

using Nop.Data;

using Nop.Services.Events;

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Nop.Services.PriceGroup

{

public class PriceGroupService : IPriceGroupService

{

private readonly IRepository<Core.Domain.PriceGroup.PriceGroup> _priceGroupRepository;

private readonly IEventPublisher _eventPublisher;

private readonly IDataProvider _dataProvider;

private readonly IDbContext _dbContext;

private readonly IRepository<Core.Domain.PriceGroup.PriceGroupStatus> _priceGroupStatusRepository;

public PriceGroupService(IRepository<Core.Domain.PriceGroup.PriceGroup> priceGroupRepository, IEventPublisher eventPublisher, IDataProvider dataProvider, IDbContext dbContext, IRepository<Core.Domain.PriceGroup.PriceGroupStatus> priceGroupStatusRepository)

{

this._priceGroupRepository = priceGroupRepository;

this._eventPublisher = eventPublisher;

this._dataProvider = dataProvider;

this._dbContext = dbContext;

this._priceGroupStatusRepository = priceGroupStatusRepository;

}

public string InsertPriceGroup(Nop.Core.Domain.PriceGroup.PriceGroup entity)

{

string sqlResult = string.Empty;

try

{

StringBuilder sbSql = new StringBuilder();

sbSql.Append("EXEC");

sbSql.Append(" EmpProcedure");

sbSql.Append(" @PriceGroupId,");

sbSql.Append("@PriceGroupName,");

sbSql.Append("@Percentage,");

sbSql.Append("@CreatedOnUtc,");

sbSql.Append("@CreatedBy,");

sbSql.Append("@UpdatedOnUtc,");

sbSql.Append("@UpdatedBy,");

sbSql.Append("@IsDeleted,");

sbSql.Append("@StatusId,");

sbSql.Append("@ResultMessage OUT");

var pOutput = new SqlParameter();

pOutput.ParameterName = "@ResultMessage";

pOutput.Direction = ParameterDirection.Output;

pOutput.SqlDbType = SqlDbType.VarChar;

pOutput.SqlValue = string.Empty;

_dbContext.ExecuteSqlCommand(sbSql.ToString(), false, null,

new SqlParameter("@PriceGroupId", entity.Id),

new SqlParameter("@PriceGroupName", entity.PriceGroupName),

new SqlParameter("@Percentage", entity.Percentage),

new SqlParameter("@CreatedOnUtc", entity.CreatedOnUtc),

new SqlParameter("@CreatedBy", entity.CreatedBy),

new SqlParameter("@UpdatedOnUtc", entity.UpdatedOnUtc),

new SqlParameter("@UpdatedBy", entity.UpdatedBy),

new SqlParameter("@IsDeleted", entity.IsDeleted),

new SqlParameter("@StatusId", entity.StatusId), pOutput

);

sqlResult = Convert.ToString(pOutput.Value);

_eventPublisher.EntityInserted(entity);

}

catch (Exception ex)

{

return ex.ToString();

}

return sqlResult;

}

public IPagedList<Core.Domain.PriceGroup.PriceGroup> GetAllPriceGroup(int pageIndex, int pageSize = int.MaxValue, int groupId = 0,string searchText="")

{

var pPriceGroupId = _dataProvider.GetParameter();

pPriceGroupId.ParameterName = "PriceGroupId";

pPriceGroupId.Value = groupId;

pPriceGroupId.DbType = DbType.Int32;

var psearchText = _dataProvider.GetParameter();

psearchText.ParameterName = "SearchText";

psearchText.Value = searchText;

psearchText.DbType = DbType.String;

var priceGroupList = _dbContext.ExecuteStoredProcedureList<Nop.Core.Domain.PriceGroup.PriceGroup>(

"GetPriceGroupDetails",

pPriceGroupId,psearchText);

var priceGroupPagedList = new PagedList<Core.Domain.PriceGroup.PriceGroup>(priceGroupList.ToList(), pageIndex, pageSize);

return priceGroupPagedList;

}

public void MarkAsDeleted(int Id)

{

var data = _priceGroupRepository.Table.Where(pg => pg.Id == Id).SingleOrDefault();

data.IsDeleted = true;

_priceGroupRepository.Update(data);

_eventPublisher.EntityUpdated(data);

}

public List<Core.Domain.PriceGroup.PriceGroup> GetAllPriceGroup()

{