How to use SQL from asp.net?

Question

Building a relatively simple website, and need to store some data in the database (mainly authentication, but there's some per-user stuff). I've worked on a couple of websites previously, and used database there too, but never liked the way I accessed the database.

The way I usually did this was by having a SqlMethods.cs, which basically was a static class with a whole lot of static methods such as bool CheckUserExistence(string username, string password) and SqlDataReader GetJobListings(int advertiserId), each of which was essentially "open connection, call a sproc, return what it returns". This approach seems un-natural to me, however. I cant quite put my mind to what I want it to look like, but this seems...weird.

So, my question is this: how do you access the database from your asp.net projects?
I am using SQL2005. I also dont think I'll need an ORM of any kind, since there really isnt that much to get from the DB, but maybe its easier with one? Suggest something. Thanks!

Edit:I currently decided to just create a static class Sql that will have a number of sql-related methods (such as ExecuteReader(sprocName, params[]), etc) that will call the sproc with the given parameters and just return the DataReader.
Then, have classes for specific functionality, such as Authentication with methods like CheckUserExistence(username, password) and LogoffUser(session). These methods would just call Sql.ExecuteReader("sp_Auth_CheckUserExistence", _some_params_here_) and process the result as needed.
I don't know if thats good or bad, but it seems to work for me at the moment. Plus, I like the way it feels - its all nicely separated functionality-wise.

So, any more suggestions?

Answer 1

Check out Linq to SQL or Linq to Entities:

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx
http://msdn.microsoft.com/en-us/library/bb386964.aspx

Answer 2

The simplest way to do it is to create a data access class for each table in your database. It should have private variables and public properties for all of the columns in the table and the methods you describe should fill the internal variables (or use them to update the database).

Public Class MyTable
    Private _id As Integer
    Private _Name as String

    Public ReadOnly Property ID As Integer
       ' Regular stuff here
    End Property

    Public Property Name As String
        ' Regular stuff here
    End Property

    Public Sub Load()
        ' Call SQL and get a data reader.
        ' Set _id and _Name from the data reader.
    End Sub
End Class

Then, in your other code:

Dim mt As New MyTable
mt.Load()
' Now use mt.ID and mt.Name to access the data