Please help me improve this class for accessing database data

Hi All

I've just written my first class that attempts to perform the following actions:
1. Connect to SQL Server 2000 using the connection defined in web.config
2. Execute a stored procedure (passed as a parameter) using any number of SQL parameters
3. Return the data as a dataTable back to the calling page

I need some really general advice from you experts to help improve it in the following ways if possible:
1. Reliability
2. Performance

Although it does work, it's probably quite badly written as to be honest I don't really know what I'm doing. Any help would be really appreciated!

Namespace myNS
Public Class dataGrabber

Private Shared myConn As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnString").ConnectionString)
Private Shared mySQLCommand As New SqlCommand
Private Shared myDataReader As SqlDataReader

Public Sub New(ByVal storedProcedureToRun As String)
mySQLCommand.Connection = myConn
mySQLCommand.CommandText = storedProcedureToRun
mySQLCommand.CommandType = CommandType.StoredProcedure
End Sub

Public Sub AddParam(ByVal paramName As String, ByVal paramDataType As SqlDbType, ByVal paramValue As String)
Dim tmpParam As New SqlParameter(paramName, paramDataType)
tmpParam.Value = paramValue
mySQLCommand.Parameters.Add(tmpParam)
End Sub

Public ReadOnly Property getData() As DataTable
Get
Dim myDataTable As New DataTable
mySQLCommand.Connection.Open()
myDataReader = mySQLCommand.ExecuteReader(CommandBehavior.CloseConnection)
myDataTable.Load(myDataReader)
mySQLCommand.Parameters.Clear()
mySQLCommand.Dispose()
Return myDataTable
End Get
End Property

End Class
End Namespace

With my expereince, I always make my data layers static, there is really no need to maintain class instances in the data layer, however there are exceptions depending on the requirements of your project.

In my data layers, I execute a command like so: MyDataLayer.ExecuteDataSet(cn, "SP_NAME", object[] args);

notice the last parameter is an object array that will hold the parameters for the stored proc.

If you want some good ideas and good practices, download microsofts data access application block and look through the code. They have some good code in there that uses caching for parameters and connections, which speeds up the datalayer.

Also take a look at patterns & practices on msdn. there is alot of good info on there.

You shouldn't make your class-level variables (myConn etc) shared. And if you want to return a DataTable you could use the DataAdapter rather than getting a Reader and loading it into a DataTable.
Thanks both for your help.

>> You shouldn't make your class-level variables (myConn etc) shared
I thought Shared just meant that other subs and functions in the class could access these once they had been declared. Obviously not. What is Shared therefore?

>> if you want to return a DataTable you could use the DataAdapter rather than getting a Reader and loading it into a DataTable
Could you please provide an example of this that would fit the code above?

Thanks again guys!
Shared means there is only one instance of the object that spans all classes. So if you create two instances of your data class they both share the same connection and command object. So if one user on your site is running SPA and at the same time another user is running SPB you will get unexpected results.

For the DataAdapter;

http://authors.aspalliance.com/aspxtreme/sys/data/Common/DataAdapterClassFill.aspx

Hi Aidy
From the examples I can see online, all use a dataAdapter to fill adataTable or dataSet. Therefore (from my basic understanding) I'dstill require the temporary dataTable in the class to pass back to the calling page, right?? On top of this,wouldn't the dataAdapter use more resources than the dataReader?

I'm using the class in my pages as follows;

Dim DBAccess As New myNS.dataGrabber("sp_getlogin") ' instance of class
DBAccess.AddParam("username", SqlDbType.NVarChar, loginControl.UserName) ' add param username
DBAccess.AddParam("password", SqlDbType.NVarChar, loginControl.Password) ' add param password
Dim loginTable As DataTable = DBAccess.getData() ' returns values from database to use for this user