/// <summary>
/// Executes the procedure and return output value and message from sql server.
/// </summary>
/// <param name="ProcedureName">Name of procedure</param>
/// <param name="ParamValue">Collection of sql parameters as two dimentional array.</param>
/// <returns>Returns Return values from sql procedure.</returns>
public MEMBERS.SQLReturnMessageNValue ExecuteProcedureReturnMessageNValue(string ProcedureName, string[,] ParamValue)
{
try
{
GetConnection();
if (!MySqlConnection.GetConnection.isConnectionOpen)
{
sqlCon.Open();
}
MEMBERS.SQLReturnMessageNValue returnval = new MEMBERS.SQLReturnMessageNValue();
SqlCommand COMMAND = new SqlCommand();
COMMAND.CommandText = ProcedureName;
COMMAND.Connection = sqlCon;
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);
///Adds the output parameter
COMMAND.Parameters.Add("@OUTVAL", SqlDbType.Int).Direction = ParameterDirection.Output;
COMMAND.Parameters.Add("@OUTMESSAGE", SqlDbType.VarChar, 500).Direction = ParameterDirection.Output;
if (sqlCon.State != ConnectionState.Open)
{
sqlCon.Open();
}
COMMAND.ExecuteNonQuery();
///Retrive value from output parameters to return value structure.
returnval.Outmsg = COMMAND.Parameters["@OUTMESSAGE"].Value.ToString();
returnval.Outval = int.Parse(COMMAND.Parameters["@OUTVAL"].Value.ToString());
sqlCon.Close();
return(returnval);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MySqlConnection.GetConnection.CloseConnection(sqlCon);
}
}
/// <summary>
/// This function is for only used in API Project
/// </summary>
/// <param name="ProceduerName"></param>
/// <param name="ParamValue"></param>
/// <param name="ReturnTypeOutVal"></param>
/// <returns></returns>
public MEMBERS.SQLReturnMessageNValue ExecuteProceduerWithMessageNValue(string ProceduerName, object[,] ParamValue, int ReturnTypeOutVal)
{
GetConnection();
if (!MySqlConnection.GetConnection.isConnectionOpen)
{
sqlCon.Open();
}
sqlCMD.Parameters.Clear();
MEMBERS.SQLReturnMessageNValue res = new MEMBERS.SQLReturnMessageNValue();
try
{
SqlCommand COMMAND = new SqlCommand();
COMMAND.CommandText = ProceduerName;
COMMAND.Connection = sqlCon;
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] == null ? null : (ParamValue[i, 1].ToString() == "null" ? null : ParamValue[i, 1].ToString())));
}
COMMAND.Parameters.AddRange(param);
COMMAND.Parameters.Add("OUTVAL", (ReturnTypeOutVal == 1 ? SqlDbType.UniqueIdentifier : SqlDbType.Int)).Direction = ParameterDirection.Output;
COMMAND.Parameters.Add("OUTMESSAGE", SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;
//COMMAND.Parameters.Add("OUTMESSAGE1", SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;
if (sqlCon.State != ConnectionState.Open)
{
sqlCon.Open();
}
COMMAND.ExecuteNonQuery();
sqlCon.Close();
SQLReturnValue M = new SQLReturnValue();
res.Outval = int.Parse(COMMAND.Parameters["OUTVAL"].Value.ToString());
res.Outmsg = COMMAND.Parameters["OUTMESSAGE"].Value.ToString();
}
catch (Exception ex)
{
throw;
}
finally
{
MySqlConnection.GetConnection.CloseConnection(sqlCon);
}
return(res);
}