C# multiple SQL select statements single method

Question

I have a task to develop a console application that is a recon of values from a few tables in the DB. I have all the queries that I need for each value that is required and understand the logic on how the values will interact. The challenge I have is the best method to retrieve and store these values.

I have researched and successfully been able to create the static method to retrieve a single value from a single SQL query but I'm curious about the best method:

1. Create multiple methods to retrieve each value (upwards of 15 different select statements) summarised below (not complete code)

   static double total1(int arg)
       {
           using (SqlConnection connection = new SqlConnection(connectionString))
           {
               connection.Open();
               SqlCommand command1 = new SqlCommand(commandText1, connection));
               return(response);
           }
   }
   static double total2(int arg)
       {
           using (SqlConnection connection = new SqlConnection(connectionString))
           {
                 connection.Open();
               SqlCommand command2 = new SqlCommand(commandText2, connection));
               return(response);
       }
   }
   

2. Try to combine all select statements in a single method (I've been unsuccessful here) summarised below (not complete code)

   using (SqlConnection connection = new SqlConnection(connectionString))
   {
       connection.Open();
       using (SqlCommand command1 = new SqlCommand(commandText1, connection))
       {
       }
       using (SqlCommand command2 = new SqlCommand(commandText2, connection))
       {
       }
       // etc
   }
   

3. Create stored procedures in SQL and execute them and pass the parameters via the c# console app

I think method 1 is going to be taxing on the server as it would require the connection to open and close multiple times (although I don't know if that's as big a issue as I think it is). Method 2 seems more reasonable although I've followed the concepts here and I get stuck when trying to get the output of the commands (I'm using return). Method 3 seems smarter to me although I'd still be in a position where I need to choose between methods 1 & 2 to execute the SP's.

I would really appreciate advice and guidance here, I'm new to C# so this is a steep learning curve when tutorials don't cover the sort of thing (or at least I can't define my problem properly)

Answer 1

    string query = "SELECT * FROM table1;";

    query += "SELECT * FROM table2";

    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);                       
                }
            }
        }
    }

Answer 2

I've recently seen that this question is still being viewed so I'll post the solution for anyone who is just starting off developing and encounters a similar challenge.

The most suitable solution was to create a stored procedure, pass each unique argument to it and return all the relevant data in a single response to the application. A custom model was defined for this output and the application logic adapted accordingly. The result is a longer running single call to the database as opposed to multiple individual ones.

Using the format from the question, the solution was:

C# code

static NewModel AllTotals(int arg1, int arg2)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        var commandText = "TheStoredProcName";
        SqlCommand command = new SqlCommand(commandText, connection));
        command.CommandType = System.Data.CommandType.StoredProcedure;
        Cmd.Parameters.AddWithValue("@arg1", arg1);
        Cmd.Parameters.AddWithValue("@arg1", arg2);
        using (SqlDataReader dr = Cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                var response = new NewModel(){
                    Value1 = Convert.ToDecimal(dr["value1"]),
                    Value2 = Convert.ToDecimal(dr["value2"]),
                };

                return(response);
            }
        }
    }

    return null;
}

SQL Stored Proc

CREATE PROCEDURE [dbo].[TheStoredProcName]
    @arg1 int,
    @arg2 int
AS
    DECLARE @value1 decimal(18,6) = ISNULL((--QUERY TO GET VALUE1),0)
    DECLARE @value2 decimal(18,6) = ISNULL((--QUERY TO GET VALUE2),0)
    
    -- select the variables into a result set for the application
    SELECT @value1 as [value1], @value2 as [value2]