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

Please help me with this connection to the database problem!

hello everyone!
I am very new to the ASP.NET, lately I have been practicing about accessing data through Access 2002 file with ASP.NET. At the beginning, it really gave me a headache. here is my problem:
My goal is going to store User's Username, password and repassword into a db file called userDB.mdb
the project has two .aspx files as WebForm1 and 2
--------------------------------
//here is the code for the WebForm1:
(i will only provide those code-behind code, just make the message clear, if anyone needs the html code, please let me know)
--------------------------------
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

namespace uncos
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label t_Username;
protected System.Web.UI.WebControls.Label t_Password;
protected System.Web.UI.WebControls.Label t_Repassword;
protected System.Web.UI.WebControls.TextBox tb_Username;
protected System.Web.UI.WebControls.TextBox tb_Password;
protected System.Web.UI.WebControls.TextBox tb_Repassword;
protected System.Web.UI.WebControls.RequiredFieldValidator val_Username;
protected System.Web.UI.WebControls.RequiredFieldValidator val_Password;
protected System.Web.UI.WebControls.CompareValidator val_Repassword;
protected System.Web.UI.WebControls.Button Button1;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void Button1_Click(object sender, System.EventArgs e)
{
if (tb_Password.Text == tb_Repassword.Text)
{
Response.Redirect(@dotnet.itags.org."WebForm2.aspx");
}
}
}
}

============================================================
//here is the code for the WebForm2:
============================================================
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Collections.Specialized;

namespace uncos
{
/// <summary>
/// Summary description for WebForm2.
/// </summary>
public class WebForm2 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;

private void Page_Load(object sender, System.EventArgs e)
{
NameValueCollection dataColl;
dataColl = Request.Form;
string tbUsername = dataColl["tb_UserName"];
string tbPassword = dataColl["tb_Password"];
string tbRepassword = dataColl["tb_Repassword"];

OleDbConnection cnn = new OleDbConnection(@dotnet.itags.org."Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\userDB.mdb");
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "INSERT INTO User_Table(User_ID,Password,Repassword) VALUES('"+tbUsername+"','"+tbPassword+"','"+tbRepassword+"')";
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;

try
{
cnn.Open();
cmd.ExecuteNonQuery();

}
catch (Exception ex)
{
Label1.Text=ex.ToString();
}
finally
{
if(cnn.State == ConnectionState.Open)
{
cnn.Close();
}
}

}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
}

============================================================
//It has runtime error.

//the error message is:
============================================================
System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file 'c:\userDB.mdb'. It is already opened exclusively by another user, or you need permission to view its data. at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at uncos.WebForm2.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\uncos\webform2.aspx.cs:line 40

============================================================
I have been doing lots of reserch to solve this problem, but none of them work.
I don't have the problems of file or folder sharing, all the accounts on my PC has the full control to that .mdb file.

.mdb file is not in the Inetpub folder as well, I put it under C:\, just in case the security permission problem occurs.

All the programs who can access that .mdb file are closed before I test the page.

I can't find anything else could cause the problem.

Please help me!!! Cheers!!!!

LeeMake sure that your account that runs ASP.NET applications has sufficient security privledges to the directory that the Access database is in.
Thanks for your advise, I am using the Administrater Account on my PC (it's XP), I have give this account full control of the C driver. But nothing changes.

Lee
No, the asp.net worker process uses an account (probably a user named ASP.NET on XP).. give this user permission to the directory, not the user you are logged in with.
Thanks again!!!

I have got four accounts on my PC.

they are :

MyName(Computer Adminstrater)

SQLDebugger(Limited Account; Password protected)

aspnet_wp account(Limited Account; Password protected)

Guest Account ( Guest Account is off)

at the moment I am in (MyName) account. did you mean that i need to login (aspnet_wp) account and give the permission to the folder and file? or other accounts maybe

cheers!!

Lee
I have reinstalled SDK1.1, it was 1.0. now that permission problem has solved:)

but i have got another problem, this time when i run the application, the errors like:
========================================================
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at uncos.WebForm2.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\uncos\webform2.aspx.cs:line 41
=========================================================
the "Insert Into" is in the WebForm2.aspx

thanks for your guys have been help me!! Cheers

Lee
Please do a research on "Sql Injection Attack". Your code is wide open to this type of attacks.
thanks bleroy, i think i really learned a lot here.

this project won't upload to any server, it is just a practice made by myself. my goal is just insert data into database file from user's input. i didn't really put very much consideration on the security issues. but your advise is valuable for me, thank you very much! Cheers!

i have changed the syntax a little bit, but the problem is still the same.

there is one thing i don't understand, if it is a syntax error, why when i compile it , it didn't give the error warning?

Cheers!

Lee
Thanks for your guys. I had solved my problems. :) I changed the code quite a lot. and it's simplier. it works now,

Cheers!