developer.* Blogs - Using Data with ASP.Net - 10 of my 'Best Practices' - Comments

Storing data in database

kabilan — Wed, 25 Oct 2006 00:49:41 -0700

Hai,

I did a website in that if i post some information in 2 seperate line, during i view the post it display in single line. Am did this in asp and acess database.

Can you sugges me about this problem.

Using Close or Cancel

Guest — Sat, 16 Sep 2006 10:47:15 -0700

Dino Esposito in Programming ASP.NET 2.0 also echoes the author's suggestion about canceling rather than just closing. While Dino doesn't say the remaining records are read before the close occurs, he does say that resources become available much more quickly by using the suggestion.

If you can cache it think first

Edward G Nilges — Sun, 10 Sep 2006 08:10:14 -0700

See my post "Caching Considered Harmful."

Caching....

Terry Cornwell — Wed, 23 Aug 2006 00:55:41 -0700

If you can cache it then do it. Common lists (Treeview data, Combo Box Selections etc... ). If it doesn't change that often then Cache it on first retrieval (Or lazy load) then use that cache. DB round trips are slow, so if you can stick it away in memory somewhere then do it.

Thats my 2 cents worth anyway.

But do they?

Chris Gaskell — Thu, 27 Jul 2006 02:34:20 -0700

I understand where you are coming from on this, but do you include columns such as 'DateCreated' and 'LastModified' in your business DTO's?

Chris Gaskell, .NET & Web development Enthusiast

Apologies

Chris Gaskell — Thu, 27 Jul 2006 02:31:59 -0700

It seems my definition of a dynamic SQL query hasn't come across as I intended. Please see my example of a dynamic query below (I think you will find this matches your descripiton of a static query)

sql = @"SELECT Col1, Col2 FROM table WHERE id=" + id;

Thanks,

Chris Gaskell, .NET & Web development Enthusiast

Limit numbers of SQL Queries

Richard Jonas — Thu, 27 Jul 2006 01:11:07 -0700

Great list - I would add the following to it:

Create a limited number of SQL queries and store them in a common file, rather than letting them appear all over your code.

You might want to change your database later. This can cause problems if you are trying to find everything that may be affected by a change in your schema. If you store every SQL query in a common file, and limit the numbers of these, there will be less to change and less risk of your missing something important.

Problem with Best Practice #7

David Parslow — Wed, 26 Jul 2006 16:40:28 -0700

Dynamic SQL queries implemented as parameterized commands improve performance (supposedly better than a stored procedure, but there is some of debate about that) and reduce the chance SQL injection attack (because it is parameterized) while also making your code much more easier to maintain (when done through an OR-Mapper or like technique). It is the static SQL queries (tends to be very common) that suffer these kinds of problems.

Best Practice #10

Shahed Khan — Wed, 26 Jul 2006 16:37:38 -0700

To answer "and how often do you actually use all the columns?"
I would say those who uses Business Objects with a N Tier Model selects all the columns and fills the Business Object a lot.

Best Practice #6

Michel — Wed, 26 Jul 2006 07:45:03 -0700

The close method of the DataReader class continues to read all remaining rows before it finally
closes the object.

Are you sure? I thought reading was done only with the Read() method.

A Nice Collection

Daniel Read — Tue, 25 Jul 2006 16:06:47 -0700

I like these tips, and also agree with Phil's vote for the using block statement. A nice invention, that.

I especially like your #3, but would vote in favor of generalizing it a bit: let the database do what the database is good at.

Thanks for posting! Hope to see more, Chris.

Best,
Dan

Best Practices

Phil — Tue, 25 Jul 2006 13:41:34 -0700

With regard number 5, I'd go one step further.

Ensure you utilise the using statments.

Using Conn As New SqlConnection()

using (SqlConnection Conn = new SqlConnection())

This will ensure the cleanup of all resources.

Using Data with ASP.Net - 10 of my 'Best Practices'

Chris Gaskell — Mon, 24 Jul 2006 01:16:19 -0700

It can hardly be said that any serious programmer has had to deal with database programming at least some time in their careers. So it would be logical then to make sure your code to these underlying databases are as efficient as possible. Hopefully I will share some of the best practices I have learned in dealing with ADO.Net programming.