RAD Studio for Microsoft .NET |  |

Modifying Database Connections
See Also
Collapse All

The basic elements of a connection string tend to be the same from one database type to another. However, each database type supports slightly different connection string syntax. This topic addresses those differences.

To modify different types of database connections

1. Click on the Data Explorer tab in the IDE.
2. Select the database type of your choice.
3. Right-click to display the popup menu.
4. Choose Modify Connection to display the Connections Editor. The properties in the Connections Editor are organized into three categories: Connections, Options, and Provider Settings. The Connections options designate the database and authentication parameters. The Options area includes various database-specific database options, including transaction isolation types. The Provider Settings area specifies assemblies and the client libraries required to accomplish the connection to the given database.
   Note: All of the procedures in this topic assume that you already have installed a database client, server, or both, and that the database instance is running.

To modify an InterBase connection

1. Either enter the database name or navigate to the database on your local disk or a network drive, by clicking the ellipsis button to browse. The standard supplied databases are typically installed into C:\Program Files\Common Files\CodeGear Shared\Data.
2. Enter the password and username. By default, these are masterkey and sysdba, respectively.
3. Set the following options, if necessary. The default values are shown in the following table.

Option | Description | Default
CommitRetain | Commits the active transaction and retains the transaction context after a commit. | False
LoginPrompt | Determines if you want the user to be prompted for a login every time the application tries to connect to the database. | False
QuoteObjects | Specifies that table names, column names, and other objects should be quoted or otherwise delimited when included in a SQL statement. This is required for databases that allow spaces in names, such as MS Access. | False
RoleName | If there is a role for you in the database, you can enter the rolename here. The role is generally an authentication alias, that combines your identify with your access rights. | myRole
ServerCharSet | Specifies the character set on the server. | —
SQLDialect | Sets or returns the SQL dialect used by the client. | 3
TransactionIsolation | Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. This specifies the value for the TAdoDbxTransaction. IsolationLevel property. | ReadCommitted
WaitOnLocks | Specifies that a transaction wait for access if it encounters a lock conflict with another transaction. | False

1. You should be able to accept the defaults for the following Provider Settings:

Option | Default
Assembly | Borland.Data.Interbase,Version=Current Product Version,Culture=neutral,PublicKeyToken=Token #
Provider | Interbase
VendorClient | gds32.dll

1. Click Test to see if the connection works.
2. Click OK to save the connection string.
   Note: If you are writing ASP.NET applications, and are running the ASP.NET Web Forms locally for testing purposes, you might need to modify the path statement that points to your database, to include the localhost: designation. For example, you would modify the path shown earlier in this topic as such: localhost:C:\Program Files\Common Files\CodeGear Shared\Data\employee.gdb.

Note: Your connection string should resemble something like
Copy Code

    database=C:\Program Files\Common Files\CodeGear Shared\Data\EMPLOYEE.GDB;
    assembly=Borland.Data.Interbase,Version=2.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b;
    vendorclient=gds32.dll;provider=Interbase;username=sysdba;password=masterkey

To modify an MS SQL Server connection

1. Enter the database name in the Database field of the Connections Editor. For example, use one of the sample MS SQL Server databases, such as Pubs or Northwind. There is no need to add the file extension to the name.
2. Enter the hostname. If you are using a local database server, enter (local) in this field.
3. If you are deferring to your OS authentication, set OSAuthentication to True.
4. If you are using database authentication, enter the password and username into the appropriate fields. By default, the SQL Server database username is sa.
5. Change the database options if necessary. The default values are shown in the following table.

Option | Description | Default
BlobSize | Specifies the upper limit of the size of any BLOB field. | 1024
LoginPrompt | Determines if you want the user to be prompted for a login every time the application tries to connect to the database. | False
QuoteObjects | Specifies that table names, column names, and other objects should be quoted or otherwise delimited when included in a SQL statement. This is required for databases that allow spaces in names, such as MS Access. | False
TransactionIsolation | Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. This specifies the value for the TAdoDbxTransaction. IsolationLevel property. | ReadCommitted

1. You should be able to accept the defaults for the following Provider Settings:

Option | Default
Assembly | Borland.Data.Mssql,Version=Current Product Version,Culture=neutral,PublicKeyToken=Token #
Provider | MSSQL
VendorClient | sqloledb.dll

1. Click Test to see if the connection works.
2. Click OK to save the connection string.
   Note: If you are writing ASP.NET applications, and are running the ASP.NET Web Forms locally for testing purposes, you might need to modify the path statement that points to your database, to include the localhost: designation, prepended to the path.

Note: Your connection string should resemble something like
Copy Code

    assembly=Borland.Data.Mssql,Version=2.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b;
    vendorclient=sqloledb.dll;osauthentication=True;database=Pubs;username=;hostname=(local);password=;
    provider=MSSQL

To modify a DB2 connection

1. Enter the path to the database.
2. Enter the password and username into the appropriate fields.
3. Set the following database options, if necessary. The default values are shown in the following table.

