How to store the results of a query in a variable.

Can anyone tell me or point me in the direction of how I can store select query results to a variable in VB.NET? Im using the SqlDataSource control with dropdowns and textboxes for searching. I want to store the search results in a variable on the button click event.

Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
Dim dr As DataRow
dr = dv.Table.Rows(0)

session("variable1") = dr("ColumnName1").ToString()
Dim strVar2 as string = dr("ColumnName2").ToString()

etc.

|||

Depending on what you are trying to store, there are several options. First, gather the data in a single variable. This can be a collection, array, some user-defined object or even a dataset.

Once you have the variable, you may do several things with it:

Session - If the resultsets will be different for each user, you may want to use session. Session variables exist for a certain amount of time, then disappear when that time expires. If the session expires, the variable will become NULL (Nothing in VB.NET).

eg: Session["DataSet"] = dataSet;

Cache - If the resultsets will be the same for each user, you may want to use cache. The Page.Cache object can store data for as long as you need it, only refreshing the data after an amount of time has elapsed or if an event triggers the cache to be emptied.

eg: Page.Cache.Add("DataSet", dataSet, cacheDependency,DateTime.Now.AddHours(5));

ViewState - If the resultsets will not be used for long, but need to be used across a postback, you may want to use viewstate. Viewstate is used to maintain state at the page level. That being said, it does not exist on another page, but does exist when you postback to the same page. Use this if you only need the data for a limited amount of time, but you plan on spending awhile on the same page.

eg: ViewState.Add("DataSet", dataSet);

Hope this helps.