What is the Connection Object ?

Whether you are working in connected or disconnected mode the first thing you have to do is to create a connection.
You can create a connection to a database by creating a Connection object.
This object is responsible for establishing a connection to the data source.

Dim objDataConnection As System.Data.SqlClient.SqlConnection 
Dim objDataConnection As System.Data.OleDb.OleDbConnection

SqlConnection

The connection string usually contains the following:

objDataConnection.ConnectionString =  "Data Source=(local);User ID=sa;Initial Catalog=pubs" 

objDataConnection.ConnectionString = "Server=betsol01;Database=prod;Trusted_Connection=True;"

OleDbConnection

The connection string usually contains the following:
Provider - Specifies the name of the provider you are using to connect to the database.
Data Source - Specified where the database is. It can be a folder path or the name of the machine where the database is located.
User ID and Password - Specifies a valid user name and password.
Initial Catalog - Specifies the name of the database when you are connecting to a SQL Server or Oracle database.

objDataConnection.ConnectionString =  "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
                                     "Password=;User ID=Admin;" & _
                                     "Data Source:=C:\Temp\Testing.mdb"

objDataConnection.ConnectionString = "Server=localhost;Uid=sa;Pwd=;Database=norwind"
objDataConnection.ConnectionString = "Server=localhost;Uid=sa;Pwd=;Database=norwind;Connection Timeout=30"

Creating a Connection Object

Dim objDataConnection As System.Data.OleDb.OleDbConnection 
objDataConnection = New System.Data.OleDb.OleDbConnection
objDataConnection.ConnectionString = "connectionstring"
objDataConnection.Open

Alternatively you could pass the connection string directly to the constructor.

Dim objDataConnection = New System.Data.OleDb.OleDbConnection("connectionstring") 
objDataConnection.Open

Closing a Connection

objDataConnection.Close 

Properties

ConnectionStringThe string used to connect to the database.
ConnectionTimeoutReturns the number of seconds after which an unsuccessful connection will time out as specified in the ConnectionString property. The default is 15 seconds. This is read-only.
DatabaseReturns the name of the database as specified in the ConnectionString property.
DataSourceReturns the name of the data source attribute as specified in the ConnectionString property.
ServerVersionReturns the version of the connected server.
StateReturns the current state of the database. Can be one or more of the following ConnectionState enumerated values:
Closed, Connecting, Open, Executing, Fetching, Broken.

Methods

BeginTransactionBegins a database transaction.
ChangeDatabaseChanges the name of the database for the current connection.
CloseCloses the connection and releases all related resources.
CreateCommandCreates a Command object related to the current connection.
OpenOpens the connection.

Events

InfoMessageFires when the database or the provider sends either an warning or an information message.
StateChangeFires when the State property changes.

Changing the Timeout

The ConnectionTimeout property is readonly but can be changed by including a different value in the connection string.

objConnection.ConnectionString = "Provider=SQLOLEDB; 
                                 Persist Security Info=False;
                                 User Id = sa;
                                 Initial Catalog = dbName;
                                 Data Source = localhost;
                                 Connect Timeout = 20;


© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext