Forum Moderators: open
I’ve been hanging around the .NET/ASP Forum for some time now and see a lot of questions revolving around database access and manipulation. Good coding standards around your database access will produce a truly fast, scalable application. Poor coding standards and you are going to fail if you grow, I promise. I run several ASP/SQL Server and .NET/SQL Server websites. In my beginnings with .NET, there was the frequent frustration of having to figure out how to get something done that used to be so easy. “Why did they move that!, Why do I need 5 ways to connect to SQL Server? Just tell me what is the best way and we’ll stick with it!” Hours of digging and multiple compiles and then it would work.. Next page, new features, same methods, right? Not necessarily. Be sure to use the right tool for the job.
There are two main Namespaces to utilize when accessing SQL Server, the System.Data.SQLClient and System.Data.Oledb. Although the Oledb client can connect and perform the same functions as the SQLClient, you will find yourself having to jump through a few hoops from time to time where the SQLClient has the functionality you need built in. Use the Oledb client when accessing other data sources such as Oracle.
A few other connection methods exist as well, but we’ll stick with the SQLClient, which is designed for SQL Server.
Error Trapping
I put this first because it is first in order of importance (and my pet peave). I cannot stress enough, proper error handling and reporting. When something goes wrong, you’ll know about it, hopefully.
Use the Try/Catch routines. The cascading errors are invaluable. Notice I catch two different error types. SqlException and Exception. This can help easily identify which section of code triggered the error.
Try
DBConnection.Open
….. Do some dataset stuff….. and close connection
DBConnection.Close
Catch sx as SqlException
strError = “SQL Error: “ & sx.ToString
‘LogOrEmailerror text here
Catch ex as Exception
StrError = “Runtime Error: “ & ex.ToString
‘LogOrEmailerror text here
End Try
Opening Connections and Using a DataReader
A convenient place to store your Connection String is in your web.config file. Add a section like the following:
<configuration>
<appSettings>
<add key="DBConnStr" value="SERVER=YourServer;Database=DatabaseName;UID=username;PWD=pw;" />
<appSettings>
You can then reference this key through the System.Configuration Namespace.
Here’s an example of a VB.NET Function that Opens a Connection, Loops through a recordset and Returns a value. (Realize the SQL query could simply SUM(Price*Quantity) for the Total but allow me to go at it the long way for the example of looping through a recordset.)
‘This example utilizes the following Namespaces you must Import at the Top of the Class.
Imports System.Data.SqlClient
Imports System.Configuration
Public Function GetOrderTotal(ByVal lOrderID as Long) as Double
Dim DBConn As New SqlConnection(ConfigurationSettings.AppSettings("DBConnStr"))
Dim sqlCommand As New SqlCommand()
Dim objReader As SqlDataReader
Dim dblTotal as Double
Try
DBConn.Open()
'Setup your Command Object
With sqlCommand
.Connection = DBConn
.CommandText = "SELECT PRICE*QUANTITY as ‘ItemTotal’ FROM CARTITEMS WHERE ORDERID = " & lOrderID
.CommandType = CommandType.Text
End With
'Populate the DataReader
objReader = sqlCommand.ExecuteReader()
While objReader.Read
dblTotal = dblTotal + objReader.Item(“ItemTotal”)
End While
‘Clean Up
ObjReader.Close
DBConn.Close
ObjReader = Nothing
DBConn = Nothing
Catch sx as SqlException
strError = “SQL Error: “ & sx.ToString
‘LogOrEmailerror text here
Catch ex as Exception
StrError = “Runtime Error: “ & ex.ToString
‘LogOrEmailerror text here
End Try
'Return the Result
Return dblTotal
End Function
OK, now you know how to pull back data in the most simplistic approach. I hope this provides the novice with some basic ideas to get started. .NET has some very powerful features that if used correctly will make you site stand out above the others. Used incorrectly, and you will end up with the masses of uninformed, uneducated that badmouth M$FT. Most people that want to slam .NET don't know how to properly implement a solution. Plan first, code later.
My favorite .NET Sites
[dotnet247.com ]
[asp.net ]
Use the site: function in Google to search MSDN: Google MSDN Search [google.com]
I hope to have a Part II soon. Any votes as to what areas of ADO people want to explore? Datasets, Using Stored Procedures, FOR XML output from SQL Server?
Good luck,
Trey
This is absolutely perfect. People like myself who haven't had the time (or the confidence) yet to learn the "correct" way to learn .NET should love this stuff.
I read about .NET all the time, and I know there's a right & a wrong way to do things. On the internet, it's hard to tell which is which. Here's my vote for you to continue to provide "tutorials"...
-Matt
P.S. as for a request: If you can stay on the ADO kick, that'd be great. So much of basic web development is inserting/updating/deleting info with a database. Datasets and SProcs are a suggestion for part 2.
