How to use arrays to return values ​​from multiple stored procedures

rojiny

The following is the code of Microsoft petshop4, but only input parameters, no return parameters

 /// <summary>
        /// Execute a SqlCommand that returns a resultset against the database specified in the connection string
        /// using the provided parameters.
        /// </ summary>
        /// <remarks>
        /// e.g .:
        /// SqlDataReader r = ExecuteReader (connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter ("@ prodid", 24));
        /// </ remarks>
        /// <param name = "connectionString"> a valid connection string for a SqlConnection </ param>
        /// <param name = "commandType"> the CommandType (stored procedure, text, etc.) </ param>
        /// <param name = "commandText"> the stored procedure name or T-SQL command </ param>
        /// <param name = "commandParameters"> an array of SqlParamters used to execute the command </ param>
        /// <returns> A SqlDataReader containing the results </ returns>
        public static SqlDataReader ExecuteReader (string connectionString, CommandType cmdType, string cmdText, params SqlParameter [] commandParameters) {
            SqlCommand cmd = new SqlCommand ();
            SqlConnection conn = new SqlConnection (connectionString);

            // we use a try / catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try {
                PrepareCommand (cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader (CommandBehavior.CloseConnection);
                cmd.Parameters.Clear ();
                return rdr;
            }
            catch {
                conn.Close ();
                throw;
            }
        }


/// <summary>
        /// Prepare a command for execution
        /// </ summary>
        /// <param name = "cmd"> SqlCommand object </ param>
        /// <param name = "conn"> SqlConnection object </ param>
        /// <param name = "trans"> SqlTransaction object </ param>
        /// <param name = "cmdType"> Cmd type e.g. stored procedure or text </ param>
        /// <param name = "cmdText"> Command text, e.g. Select * from Products </ param>
        /// <param name = "cmdParms"> SqlParameters to use in the command </ param>
        private static void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter [] cmdParms) {

            if (conn.State! = ConnectionState.Open)
                conn.Open ();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans! = null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms! = null) {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add (parm);
            }
        }
    }



Calling method
SqlParameter [] parms = new SqlParameter []
        {
new SqlParameter ("@ DealId", SqlDbType.VarChar, 10)
};


 parms [0] .Value = "";
GridView1.DataSource = SqlHelper.GetDataset (SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "P_DealedProcess", parms);

        GridView1.DataBind ();

everever30

SqlParameter [] parms = new SqlParameter []
        {
new SqlParameter ("@ DealId", SqlDbType.VarChar, 10)
                           new SqlParameter .... // Define output parameters
};


 parms [0] .Value = "";
GridView1.DataSource = SqlHelper.GetDataset (SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "P_DealedProcess", parms);

object out = parms [1] .Value; // out is the value of the output parameter.
        GridView1.DataBind ();

rojiny

everever30
  
               I want to write a general function that returns multiple values ​​of a stored procedure, I don't know how to write

rojiny

cmd.Parameters.Clear (); The array has been deleted, object out = parms [1] .Value; // out is the value of the output parameter.
This sentence is wrong

shenmue024

cmd.Parameters.Clear ();

before
object [] ary = new object [cmd.Parameters.Count];
for (int i = 0; i <ary.Length; i ++)
{
ary [i] = cmd.Parameters [i] .Value;
}


Finally return ary,

Does lz mean this?

rojiny

shenmue024
   

           Yes this is being tested

rojiny

shenmue024

          There should be an array parameter in the function to receive the value of object [] ary

       Question 1
                How to use a dynamic array (before the array starts to use, do not know how many values ​​to return) as parameters
       Question 2
                How to pass the value of object [] ary to a dynamic array parameter