Make connection to database only once on page load

Question

When I load my page I populate my repeater with the following code.

        Dim conn As Data.SqlClient.SqlConnection
        Dim Comm As Data.SqlClient.SqlCommand
        Dim reader As Data.SqlClient.SqlDataReader

        'conn = New Data.SqlClient.SqlConnection("Server=localhost\sqlexpress; " & _
        '"Database=MyDB; Integrated Security=true")
        conn = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)

        Comm = New Data.SqlClient.SqlCommand( _
        ("HomePage"), conn)

        Comm.CommandType = CommandType.StoredProcedure
        Comm.Parameters.AddWithValue("@currentState", "Florida")

        ' Open the connection
        conn.Open()
        ' Execute the category command
        reader = Comm.ExecuteReader()

        ' Bind the reader to the repeater.......
        blogRepeater.DataSource = reader

        blogRepeater.DataBind()

        ' Close the reader
        reader.Close()
        ' Close the connection
        conn.Close()

    End Try

Now I want to call another Stored Procedure (at the same time) so that I can populate some text fields (also on Page Load). But how can I do this so that I only make a call to my database once for better performance?

C# examples will also work if you don't know VB.NET

Answer 1

Just don't close the connection and fire off another stored procedure. Close the connection afterwards. So you would Dim another SQL command and execute it.

Something like:

Dim Comm2 As Data.SqlClient.SqlCommand
Dim reader2 as Data.SqlClient.SqlDataReader

Comm2.CommandType = CommandType.StoredProcedure
Comm2.Paramaters.AddWithValue("@whateverValue", "Whatever")

then just after you open the connection

reader2 = Comm2.ExecuteReader()

Then you'll find reader2 has what you want, but you used the same connection for both.

Answer 2

ADO.Net handles connection pooling so its not important to take care about it, also if u leave a connection open, may be your connection closed after (for example) 24 hour, because its dependent on server settings.