How can I execute multiple database requests in ASP.NET C#?

Question

In every ASP.NET application I have written, I would make number of request to the database before outputting the information onto the web page.

For example:

var DataTable1 = GetDataTable("Select * From Customers");
var DataTable2 = GetDataTable("Select * From Products");
var DataTable3 = GetDataTable("Select * From Orders");

As far as I'm aware, the code above would make 3 separate trips to the database and would do them one after the other.

Is there anyway I can gather together my parameterized SQL statements and make only 1 trip to the database server?

Answer 1

Create a Thread class and place a method in that class which accept string query as input: then create a Thread object and start the object.

Check this out http://msdn.microsoft.com/en-us/library/aa645740(VS.71).aspx

Answer 2

var SqlString = "SELECT * FROM Customers; SELECT * FROM Products; SELECT * FROM ORDERS;");
var ds = GetDataSet(SqlString);
var DataTable1 = ds.Tables(0);
var DataTable2 = ds.Tables(1);
var DataTable3 = ds.Tables(2);

Answer 3

Use semi-colons to separate SQL statements and reader.NextResult to get each set. Your code will end up looking something like this.

    Using con As New SqlConnection
        Using cmd As New SqlCommand("Select * From Customers; Select * From Products; Select * From Orders")
            Using reader = cmd.ExecuteReader
                Dim dt1 As DataTable
                dt1.Load(reader)

                reader.NextResult()

                Dim dt2 As DataTable
                dt2.Load(reader)

                reader.NextResult()


                Dim dt3 As DataTable
                dt3.Load(reader)

            End Using
        End Using
    End Using