The connection object establishes a connection to the database. Two of the most common Connection objects used are OleDbConnection and SqlConnection. If we are querying a SQL Server database, we should use the SqlConnection class. The ConnectionString property of the Connection object is used to provide information such as data source and database name, that is used to establish the connection with the database.
Required Connection String Fields
For every connection string, we must specify the following.
Dim myConnection As SqlConnection = New SqlConnection(“Data Source=SYS1;Integrated Security=SSPI;Initial Catalog=FinAccounting”)
When we use a SqlConnection, you don’t have to specify a provider, because the class talks directly to the SQL Server API.
If we are using the OLE DB provider, the connection string is as follows:
Dim myConnection As SqlConnection = New SqlConnection(“Data Source=SYS1;Integrated Security=SSPI;Initial Catalog=FinAccounting; Provider=MSDAORA”)
The above string can be used to connect to an Oracle database through the MSDAORA OLE DB provider.
The Data source:
The Datasource is the location of the database or database server. For example, if your database server is on the same machine as your development environment, you could use the name localhost. If the database is remote, we must provide the name of the database server(SYS1 as mentioned above).
The Initial Catalog:
A catalog is the same thing as a database, so initial catalog refers to the name of the database we want to query. Examples are Northwind and FinAccounting.
We can pass an explicit user ID and password, or we can use the Integrated Security setting to specify that the connection is secure. The Integrated Security field accepts one of three values: True, False, or SSPI(Security Support Provider Interface). SSPI is essentially a buffer between your application and the various security protocols used by data and service providers, such as
databases or web servers. If integrated security is not supported, the connection must indicate a valid user and password combination. For a newly installed SQL Server database, the sa (System administrator) account is present without a password as shown below.
Dim myConnection As SqlConnection = New SqlConnection(“Data Source=SYS1;Integrated Security=SSPI;Initial Catalog=FinAccounting; user id=sa;password=”