Database access layer question

[Winla]

When I change someone's code, there is a middle tier data access class like this:

public Class DATAACCESS

Dim objConn As New SQLConnection(Connectstring)
..................................

Public Function OpenDataBase() As Boolean
objConn.Open()
End Function
public Function GetDataSet(ByVal strSQL As String) As DataSet
...........
end function
.......
end class

Then in Window presentation tier, whenever there is a need for data, the Code is :
dim DA as NEW DATAACCESS
DA.OpenDataBase
myDS= DA.GetDataSet(mySQL)

The program now runs very slow. I am wondering if SQLconnection in DATAACCESS class is part of the problem. For frequent data access cases, how to define middle dataaccess class to get efficiency?

Thanks for help

[salvelinus]

Where do you give a return value from your opendatabase function?

[Winla]

objConn is a private member of the DATAACCESS class.

[salvelinus]

That's great, and I don't know if it relates to your question, but the point is you have a function that is supposed to return a boolean value, but returns nothing. It should also be in a try/catch block.
Can't see why the code should slow you down, though.

Public Function OpenDataBase() As Boolean
Try
   objConn.Open()
   Return True
Catch ex as Exception
   Messagebox.Show(ex.Message)
   Return False
End Function

For a data access class, you may want to throw the exception instead, and catch it in the app layer.

[Winla]

Originally posted by salvelinus
That's great, and I don't know if it relates to your question, but the point is you have a function that is supposed to return a boolean value, but returns nothing. It should also be in a try/catch block.
Can't see why the code should slow you down, though.

Thanks for replay.

I just tried to simplify the coding. syntax is no problem here. The problem is that I have many places to call DATAACESS class which establish a new connection for each new instance. I guess that is the reason the program is runing slowly.
I need someone to give me comments and suggestions.

thanks

[salvelinus]

Do you close your connections when you're done? If you have a bunch of open connections, I can see it slowing you down.
.Net is currently optimized for connecting to a db, getting data, disconnecting from the db, letting the user do etc with the data, reconnecting to update a/o get more data, and so on.
You shouldn't have a bunch of open connections. If you need them (the example MS uses is an airline reservation system), .Net isn't your best choice at this time.

[Winla]

If you look at the original post, you should be able to see that in DATAACCESS class there is a connection open function for a new SQLconnection. If in a form load function there are three places to create DATAACCESS class instance, so there are three times of SQLconnection open and close, I don't know if this is a big issue for slow running.

[salvelinus]

Try reading my posts. I've read your first post several times to see if I missed anything. You have a function, not a sub, that returns no value, and doesn't have any error catching.
How do I know if you have a Close function in your class? I don't see it. You can open three connections in Form_Load, but they should usually be done sequentially, closing one before opening another.
I see that you can create x number of instances of the DATAACCESS class, but so what? I don't know if you can have several connections open at the same time, but if you can, it sure sound like a performance slowing issue.
I think that for filling datasets, if the connection isn't open, calling .Fill will open the connection, do its work, then close, but if the connection is already open, it won't close it (at least in VS 2002).
You should open each connection just to get the data you need, then close it. Open another connection to get other data, then close it. Work with your data, open the connection again to update changes, then close it.
Yes, this could create issues with multiple users working with the same data. See my previous post about that.

[VBCrazyCoder]

It is much more efficient to create your own collection and fill it than to fill a dataset (up to a point - 100,000 items+). Also, you might want to read up on connection pooling.

Another thing to try is using the Microsoft Data Application Block - you can replace your middle tier with this block and see if your performance is still slow.