how to update dataset after session cache?

[Ed 54]

I'm teaching myself ADO .NET, and am struggling with how to programmatically update a dataset after I have cached it and recovered it in an ASP .NET web app. I want to use a DataAdapter to populate a DataSet, cache the DataSet in Session, then recover the DataSet in a different page, work on it, and update the database. I can populate the DataSet, cache it, recover it, and make changes. However, I cannot get the adapter.update(dataset, "table") command to work properly. Have tried multiple solutions, with multiple errors revolving around lack of INSERT commands, uninitialized connection strings, etc. What programmatic steps must I take in a new page in order to reconnect that dataset with the origin database? Do I have to instantiate a new DataAdapter, build new commands, open a new connection, etc? Can I cache and recover any of the relevant objects (DataAdapter, SqlCommand, commandBuilder) and use them? Here's the code to populate and cache the DataSet. It works fine (but may be excessive):

    using (SqlConnection connection = new SqlConnection(connStr))
    {
        try
        {
            SqlDataAdapter pointAdapter = new SqlDataAdapter();
            pointAdapter.TableMappings.Add("Table", "UserPoints");
            SqlCommand pointCommand = new SqlCommand();
            pointCommand.CommandText = "SELECT \* FROM Points WHERE userID = @userID";
            pointCommand.Parameters.AddWithValue("@userID", userIDstr);
            pointCommand.Connection = connection;
            pointCommand.CommandType = CommandType.Text;
            pointAdapter.SelectCommand = pointCommand;
            SqlCommandBuilder pointCmdBuilder = new SqlCommandBuilder(pointAdapter);
            pointAdapter.Fill(ds);
            Session\["UserDS"\] = ds; 

And here's what I'm trying to do in a different page:

    dataset = (DataSet)Session\["UserDS"\];
    // ... do some stuff to rows
    using (SqlConnection connection = new SqlConnection(connStr))
    {
        try
        {
            SqlDataAdapter pointAdapter = (SqlDataAdapter)Session\["pointAdapter"\];
            pointAdapter.Update(dataset, "UserPoints"); 

With this code I get a "connection string uninitialized" error. But I've gotten several different errors, enough to convince me I'm going about this the hard way. Is there a standard pattern for this sort of thing, or a tutorial someone can point me to?

[Igor Sukhov]

In your second code you're creating the instance of the SqlConnection class (initialised with the connection string) and never use it due to the fact that SqlDataAdapter object is restored from the session state. The standard aproach in hadling data with the ADO.NET is that simple: *Create and open connection *Populate and update data *Close connection There's no reason to store SqlConnection, DataAdapter classes because you'll not benefit from it - just recreate them when you're need them. Another hint to you - no DataAdapter neither SqlConnection is serializable therefore they're not designed to be stored persistently in the session state. To prove it right - change the session state model from "InProc" to "StateServer" or "SqlServer" and you'll get exception while putting this objects into state. To sum up that I wrote above: don't try to cache anything from the data. When you decide to cache the data in the session state - make sure it not going to exhaust web server memory as for the every user session there'll be and cached dataset in the memory. Best regards, ----------- Igor Sukhovhttp://sukhov.net

[Ed 54]

Thanks Igor. As you suggested, I ditched the attempt to store SqlConnection and DataAdapter classes, and created a new insance when I needed them. It works fine now. I am still caching the dataset, because it is relatively small and I think I'd take a bigger hit from loading it every time I need it (which is often). But I'll watch out for this as I scale my app.