Database Connection Function

[momo]

Hello to all,

I need some help. I am looking for a database connection function that I can
use in my ASP.NET site. This would be in VB.NET. What I want to be able to
do is call the function and it creates the connection and I can use it in my
site. I want to pass the function some values and it uses it. Please take a
look at what I have started with and help me how to get it right.

Function OpenMsAccessOledbConn(ByVal dbPath As String, ByVal dbID As String,
ByVal dbPassword As String)
Dim dbConn As OleDbConnection
Dim dbConnStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"User ID=" & dbID & ";" & _
"Password=" & dbPassword
dbConn = New OleDb.OleDbConnection(dbConnStr)
dbConn.Open()
End Function

When I call the function this is what I will use:

call OpenMsAccessOledbConn("db1.mdb", "admin", "admin")

Tell me also if there is a better way for doing this.

Thanks in advance.

 

[Karl Seguin [MVP]]

I don't much care for this approach.

Connections should be opened as late as possible and closed as early as
possible.

Having a function open the connection means it isn't being opened as late as
possible, and makes it that much likelier that it won't be closed.

Also, string.format() is ur best friend...


public shared function GetMsAccessOldebConnection(path as string, user as
string, password as string) as OleDbConnection
dim connectionString as string =
string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};...", path);
return new OleDbConnection(connectionString);
end function


Karl

 

[Jason Hales]

It's been a while since I've looked at VB.NET but it looks to me that
all your function is doing is opening an OleDbConnection but you aren't
returning it out of your function - so you won't be able to use it to
pass SQL to Access.

It might be best to declare it as returning an OleDbConnection object
and return out dbConn

Also you might want to use
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User
ID={1};Password={2}", Path , dbID, dbPassword) to make the
concatenation of connection strign faster and easier to rad

 

[sloan]

I agree with Karl.

This method has a vb6 flavor to it. I can't "prove it", I'm just offering
an opinion.

I would also suggest looking at the EnterpriseLibrary.

The gist of the EnterpriseLibrary is that alot of developers have the same
types of need. One of them: DataAccess.

You can get the EnterpriseLibrary for 1.1 or 2.0.

If you get the 1.1 version, you'll need to do some additions for OleDB

http://www.gotdotnet.com/Community/...mpleGuid=137F13F8-AA21-4042-BAB1-3781DF71DCDD

...

1. Get the 1.1 EnterpriseLibrary
2. Make the additions/slight mods to incorporate the OleDB provider
3. Learn how to effectively use the EnterpriseLibrary

the start up costs are well worth it.

 

[momo]

Thanks Karl for your help. I am new to ASP.NET. Can you tell me how you
would used this function in your code.


"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
net> wrote in message news:[email protected]...

 

[Karl Seguin [MVP]]

Well, you'd do something like:

dim connection as OleDbConnecton
dim command as OleDbCommand
....set up ur command
try
connection = GetMsAccessOldebConnection()
connection.Open()
command.ExecuteNonQuery()
finally
if not connection is nothing hten
connection.Dispose()
end if
if not command is nothing hten
command .Dispose()
end if
end try



--
http://www.openmymind.net/
http://www.fuelindustries.com/

Thanks Karl for your help. I am new to ASP.NET. Can you tell me how you
would used this function in your code.


"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
net> wrote in message news:[email protected]...

 

[momo]

Okay, here is what I understand so far.

Sub GetMsAccessOledbConnection(ByVal dbPath As String, ByVal dbID As
String, ByVal dbPassword As String)
Dim dbConn As OleDbConnection
Dim dbConnStr As String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};User ID={1};Password={2}", dbPath , dbID, dbPassword)
return new OleDbConnection(dbConnStr);
End Sub

dim CONNdb as OleDbConnecton
dim CMDdb as OleDbCommand
Dim strSQL as String
strSQL="select * from table"
CMDdb=New OLEDBCommand(strSQL,CONNdb)

try
CONNdb = GetMsAccessOldebConnection("db1.mdb", "admin", "admin")
CONNdb.Open()
CMDdb.ExecuteNonQuery()
finally

Tell me what may be wrong with it

Also explain this code and please write it out for me.

if not connection is nothing hten
connection.Dispose()
end if
if not command is nothing hten
command .Dispose()
end if
end try

thanks again

"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
net> wrote in message news:[email protected]...

 

[Karl Seguin [MVP]]

Well, ExecuteNonQuery() is used for Deletes/Updates/Inserts

you'll either want to use an DataReader with ExecuteQuery() or fill a
DataSet/DataTable which'll require an Adapter..

the OleDbConnection and OledbCommand objects implement the IDisposable
interface. This interface defines the method Dispose() which should be
called to clean up any resources.

Before we call Dispose() on both the command and the connection, we make
sure they aren't null/nothing...if they are null/nothing, calling Dispose()
will only 'cuz an error.

Karl