SQL Query from VS2005 - am I doing it right?

[johnc83]

Hi all,

I am working on my application and it's going quite smoothly but I can't help getting the feeling that I am doing my SQL queries inefficiently..

Example:

When leaving a textbox I would like to check that the value entered in that box is present in the column of my customers table. At the moment I have a stored procedure with this code...

SELECT     COUNT(*) AS Expr1
FROM         tblCustomers
WHERE     (Cust_Code = @Param1)

I have then attached this to my customers tableadapter in VS. This is then called from the textbox_leave procedure.

Should I be doing this query directly in the procedure instead of through tableadapter/stored procedure.

Any help/advice would be really appreciated.

Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Qamgine]

Without knowing the scope of the application, I would suggest that you only do these types of checks before an update / save / display through the interface.

Meaning: If your interface allows them to create a record in your database, don't do any error checking until the user clicks "Save,OK, etc". This puts all of the db bashing in a single module and it doesn't slow the user down when doing data entry.

You could also limit their selection by using ComboBoxes that only list the acceptable values for a given field. Meaning: You could create a combo box and fill it with all of the Cust_Codes. This would eliminate any checking after the user enters data.

Whichever method you choose, try to avoid bashing the database more than you need to. It slows down performance, and causes a lot of irritation with the end user. Less is more.

P.S. I always uses sprocs for my database calls. I never inject sql.

 

[TipGiver]

Hi,
this is what i do. I never sore queries in the db. I have a dll called e.g. "dbqueries" and i have looooong strings. This makes it easy to do a change and then distribute the dll only.
Anyway, if some of them are standard, store them in db.

The query shown here returns a number only. You can execute it and save the output n a datatable and then read the cell 0,0. Is is better not to do this, but to call the -if i remember correct- .executeScalar of the dataadapter.