Option | Description | Default
LoginPrompt | Determines if you want the user to be prompted for a login every time the application tries to connect to the database. | False
QuoteObjects | Specifies that table names, column names, and other objects should be quoted or otherwise delimited when included in a SQL statement. This is required for databases that allow spaces in names. | False
TransactionIsolation | Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. This specifies the value for the TAdoDbxTransaction. IsolationLevel property. | ReadCommitted

1. You should be able to accept the defaults for the following Provider Settings:

Option | Default
Assembly | Borland.Data.Db2,Version=Current Product Version,Culture=neutral,PublicKeyToken=Token #
Provider | DB2
VendorClient | db2cli.dll

1. Click Test to see if the connection works.
2. Click OK to save the connection string.

To modify an Oracle connection

1. Enter the path to the database.
2. If you are deferring to your OS authentication, set OSAuthentication to True. This means that the system defers to your local system username and password to login to the database.
3. If you are using database authentication, enter the password and username into the appropriate fields. For example, the typical Oracle username and password for the sample database is SCOTT and TIGER, respectively.
4. Set the following database options, if necessary. The default values are shown in the following table.

Option | Description | Default
LoginPrompt | Determines if you want the user to be prompted for a login every time the application tries to connect to the database. | False
QuoteObjects | Specifies that table names, column names, and other objects should be quoted or otherwise delimited when included in a SQL statement. This is required for databases that allow spaces in names. | False
TransactionIsolation | Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. This specifies the value for the TAdoDbxTransaction. IsolationLevel property. | ReadCommitted

1. You should be able to accept the defaults for the following Provider Settings:

Option | Default
Assembly | Borland.Data.Oracle,Version=Current Product Version,Culture=neutral,PublicKeyToken=Token #
Provider | Oracle
VendorClient | oci.dll

1. Click Test to see if the connection works.
2. Click OK to save the connection string.

To modify an MS Access connection

1. Either enter the database name or navigate to the database on your local disk or a network drive, by clicking the ellipsis button to browse. If you have the Office Component Toolkit installed, you might find Northwind in C:\Program Files\Office Component Toolpack\Data\Northwind.mdb.
2. Enter the username and password. By default, you can generally try admin for the username and leave the password field empty.
3. Set the following database options, if necessary. The default values are shown in the following table.

Option | Description | Default
BlobSize | Specifies the upper limit of the size of any BLOB field. | 1024
LoginPrompt | Determines if you want the user to be prompted for a login every time the application tries to connect to the database. | False
QuoteObjects | Specifies that table names, column names, and other objects should be quoted or otherwise delimited when included in a SQL statement. This is required for databases that allow spaces in names, such as MS Access. | False
TransactionIsolation | Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. This specifies the value for the TAdoDbxTransaction. IsolationLevel property. | ReadCommitted

1. You should be able to accept the defaults for the following Provider Settings:

Option | Default
Assembly | Borland.Data.Msacc,Version=Current Product Version,Culture=neutral,PublicKeyToken=Token #
Provider | MSAccess
VendorClient | msjet40.dll

1. Click Test to see if the connection works.
2. Click OK to save the connection string.

Note: Your connection string should resemble something like
Copy Code

    database=C:\Program Files\Office Component Toolpack\Data\Northwind.mdb;
    assembly=Borland.Data.Msacc,Version=2.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b;
    vendorclient=msjet40.dll;provider=MSAccess;username=admin;password=

To modify a Sybase connection

1. Enter the path to the database.
2. Enter the password and username into the appropriate fields.
3. Set the following database options, if necessary. The default values are shown in the following table.

Option | Description | Default
BlobSize | Specifies the upper limit of the size of any BLOB field. | 1024
ClientAppName | Client application name set by the middle-tier application. | —
ClientHostName | Client host name set by the middle-tier application. | —
LoginPrompt | Determines if you want the user to be prompted for a login every time the application tries to connect to the database. | False
PacketSize | Specifies the number of bytes per network packet transferred from the database server to the client. | 512
QuoteObjects | Specifies that table names, column names, and other objects should be quoted or otherwise delimited when included in a SQL statement. This is required for databases that allow spaces in names, such as MS Access. | False
TransactionIsolation | Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. This specifies the value for the TAdoDbxTransaction. IsolationLevel property. | ReadCommitted

1. You should be able to accept the defaults for the following Provider Settings:

Option | Default
Assembly | Borland.Data.Sybase,Version=Current Product Version,Culture=neutral,PublicKeyToken=Token #
Provider | Sybase
VendorClient | libct.dll

1. Click Test to see if the connection works.
2. Click OK to save the connection string.

Note: Your connection string should resemble something like
Copy Code

    assembly=Borland.Data.Sybase,Version=2.0.0.0,Culture=neutral,
    PublicKeyToken=91d62ebb5b0d1b1b;vendorclient=libct.dll;database=Pubs;
    username=admin;hostname=host1;password=;provider=Sybase

See Also

ADO.NET Overview

Database Providers for .NET

ADO.NET Component Designers

Building an ASP.NET Database Application

Creating Database Projects from the Data Explorer

Passing Parameters in a Database Application

Copyright(C) 2008 CodeGear(TM). All Rights Reserved.
What do you think about this topic? Send feedback!