Dataset persistence???

[Jason]

I am having problems understanding how to access a datasource only
once, fill a single dataset, and then reference that dataset multiple
times through different user controls(ascx) found on the same page.

My main page(aspx) contains multiple user controls. The main page also
contains a publicly declared dataset. At the first instance of needing
data I call (from an ascx) a function that fills the dataset and then
returns it to the calling user control. Every instance after that I
would like to call a function that just returns the data that has
already been populated in the dataset. But the dataset is empty. I
have to go back to the datasource everytime. How can I make the
dataset persistent without storing it in something that will use up
resources like a session variable?

Here are some examples of the code in question:

'(Main.ASPX.vb) DsAdmin_ is publicly defined at the top of the class.

Function CreateDataSource(ByVal strdTable As String, ByVal strSQL As
String) As DataSet
Dim ErrMsg As String = Nothing
Try
objData.OpenConn()
Dim objCmd As New SqlClient.SqlDataAdapter(strSQL,
objData.Conn)
objCmd.Fill(DsAdmin_, strdTable)
CreateDataSource = DsAdmin_
Catch exp As Exception
ErrMsg = Err.Description & Err.Source
End Try
objData.CloseConn(
End Function

Public Function GetData() As DataSet
GetData = DsAdmin_
End Function


'code snippets from UserControl1.ascx.vb: This control returns data.
DSAdmin_ is locally defined in a sub of this control.

Public Main As New MyProject.Main

DsAdmin_ = Main.CreateDataSource("dtblUser", "Execute
sp_Admin_GetUserDetails " & (Request("id"))



'code snippets from UserControl2.ascx.vb: This dataset is empty.
DSAdmin_ is locally defined in a sub of this control.

Public Main As New MyProject.Main

DsAdmin_ = Main.GetData()


Any help would be GREATLY Appreciated!!!

 

[Guest]

You have to manually save the filled dataset somewhere...
You can choose between Session, ViewState, Cache or hard
disk, depending of your scenario (Small datasets go well
in viewstate, datasets that should be available to all
users might go to hard drive or Cache object, etc...). A
simple code would be:

private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
DataSet myDataSet = new DataSet();
fill(myDataSet); //<-- This is a method where
you fill your dataset
Session["myDataSet"] = myDataSet; //<- here we
are saving the dataset for later retrieval.
}
else
{
myDataSet = (DataSet)Session["myDataSet"];
}
myDataSetManupulationMethod(myDataSet); //<-- the
dataset will be available and filled regardless is a
postback or not.
}

P.D.: Sorry about the examples in C#, but i don't write
VB.NET code.

 

[Charles Rumbold]

I am not certain that you will like the answer but...

If you want the DataSet to exist between server roundtrips then you
need to save it somewhere. The only places are:
1) In ViewState
2) In Session
3) Some other handrolled storage
4) Refresh from the DB.

If you have a lot of data (100's or 1000's or rows) then 1 & 2 don't
work too well (notwithstanding do you really need/use all the data).
Many people frown on Session anyway as unscalable - but probably ok
for a small intranet app. Why do 3 when 4 is probably quicker.

It may seem inefficient but is probably most scalable to refresh data
from db on each page request and spend your time making certain you
only get the minimum data. Only if your data source is very slow
would it be worth doing your own storage, like writing/reading a temp
XML file, and this may not be scalable.

HTH
Charles

 

[Jason Honn]

Thank you for your replay. Is it necessary to save the dataset in a
session variable if I am not going back to the client until all of my
user controls are processed? All my user controls are being called by
the server without a post back. They are setup as modules in one aspx
page.

 

[Charles Rumbold]

No - if you are processing the dataset multiple times in one round
trip then populate the dataset once and pass a reference to your
controls. Obvious strategies for this is to have a 'Show' method for
each control which takes a refrence to a data set as a a parameter.
Each control can process the data set immediately or hold the
reference until it needs to process it. Or you can make the data set
a public property of the containing page and pass a reference to the
page to the controls (in Page_Load or OnInit). Be careful to program
defensively as these refrences will be null after any post-back. The
public property is more defensive as you can code like:

private MyDataSet _myDataSet;
public MyDataSet Data {
get {
if( _myDataSet == null )
_myDataSet = InitialiaseDataSet();
return _myDataSet;
}
}

The best being that you never return a null, the worst being you
initialise the data once on each page request. You can refine the
InitialiseDataSet to be the best strategy (ViewState, Session, DB,
other).

HTH,
Charles

 

[Jason Honn]

Charles,

That is exactly what I am after. Is there a more complete example of
what you are describing somewhere that I could look at?(VB or C#) I am
very new to the .Net environment, so anything would help.

Thanks again
Jason

 

[eliza81]

Populate multiple controls from DB in a single function using a single DataSet

Step 1:
- Write the different queries for getting the data from the DB for different controls in a single string variable by separating them with semicolon.

Ex -
//Create the SQL query.
string selectQueryForControlPopulation =
"SELECT COL1,COL2,... FROM TABLE_NAME1 WHERE CONDITION; SELECT COL1,COL2,..FROM TABLE_NAME2 WHERE CONDITION; ......";
Step 2:
- Create the DataAdapter, DataSet object. Execute the query and fill the DataSet object.

Ex -
//Create the Connection object.
OleDbConnection oConnection = new OleDbConnection(ConfigurationSettings.AppSettings["SQLConnectionString"]);

//Create the Command object.
OleDbCommand oCommand = new OleDbCommand(selectQueryForControlPopulation , oConnection );

//Create the DataAdapter object and set its property.
OleDbDataAdapter oAdapter = new OleDbDataAdapter();
oAdapter .SelectCommand = oCommandPopulateOrganizations;

// Create a DataSet object.
DataSet oDataSet = new DataSet();

//Filling the DataSet object.
oAdapter .Fill(oDataSet);

(**) The DataSet object contain the result of different queries as different tables. We can access those table to fill our controls.