How to use oledb in aspx page?

[jsteph]

Hi all,
So I'm new to .Net. I've got a process that I did successfully in a vb.Net WinForms app, using the System.Data reference and Oledb.DataReader, etc. It's simply reading a sql-server table.

I want to have this same functionality in a vb/Asp.net web app, and I don't know how to add the system.data reference. When I go to the Menu--Website--Add Reference, one of two things happens:

1. Nothing. Hourglass for a second, then no dialog box, no nothing.

2. I get the familar references dialog box, click System.Data, then close, but the system.data is nowhere to be found--not in Web.config, not in the aspx page, and I still get the errors that Oledb.OledbDataReader is not defined.

I have gotten the site to work using what I guess you'd call 'old school' Server.CreateObject(blah, blah). But I would like to conform to whatever the best practices are for .Net.

Thanks for any help,
--Jim

 

[ca8msm]

The System.Data namespace should be included in your project by default. Try typing "Imports System.Data" at the top of your code behind page and see if intellisense finds it. However, if VS is hanging when importing references you may have a larger issue to fix prior to starting your development.

Mark,

Darlington Web Design
Experts, Information, Ideas & Knowledge
ASP.NET Tips & Tricks

 

[jmeckley]

faq855-7190
if you have questions please ask.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jsteph]

Ok, after looking this over and about my 25th google search, I found this works:
<%@ Import Namespace="System.Data" %>

...at the top of the aspx page under the <%@ Page Language...%> tag.

It works perfectly, but my next issue may be found in jmeckley's link.

What I'm referring to is that this aspx page is used as the src of an iFrame. It is called via an ajax call in the 'Parent' page, which houses the iFrame.

So each time the user enters criteria in the parent page, it sends an xmlhttprequest to the server with that criteria, which loads the 'child' page (the one with the Connection and all the oledb stuff) as the frame contents--basically it's a simple read of a table generating an html table.

I'm not sure how Connection-pooling works in regard to this, but it seems a waste to create/open a new connection object every time that iframe is loaded--I want to have a session-scoped connection. Back to my 'old-school' ways, I used to have a global.asa file which opened the connection on Session_OnStart and saved the connection object to a Session Variable.

So the follow-up question is--does the iis/.net framework on the server somehow pool this connection even though a new page is requested? Or is there a way to open/save this connection at session scope in .Net? I'm not a master of the web.config file, but I assume it would be there where it might go, but something needs to execute to open it and handle errors, so I'm wondering how that is accomplished.

jmeckley's link mentions something similar but it's about a connectiion at a level below the Session, for my purposes (read-only, no transactions, etc) I think session scope is fine.

Thanks for any further help,
--Jim

 

[jmeckley]

you don't want to store connections in session. think about it. you only need access to the session for a few milliseconds, but a user will view the page for minutes. (people are much slower than computers) during those minutes the connection is not being utilized but the memory/resources are tied up. if enough people connect all the connections will be allocated in sessions and new requests will error.

by default session will expire 20 minutes after the previous request by the same user. Most people do not log out of web applications. they simply close the browser. that means a database connection will be unobtainable for 20 minutes.

there is also the issue of exception handling. if a sqlexception occurs you need to dispose of the connection and start over. starting over with session means resetting the users session (unless you want to mess with selectively resetting values in session... too much friction for me). By managing at the request, when an exception is thrown, tell the user an error occurred and try again. it fits the request/response model perfectly.

you may not need it for every single request, but if a majority of the requests do require a database connection this is a good approach to gain simplicity and maintainability. They go hand in hand.

creating the connection is cheap. it's the opening of the connection that becomes costly (remote call). This could be handled by adding logic to open the connect once the first command object executes. since the actual ado.net objects are wrapped in a custom implementation the logic could be placed here. to take it one step farther you could have the custom connection implementation create the IDbConnection when the first IDbCommand is built.

lazy loading connections and commands. This is how some data access frameworks work. you create the "gateway" to the database everytime. the framework will then handle the ado.net details waiting to the very last possible moment before the objects are created.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259