Running Insert/update SQL against ADO.NET dataset

[Matt]

So as you all know the great thing about ADO.NET is that I can take an
entire table from a database and dump it into an in memory datatable
using ADO.NET.

Well my question is that now that I have this temporary table, is it
possible to run SQL commands against it like it were an "actual"
table.

To give a simplistic example, lets say I have a table in my SQL Server
database called "Customers" that contains 2 columns (CustomerID,
CustomerName)

I dump that table into a ADO.NET object so now I have all of the rows
in that in memory datatable. Now I have this stored procedure that I
want to run against this in memory table. Would that be possible?

I know you can add a row by creating a datarow and doing it all
manually...but I much rather just run a stored proc.

Is this even possible??

 

[Guest]

So as you all know the great thing about ADO.NET is that I can take an
entire table from a database and dump it into an in memory datatable
using ADO.NET.

Yes, but that doesn't make it a great thing. In fact in most cases, it's
a downright bad thing!

Well my question is that now that I have this temporary table, is it
possible to run SQL commands against it like it were an "actual"
table.

You can do basic commands through a dataview. But no, it's not an in-
memory database with full SQL support.

LINQ however should be able to provide with the features you need.

I dump that table into a ADO.NET object so now I have all of the rows
in that in memory datatable. Now I have this stored procedure that I
want to run against this in memory table. Would that be possible?

No, stored procedures are for databases.

I know you can add a row by creating a datarow and doing it all
manually...but I much rather just run a stored proc.

Is this even possible??

Why not run the query against a DB instead of an in memory version?

 

[aaron.kempf]

I agree with the original question-- if it is an in-memory database--
why can't we submit queries to it?

COM+ with Windows 2000 pre-release included a seperate 'in-memory'
database but this was postponed until the release of .NET.

I think that a simple- in memory database- would be a lot of fun;
especially if it automatically synchronized and logged.

I don't thnk that most server databases really scale as well with
additional memory-- as it should.

Hhaving a 2gb database on a server with 4gb of ram-- everything should
be instant.

I'm not sure that SQL Server really is loose enough with memory.

Google has some in-memory database structures, I believe.

honestly http://code.google.com

-Aaron

 

[Cor Ligthert[MVP]]

Matt,

If you create only a table, then you need only to add an insert command to
the insertcommand part of a dataadapter and a transact SQL insert command,
than can be either a stored procedure or in line in the command code (If it
is a small project that is in my opinion advisable, is it a huge solution
then I would use a stored procedure).

It is just
A DataTable where new rows are inserted,
A SQLDataAdapter
A SQL transact insert string
A SQL connection
A SQLCommand that has set its properties with that
Adding the later to the adapter
Doing a SQLDataAdapter Update with the table

Cor

 

[Matt]

Cor -

I'm not looking to update the original database table via the
dataadapter. I'm looking to update the in memory datatable with the
insert command. Will that still work?

Everyone -

Basically I have a folder full of stored procedures that I want to run
against an in memory database.

 

[Kerry Moorman]

Matt,

You don't have an in-memory database and you can't run stored procedures
against datatables.

Kerry Moorman

 

[aaron.kempf]

Well that is how ADO.net is presented to us. As an in-memory
database.
It seems to me like you should be able to write optimized Stored
Procedures to work against this IMDB.

In my opinion-- ADO.net can't even keep a connection _OPEN_.
Now _THAT_ is helpful.

-Aaron

 

[aaron.kempf]

I call bullshit.

this is just another symptom that .NOT is .NOT fully baked yet.

Give us a convienent environment for building data entry apps.. and
take your XML bullshit and shove it

-Aaron