Group By Question - Database Discussion Boards

how to update dataset after session cache?

Ed 548-Jul-06 12:40

8-Jul-06 12:40

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?

Re: how to update dataset after session cache?

Igor Sukhov9-Jul-06 0:24

Igor Sukhov

9-Jul-06 0:24

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

Re: how to update dataset after session cache?

Ed 5410-Jul-06 8:35

Ed 54

10-Jul-06 8:35

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.

About DataType in the access

KSCsoft8-Jul-06 5:04

KSCsoft

8-Jul-06 5:04

the Max Size of "text" is 255,but it's so short to me.
how should I do?

thank you.

KSCsoft

Re: About DataType in the access

Rob Graham8-Jul-06 5:27

Rob Graham

8-Jul-06 5:27

if you need a larger test field, use Memo.

transaction

md_refay8-Jul-06 3:27

md_refay

8-Jul-06 3:27

i want to make transaction on disconnected database what shall i do

ma_refay

Re: transaction

Rob Graham8-Jul-06 5:28

Rob Graham

8-Jul-06 5:28

You can't. You'll have to reconnect to start a transaction in the connection's context.

Group By Question

Chris McGlothen7-Jul-06 12:03

Chris McGlothen

7-Jul-06 12:03

I'm having an issue with combining data rows returned by a query. I am working with sales data from a table joined to several other tables(confusing I know). When run the query returns multiple instances of the same company name(chain of stores), all from different areas of the country.

My question is whether or not there is a way to combine all the data for one company name and be able to get totals for ie: price, # of sales, etc. I've tried using a group by statement, but as I need all the column data from the original table I'm having to group by every column name.

Thanks.

Cheers,
Lil Turtle

Re: Group By Question

Asif Sayed7-Jul-06 17:33

Asif Sayed

7-Jul-06 17:33

If you are using SQL server then the best idea is to have a view which will return all company names. Once you are done with this you can write another SQL select on view with group by clause for any further data selection.

Hope this helps.

Difficult - > Challenging, this simple replacement made me take my life little easy;)

Re: Group By Question

Chris McGlothen10-Jul-06 4:26

Chris McGlothen

10-Jul-06 4:26

A view in this instance is not needed as the data returned is for a report that I'm doing and not part of a permanent data store. Thanks for the tip though I can apply this to other endevors.;)
And I do appreciate the help, Thanks.

A humble novice,
Lil Turtle

Re: Group By Question

Colin Angus Mackay7-Jul-06 23:22

Colin Angus Mackay

7-Jul-06 23:22

Lil Turtle wrote:
I am working with sales data from a table joined to several other tables(confusing I know).

Not really - Joining tables will be a regular occurrance if your database is normalised properly.

Lil Turtle wrote:
My question is whether or not there is a way to combine all the data for one company name and be able to get totals for ie: price, # of sales, etc. I've tried using a group by statement, but as I need all the column data from the original table I'm having to group by every column name.

Then what you might try to do is to perform the GROUP BY in a subquery and then join the subquery to the table which contains all the other data that you want. If you want an example, you might like to show your existing code.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: Group By Question

Chris McGlothen10-Jul-06 4:23

Chris McGlothen

10-Jul-06 4:23

The GROUP BY in the sub query worked like a dream. Thanks for the response, I appreciate the help.

A humble novice,
Lil Turtle

SQL database administration levels

GETEL - General Telecom7-Jul-06 9:24

GETEL - General Telecom

7-Jul-06 9:24

I am only one administator of SQL 2005 database , I want to divide database in seven administration levels !

GETEL - General Telecom

Re: SQL database administration levels

Colin Angus Mackay7-Jul-06 9:47

Colin Angus Mackay

7-Jul-06 9:47

GETEL - General Telecom wrote:
I am only one administator of SQL 2005 database , I want to divide database in seven administration levels !

Good on you! Keep up the great work!

I presume you wanted to ask a question.... And I presume that question is "How do I divide the database into seven administration levels?"

My question is return is: What do you mean by an "administration level"?

Re: SQL database administration levels

Paul Conrad7-Jul-06 14:39

Paul Conrad

7-Jul-06 14:39

Colin Angus Mackay wrote:

My question is return is: What do you mean by an "administration level"?

Thank you, Colin. I don't quite follow what GETEL - General Telecom is asking for. Would this be different user roles?

