VistaDB 6
VistaDB / Getting Started / Using VistaDB with Entity Framework / Using VistaDB with ADO.NET - Connection Strings
In This Topic
    Using VistaDB with ADO.NET - Connection Strings
    In This Topic

    ADO.NET uses connection strings to specify what database to open and any options necessary for tuning the behavior of the engine and the connection.  Like all ADO.NET providers, VistaDB supports a number of options in its connection strings.  Connection strings are composed of one or more options specified as keyword=value followed by a semicolon(;)

    Most VistaDB Connection strings consist of a Data Source and an Open Mode, however there are several other options for tuning the interaction between your application and the database.

    Connection String Options

    Keyword Default Description
    Compatibility Mode Normal

    CompatibilityMode current setting for SQL Server Vs legacy VistaDB compatibility on this connection.  If specified, it must be one of the following values:

    • Normal: Non-breaking corrections and enhancements are incorporated in minor versions, but breaking changes wait for new major versions.
    • Strict: Client prefers strict SQL Server syntax.  Breaking changes to correct syntax incompatibilities may be added in any new release.
    • Legacy: Client relies on deprecated syntax features of VistaDB. 
    Connect Timeout 0 The maximum time in seconds to wait for a valid connection. 
    Data Source


    The file name of the database to open.  If no directory is specified it will use the current working directory. 

    To specify a full path or a name with spaces use single quotes (')

    A special format is supported for web applications to map the App_Data directory. Note that no trailing slash is used after |DataDirectory|.

    Isolated Storage False When True indicates the database resides in isolated storage.
    Max Pool Size 100 Sets the maximum number of connections to be cached in the connection pool. This must be a positive number larger than the Minimum Pool Size.
    Min Pool Size 1 Sets the minimum pool size for the connection. This must be a positive number smaller than Maximum Pool Size.
    Open Mode SingeProcessReadWrite The mode for opening the database. This should not be set to exclusive if connection pooling is enabled. It must be one of the following values:

    • ExclusiveReadWrite: Single connection read / write. Connection pooling must be turned off (each connection pool entry counts as a connection - exclusive means only 1 connection, not one app).
    • ExclusiveReadOnly: Single connection read only mode.
    • SingleProcessReadWrite: Multiple connections within a single process read / write. This is probably the default you should use most of the time.
    • SingleProcessReadOnly: Multiple connections within a single process read only - other clients may write to the database using the SingleProcessReadWrite mode.
    • MultiProcessReadWrite: Multiple connections, possibly from multiple processes read / write. This is the slowest open mode.
    • MultiProcessReadOnly: Multiple connections, possibly from multiple processes read only - other clients may write to the database using MultiProcessReadWrite.
    • SharedReadOnly: Multiple connections, possibly from multiple processes read only for all clients.  This gives the fastest read performance by disabling all locking in the database.
    Password The password used to decrypt an encrypted database.  The password must not be quoted or encoded.  It's recommended you place the password as the last item in the connection string so it doesn't require a trailing semicolon.
    Pooling False Turn connection pooling on or off for the given connection.  True means enable pooling, False means disable. Used in conjunction with Min Pool Size and Max Pool Size.
    Transaction Mode On

    Indicates how the engine should treat attempts to use transactions.  If specified, it must be one of the following values:

    • On: Transaction support is on and operates normally.
    • Off: Transactions are disabled, any attempt to start one will thrown an exception.
    • Ignore: Transactions are disabled, any attempt to start or commit or abort one will be ignored.

    Common Examples

    Connecting to a Database in a known location

    Data Source='c:\ProgramData\Your Application\YourDatabase.vdb6';Pooling=true;
    

    This opens the database YourDatabase.vdb6 located in c:\ProgramData\Your Application. The path to the file is quoted to allow for a space in the file name or a directory name.  Connection pooling is enabled and the default connection mode (NonExclusiveReadWrite) is being used.

    Connecting to a Database in a Web Application

    Data Source=|DataDirectory|YourDatabase.vdb6;Pooling=true;
    

    This opens the database YourDatabase.vdb6 located in the App_Data folder for the current application (typically an ASP.NET application).  This is recommended for web applications since it will resolve correctly on any deployed configuration.  Connection pooling is enabled and the default connection mode (NonExclusiveReadWrite) is being used.

    Exclusive Connection for Bulk Insert and Update

    Data Source=YourDatabase.vdb6;Open Mode=ExclusiveReadWrite;Transaction Mode=Ignore
    

    This opens the database YourDatabase.vdb6 located in the current working directory which is typically where your application is running from.  The database is opened in exclusive read-write mode so that only one connection can work with it at a time.  This minimizes locks during bulk insert and update activities.

    If using an ORM that creates explicit transactions it's often recommended to disable these in this scenario to maximize throughput and rely on VistaDB's built-in rollback of individual errors.  This is done by setting Transaction Mode to Ignore.

    Connecting to a Database in the Working Directory Read-Only

    Data Source=YourDatabase.vdb6;Pooling=true;Open Mode=SharedReadOnly
    

    This opens the database YourDatabase.vdb6 located in the current working directory which is typically where your application is running from.  Note that users may not have read-write access to that location, so the connection is opened read-only in this case.

    Connecting to an Encrypted Database in the Working Directory Read-Only

    Data Source=YourDatabase.vdb6;Pooling=true;Open Mode=SharedReadOnly;Password=abcdefg1!
    

    This opens the database YourDatabase.vdb6 located in the current working directory and using the password "abcdefg1!" to decrypt it.  Note that users may not have read-write access to the working directory, so the connection is opened read-only in this case.

    For more examples, see Using VistaDB with ADO.NET - Using Connection Strings in Code.

    See Also