|
|
The Connection object provides all the means of connection to the database on which a desired transaction is to be made. This is the first object that any developer comes face-to-face with while trying to use the .NET Data Provider. Before any commands can be executed on the database, a successful connection to that particular database must be established using the Connection object.
Type: String
Attribute: Read/Write
Default: “”
Description: The ConnectionString property of the Connection object defines a valid connection string to the database with which the Connection object is to connect. The string usually contains parameters necessary to connect to the data source. The string is usually a semicolon-separated parameter list, with each parameter set to its appropriate value:
Provider: Specifies the OLE DB provider through which you wish to connect to a data source. You need to specify a provider only when you wish to connect through the OLE DB .NET Data Provider. This parameter does not have to be specified when connecting through the SQL Server .NET Data Provider.
Data Source: Specifies the name of the database server that is hosting the database to which you wish to connect
Initial Catalog: Specifies the name of the database to which you wish to connect. The database must be located on the Data Source database server.
UserID: Specifies a valid user ID for a user who has access to login to the database server specified by the Data Source parameter
Password: Specifies the password for the user specified in the UserID parameter
A typical connection string would look like this:
Connection.ConnectionString = "Provider=MSOLAP;DataSource=
LOCALHOST;UserID=sa;password=sa"
It is good coding practice to prepare the value of the connection string in one place. A typical scenario is to store the value inside a string variable and then use that variable to set the value of this property.
Type: Integer
Attribute: Read-only
Default: 15
Description: The ConnectionTimeout property is an integer value that specifies the number of seconds that the Connection object should wait for a connection to be established to a server before generating an error.
Type: String
Attribute: Read-only
Default: “”
Description: The Database property obtains the name of the database to which the Connection object is connected.
A typical use of the Database property is when you wish to show a user which database is currently being used.
Type: String
Attribute: Read Only
Default: “”
Description: The DataSource property obtains the location and filename of the data source to which the Connection object is currently connected.
A typical use of the DataSource property is when you wish to show a user which data source is currently being used.
Type: String
Attribute: Read-only
Default: “”
Description: The Provider property obtains the current OLE DB provider through which the Connection object is connecting to the current data source.
A typical use of the Provider property is when you wish to show a user which provider is currently being used for transactions to a particular data source.
Type: String
Attribute: Read-only
Default: “”
Description: The ServerVersion property obtains the version information of the database server to which the client is connected.
A typical use of the ServerVersion property is when you wish to show a user the version information for the particular server to which the Connection object is connected.
Type: ConnectionState enumeration residing inside the System.Data namespace
Attribute: Read-only
Description: The State property obtains the current state of the connection to the database server. Possible values are:
Open: The connection to the server exists and can be used to issue commands to the database.
Closed: The connection to the server does not exist and cannot be used to issue commands to the database.
A typical use of the State property is when you wish to test whether a valid connection exists to the data source before performing any operations. This is a solid mechanism to avoid errors in your applications.
Call this method to perform any transaction on a database. You must call the Commit() or Rollback() method after the transaction has been made.
Changes the database for an opened connection. The database must reside on the same server as the previous database.
This method is used when you wish to change the database that you are working with. It is a very convenient way to manage memory because you are not creating a new connection.
This method is used when you wish to create a new Command object. After creating the Command object, you can start issuing SQL commands to the database to which the Connection object has an open connection.
This method will always return False if you pass an object that is not of the same type as the Connection object. It is useful when you need to test whether two Connection objects have the same type of connection.
This method returns the System.Type value for the Connection object. It is usually the class name of the object.
In this section you will learn to connect to an SQL Server database using the SQL Server .NET Data Provider. This is simply to have a taste of how this provider implements its flavor of the Connection object differently from the generic Connection object.
The following code assumes that you are referencing the System.Data.SqlClient namespace that holds all the logic for the SQL Server .NET Data Provider.
The Connection object and connection string are declared:
Dim objConn As New System.Data.SqlClient.
SqlConnection()
Dim strConn As String
The connection string is initialized. Notice that all you have to specify in the connection string is the server name, the initial catalog, and the login information.
strConn = "Data Source=LOCALHOST; Initial
Catalog=Northwind; UserID=sa;Password=sa"
In the last section of the code, the ConnectionString property of the Connection object is set to the value of strConn, and the object attempts to open a connection to the database server by invoking its Open() method.
With objConn .ConnectionString = strConn .Open() End With
These are all the steps that are required to open a connection to an SQL Server database. The process is the same for other OLE DB data sources, except that you have to specify an OLE DB provider as the Provider parameter in the connection string.
|
|