Connection object allows you to:
- Configuring a connection (connection / relationship)
- Create and menterminasi session / part with the data sources
- Identify an Ole DB provider
- execute a query
- manage transactions on the open connection
- Choosing a cursor library is available to data provider
ADO can use the Ole DB provider for any make a connection. Provider is specified through the property owned by Provider from Connection object. In general, you should use SQLOLEDB to connect to the property owned by Provider Connection object from this, although you can also use the MSDASQL. If no provider is specified, use ADO by default MSDASQL its
Using the Execute method of Connection object is one way to execute a SQL statement instead of using a SQL Server datasource
There are several differences in property Connection between MSDASQL and SQLOLEDB. Detailed information about properties owned Connection you can see from the Microsoft MSDN ™ Library, Microsoft Visual Studio ® version 6.0, or the section "About ADO" on this site (red)
As MSDASQL, SQLOLEDB allow you to use a connection string to specify parameters separately from Connections. If you will use these parameters separately, see the following coding:
- Use the Initial Catalog property to specify a database
- Use the Data Source property to specify the server name
- Use the Integrated Security property, set to a value of "SSPI", to specify the Windows NT Authentication (Authentication from Windows NT)
If you write a connection string to be used with the SQLOLEDB:
- se keywords Initial Catalog or Database to specify a database
- Use the keyword Server or Data Source to specify the server name
- se the keyword Trusted_Connection, set the value to "yes", to specify the Windows NT Authentication
To specify SQL Server Authentication, use the keyword UID or User ID for the user (user) (sa for example) and a keyword or password pwd password for it
Note: SQLOLEDB does not allow multiple connection. Unlike MSDASQL, SQLOLEDB does not try to re-connect when the connection is blocked
Example:
A. Use SQLOLEDB to connect to SQL Server: setting properties independently (individual properties)
Source code below is taken from the introductory Visual Basic ADO Sample, show how to use Microsoft Visual Basic to connect to a database in SQL Server with SQLOLEDB
'Inisialisasi variabel
Dim cn As New ADODB Connection
. . .
Dim ServerName As String, DatabaseName As String
Dim UserName As String, Password As String
'Tampung isi dari textbox ke dalam variabel connection.
ServerName = txtServerName.txt
DatabaseName = txtDatabaseName.txt
UserName = txtUserName.txt
Password = txtPassword.txt
'Spesifikasikan OLE DB provider.
cn.Provider = "sqloledb"
'Set SQLOLEDB property-property Connection.
cn.Properties("Data Source").Value = ServerName
cn.Properties("Initial Catalog").Value = DatabaseName
'Periksa untuk tipe otorisasi login: WinNT atau SQL Server.
If optWinNTAuth.Value = True Then
cn.Properties("Integrated Security").Value = "SSPI"
Else
cn.Properties("User ID").Value = UserName
cn.Properties("Password").Value = Password
End If
'Buka database.
cn.Open
B. Use SQLOLEDB to connect to SQL Server: connection string method.
Source code snippet below shows how to connect to a database in SQL Server using the SQLOLEDB
'Inisialisasi variabel
Dim cn As New ADODB Connection
Dim provStr As String
'Spesifikasikan OLE DB provider.
cn.Provider = "sqloledb"
'Spesifikasikan connection string pada method Open.
ProvStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes"
cn.Open provStr
C. Contact Using MSDASQL for SQL Server
If you need to use the MSDASQL SQL Server, there are two primary ways here to make a connection. The first, based on the function of the SQLConnect ODBC API. This connection type is useful in the case where you do not want to specify coding information on data source. This may be problematic if the data source may be changed or if you do not know the exact data
In the following snippet of source code, ConnectionTimeout method to set the value of time-out from the connection to 100 seconds. Next, the data source name, user ID, and password as a parameter to miss the Open method of the Connection object, use the ODBC data source named "MyDataSource" which refers to the database "pubs" on a SQL Server. Login ID "sa" is provided as a second parameter, and "Password" is the third parameter.
Dim cn As New ADODB.Connection
cn.ConnectionTimeout = 100
' DSN connection. You can use variables for the parameters this.
cn.Open "MyDataSource", "sa", "MyPassword"
'Alternative syntax like the following:
'cn.Open "DSN=DataSourceName;UID=sa;PWD=Password;"
cn.Close
The second method of opening a connection through the MSDASQL is based on the function SQLDriverConnect. Type of connection is useful in situations where you want a driver from the specified connection string. To make a connection, use the Open method of the Connection object and specify the driver, the server name, user ID, password, and database.
Dim cn As New ADODB.Connection
'Connection ke SQL Server tanpa menggunakan ODBC data source
cn.Open "Driver={SQL Server};Server=Server1;Uid=SA;Pwd=;Database=pubs"
cn.Close
D. Using the Connection object to execute the commands
The following example shows how to use the Execute method of Connection object to execute the commands.
Dim cn As New ADODB.Connection
. . .
Dim rs As New ADODB.Recordset
cmd1 = txtQuery.Text
Set rs = cn.Execute(cmd1)
After the Connection and the Recordset is created, the variables in the cmd1-assign the value of a query string entered by the user (txtQuery.Text) from a Visual Basic form. Recordset in next-assign the results of a query, issue the Execute method of Connection object, the variable cmd1 used as a query string parameter
E. A Transaction Set
The following example shows how to use the transaction method BeginTrans, CommitTrans, and RollbackTrans to manage a transaction
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
. . .
'Open connection.
cn.Open
'Buka tabel titles.
rs.Open "SELECT * FROM titles", Cn, adOpenDynamic, adLockPessimistic
. . .
'Users make changes in coding this
. . .
'Ask if the user will save all the changes have been made.
If MsgBox("Simpan semua perubahan?", vbYesNo) = vbYes Then
cn.CommitTrans
Else
cn.RollbackTrans
End If
After the connection was established, a recordset is opened using a SELECT query, using a dynamic cursor that bertipe and pessimistic locking bertipe (property of a Recordset). After you edit or update the data, you choose whether to save changes or cancel it. Data that has changed in the next transaction can be saved (CommitTrans) or cancel it (RollbackTrans).
0 comments:
Post a Comment