public static int RunTVPScript(string conStr, string script, string pamteterName, string pameterType, DataTable table)
{
int count = 0;
try
{
SqlConnection con;
// modify connection string to connect to your database
con = new SqlConnection(conStr);
con.Open();
using (con)
{
// Configure the SqlCommand and SqlParameter.
SqlCommand sqlCmd = new SqlCommand(script, con);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = 6000;
SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue(pamteterName, table); //Needed TVP
tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP
tvpParam.TypeName = pameterType;
count = sqlCmd.ExecuteNonQuery();
}
con.Close();
}
catch (Exception ex)
{
LogerHelper.WriteLog("Error when run DBHelper.RunTVP", ex);
}
return(count);
}
public static bool RunTVP(string conStr, string usp, string pamteterName, DataTable table)
{
try
{
SqlConnection con;
// modify connection string to connect to your database
con = new SqlConnection(conStr);
con.Open();
using (con)
{
// Configure the SqlCommand and SqlParameter.
SqlCommand sqlCmd = new SqlCommand(usp, con);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandTimeout = 600;
SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue(pamteterName, table); //Needed TVP
tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP
sqlCmd.ExecuteNonQuery();
}
con.Close();
return(true);
}
catch (Exception ex)
{
LogerHelper.WriteLog("Error when run DBHelper.RunTVP", ex);
return(false);
}
}