how to re-use an ado.net datatable so as to avoid re-querying?

Is it possible to re-use a DataTable in .net? Here's an example from code-behind:

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    ' the stored proc
    Dim SqlQuery As开发者_运维百科 String = "some_silly_stored_proc"
    Dim conn As String = My.Settings.csSomeProject

    Using dbcon = New SqlConnection()

        Using cmd As New SqlCommand(SqlQuery, dbcon)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@SomeSillyParamID", SqlDbType.Int)
            cmd.Parameters("@SomeSillyParamID").Value = pn.Silly_now

            Using myCommand As New SqlDataAdapter()
                Dim sillyDS As New DataSet()

                dbcon.ConnectionString = conn

                dbcon.Open()
                myCommand.SelectCommand = cmd
                myCommand.Fill(sillyDS)
                dbcon.Close()

                r.someResultRows = sillyDS.Tables(0)
                r.someResultRowsAgain = sillyDS.Tables(1)
                ' etc...

            End Using
        End Using
    End Using

When I attempt to re-access the DataTable in code-behind, the original result-set doesn't get returned with all the right data if an event fires outside of the Page_Load event.

If I test with this code in code behind, the some of the columns with integers and decimals come back as 0 instead of their original values. For example, the value for Row 1, Column 2 comes back as 0 instead of the integer 82.

    Dim var1, var2, var3, var4, var5, var6 As String
    Dim _r As DataRow

    For Each _r In r.sillyDS.Tables(0)
        var1 = _r(0).ToString()
        var2 = _r(1).ToString()
        var3 = _r(2).ToString()
        var4 = _r(3).ToString()
        var5 = _r(4).ToString()
        var6 = _r(5).ToString()
    Next

Yet if a similar "For Each" loop is on the .aspx page, all of the values come back correctly for each cell, not just some.

I don't see where r is defined (look at ASP.NET Page Life Cycle), so can't tell why values are empty, but generally you can store Dataset in Session if data is unique for current user or in a Cache if data is good for all users of your web app.

Both objects are part of Page so for storing dataset in Page methods you can use

Session["sillyDS"] = sillyDS;

or

Cache["sillyDS"] = sillyDS

and when retrieving

sillyDS = Session["sillyDS"];

or

sillyDS = Cache["sillyDS"]

And if your user opens multiple tabs of your application and data stored can be different for each tab then viewstate is your only option, because Session is the same for thaat user. But don't save whole Dataset or any large object to Viewstate, store some sort of ID in viewstate and use that to get value in Session.

Take a look at ASP .NET State Management and ASP.NET State Management Recommendations

Keep the dataSet in Viewstate"

ViewState["sillyDS"] = sillyDS;

Use it anywhere in the page

DataSet sillyDS = new DataSet();
sillyDS (DataSet)ViewState["sillyDS"];

Note - the drawback to this method is that you will be sending potentially-very-large sets of data to the client between postbacks. As a result, you are probably better off using the Session State, Application State, or ASP.NET Cache.