Last active
August 22, 2022 13:09
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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