示例#1
0
        /// <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);
            }
        }
示例#2
0
 /// <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);
 }