ADO.Net Direct Connect or In Memory Tables?

[jmeckley]

I am exploring ASP.Net and in the up comming months I will be designing appliations with it (SQL Server 2000 back end). The applications will be for intranet use only. Staff will not be able to access them outside the office.

The help files and tutorials say that with ADO.Net the system creates an instance of the table which is stored in memory. From this table code is generated that can select, insert, update and delete from the table. The table is then sent from memory to the database (don't quite understand how that happens yet, so I understand that it just does.)

If my understanding of ADO.Net is correct so far would it be better to load individual tables in memory and then create relationships and allow the system to write S,I,U and D statements for me? Or would it better to write custom select statments and then write custom I, U and D statements. and execute against the database directly?

I understand that there are many factors that determine which method is best. I would concider the best method to be the one that takes the least amount of time to develop and the fastest access for users. (loading tables into memory).

Thank you for any advice on the subject.

Jason Meckley
Database Analyst
WITF

 

[jfrost10]

Hey Jason,

The virtual tables mentioned in the documenation are DataTables (which can be stored in DataSets).

From my experience, storing huge tables in memory is NOT the way to go. Way to much overhead, and its not very difficult to write simple update/insert/delete/select code to manipulate the data.

Even within an intranet, if you have enough users creating virtual tables in memory, your web server is going to take a beating.

D'Arcy

 

[kaijanm]

I agree with D'Arcy on this.

Also, I've found it very confusing to set up the relationship in ASP, whereas SQL Server makes that super easy. It's so easy to set up stored procs on SQL Server and then you have only one place to look for making changes to data access.

My experience says that you should let SQL do all the things it's good at and then just use calls to it where you need to.

Kimberly

 

[jmeckley]

Thank you for the advise. It has been very helpful

Jason Meckley
Database Analyst
WITF

 

[Turchan]

Jason,
You may want to look at Microsoft Application Blocks (

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp)that

is distributed royalty free by Microsoft. If you use that you can benefit from using Dataset. It acts as a virtual databse that can hold more that one table and you can access records in any of them. I do use datasets in my applications however whatever I can move to back-end I try to do that.
Kevin.