Pass user defined table value in store procedure using Entity framework.
public int RequestTypeInsert<T>(int companyId, IEnumerable<T> data)
{
using (CustomerServicesManagementModelContainer context = new CustomerServicesManagementModelContainer())
{
//// convert source data to DataTable
DataTable table = data.ToDataTable();
List<SqlParameter> _sqlParameters = new List<SqlParameter>() {
new SqlParameter { ParameterName= "@CompanyID",Value=companyId,SqlDbType = SqlDbType.Int },
new SqlParameter { ParameterName= "@tvpRequestType",Value=table,SqlDbType = SqlDbType.Structured,TypeName="SERTICRequestTypeInsert" }};
SqlParameter _sqlParam = new SqlParameter { ParameterName = "@RequestTypeID", Value = 0, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
_sqlParameters.Add(_sqlParam);
context.ExecuteSqlProcedure(_sqlParameters.ToArray(), "SERTICRequestTypeInsert");
return Convert.ToInt32(_sqlParam.Value);
}
}
public static int ExecuteSqlProcedure(this DbContext context, SqlParameter[] parameters, string procedureName)
{
var inputparamNames = parameters.Where(x => x.Direction == ParameterDirection.Input).Select(x => x.ParameterName).ToArray();
var outputparamNames = parameters.Where(x => x.Direction == ParameterDirection.Output).Select(x => x.ParameterName + " output").ToArray();
if (outputparamNames.Count() > 0)
inputparamNames = inputparamNames.Union(outputparamNames).ToArray();
string strParams = string.Join(",", inputparamNames);
//// execute sp sql
string sql = String.Format("EXEC {0} {1};", procedureName, strParams);
//// execute sql
return context.Database.ExecuteSqlCommand(sql, parameters);
}
{
using (CustomerServicesManagementModelContainer context = new CustomerServicesManagementModelContainer())
{
//// convert source data to DataTable
DataTable table = data.ToDataTable();
List<SqlParameter> _sqlParameters = new List<SqlParameter>() {
new SqlParameter { ParameterName= "@CompanyID",Value=companyId,SqlDbType = SqlDbType.Int },
new SqlParameter { ParameterName= "@tvpRequestType",Value=table,SqlDbType = SqlDbType.Structured,TypeName="SERTICRequestTypeInsert" }};
SqlParameter _sqlParam = new SqlParameter { ParameterName = "@RequestTypeID", Value = 0, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
_sqlParameters.Add(_sqlParam);
context.ExecuteSqlProcedure(_sqlParameters.ToArray(), "SERTICRequestTypeInsert");
return Convert.ToInt32(_sqlParam.Value);
}
}
public static int ExecuteSqlProcedure(this DbContext context, SqlParameter[] parameters, string procedureName)
{
var inputparamNames = parameters.Where(x => x.Direction == ParameterDirection.Input).Select(x => x.ParameterName).ToArray();
var outputparamNames = parameters.Where(x => x.Direction == ParameterDirection.Output).Select(x => x.ParameterName + " output").ToArray();
if (outputparamNames.Count() > 0)
inputparamNames = inputparamNames.Union(outputparamNames).ToArray();
string strParams = string.Join(",", inputparamNames);
//// execute sp sql
string sql = String.Format("EXEC {0} {1};", procedureName, strParams);
//// execute sql
return context.Database.ExecuteSqlCommand(sql, parameters);
}
Comments
Post a Comment