Database Access
Configuring the Connection to the Database

How XMLNuke connects to the database and how to configure the access connection.

Table of contents
1. Configuring the Connection to the Database
2. Connecting through the DBDataSet
3. The IIterator interface and the objects for standardized data access.

On this page
Data Repository - DBDataSet
Defining the configuration of the database
Defining a connection for CSharp
Defining the connection for PHP

Data Repository - DBDataSet

XMLNuke also allows programmers to read and store information through a relational Database. The class used for this is the DBDataSet and DBIterator. The main difference is that because XMLNuke works with XML, every access to the database is understood, by both the framework and the programmer, as an access to an XML file. Thus, manipulating an AnyDataSet document or a connection through a DBDataSet is done in a way that is both similar and transparent to the user.

Every process then has the following steps:

1. Configure the connection to the Database
2. Use a DBDataSet to run a query
3. Iterate the query through the DBIterator

Defining the configuration of the database

The configuration file should be called _db.anydata.xml and should be located in the AnyDataSet folder of the site or in the SHARED folder of XMLNuke. Below is an example of a configuration file:

<anydataset>
	<row>
		<field name="dbname">exemple1</field>
		<field name="dbtype">ODBC</field>
		<field name="dbconnectionstring">
			Driver={MySQL ODBC 3.51 Driver};
			Server=ipservidor;
			UID=user;
			PWD=password;Database=dbname
		</field>
	</row>
	<row>
		<field name="dbname">exemple2</field>
		<field name="dbtype">ODBC</field>
		<field name="dbconnectionstring">DSN=dsnname;</field>
	</row>
</anydataset>

It's important to note that each ROW line contains the information required to connect to a DATABASE. Thus, in the example, there are two configurations to access the database, which are called example1 and example2.

Below is the explanation for each field:

• dbname: Contains the NAME of the connection for XMLNuke. The programmer ONLY needs to know the dbname to establish the connection.
• dbtype: The type of connection and/or database. See details in the following topic specific for each database and engine.
• dbconnectionstring: The connection String according to dbtype

Defining a connection for CSharp

Since version 3.1 of Xmlnuke the system is using the class DbProviderFactories to detect what is the class most appropriate to connect to database. All other mechanisms for connecting existing before version 3.1 no longer exist. With this mechanism in the connection you must install the NET provider of database and then set the connection string through the tool of administration "DB Connection Manager". You can also configure the setting appropriate values in the XML _db.anydata.xml according to the values in the file "machine.config" of the client. See example:

...
<system.data>
  <DbProviderFactories>
    <add name="SqlClient Data Provider"    invariant="System.Data.SqlClient"    
	 description=".Net Framework Data Provider for SqlServer" 
	 type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
  </DbProviderFactories>
</system.data>
...

<anydataset>
	<row>
		<field name="dbname">your_connection_name</field>
		<field name="dbtype">System.Data.SqlClient</field>
		<field name="dbconnectionstring">Data Source=server; Database=database; User id=user; Password=pass</field>
	</row>
</anydataset>

Note that the "System.Data.SqlClient" which is assigned to the property dbtype is the value defined in the attribute "invariant" in the node DbProviderFactories.

Below are listed some of the possible values of "invariant":

DbType	Database	Provider
System.Data.OleDb	Generic providers based on OleDb	System.Data.OleDb.OleDbFactory. Native provider. Not tunned for specific databases.
System.Data.Odbc	Generic providers based on ODBC	System.Data.Odbc.OdbcFactory. Native Provider. Not tunned for specific databases.
System.Data.SqlClient	Microsoft SQL Server. All versions.	System.Data.SqlClient.SqlClientFactory. Native Provider.
MySql.Data.MySqlClient	MySQL Server 4.x e 5.x	MySql.Data.MySqlClient.MySqlClientFactory. Official MySQL connector v5.2. Download here.
System.Data.OracleClient	Oracle Database Server 8, 9, 10 and 11.	System.Data.OracleClient.OracleClientFactory. Official Oracle connector (ODPNET). Download here.
Mono.Data.Sqlite	SQLite 3.0. Mono.	Mono.Data.Sqlite.SqliteFactory. Native in Mono installations.
System.Data.SQLite	SQLite 3.0. Windows.	System.Data.SQLite.SQLiteFactory. Download here the latest version.
Npgsql	PostGres SQL. Several versions.	Npgsql.NpgsqlFactory . Download here.
FirebirdSql.Data.FirebirdClient	Firebird and Interbase	FirebirdSql.Data.FirebirdClient.FirebirdClientFactory. Official Firebird Provider. Download here.

Important Note: The provider of connection to the database must be registered in the GAC.

Defining the connection for PHP

XMLNuke PHP uses native access from PHP5 to connect to the database. One of the advantages of using the PDO is the fact that it is native to the PHP language. It also offers the standardization for accessing the database. XMLNuke PHP has two ways of defining a connection to the database:

dbtype=PDODRIVER

For this method, the DBTYPE should be configured with the NAME of the driver on PHP (see table below). The dbconnectionstring field should then be configured to:

server_name;user;password;database

<anydataset>
	<row>
		<field name="dbname">exemple</field>
		<field name="dbtype">mysql</field>
		<field name="dbconnectionstring">server;username;password;datasource</field>
	</row>
</anydataset>

dbtype=dsn

For this method, a default connection is used similar to a URL and it is the recommended default for using with XMLNuke. This method should be defined as follows:

database://username:password@server/database

An example of the connection can be: oci://scott:tiger@192.168.1.15/database

<anydataset>
	<row>
		<field name="dbname">exemple</field>
		<field name="dbtype">dsn</field>
		<field name="dbconnectionstring">oci://scott:tiger@192.168.1.15/database</field>
	</row>
</anydataset>

The possible configurations are:

Name	Database
dblib	FreeTDS / Microsoft SQL Server / Sybase
firebird	Firebird/Interbase 6
informix	IBM Informix Dynamic Server
mysql	MySQL 3.x/4.x/5.x
oci	Oracle Call Interface
odbc	ODBC v3 (IBM DB2, unixODBC and win32 ODBC)
pgsql	PostgreSQL
sqlite	SQLite 3 and SQLite 2