/// <summary>
/// This function is for only used in API Project
/// </summary>
/// <param name="ProcedureName"></param>
/// <param name="ParamValue"></param>
/// <param name="AddOutputParameters"></param>
/// <returns></returns>
public SQLReturnValue ExecuteProcWithMessage(string ProcedureName, string[,] ParamValue, bool AddOutputParameters)
{
GetConnection();
if (!MySqlConnection.GetConnection.isConnectionOpen)
{
sqlCon.Open();
}
sqlCMD.Parameters.Clear();
try
{
SqlCommand COMMAND = new SqlCommand();
COMMAND.CommandText = ProcedureName;
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].ToString());
}
COMMAND.Parameters.AddRange(param);
if (AddOutputParameters == true)
{
COMMAND.Parameters.Add("@OUTVAL", SqlDbType.Int).Direction = ParameterDirection.Output;
COMMAND.Parameters.Add("@OUTMESSAGE", SqlDbType.VarChar, 500).Direction = ParameterDirection.Output;
//COMMAND.Parameters.Add("@OUTMESSAGE1", SqlDbType.VarChar, 500).Direction = ParameterDirection.Output;
}
if (sqlCon.State != ConnectionState.Open)
{
sqlCon.Open();
}
COMMAND.ExecuteNonQuery();
sqlCon.Close();
SQLReturnValue M = new SQLReturnValue();
M.ValueFromSQL = int.Parse(COMMAND.Parameters["@OUTVAL"].Value.ToString());
M.MessageFromSQL = COMMAND.Parameters["@OUTMESSAGE"].Value.ToString();
//M.MessageFromSQL1 = COMMAND.Parameters["@OUTMESSAGE1"].Value.ToString();
return(M);
}
catch
{
throw;
}
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);
}