About Datbase Connection in Project?

[dineshchirayil]

hi


I am doing one project which has ten or more forms and using access as database .I want to know about connect connection which is used in forms for insert and updating .now I am using OLEDBConnection. We wrote a Module for that. Like this

Imports ODS = System.Data.OleDb

Public con As New ODS.OleDbConnection()
Public Com As New ODS.OleDbCommand()


Sub ConnecttoDatabase()
con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" & Application.StartupPath & "\data.mdb"
Com.Connection = con
Try
con.Open()
'MsgBox("Successfully Connected.", MsgBoxStyle.Information, "Success")
Catch ex As Exception
MsgBox("This Error was occurred While Trying to Connect to Database. " & ex.Message)
End
End Try
End Sub

I open connection at startup and close when application ends.
I want know whether it is the Proper way or not
Some one told me that every insertion of data we have to open and after close the connection. I want to know which is the PROPER WAY to do this. And what is connection pooling ? Help me .....

[Shuja Ali]

.NET is built upon disconnected database access. .

I would open the connection only at times when it is necessary, like while updating the records, getting records from the database, etc. And when you use DataAdapters & dataSets, the built in functionaity of these objects is that they will only open the connection to the database when it is required. Otherwise the connection will be closed.

PS: I am not sure how much it is gonna help in Access Database, but when you use SQL Server/Oracle, this surely improves the performance of the application.

[poochi]

Check this link ADO.NET connection pooling explained (http://www.ondotnet.com/pub/a/dotnet/2004/02/09/connpool.html)