Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save ambitiousrahul/279346c401f7c6166cab2bee37b8fafc to your computer and use it in GitHub Desktop.
Save ambitiousrahul/279346c401f7c6166cab2bee37b8fafc to your computer and use it in GitHub Desktop.
This methods calls stored procedure with table type input parameter using c# core
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;
///<summary>
/// Method that calls stored procedure accepting a table type parameter
///</summary>
async Task<int> UpdateProcedure(UpdateViewModel upViewMOdel)
{
List<SqlDataRecord> updateParams = new List<SqlDataRecord>();
SqlMetaData tableParam1 = GenerateSqlParameter("tableParam1", SqlDbType.VarChar, 50);
SqlMetaData tableParam2 = GenerateSqlParameter("tableParam2", SqlDbType.VarChar, 50);
SqlMetaData tableParam3 = GenerateSqlParameter("tableParam3", SqlDbType.VarChar, 24);
SqlMetaData tableParam4 = GenerateSqlParameter("tableParam4", SqlDbType.VarChar, 100);
SqlMetaData tableParam5 = GenerateSqlParameter("tableParam5", SqlDbType.VarChar, 200);
for (int index = 0; index < upViewMOdel.ArrayCodes.Length; index++)
{
SqlDataRecord tableRowsData = new SqlDataRecord(new[]
{
tableParam1, tableParam2, tableParam3, tableParam4, tableParam5
}
);
tableRowsData.SetSqlString(0, upViewMOdel.Parameter1);
tableRowsData.SetSqlString(1,upViewMOdel.Parameter2[index]);
tableRowsData.SetSqlString(2, upViewMOdel.Parameter3);
tableRowsData.SetSqlString(3, upViewMOdel.Parameter4);
tableRowsData.SetSqlString(4, upViewMOdel.Parameter5);
updateParams.Add(tableRowsData);
}
var tblParam = new SqlParameter("@TableTypeParamInProcedure",SqlDbType.Structured)
{
TypeName = "TblTypeParameterDefinedInDBForStoredProcedure",
Value = updateParams
};
int result = await db.Database.ExecuteSqlRawAsync("usp_UpdateWithTableTypeParam @TableTypeParamInProcedure", tblParam);
return result;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment