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

Comments

Popular posts from this blog

SECURING WEBAPI USING JSON WEB TOKEN (JWT) IN WEB API C#

Adding ASP.NET MVC5 Identity Authentication to an existing project

Multiple model pass in MVC using tuple or Tuple in MVC