/// <summary>
/// Execute procedure with parameters
/// 12/8/2017 pritey
/// </summary>
/// <param name="ProcedureName"></param>
/// <param name="ParamValue"></param>
/// <param name="dtExamAnswer"></param>
/// <param name="TableParamName"></param>
/// <param name="ReturnTypeOutVal"></param>
/// <returns></returns>
public MEMBERS.SQLReturnValue ExecuteProcedureWithParam_Datatable(string ProcedureName, object[,] ParamValue, DataTable dtExamAnswer, string TableParamName, int ReturnTypeOutVal)
{
SqlCommand COMMAND = new SqlCommand();
COMMAND.CommandText = ProcedureName;
SqlConnection MYCON = new SqlConnection(con.ConnectionString);
COMMAND.Connection = MYCON;
COMMAND.CommandTimeout = 0;
COMMAND.CommandType = CommandType.StoredProcedure;
SqlParameter[] param = new SqlParameter[ParamValue.GetUpperBound(0) + 1];
for (int i = 0; i < param.Length; i++)
{
param[i] = new SqlParameter("@" + ParamValue[i, 0].ToString(), ParamValue[i, 1].ToString());
}
COMMAND.Parameters.AddRange(param);
if (dtExamAnswer != null)
{
SqlParameter ParamTb = new SqlParameter("@" + TableParamName, dtExamAnswer);
ParamTb.SqlDbType = SqlDbType.Structured;
COMMAND.Parameters.Add(ParamTb);
}
COMMAND.Parameters.Add("OUTVAL", (ReturnTypeOutVal == 1 ? SqlDbType.UniqueIdentifier : ReturnTypeOutVal == 2 ? SqlDbType.Int : SqlDbType.NVarChar), -1).Direction = ParameterDirection.Output;
if (MYCON.State != ConnectionState.Open)
{
MYCON.Open();
}
COMMAND.ExecuteNonQuery();
MYCON.Close();
MEMBERS.SQLReturnValue res = new MEMBERS.SQLReturnValue();
res.Outval = COMMAND.Parameters["OUTVAL"].Value;
return(res);
}
/// <summary>
/// Execute Procedure With Value
/// 12/8/2017 pritey
/// </summary>
/// <param name="procedureName"></param>
/// <param name="ParamValue"></param>
/// <param name="ReturnTypeOutVal"></param>
/// <returns></returns>
public MEMBERS.SQLReturnValue ExecuteProcedureWithValue(string procedureName, object[,] ParamValue, int ReturnTypeOutVal)
{
MEMBERS.SQLReturnValue res = new MEMBERS.SQLReturnValue();
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = procedureName;
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter[] param = new SqlParameter[ParamValue.GetUpperBound(0) + 1];
for (int i = 0; i < param.Length; i++)
{
param[i] = new SqlParameter("@" + ParamValue[i, 0].ToString(), (ParamValue[i, 1] == null ? null : (ParamValue[i, 1].ToString() == "null" ? null : ParamValue[i, 1].ToString())));
}
cmd.Parameters.AddRange(param);
cmd.Parameters.Add("OUTVAL", (ReturnTypeOutVal == 1 ? SqlDbType.UniqueIdentifier : ReturnTypeOutVal == 2 ? SqlDbType.Int : SqlDbType.NVarChar), -1).Direction = ParameterDirection.Output;
if (con.State != ConnectionState.Open)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();
res.Outval = cmd.Parameters["OUTVAL"].Value;
}
catch (Exception ex)
{
res.Outval = ex.Message;
}
return(res);
}
/// <summary>
/// Execute Procedure With Datatable
/// 12/8/2017 pritey
/// </summary>
/// <param name="ProcedureName"></param>
/// <param name="dtData"></param>
/// <param name="TableParamName"></param>
/// <param name="ReturnTypeOutVal"></param>
/// <returns></returns>
public MEMBERS.SQLReturnValue ExecuteProcedureWithDatatable(string ProcedureName, DataTable dtData, string TableParamName, int ReturnTypeOutVal)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = ProcedureName;
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
if (dtData != null)
{
SqlParameter ParamTb = new SqlParameter("@" + TableParamName, dtData);
ParamTb.SqlDbType = SqlDbType.Structured;
cmd.Parameters.Add(ParamTb);
}
cmd.Parameters.Add("OUTVAL", (ReturnTypeOutVal == 1 ? SqlDbType.UniqueIdentifier : ReturnTypeOutVal == 2 ? SqlDbType.Int : SqlDbType.NVarChar), -1).Direction = ParameterDirection.Output;
if (con.State != ConnectionState.Open)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();
MEMBERS.SQLReturnValue res = new MEMBERS.SQLReturnValue();
res.Outval = cmd.Parameters["OUTVAL"].Value;
return(res);
}
catch (Exception ee)
{
MEMBERS.SQLReturnValue res = new MEMBERS.SQLReturnValue();
res.Outval = ee.Message;
return(res);
}
}