Using Connection Objects
Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013
This lesson describes how to use ADO.NET Connection
You typically interact with your database using connection objects, command objects, and data reader objects.
In order to do anything with a data, you need to establish a session with the database with an object called a connection. To connect to a specific data source, you use this data connection object which also provides access to a related transaction object.
First of all you will need to write a very simple program just to open and check a connection.
Try connection.Open() If connection.State = ConnectionState.Open Then ' ToDo: something End If '...
When you are finished with the database, you should call Close() to terminate the session, because database sessions are relatively expensive as they use resources on the client and the server side.
' prefered method of closing any open connection connection.Close()
TIP: Too many open connections slow a server down and prevent new connections to be made. So, leaving open connections when they are no longer needed is a bad practice.
ADO.NET comes with numerous data providers that allow you to communicate with a particular database management system so you have numerous connection objects to choose among:
You usually store the connection as shared function in a separate class:
Public Shared Function SqlServerConnection() As SqlConnection Return New SqlConnection("Data Source=SqlServerNameOrIP;Initial Catalog=DBName;Integrated Security=True"); End Function
Public Shared Function OracleConnection() As OracleConnection Return New OracleConnection("Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;"); End Function
Public Shared Function MsAccessConnection() As OleDbConnection Return New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBName.mdb;User Id=admin; Password=;"); End Function
Dim connection As SqlConnection = MyClass.SqlServerConnection
Note: Regardless of the fact that each connection object has a unique name, all connection objects implement a common interface named IDbConnection which defines a set of members that are common to all ADO.NET connection objects so you are guaranteed that every connection object supports members like Open(), Close(), CreateCommand(), etc.