Best practice for SQL connections and Asp.Net

Hi.

We have developed as quite simple ASP.Net webpage that fetches a number of information from a SQL 2005 database. We are having some problems though, becuase of a firewall that is beetween the webserver and the SQL server, and I think this is because of bad code from my part. I'm not that experiensed yet, so I'm sure that there is much to learn.

Usualy when I do a query against a SQL database, I do something like this:

Function GO_FormatRecordBy(ByVal intRecordByAs Integer)
Dim dbQueryStringAs String
Dim dbCommandAs OleDbCommand
Dim dbQueryResultAs OleDbDataReader

dbQueryString ="SELECT Name FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"
dbCommand = New OleDbCommand(dbQueryString, dbConn)

dbConn.Open()

dbQueryResult = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
dbQueryResult.Read()

dbConn.Close()

dbCommand = Nothing

Return dbQueryResult("Name")

End Function

Now, lets say that I have a DataList that I populate with Integer values, and I want to "resolve" the from another table, then i do a function like the one above. I guess that this means that I open and close quite alot of connections against the database server when I have a large tabel. Is there any better way of doing this? Chould one open a database connection globaly in lets say the ASA fil? Whould that be a better aproch?

When I added the CommandBehavior.CloseConnection to the ExecuteReader statment, I noticed that it was a bit faster, and I think there was fewer connections in the database, so maby there is more to the "closing connections" then I usualy do.

Any tips on this?

Best reagrds,
Johan Christensson

The thing with DataReaders is that you have to explicitly close them when you have finished with them. The CommandBehaviour.CloseConnection option makes sure that the connection is closed when you close the DataReader.

Since you are only retrieving one value in the example you have shown, you should use ExecuteScalar(), which doesn't need a DataReader. That would be the most efficient way of accomplishing this particular task.

Also, in answer to your question about global connections, forget you ever thought about it. That is a very poor idea. It forces everyone who accesses your app to use the same connection, and as traffic increases, delays occur as requests are queued.

I don't quite understand what you mean when you talk about resolving records from another table, but my instinct is that nested datalists or join queries might be a better way to go.

|||

Thanks for your awnser.

What I mean with "resolving records fro another tabel" is that lets say that I have one table that consists, in this case of a SiteID and a TechnicanID, when the DataList is generated, I invoke a function where the output in the DataList is looked up in another table and resolved as a name. I guess that this is quite stupid, since I guess that it would generate an extra query, in this case two querys for each row in the datalist. Right now I have a datalist/asp page that generated about 400 connections to the database, and because of this, the firewall blocks the webserver.

I guess that this would better be done using some kind of SQL statement, but I'm not sure on how. Lets say that I have the following SQL string:

dbQueryString ="SELECT SiteID, TechnicanID FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"

And I want the values from SiteID and TechnicanID to be the result from a query in another table, how do I do this?

Best reagrds,
Johan Christensson

|||

I think this will work for you:

dbQueryString = "Select tblSites.SiteName, tblTechnicians.TechnicianName from tblRegistrators inner join tblSites on tblRegistrators.SiteID = tblSites.SiteID inner join tblTechnicians on tblRegistrators.TechnicianID = tblTechnicians.TechnicianID where tblRegistrators.ID = '"&intRecordBy&"'"

you may want to look up some information on Joins (Inner and Outer primarily) they make life much simpler, and more complicated at the same time. Trying to sort out queries with upwards of 20 joins is one of the reasons I have so much grey hair

Hope this helps

-madrak

|||

Madrak has given you a solution using Joins in your SQL as I suggested earlier, but another approach could be to select all from the SiteID and TechnicianID tables into separate DataSets on one connection. Once the dataset has been populated, you can close the connection and work with it in a disconnected fashion, looping though it to your heart's content.

You may well have trouble getting to grips with Joins to start with, so I suggest that you make use of the Query Designer in SQL Management Studio (Express) to write the query for you. Simply add the 3 tables to the designer, then drag foreign keys onto primary keys to create relationships eg SiteID in tblRegistrators would be a foreign key and should be dragged to SiteID (primary key) in what I presume might be called tblSites. As soon as you do that, you will see that the textual part of the query builds the default inner join into itself.

But in essence, your instinct is correct. Hammering a database repeatedly in a loop is poor practice - especially if you aren't closing DataReaders correctly

|||

After some googeling I figured out that I should do some kind of nested statment, but the anwser from Madrak actualy was in a format that I understod it. :) Not only does it work, is fast as lightning. It took som trial and error in teh Query Designer before I got hold of it. The way I have done this previusly, always was very slow, so I have some tweeking to do i guess on some other webpages.

So thanks a milion.

A followup question on Mikesdotnetting awnser:

What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?

Thanks again.

Best regards,
Johan Christensson

|||

JohanCh:

A followup question on Mikesdotnetting awnser:

What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?

Oh, without a doubt I would use Joins in my SQL. I'm a firm believer in making as few requests of the database as possible. The Join approach requires just one request. DataSets require 3. You might think 3 is nothing compared to the 35,000 you were making (), but I look at it as 300% more than I need. And it's a lot of code less too.

Best method of doing Connection Strings

I am using SQL 2000 sp3a on Windows 2000 sp3. I have developed an Intranet application using asp.net/vb.net. Currently my connection string is:

data source=intraweb1;initial catalog=ASGWEB;password=blahblah;persist security info=True;user id=justauser;packet size=4096

So all my users are coming in with one SQL database id. Is this the best method for a combination of security and performance?

I do not allow anonymous to the website so I was thinking of setting up an application role and putting the domain users account in it. But from some other threads I was reading this does not work well with connection pooling.> Is this the best method for a combination of security and performance?

yeah, that's fine. I hardly ever do it otherwise - it's not fine-grained security-wise, but do you need it to be?

as for the connection pooling thing, yup - connection polling makes a pollfor the user id, so with multiple users you'd probably lose the beneficial effects, besides needing more CALs|||::besides needing more CALs

Using onedb server is does NOT save you CAL's. Read the licensing condition. You still need one CAL for every user. They say user - NOT user id. This is actually extremely clear, especially in the descriptions and comments.|||I had a discussion about this recently, and the concensus seemed to be one Device Access license for IIS to grab data if you're using one user ID. licencing is a nightmare though, and don't claim to be an expert on it by any means. I usually just ask MS whet the deal is and get multiple answers (!)

Beginner- Need Help With SQL Server 2005 Express

I am an absolute beginner with SQL Server, and I have now developed an app that needs to store, read, and search for saved data. Basically, my program will have to search through the databse to find a particular URL match to a string I supply, and then retrieve the Username and Password data that is associated with that URL, storing those to strings.

My problem is, I've learned how to create a database and tables in the IDE, but all of the examples on the web that I've found only talk about using SQL Server with Visual Basic 2005 in uses such as databinding to controls and displaying the database in datagrids and such on the form. I need help with how to interact with the database in code, seeing as the user of the program will likely never actually see the database contents- the data will be for the program's use only.

So, I was wondering if anyone could provide/guide me to some helpful information on interacting with SQL databases through code in VB Express 2005.

Thanks for any help.

In your VB code, define variables, and then instead of assigning data to control.text, assign data to the variables.

|||OK, but then how do I search through all of the fields in 1 column, and then if I find the right one, extract the adjacent columns for that field and save them as strings?