VistaDB Connection String Examples for ADO.NET

Database Connection Strings

Connection Strings are how you tell an ADO.NET provider where to find your database, and what options you want to include with that connection.  Below are some samples, and what they might look like for SQL Server as well.

.NET Framework Data Provider for VistaDB ADO.NET Interfaces

Usage: VistaDB.Provider

• Connection String Parts - To put multiple parts in a connection string separate them with a ;
  • Data Source='|DataDirectory|database.vdb4'
    • This point to the database file you wish to load.  This should be an absolute path, or the local path is assumed.
    • |DataDirectory| is a special shorthand to tell the engine you want it to try to find the database in a local working path.  This is commonly used for websites where App_Data is the database directory.  The path separator (\) after the macro is optional in VistaDB, but required in most other databases.
  • Open Mode=ModeName 
    • The file open mode for this connection, exclusive connections cannot use connection pooling since only one connection may exist to an exclusive file.
      • ExclusiveReadWrite - Single process read write.  This is the fastest access mode since the database does not have to worry about multi user locks.
      • ExclusiveReadOnly - Single process with only read access.
      • NonExclusiveReadWrite - Shared access for multiple processes.  Lock files may be generated when running in this mode.  Your application should also be prepared to handle LockTimeout and Concurrency error exceptions.
      • NonExclusiveReadOnly - Database opened for read only, but other clients may write to database.
      • SharedReadOnly - Database file opened in shared mode for read-only operations by all clients.   No locking is performed.
  • Pooling=[True|False]
    • Turn connection pooling on or off for the given connection. Used in conjunction with Min Pool Size and Max Pool Size.
  • Min Pool Size=[Int]
    • Minimum pool size to reserve. The result is that this number of connections is opened to the database all with the same connection mode.  Not compatible with exclusive mode.
    • This must be a positive number and less than Max Pool Size.
    • In general you want to open 3-5 as your minimum pool size for most applications.
  • Max Pool Size=[Int]
    • Maximum pool size.  If the number of pool entries exceeds this number any new connections will be blocked until a connection is recycled.
    • This must be a positive number and great than Min Pool Size.
    • A good general number is twice the number of the Min Pool, unless you expect your application to spin up a lot of threads.
  • Password=secret
    • The encryption key to the database.  This is actually for encryption in VistaDB, not user level access.  Encryption has 40% or more overhead to encrypt each packet to disk.
    • Leave off connection string if it is not used.  Setting it to a blank string means a blank encryption key (encryption is on with blank key), this is not usually the desired behavior.
    • Do not quote with ' or " (spaces may not be used)
  • Transaction Mode=[on|off|ignore]  (vdb4 only)
    • on is the default and transactions operate normally
    • off means transactions are not to be attempted, creating one is an error
    • ignore means that attempts to create transactions should be ignored.  This is useful for people using tools that generate transactions through SQL and are unable to turn them off in the tool (ORMs frequently do this).

Example Connection String in Source Code

C# Code Example with path

myconnectionstring = "data source='c:\\temp\\database.vdb4';Pooling=true";

Or you can use the @ symbol at the front of a string in C# means to take the string as a literal.

    string myconnectionstring = @"data source='c:\temp\database.vdb4';Pooling=false"; 
   

|DataDirectory| is also legal in the data source path.

    string myconnectionstring = @"data source='|DataDirectory|database.vdb4'"; 
   

VB Code Example with path

Dim myconnection as string
myconnection = "data source='c:\\temp\\database.vdb4'"

 

Direct Data Access Provider for VistaDB DDA Interface

DDA Opens a database through a function call on the DDA Obj manager.  The options are specified as parameters.

• OpenDatabase( string Filename, mode as VistaDBOpenDatabaseMode, string password)
• Filename is the path to the database to open.  It can also use the |DataDirectory| macro to mean look in the local application path or App_Data for websites.
• VistaDBOpenDatabaseMode
  • ExclusiveReadWrite - Single process read write.  This is the fastest access mode since the database does not have to worry about multi user locks.
  • ExclusiveReadOnly - Single process with only read access.
  • NonExclusiveReadWrite - Shared access for multiple processes.  Lock files may be generated when running in this mode.  Your application should also be prepared to handle LockTimeout and Concurrency error exceptions.
  • NonExclusiveReadOnly - Database opened for read only, but other clients may write to database.
  • SharedReadOnly - Database file opened in shared mode for read-only operations by all clients.   No locking is performed.
• Password - Set this to null (Nothing in VB) if there is no encryption on the database.  Do not set it to an empty string unless that is your password (yes, it will accept string.Empty as a password although it probably should not).

SQL Server Connection String Tags (not used in VistaDB)

• Initial Catalog=
  • Since there is only one database per file in VistaDB, there is no concept of an initial catalog.
• User Id=
  • All database files are fully available to VistaDB connections, there are no per user restrictions.
• Trusted_Connection=
  • All connections are trusted.
• Integrated Security=
  • No concept since all users have to be logged into a machine in order to reach the file.
• Network Library=
  • There is only one managed assembly for VistaDB, libraries are not used.
• MultipleActiveResultSets=
  • MARS is not supported at this time.
• AttachDbFilename=
  • The filename is part of the DataSource identifier.
• Failover Partnering=
  • There are no server components or mirroring in VistaDB.
• Asynchronous Processing=
  • All connections are synchronous in VistaDB.  Asynchronous operation is not supported.
• Provider=
  • Mostly used to specify a new client in SQL Server 2008.  Not needed in VistaDB.
• Encrypt=
  • Specify a password for the database and it is encrypted.
• Driver=
  • Used to specify an alternate driver with SQL Server 2008.  Not supported.
• TrustServerCertificate
  • Not supported.
• Workstation ID
  • Not supported.
• Persist Security Info=
  • Not needed by VistaDB.
• Max Buffer Size=
  • Since VistaDB resides within your application namespace, there is no need for such an option.  We already have optimal usage through passing references.
• Connection Timeout=
  • Since VistaDB resides within your application namespace, there is no need for such an option.