Re: SQL database administration levels

Colin Angus Mackay7-Jul-06 23:16

Colin Angus Mackay

7-Jul-06 23:16

Paul Conrad wrote:
Would this be different user roles?

I suppose it could be.

Field in multiple tables

VK-Cadec7-Jul-06 9:02

VK-Cadec

7-Jul-06 9:02

Hi - We have a particular field called "EventNum" which is written to a whole bunch of tables. I am new to this group and table struture.

Is there a way I can select out this value from across all the tables in a particular database.

something like - "select * from dbo.* where eventnum=11111111"

this same info is spanned across many tables.

many thanks.

Re: Field in multiple tables

Colin Angus Mackay7-Jul-06 9:43

Colin Angus Mackay

7-Jul-06 9:43

VK-Cadec wrote:
Is there a way I can select out this value from across all the tables in a particular database

No, you have to select the tables individually because each will likely have a different shape even if they do share a common column. In one table this column will likely be the primary key. In other tables it will likely be a foreign key.

Re: Field in multiple tables

VK-Cadec7-Jul-06 9:46

VK-Cadec

7-Jul-06 9:46

Suppose it is the same name in all the tables and it is the primary key in the most of the tables, then?

Thanks.

Re: Field in multiple tables [modified]

Colin Angus Mackay7-Jul-06 9:47

Colin Angus Mackay

7-Jul-06 9:47

It doesn't change my answer. "No, you have to select the tables individually because each will likely have a different shape even if they do share a common column."

Re: Field in multiple tables

Colin Angus Mackay7-Jul-06 9:50

Colin Angus Mackay

7-Jul-06 9:50

If the tables have the same shape (i.e. they have the same columns) then you can UNION the results together in one result set. But, you still have to specify each table in the SQL.

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3

Re: Field in multiple tables

Colin Angus Mackay8-Jul-06 1:55

Colin Angus Mackay

8-Jul-06 1:55

Further to Ennis Ray Lynch, Jr.'s answer. You can put several SQL statements together to get the effect that you want:

DECLARE @sql nvarchar(4000);
DECLARE EventCursor CURSOR
FOR
SELECT 'SELECT * FROM ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] WHERE eventNum= @eventNum' 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'EventNum'
OPEN EventCursor
FETCH NEXT FROM EventCursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @stmt = @sql,
         @params = N'@eventNum int',
         @eventNum = 4 -- Replace this number with the desired event number
                       -- or a parameter that represents it.
    FETCH NEXT FROM EventCursor INTO @sql
END
CLOSE EventCursor
DEALLOCATE EventCursor

Yes

Ennis Ray Lynch, Jr.7-Jul-06 12:36

Ennis Ray Lynch, Jr.

7-Jul-06 12:36

Don't listen to the naysayers. Using T-SQL you can. I do not have access to SQL Server atm so I will describe it and leave it to you. Select from the system objects table where name = Event Num making sure you only get column definitions. Select all table names from the system tables (it might be objects to) that contain the given column. Then you can use a T-SQL query to return multiple result sets each one using exec "select * from " table name" WHERE eventNum=" + whatever.

Sorry I am not in front of SQL Server or I would have given you the code.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Yes

Colin Angus Mackay8-Jul-06 1:47

Colin Angus Mackay

8-Jul-06 1:47

Ennis Ray Lynch, Jr. wrote:
Don't listen to the naysayers. Using T-SQL you can

You are still doing what I said and SELECTing the tables individually. You are just automating the process slightly by building a string with the SQL in in and then executing that dyanmic SQL. There is no single command to do what the OP requested.

DECLARE @sql nvarchar(4000);
DECLARE EventCursor CURSOR
FOR
SELECT 'SELECT * FROM ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] WHERE eventNum= @eventNum' 
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'EventNum'
OPEN EventCursor
FETCH NEXT FROM EventCursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @stmt = @sql, 
        @params = N'@eventNum int', 
        @eventNum = 4 -- Replace this number with the desired event number 
                      -- or a parameter that represents it.
    FETCH NEXT FROM EventCursor INTO @sql
END
CLOSE EventCursor
DEALLOCATE EventCursor

Thanks for the code

Ennis Ray Lynch, Jr.8-Jul-06 7:30

Ennis Ray Lynch, Jr.

8-Jul-06 7:30

Still no manual discovery is required which I think is what he wanted.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.