Connection Strings

Contents

Introduction. 1

In Spludlow.config. 1

Introduction

The DAL has a few syntax tricks you can use in the connection string, used for; specifying the database type, having the string in the Spludlow.config file, and short hand for speed.

In Spludlow.config

If the connection string starts with ‘@’ then it is specifying a configuration key. For example:

CODE: Spludlow.Data.IDAL dal = new Spludlow.Data.DALSql("@Spludlow");

CONFIG: <add key="Spludlow.ConnectionString.Spludlow" value="Data Source=DATABASEHOST;Initial Catalog=SpludlowV1;Integrated Security=True"/>

Note: Connection string keys used by the DAL are prefixed with “Spludlow.ConnectionString.”.

Database type

The DAL by default assumes you are using SQL Server. If you want to use another database type you prefix the connection string with the database type in square brackets, like so:

“[mysql] Server=192.168.0.99;Database=SpludlowV1;Uid=SpludlowUser;Pwd=u7UJ5UVFkCZzOkk;”

Here are the currently supported database types. Only SQL Server is fully supported at the moment, others provide limited read only functionality.

case "sql":

assType = new string[] { "Spludlow", "Spludlow.Data.DALSql" };

break;

case "odbc":

assType = new string[] { "Spludlow", "Spludlow.Data.DALODBC" };

break;

case "oledb":

assType = new string[] { "Spludlow", "Spludlow.Data.DALOleDb" };

break;

case "mysql":

assType = new string[] { "Spludlow.Data.MySQL", "Spludlow.Data.DALMySQL" };

break;

case "sqlite":

assType = new string[] { "Spludlow.Data.SQLite", "Spludlow.Data.DALSQLite" };

break;

case "oracle":

assType = new string[] { "Spludlow.Data.Oracle", "Spludlow.Data.DALOracle" };

break;

case "postgresql":

assType = new string[] { "Spludlow.Data.PostgreSQL", "Spludlow.Data.DALPostgreSQL" };

break;

case "db2":

assType = new string[] { "Spludlow.Data.DB2", "Spludlow.Data.DALDB2" };

break;

Note: When using DALs other that the standard Microsoft ones (SQL, ODBC, and OLEDB) then the DAL implementations are in separate assemblies (You can also write your own pretty easily). For the framework to use them they must be compiled and present on the host using them, they must also be listed in Applications.txt as a “Lib”, for example:

Spludlow.Data.MySQL C:\Program Files\SpludlowV1\ Spludlow.Data.MySQL Lib

Any projects, like your business logic library, do not need a reference to additional DAL implementations (they don’t need compiling in). The framework will find the DAL library from Applications.txt.

Shorthand

Each DAL provider for a particular database type may allow a shorthand connection string for extra ease. They are generally only available when using a connection string that has only one or two parameters, mainly those without usernames and passwords. The use of the shorthand is enabled by detecting there are no semi-colons “;” in the connection string. If your connection string only has one parameter and you don’t want shorthand mode, just stick a semi-colon “;” on the end.

SQL Server

The connection string is “HOST@DATABASE” or just “HOST” (the “master” database will be used, maybe you are creating databases in code). This only works if you are using Windows Authentication where you don’t need any credentials in the connection string. This example would equate to:

“Data Source=MYHOST;Initial Catalog=MYDATABASE;Integrated Security=True;”

OLEDB

The Access provider (“ACE” or “JET”) an at sign (@) and the path to an Access database for example “ACE@C:\MyDatabase.accdb” or “JET@C:\MyDatabase.mdb”. You can also have just the path and the ACE provider will be used. These 2 examples produce the following connection strings:

ACE "Provider=Microsoft.ACE.OLEDB.12.0;User ID='Admin';Password='';Data source='C:\MyDatabase.accdb';"

JET "Provider=Microsoft.Jet.OLEDB.4.0;User ID='Admin';Password='';Data source='C:\MyDatabase.mdb';"

NOTE: It is recommended that you use the newer ACE provider, you may need to use JET to use old versions of Access databases (V2.0 and before).