Database Connectivity

To link DbNetGrid or DbNetEdit to a database you will need to specify the ConnectionString property. This property refers to a name attribute  of an element in the <ConnectionStrings> section of the web.config file. e.g.

<?xml version="1.0"?>
<configuration>
	<connectionStrings>
		<add name="SamplesDatabase" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=~/App_Data/dbnetsuitevs.mdb"/>
		<add name="OracleDB" connectionString="Data Source=HUMANRESOURCES;user id=scott;password=tiger;" providerName="OracleClient">
		<add name="MySqlDB" connectionString="Server=DbServer;Port=3306;Database=World;User ID=root;Password=password;" providerName="MySql">
	</connectionStrings>
	...
</configuration>

The connectionString attribute defines the actual ADO.NET connection string used to connect to the database. In many cases DbNetSuite can derive the ADO.Net provider used to create the database connection and will default to SqlClient when unable to do so. For connections to databases such as MySql and Oracle you can specify the providerName attribute as one of the supported ADO.NET Data Providers

DbNetSuite supports the following databases.

Supported Databases

SQL Server

Connection strings for SQL Server are assigned in one of two formats depending on whether you want to connect using Windows or SQL Server authentication.

Using Windows authentication

<add name="connectionName" connectionString="Server=ServerName;Database=DatabaseName;Trusted_Connection=true;"/>

Using SQL Server authentication

<add name="connectionName" connectionString="Server=ServerName;Database=DatabaseName;UID=username;PWD=password;"/>

SQL Server CE (Compact Edition)

Connection strings for SQL Server CE simply require you to specify the SDF file as the data source.

<add name="connectionName" connectionString="Data Source=~/App_Data/Northwind.sdf;"/>

Access

Data source property should specify the path to an Access MDB file

Example connection string

<add name="connectionName" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\msjet\hr.mdb"/>

Access 2007

Data source property should specify the path to an Access ACCMB file.

Example connection string

<add name="connectionName" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\access2007\Marketing projects.accdb"/>

Note that connection to the new Access 2007 ACCMB file format requires the 2007 Office Data Connectivity Components to be installed

Oracle

Connection strings for Oracle are assigned in one of two formats depending on whether you want to connect using Windows or Oracle authentication. The Data Source refers to

Using Windows authentication

Example connection string

<add name="connectionName" connectionString="Server=//server4/XE;Integrated Security=SSPI;" providerName="OracleClient"/>

Using SQL Server authentication

Example connection string

<add name="connectionName" connectionString="Server=//server4/XE;User ID=nwind;Password=nwind;" providerName="OracleClient"/>

MySql

Example connection string

<add name="connectionName" connectionString="Server=servername;Port=3306;Database=databasename;User ID=username;Password=password;" providerName="MySql"/>

DB2

Example connection string

<add name="connectionName" connectionString="Server=Servername;Database=DatabaseName;uid=username;pwd=password;CurrentSchema=schemaname;" providerName="DB2"/>

Visual FoxPro

Connection requires the Visual FoxPro OleDb driver which can be downloaded from here

Example connection string

<add name="connectionName" connectionString="Provider=vfpoledb;data source=/data/vfp/northwind/northwind.dbc;" providerName="OleDb"/>

VistaDB

Connection requires the VistaDB .NET data provider.

Example connection string

<add name="connectionName" connectionString="Data Source=/data/vistadb/northwind.vdb3;" providerName="VistaDB"/>

dBASE

Connection requires the MS JET OleDB driver (specifying DBASE in the Extended Properties property)

Example connection string

<add name="connectionName" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\GoldMine\Demo;Extended Properties=DBASE IV;"/>

Pervasive

Connection requires the Pervasive .Net Data Provider

Example connection string

<add name="connectionName" connectionString="Server=ServerName;ServerDSN=DataSetName;" providerName="Pervasive"/>

Firebird

Connection requires the Firebird .Net Data Provider

Example connection string

<add name="connectionName" connectionString="Server=ServerName;Database=C:\firebird\nwind\nwind.FDB;User ID=sysdba;Password=masterkey;" providerName="Firebird"/>

Postgresql

Connection requires the Npgsql Data Provider

Example connection string

<add name="connectionName" connectionString="server=localhost;User Id=postgres;Password=postgres;Database=northwind;Port=5432;" providerName="Npgsql"/>

Data Providers

SqlClient
Provider for connections to SQL Server.

SqlServerCe
Provider for connections to SQL Server CE (Compact Edition).

OleDb

Provider for connections to Access and Visual FoxPro.

OracleClient

Provider for connections to Oracle. Also requires the installation of the Oracle Call Interface (OCI) on the client.

MySql

Provider for connections to MySql.

DB2

Provider for connections to DB2.  Also requires the installation of the DB2 client.

VistaDB

Provider for connections to VistaDB.

Pervasive

Provider for connections to Pervasive.

Firebird

Provider for connections to Firebird.

Odbc

Connect to one of the supported databases using an ODBC DSN

Npgsql(PostgreSql)

Data Provider for connections to PostgreSql.

Creating a custom database connection

You can create a custom database connection using a Plugin DLL that implements the IDatabaseConnection interface. The interface has a single method called GetConnection which has a single argument which is the connection string alias specified in the application and returns an instance of the DbNetData class which encapsulates the database functionality used by DbNetSuite. To use a custom database connection do the following:

Example Code

using System;
using System.Collections.Generic;
using System.Text;
using DbNetLink.Data;
using DbNetLink.DbNetSuite;

namespace Plugins
{
    class ConnectionPluginSample : IDatabaseConnection
    {
        public DbNetData GetConnection(string ConnectionAlias)
        {
            DbNetData Db = new DbNetData("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=~/Data/db2.mdb");
            return Db;
        }
    }		
}