9. Database access
9.1. Connector ADO.NET
Let's take another look at the layered architecture used on various occasions
 |
In the examples studied, the [dao] layer has so far exploited two types of data source:
- hard-coded data
- data from text files
In this chapter, we study the case where the data comes from a database. The 3-layer architecture then evolves into a multi-layer architecture. There are various types of multi-layer architecture. We will study the basic concepts with the following :
 |
In the diagram above, the [dao] layer [1] dialogues with the SGBD [3] through a class library specific to the SGBD used and delivered with it. This layer implements standard functions known as ADO (Active X Data Objects). Such a layer is called a provider (database access provider ici) or even connector. Most SGBD now feature a ADO.NET connector, which was not the case in the early days of the .NET platform. The .NET connectors do not offer a standard interface to the [dao] layer, so the latter has the connector class names in its code. If you change the SGBD, you change the connector and the classes, and you have to change the [dao] layer. It's both a efficient because the .NET connector, having been written for a particular SGBD, knows how best to use it, and rigid because changing the SGBD means changing the [dao] layer. This second argument should be put into perspective: companies don't change their SGBD very often. We'll see later that, since version 2.0 of .NET, there's been a generic connector that brings flexibility without sacrificing performance.
9.2. The two ways of using a data source
The .NET platform allows you to exploit a data source in two different ways:
- connected mode
- offline mode
In connected, the application
- opens a connection to the data source
- works with the read/write data source
- closes the connection
In offline, the application
- opens a connection to the data source
- obtains a memory copy of all or part of the source data
- closes the connection
- works with memory copy of read/write data
- when the job is finished, opens a connection, sends the modified data to the data source to be taken into account, closes the connection
We will only study ici in connected mode.
9.3. Basic concepts of database operation
We're going to demonstrate the main concepts of database use with a SQL Server Compact 3.5 database. This SGBD is delivered with Visual Studio Express. It's a lightweight SGBD that can only manage one user at a time. It is, however, sufficient to introduce database programming. At a later date, we'll be presenting other SGBD.
The architecture used will be as follows:
 |
A console application [1] will operate a SqlServer Compact database [3,4] via the Ado.Net connector of this SGBD [2].
9.3.1. Visit sample database
We're going to build the database directly in Visual Studio Express. To do this, we create a new console-type project.
 |
- [1]: the project
- [2]: opens a "Database Explorer" view
- [3]: create a new connection
 |
- [4]: Selects the type of SGBD
- [5,6]: select SGBD SQL Server Compact
- [7]: create the database
- [8]: a SQL Server Compact database is encapsulated in a single .sdf. We indicate where to create it, ici in the C# project folder.
- [9]: the new database has been given the name [dbarticles.sdf]
- [10]: the French language is selected. This has an impact on sorting operations.
- [11,12]: the database can be password-protected. Ici "dbarticles".
- [13]: validate the information page. The database is now physically created:
 |
- [14]: the name of the database just created
- [15]: check the option "Save my password" so you don't have to retype it each time
- [16]: check connection
- [17]: all is well
- [18]: validates the information page
- [19]: the connection appears in the database explorer
- [20]: for the moment, the database has no tables. Let's create one. An article will have the following fields:
- id : a unique identifier - primary key
- name : item name - unique
- price : item price
- stockactuel : its current stock
- stockminimum : the minimum stock level below which the item must be replenished
 |
- [21]: the [id] field is of integer type and is the table's primary key [22].
- [23]: this primary key is of type Identity. This notion, specific to SGBD SQL Servers, indicates that the primary key will be generated by the SGBD itself. Ici the primary key will be an integer starting at 1 and incremented by 1 for each new key.
 |
- [24]: the other fields are created. Note that the [name] field has a uniqueness constraint [25].
- [26]: a name is given to the table
- [27]: once the table structure has been validated, it appears in the database.
 |
- [28]: request to see table contents
- [29]: currently empty
- [30]: this is filled in with some data. A line is validated as soon as the next line is entered. The [id] field is not entered: it is generated automatically when the line is validated.
We now need to configure the project so that this database, currently at the root of the project, is automatically copied to the project execution folder:
 |
- [1]: request to view all files
- [2]: base [dbarticles.sdf] appears
- [3]: we include it in the project
 |
- [4]: the operation of adding a data source to a project launches a wizard that we don't need ici [5].
- [6]: the base is now part of the project. We return to normal mode [7].
- [8]: the project with its base
- [9]: in the database properties, we can see [10] that the database will be automatically copied to the project execution folder. This is where the program we're about to write will look for it.
Now that we have a database available, we'll be able to exploit it. First, let's take a look at SQL.
9.3.2. The four basic SQL language commands
SQL (Structured Language Query) is a partially standardized language for querying and updating databases. All SGBD respect the standardized part of SQL, but add proprietary extensions to the language that exploit certain features of SGBD. We've already seen two examples: the automatic generation of primary keys and the types allowed for table columns are often dependent on SGBD.
The four basic SQL language commands we present are standardized and accepted by all SGBD :
The query that retrieves the data contained in a database. Only the keywords in the first line are mandatory, the others are optional. Other keywords not shown are ici.
| |
Inserts a line in table. (col1, col2, ...) specifies the row columns to be initialized with the values (val1, val2, ...). | |
Updates the table checking condition (all lines if no where). For these rows, the coli receives the value vali | |
Deletes all table checking condition |
We're going to write a console application to issue SQL commands to the [dbarticles] database we created earlier. Here is a execution example. The reader is invited to understand the SQL orders issued and their results.
- line 1: the connection string: this contains all the parameters needed to connect to the database.
- line 3: requests the contents of table [articles]
- line 16: a new line is inserted. Note that the id is not initialized in this operation, as it is the SGBD that will generate the value of this field.
- line 19: check. Line 28, the line has been added.
- line 30: the price of the item just added is increased by 10%.
- line 33: check
- line 42: the price increase has taken place
- line 44: deletes the article added previously
- line 47: check
- lines 53-55: the article is no longer there.
9.3.3. Basic ADO.NET interfaces for connected mode
Let's return to the diagram of an application using a database through a ADO.NET connector:
 |
In connected, the :
- opens a connection to the data source
- works with the read/write data source
- closes the connection
Three ADO.NET interfaces are mainly concerned by these operations:
- IDbConnection which encapsulates the connection's properties and methods.
- IDbCommand which encapsulates the properties and methods of the SQL command being executed.
- IDataReader which encapsulates the properties and methods of the result of a SQL Select order.
Used to manage the database connection. Methods M and properties P of this interface will be as follows:
Name | Type | Role |
P | connecting chain to the base. It specifies all the parameters required to establish a connection with a specific base. | |
M | opens the connection to the base defined by ConnectionString | |
M | closes the connection | |
M | starts a transaction. | |
P | connection status : ConnectionState.Closed, ConnectionState.Open, ConnectionState.Connecting, ConnectionState.Executing, ConnectionState.Fetching, ConnectionState.Broken |
If Connection is a class implementing the IDbConnection, the connection can be opened as follows:
Used to execute a SQL command or stored procedure. Methods M and properties P of this interface will be as follows:
Name | Type | Role |
P | indicates what is to be executed - takes its values from an enumeration : - CommandType.Text : executes the SQL command defined in the CommandText. This is the default value. - CommandType.StoredProcedure : executes a procedure stored in the | |
P | - text of SQL command to be executed if CommandType= CommandType.Text - the name of the stored procedure to be executed if CommandType= CommandType.StoredProcedure | |
P | the connection IDbConnection to be used to execute the SQL command | |
P | the transaction IDbTransaction in which to execute the SQL command | |
P | the parameter list of a parameterized SQL order. The order update articles set price=price*1.1 where id=@id has the @id parameter. | |
M | to execute a SQL order Select. The result is an object IDataReader representing the result of Select. | |
M | to execute a SQL order Update, Insert, Delete. This gives the number of lines affected by the operation (updated, inserted, deleted). | |
M | to execute a SQL order Select returns a single result as in : select count(*) from articles. | |
M | to create parameters IDbParameter order SQL. | |
M | optimizes the execution of a parameterized query when it is executed multiple times with different parameters. |
If Command is a class implementing the IDbCommand, execution of a SQL order without a transaction will take the following form:
Used to encapsulate the results of a SQL order Select. An object IDataReader represents a table with rows and columns, which are processed sequentially: first the 1st row, then the second, .... Methods M and properties P of this interface will be as follows:
Name | Type | Role |
P | the number of columns in the table IDataReader | |
M | GetName(i) returns the name of column n° i table IDataReader. | |
P | Item[i] represents column n° i in the current row of the table IDataReader. | |
M | moves to the next line in the table IDataReader. Render Boolean True if the reading was possible, False or else. | |
M | close the table IDataReader. | |
M | GetBoolean(i): returns the Boolean value of column no. i in the current table row IDataReader. Other similar methods include : GetDateTime, GetDecimal, GetDouble, GetFloat, GetInt16, GetInt32, GetInt64, GetString. | |
M | Getvalue(i): returns the value of column no. i in the current table row IDataReader as a type object. | |
M | IsDBNull(i) makes True if column no. i in the current row of the IDataReader has no value, symbolized by SQL NULL. |
Exploiting an object IDataReader often looks like this:
9.3.4. Error management
Let's review the architecture of a database application:
 |
The [dao] layer may encounter numerous errors during database operation. These will be raised as exceptions by the ADO.NET connector. The [dao] layer code must handle them. Any operation with the database must be carried out in a try / catch / finally mode, in order to intercept and handle any exception and free the resources that need to be freed. For example, the code seen above to exploit the result of an order Select becomes the following:
Whatever happens, the objects IDataReader and IDbConnection must be closed. This is why this closing is included in the clauses finally.
Closing the connection and object IDataReader can be automated with a using :
- Line 3, clause using ensures that the open connection in the using(...){...} will be closed outside it, regardless of how you exit the block: normally or by the arrival of an exception. This saves a finally, but the interest is not in this minor economy. The use of a using prevents the developer from closing the connection himself. Or forgetting to close a connection can go unnoticed and "crash" the application in a way that appears random, each time the SGBD reaches the maximum number of open connections it can support.
- Line 11: proceed in the same way to close the object IDataReader.
9.3.5. Example project configuration
The final project will be as follows:
 |
- [1]: the project will have a configuration file [App.config]
- [2]: it uses two DLL classes that are not referenced by default and must therefore be added to the project references:
- [System.Configuration] to use the configuration file [App.config]
- [System.Data.SqlServerCe] to operate the Sql Server Compact database
- [3, 4]: remind you how to add references to a project.
- [5, 6]: recall how to add the [App.config] file to a project.
The configuration file [App.config] will be as follows:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbSqlServerCe" connectionString="Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;" />
</connectionStrings>
</configuration>
- lines 3-5: the < tagconnectionStrings> defines database connection strings. A connection string has the form "parameter1=value1;parameter2=value2;...". It defines all the parameters required to establish a connection with a particular database. These connection strings change with each SGBD. The [http://www.connectionstrings.com/] gives the shape of these for the main SGBD.
- line 4: defines a specific connection string, ici for the SQL Server Compact database dbarticles.sdf that we created earlier:
- name = name of the connection string. It is via this name that a connection string is retrieved by the C# program
- connectionString : the connection chain for a base SQL Server Compact
- DataSource : designates the base path. The syntax |DataDirectory| designates the project execution folder.
- Password : base password. This parameter is absent if there is no password.
The C# code to retrieve the previous connection string is as follows:
string connectionString = ConfigurationManager.ConnectionStrings["dbSqlServerCe"].ConnectionString;
- ConfigurationManager is the class of the DLL [System.Configuration], which is used to operate the [App.config] file.
- ConnectionsStrings["nom"].ConnectionString : designates the connectionString tag < add name="name" connectionString="..."> section <connectionStrings> from [App.config]
The project is now configured. We're now looking at the [Program.cs] class, an example of which we saw earlier.
9.3.6. The sample program
The [program.cs] program is as follows:
using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Text;
using System.Text.RegularExpressions;
using System.Configuration;
namespace Chap7 {
class SqlCommands {
static void Main(string[] args) {
// console application - executes SQL requests typed from the keyboard
// on a database whose connection string is obtained from a configuration file
// use of configuration file [App.config]
string connectionString = null;
try {
connectionString = ConfigurationManager.ConnectionStrings["dbSqlServerCe"].ConnectionString;
} catch (Exception e) {
Console.WriteLine("Erreur de configuration : {0}", e.Message);
return;
}
// display connection string
Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);
// build a dictionary of accepted sql commands
string[] commandesSQL = new string[] { "select", "insert", "update", "delete" };
Dictionary<string, bool> dicoCommandes = new Dictionary<string, bool>();
for (int i = 0; i < commandesSQL.Length; i++) {
dicoCommandes.Add(commandesSQL[i], true);
}
// read-execute SQL commands typed on the keyboard
string requête = nu ll; // query text SQL
string[] cham ps; // query fields
Regex modèle = new Regex(@"\s+ "); // sequence of spaces
// input-execution loop for SQL commands typed on keyboard
while (true) {
// request for query
Console.Write("\nRequête SQL (rien pour arrêter) : ");
requête = Console.ReadLine().Trim().ToLower();
// finished?
if (requête == "")
break;
// the query is broken down into fields
champs = modèle.Split(requête);
// valid request?
if (champs.Length == 0 || ! dicoCommandes.ContainsKey(champs[0])) {
// error msg
Console.WriteLine("Requête invalide. Utilisez select, insert, update, delete ou rien pour arrêter");
// following request
continue;
}
// query execution
if (champs[0] == "select") {
ExecuteSelect(connectionString, requête);
} else
ExecuteUpdate(connectionString, requête);
}
}
// execute an update request
static void ExecuteUpdate(string connectionString, string requête) {
...
}
// executing a Select query
static void ExecuteSelect(string connectionString, string requête) {
....
}
}
}
- lines 1-6: namespaces used in the application. Managing a SQL Server Compact database requires the namespace [System.Data.SqlServerCe] on line 3. This is a dependency on a SGBD proprietary namespace. This means that the program will have to be modified if the SGBD is changed.
- line 18: the database connection string is read from file [App.config] and displayed on line 25. It will be used to establish a connection with the database.
- lines 28-32: a dictionary storing the names of the four authorized SQL orders: select, insert, update, delete.
- lines 40-62: the loop for entering SQL orders typed on the keyboard and executing them on the database
- line 48: the line typed on the keyboard is broken down into fields to determine the first term, which must be : select, insert, update, delete
- lines 50-55: if the query is invalid, an error message is displayed and you move on to the next query.
- lines 57-61: the SQL command entered is executed. This execution takes a different form depending on whether the order is select or an order insert, update, delete. In the first case, the order retrieves data from the database without modifying it; in the second, it updates the database without retrieving data. In both cases, execution is delegated to a method that needs two parameters:
- the connection string that will allow it to connect to the database
- the SQL command to be executed on this connection
9.3.7. Executing a SELECT query
Execution of SQL orders requires the following steps:
- Database connection
- Send SQL orders to base
- Processing order results SQL
- Closing the connection
Steps 2 and 3 are carried out repeatedly, with the connection being closed only when the database is no longer being used. Open connections are limited resources of a SGBD. They must be conserved. This is why we always try to limit the lifetime of an open connection. In this example, the connection is closed after each SQL command. A new connection is opened for the next SQL order. Opening and closing a connection is costly. To reduce this cost, some SGBD offer the notion of connection pools open: when the application is initialized, N connections are opened and assigned to the pool. They remain open until the end of the application. When the application opens a connection, it receives one of the N connections already open in the pool. When it closes the connection, it simply returns it to the pool. The advantage of this system is that it is transparent to the developer: the program does not have to be modified to use the connection pool. Connection pool configuration is dependent on SGBD.
First, we look at the execution of SQL orders Select. The method ExecuteSelect of our example program is as follows:
// execute a Select query
static void ExecuteSelect(string connectionString, string requête) {
// handle any exceptions
try {
using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
// opening connection
connexion.Open();
// executes sqlCommand with select query
SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
SqlCeDataReader reader= sqlCommand.ExecuteReader();
// displaying results
AfficheReader(reader);
}
} catch (Exception ex) {
// error msg
Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
}
}
// reader display
static void AfficheReader(IDataReader reader) {
...
}
- line 2: the method receives two parameters:
- the connection string [connectionString] that will allow it to connect to the database
- order SQL Select [request] to be executed on this connection
- line 4: any operation with a database may generate an exception that you may want to handle. This is all the more important ici as the SQL commands given by the user may be syntactically erroneous. We need to be able to tell him. All the code is therefore inside a try / catch.
- line 5: there are several things ici :
- the connection to the database is initialized with the connection string [connectionString]. It is not yet open. It will be opened on line 7.
- the clause using (Resource) {...} is a syntactic facility guaranteeing resource release Resource, ici a connection, at the output of the block controlled by the using.
- the connection is of a proprietary type: SqlCeConnection, specific to SGBD SQL Server Compact.
- line 7: the connection is opened. This is when the connection string parameters are used.
- line 9: a SQL order is issued via an owner object SqlCeCommand. Line 9 initializes this object with two pieces of information: the connection to use and the SQL command to send on it. The object SqlCeCommand can be used to execute an order Select an order Update, Insert, Delete. Its properties and methods were presented paragraph 9.3.3.
- line 10: a SQL order Select is executed via the ExecuteReader object SqlCeCommand which makes an object IDataReader whose methods and properties paragraph 9.3.3.
- line 12: results display is entrusted to the AfficheReader next :
// reader display
static void AfficheReader(IDataReader reader) {
using (reader) {
// exploitation of results
// -- columns
StringBuilder ligne = new StringBuilder();
int i;
for (i = 0; i < reader.FieldCount - 1; i++) {
ligne.Append(reader.GetName(i)).Append(",");
}
ligne.Append(reader.GetName(i));
Console.WriteLine("\n{0}\n{1}\n{2}\n", "".PadLeft(ligne.Length, '-'), ligne, "".PadLeft(ligne.Length, '-'));
// -- data
while (reader.Read()) {
// current line operation
ligne = new StringBuilder();
for (i = 0; i < reader.FieldCount; i++) {
ligne.Append(reader[i].ToString()).Append(" ");
}
Console.WriteLine(ligne);
}
}
}
- line 2: the method receives an object IDataReader. Note that ici is an interface and not a specific class.
- line 3: the clause using is used to automatically manage the closing of the IDataReader.
- lines 8-10: the column names of the result table of the Select. These are the columns coli of the request select col1, col2, ... from table ...
- lines 14-21: browse the results table and display the values for each table line.
- line 18: we don't know the type of column i in the result because we don't know the table being queried. The syntax reader.GetXXX(i) where XXX is the type of column no. i, as this type is not known. We then use the syntax reader.Item[i].ToString() to obtain the string representation of column no. i. The syntax reader.Item[i].ToString() can be abbreviated to reader[i].ToString().
9.3.8. Executing an update order : INSERT, UPDATE, DELETE
The method code ExecuteUpdate is as follows:
// execute an update request
static void ExecuteUpdate(string connectionString, string requête) {
// handle any exceptions
try {
using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
// opening connection
connexion.Open();
// executes sqlCommand with update request
SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
int nbLignes = sqlCommand.ExecuteNonQuery();
// result display
Console.WriteLine("Il y a eu {0} ligne(s) modifiée(s)", nbLignes);
}
} catch (Exception ex) {
// error msg
Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
}
}
We've said that executing a query command Select was no different from that of an update order Update, Insert, Delete by the object method SqlCeCommand used : ExecuteReader for Select, ExecuteNonQuery for Update, Insert, Delete. We comment only on the latter method in the code above:
- line 10: order Update, Insert, Delete is executed by the ExecuteNonQuery object SqlCeCommand. If successful, this method returns the number of lines updated (update) or inserted (insert) or destroyed (delete).
- line 12: this number of lines is displayed on screen
The reader is invited to review an example of how to execute this code, paragraphe 9.3.2.
9.4. Other connectors ADO.NET
The code we have studied is proprietary: it depends on the [System.Data.SqlServerCe] for the SGBD SQL Server Compact. We'll now build the same program with different .NET connectors and see what changes.
9.4.1. Connector SQL Server 2005
The architecture used will be as follows:
 |
Installation of SQL Server 2005 is described in the appendices in paragraph 1.1.
We create a second project in the same solution as before, then create the SQL server 2005 database. The SGBD SQL Server 2005 must be started before the following operations:
 |
- [1]: create a new project in the current solution and make it the current project.
- [2]: create a new connection
- [3]: select connection type
 |
- [4]: select SGBD SQL Server
- [5]: result of previous choice
- [6]: use the [Browse] button to specify where to create the SQL Server 2005 database. The database is encapsulated in an .mdf file.
- [7]: select the root of the new project and call up the base [dbarticles.mdf].
- [8]: use Windows authentication.
- [9]: validate the information page
 |
- [11]: SQL Server database
- [12]: create a table. This will be identical to the SQL Server Compact database built previously.
- [13]: the [id] field
- [14]: the [id] field is of type Identity.
- [15,16]: field [id] is primary key
 |
- [17]: other table fields
- [18]: give the table the name [articles] when you save it (Ctrl+S).
We still need to put data in the :
 |  |
We include the database in the :
 |
The project references are as follows:
 |
The configuration file [App.config] is as follows:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="connectString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;" />
<add name="connectString2" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;Connect Timeout=30;" />
</connectionStrings>
</configuration>
- line 4: database connection string [dbarticles.mdf] with Windows authentication
- line 5: database connection string [dbarticles.mdf] with SQL Server authentication. [sa,msde] is the pair (login,password) of the SQL Server administrator as defined in paragraph 1.1.
The [Program.cs] program evolves as follows:
using System.Data.SqlClient;
...
namespace Chap7 {
class SqlCommands {
static void Main(string[] args) {
...
// use of configuration file [App.config]
string connectionString = null;
try {
connectionString = ConfigurationManager.ConnectionStrings["connectString2"].ConnectionString;
} catch (Exception e) {
...
}
...
// read-execute SQL commands typed on the keyboard
...
}
// execute an update request
static void ExecuteUpdate(string connectionString, string requête) {
// handle any exceptions
try {
using (SqlConnection connexion = new SqlConnection(connectionString)) {
// opening connection
connexion.Open();
// executes sqlCommand with update request
SqlCommand sqlCommand = new SqlCommand(requête, connexion);
int nbLignes = sqlCommand.ExecuteNonQuery();
// result display
Console.WriteLine("Il y a eu {0} ligne(s) modifiée(s)", nbLignes);
}
} catch (Exception ex) {
....
}
}
// execute a Select query
static void ExecuteSelect(string connectionString, string requête) {
// handle any exceptions
try {
using (SqlConnection connexion = new SqlConnection(connectionString)) {
// opening connection
connexion.Open();
// executes sqlCommand with select query
SqlCommand sqlCommand = new SqlCommand(requête, connexion);
SqlDataReader reader = sqlCommand.ExecuteReader();
// exploitation of results
...
}
} catch (Exception ex) {
...
}
}
}
}
- line 1: space of names [System.Data.SqlClientcontains classes for managing a SQL Server 2005 database
- line 24: the connection is SQLConnection
- line 28: the object encapsulating SQL orders is of type SQLCommand
- line 47: the object encapsulating the result of a SQL Select order is of type SQLDataReader
The code is identical to that used with the SGBD SQL Server Compact, except for the class names. To execute it, you can use (line 11) either of the two connection strings defined in [App.config].
9.4.2. Connector MySQL5
The architecture used will be as follows:
 |
The installation of MySQL5 is described in the appendices in paragraph 1.2 connector and the Ado.Net connector on the paragraph 1.2.5.
We create a third project in the same solution as before and add the references it needs:
 |
- [1]: the new project
- [2]: to which we add references
- [3]: the DLL [MySQL.Data] of the Ado.Net connector of MySql5 as well as that of [System.Configuration] [4].
We now create the database [dbarticles] and its table [articles]. The SGBD MySQL5 must be launched. In addition, we launch the [Query Browser] client (see paragraph 1.2.3).
 |
- [1]: in [Query Browser], right-click in the [Schemata] zone [2] to create [3] a new schema, the term used to describe a database.
- [4]: the database will be called [dbarticles]. In [5], we see it. For the moment, it has no tables. We will execute the following SQL script:
- line 1: the [dbarticles] database becomes the current database. The following SQL commands will be executed on it.
- lines 4-10: definition of table [ARTICLES]. Note that SQL owns MySQL. The column types and automatic generation of the primary key (AUTO_INCREMENT attribute) differ from those encountered with SGBD SQL Server Compact and Express.
- lines 12-14: insertion of three lines
- lines 16-21: add integrity constraints on columns.
This script is executed in [MySQL Query Browser] :
 |
- in [MySQL Query Browser] [6], we load the script [7]. You can see it in [8]. In [9], it is executed.
 |
- in [10], table [articles] has been created. Double-click on it. This brings up window [11] with query [12] inside, ready to be executed by [13]. In [14], the result of the execution. We have the three expected lines. Note that the values in field [ID] were generated automatically (field attribute AUTO_INCREMENT).
Now that the database is ready, we can get back to developing the application in Visual Studio.
 |
In [1], the program [Program.cs] and the configuration file [App.config]. The latter is as follows:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbArticlesMySql5" connectionString="Server=localhost;Database=dbarticles;Uid=root;Pwd=root;" />
</connectionStrings>
</configuration>
Line 4, the elements of the connection chain are as follows:
- Server : name of the machine on which the SGBD MySQL, ici is located localhost, c.a.d. the machine on which the program will be run.
- Database : the name of the managed database, ici dbarticles
- Uid : user login, ici root
- Pwd : its password, ici root. These two pieces of information designate the administrator created in paragraph 1.2.
The [Program.cs] program is identical to that of previous versions, except for the following details:
MySql.Data.MySqlClient | |
MySqlConnection | |
MySqlCommand | |
MySqlDataReader |
The program uses the connection string named dbArticlesMySql5 in file [App.config]. Execution gives the following results:
9.4.3. Connector ODBC
The architecture used will be as follows:
 |
The advantage of ODBC connectors is that they present a standard interface to the applications that use them. Thus, with a single code, the new application will be able to communicate with any SGBD with a ODBC, c.a.d or SGBD connector. The performance of ODBC connectors is not as good as that of "proprietary" connectors, which are able to exploit all the features of a particular SGBD. On the other hand, you get great application flexibility: you can change SGBD without changing the code.
We'll look at an example where the application uses a MySQL5 database or a SQL server Express database, depending on the connection string you give it. In the following, we assume that :
- SGBD SQL Server Express and MySQL5 have been launched
- that the ODBC driver from MySQL5 is present on the machine (see paragraph 1.2.6). The default is SQL Server 2005.
- the databases used are those in paragraph 9.4.2 for the MySQL5 base, that of the paragraph 9.4.1 for the SQL Server Express database.
The new Visual studio project is as follows:
 |
Above, the SQL Server [dbarticles.mdf] database created in paragraph 9.4.1 has been copied into the project file.
The configuration file [App.config] is as follows:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbArticlesOdbcMySql5" connectionString="Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=dbarticles; User=root;Password=root;" />
<add name="dbArticlesOdbcSqlServer2005" connectionString="Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
</connectionStrings>
</configuration>
- line 4: source connection string ODBC MySQL5. This is a previously studied string in which we find a new parameter Driver which defines the ODBC driver to be used.
- line 5: source connection string ODBC SQL Server Express. This is the string already used in a previous example, to which the parameter Driver has been added.
The [Program.cs] program is identical to that of previous versions, except for the following details:
System.Data.Odbc | |
OdbcConnection | |
OdbcCommand | |
OdbcDataReader |
The program uses one of the two connection strings defined in file [App.config]. Execution gives the following results:
With connection string [dbArticlesOdbcSqlServer2005] :
With connection string [dbArticlesOdbcMySql5] :
9.4.4. Connector OLE DB
The architecture used will be as follows:
 |
As with ODBC connectors, OLE DB connectors are also available (Object Linking and Embedding DataBase) drivers provide a standard interface to the applications that use them. ODBC drivers enable access to databases. Data sources drivers for OLE DB are more varied: databases, messaging systems, directories, etc. Any data source can be the subject of a Ole DB driver, if an editor so decides. This provides standard access to a wide variety of data.
We'll look at an example where the application uses either a ACCESS or a SQL Server Express database, depending on the connection string you give it. In what follows, we assume that the SGBD SQL Server Express has been launched and that the database used is that of the previous example.
The new Visual studio project is as follows:
 |
- in [1]: the namespace required for OLE DB connectors is [System.Data.OleDb] present in reference [System.Data] above. The SQL Server [dbarticles.mdf] database has been copied from the previous project. Base [dbarticles.mdb] was created with Access.
- in [2]: like the SQL Server database, the ACCESS database has the [Copy to Output Directory=Copy Always] property, so that it is automatically copied to the project execution folder.
The ACCESS [dbarticles.mdb] database is as follows:
 |
In [1], the structure of table [articles] and in [2] its contents.
The configuration file [App.config] is as follows:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbArticlesOleDbAccess" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\dbarticles.mdb;"/>
<add name="dbArticlesOleDbSqlServer2005" connectionString="Provider=SQLNCLI;Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
</connectionStrings>
</configuration>
- line 4: source connection string OLE DB ACCESS. It contains the parameter Provider which defines the OLE DB driver to be used and the database path
- line 5: source connection string OLE DB Server Express.
The [Program.cs] program is identical to that of previous versions, except for the following details:
System.Data.OleDb | |
OleDbConnection | |
OleDbCommand | |
OleDbDataReader |
The program uses one of the two connection strings defined in file [App.config]. Execution gives the following results with connection string [dbArticlesOleDbAccess]:
9.4.5. Generic connector
The architecture used will be as follows:
 |
Like the ODBC and OLE DB connectors, the generic connector presents a standard interface to the applications that use it, but improves performance without sacrificing flexibility. The generic connector is based on the proprietary SGBD connectors. The application uses classes from the generic connector. These classes act as intermediaries between the application and the proprietary connector.
In the example above, when the application requests a connection to the generic connector, the latter returns a IDbConnection, the connection interface described byragraphe 9.3.3, implemented by a MySQLConnection or SQLConnection according to the nature of the request made to it. The generic connector is said to have classes of type factory : we use a factory to ask it to create objects and give references to them (pointers). Hence its name (factory=factory, object production plant).
There is no generic connector for all SGBD (April 2008). To find out which ones are installed on a given machine, use the following program:
using System;
using System.Data;
using System.Data.Common;
namespace Chap7 {
class Providers {
public static void Main() {
DataTable dt = DbProviderFactories.GetFactoryClasses();
foreach (DataColumn col in dt.Columns) {
Console.Write("{0}|", col.ColumnName);
}
Console.WriteLine("\n".PadRight(40, '-'));
foreach (DataRow row in dt.Rows) {
foreach (object item in row.ItemArray) {
Console.Write("{0}|", item);
}
Console.WriteLine("\n".PadRight(40, '-'));
}
}
}
}
- line 8: static method [DbProviderFactories.GetFactoryClasses()] returns a list of installed generic connectors, in the form of a database table stored in memory (DataTable).
- lines 9-11: display table column names dt :
- dt.Columns is the list of table columns. A C column is of type DataColumn
- [DataColumn].ColumnName is the name of the column
- rows 13-18: display table rows dt :
- dt.Rows is the list of table rows. An L line is of type DataRow
- [DataRow].ItemArray is an array of objects, where each object represents a column of the row
The result on my machine is as follows:
- line 1: the table has four columns. The first three are the most useful for us ici.
The following display shows that the following generic connectors are available:
Name | Identifier |
System.Data.Odbc | |
System.Data.OleDb | |
System.Data.OracleClient | |
System.Data.SqlClient | |
System.Data.SqlServerCe.3.5 | |
MySql.Data.MySqlClient |
A generic connector is accessible in a C# program via its identifier.
We'll look at an example where the application exploits the various databases we've built so far. The application will receive two parameters:
- a first parameter specifies the type of SGBD used so that the correct class library is used
- the second parameter specifies the managed database, via a connection string.
The new Visual studio project is as follows:
 |
- in [1]: the namespace required for generic connectors is [System.Data.common], present in reference [System.Data].
The configuration file [App.config] is as follows:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbArticlesSqlServerCe" connectionString="Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;" />
<add name="dbArticlesSqlServer" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
<add name="dbArticlesMySql5" connectionString="Server=localhost;Database=dbarticles;Uid=root;Pwd=root;" />
<add name="dbArticlesOdbcMySql5" connectionString="Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=dbarticles; User=root;Password=root;Option=3;" />
<add name="dbArticlesOleDbSqlServer2005" connectionString="Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
<add name="dbArticlesOdbcSqlServer2005" connectionString="Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
<add name="dbArticlesOleDbAccess" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\dbarticles.mdb;Persist Security Info=True"/>
</connectionStrings>
<appSettings>
<add key="factorySqlServerCe" value="System.Data.SqlServerCe.3.5"/>
<add key="factoryMySql" value="MySql.Data.MySqlClient"/>
<add key="factorySqlServer" value="System.Data.SqlClient"/>
<add key="factoryOdbc" value="System.Data.Odbc"/>
<add key="factoryOleDb" value="System.Data.OleDb"/>
</appSettings>
</configuration>
- lines 3-11: connection strings for the various databases used.
- lines 13-17: names of generic connectors to be used
The [Program.cs] program is as follows:
...
using System.Data.Common;
namespace Chap7 {
class SqlCommands {
static void Main(string[] args) {
// console application - executes SQL requests typed from the keyboard
// on a database whose connection string is obtained from a configuration file, along with the connector name of the associated SGBD
// checking parameters
if (args.Length != 2) {
Console.WriteLine("Syntaxe : pg factory connectionString");
return;
}
// using the configuration file
string factory = null;
string connectionString = null;
DbProviderFactory connecteur = null;
try {
// factory
factory = ConfigurationManager.AppSettings[args[0]];
// connecting chain
connectionString = ConfigurationManager.ConnectionStrings[args[1]].ConnectionString;
// we retrieve a generic connector for the SGBD
connecteur = DbProviderFactories.GetFactory(factory);
} catch (Exception e) {
Console.WriteLine("Erreur de configuration : {0}", e.Message);
return;
}
// displays
Console.WriteLine("Provider factory : [{0}]\n", factory);
Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);
...
// query execution
if (champs[0] == "select") {
ExecuteSelect(connecteur,connectionString, requête);
} else
ExecuteUpdate(connecteur, connectionString, requête);
}
}
// execute an update request
static void ExecuteUpdate(DbProviderFactory connecteur, string connectionString, string requête) {
// handle any exceptions
try {
using (DbConnection connexion = connecteur.CreateConnection()) {
// connection configuration
connexion.ConnectionString = connectionString;
// opening connection
connexion.Open();
// configuration Command
DbCommand sqlCommand = connecteur.CreateCommand();
sqlCommand.CommandText = requête;
sqlCommand.Connection = connexion;
// request execution
int nbLignes = sqlCommand.ExecuteNonQuery();
// result display
Console.WriteLine("Il y a eu {0} ligne(s) modifiée(s)", nbLignes);
}
} catch (Exception ex) {
// error msg
Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
}
}
// execute a Select query
static void ExecuteSelect(DbProviderFactory connecteur, string connectionString, string requête) {
// handle any exceptions
try {
using (DbConnection connexion = connecteur.CreateConnection()) {
// connection configuration
connexion.ConnectionString = connectionString;
// opening connection
connexion.Open();
// configuration Command
DbCommand sqlCommand = connecteur.CreateCommand();
sqlCommand.CommandText = requête;
sqlCommand.Connection = connexion;
// request execution
DbDataReader reader = sqlCommand.ExecuteReader();
// display of results
...
}
} catch (Exception ex) {
// error msg
Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
}
}
}
}
- lines 12-14: the application receives two parameters: the name of the generic connector and the database connection string in the form of keys in file [App.config].
- lines 23, 25: retrieve generic connector name and connection string from [App.config]
- line 27: the generic connector is instantiated. From this point on, it is associated with a specific SGBD.
- lines 39-43: execution of the SQL command entered on the keyboard is delegated to two methods, to which we pass:
- the request to be executed
- the connection string identifying the database on which the query will be executed
- the generic connector that identifies the classes to be used to communicate with the SGBD managing the database.
- lines 50-54: a connection is obtained using the CreateConnection (line 50) of the generic connector, then configured with the connection string of the base to be managed (line 52). It is then opened (line 54).
- lines 56-58: the object Command required to execute the SQL order is created with the CreateCommand of the generic connector. It is then configured with the text of the SQL command to be executed (line 57) and the connection on which to execute it (line 58).
- line 60: SQL update order is executed
- lines 74-87: a similar code is used. The novelty lies in line 84. The object Reader obtained by executing the order Select type is DbDataReader which can be used in the same way as OleDbDataReader, OdbcDataReader, ... we've already met.
Here are a few examples.
With base MySQL5 :
 |
Open the project properties page [1] and select the [Debug] tab [2]. In [3], the connector key for line 14 of [App.config]. In [4], the key of the connection string on line 6 of [App.config]. The results are as follows:
With SQL Server Compact :
 |
In [1], the connector key for line 13 of [App.config]. In [2], the key of the connection string on line 4 of [App.config]. The results are as follows:
The reader is invited to test the other databases.
9.4.6. Which connector to choose?
Let's return to the architecture of a database application:
 |
We have seen various types of ADO.NET connectors:
- proprietary connectors are the most efficient, but make the [dao] layer dependent on proprietary classes. Changing the SGBD means changing the [dao] layer.
- ODBC or OLE DB connectors allow you to work with multiple databases without changing the [dao] layer. They are less powerful than proprietary connectors.
- the generic connector relies on proprietary connectors while presenting a standard interface to the [dao] layer.
So it seems that the generic connector is the ideal connector. In practice, however, the generic connector fails to hide all the particularities of a SGBD behind a standard interface. In the next paragraph, we'll look at the notion of parameterized queries. With SQL Server, a parameterized query has the following form:
With MySQL5, the same query would be written :
There is therefore a difference in syntax. The interface property IDbCommand described byragraphe 9.3.3, is as follows:
the parameter list of a parameterized SQL order. The order update articles set price=price*1.1 where id=@id has the @id parameter. |
The property Parameters type is IDataParameterCollection, an interface. It represents all parameters of the order SQL CommandText. The property Parameters has a method Add to add IDataParameter, an interface again. It has the following properties:
- ParameterName : parameter name
- DbType : the SQL type of the parameter
- Value : the value assigned to the
- ...
The type IDataParameter is well suited to parameters of the order SQL
because it contains named parameters. The ParameterName can be used.
The type IDataParameter not suitable for SQL order
because the parameters are not named. The order in which the parameters are added to the [IDbCommand.Parameters] collection is then taken into account. In this example, the 4 parameters should be inserted in the following order name, price, stockactuel, stockminimum. In a query with named parameters, the order in which the parameters are added is irrelevant. In the end, the developer can't totally ignore the SGBD he uses when initializing the parameters of a parameterized query. This is one of the current limitations of the generic connector.
There are frameworks which overcome these limitations and add new functionalities to the [dao] layer:
 |
A framework is a set of class libraries designed to facilitate a particular way of architecting an application. There are a number of such frameworks, allowing you to write layers [dao] that are both high-performance and insensitive to changes in SGBD :
- Spring.Net [http://www.springframework.net/] already presented in this document offers the equivalent of the generic connector studied, without its limitations, as well as various facilities to simplify data access. A version Java is also available.
- iBatis.Net [http://ibatis.apache.org] is older and richer than Spring.Net. A Java version is available.
- NHibernate [http://www.hibernate.org/] is a port of the Java version Hibernate world-famous Java. NHibernate allows the [dao] layer to exchange with the SGBD without issuing SQL commands. The [dao] layer works with Hibernate objects. A query language HBL (Hibernate Query language) is used to query objects managed by Hibernate. It is these objects that issue SQL commands. Hibernate can adapt to SQL owners of SGBD.
- LINQ (Language INtegrated Query), integrated into version 3.5 .NET and available in C# 2008. LINQ follows in the footsteps of NHibernate, but for the moment (May 2008) only the SGBD SQL Server is supported. This should evolve over time. LINQ goes further than NHibernate: its query language enables standard queries of three different types of data source:
- collections of objects (LINQ to Objects)
- a Xml file (LINQ to Xml)
- a database (LINQ to SQL)
These frameworks will not be discussed in this document. However, we strongly recommend their use in professional applications.
9.5. Parametric queries
In the previous paragraph, we discussed parameterized queries. We present them ici with an example for the SGBD SQL Server Compact. The project is as follows
 |
- in [1], the project. Only [App.config], [Article.cs] and [Parametres.cs] are used. Note also the SQL Server Ce [dbarticles.sdf] base.
- in [2], the project is configured to run [Parametres.cs]
- in [3], the project references
The [App.config] configuration file defines the database connection string:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbArticlesSqlServerCe" connectionString="Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;" />
</connectionStrings>
</configuration>
The [Article.cs] file defines an [Article] class. An object Article will be used to encapsulate the information in a row of the ARTICLES database [dbarticles.sdf] :
namespace Chap7 {
class Article {
// properties
public int Id { get; set; }
public string Nom { get; set; }
public decimal Prix { get; set; }
public int StockActuel { get; set; }
public int StockMinimum { get; set; }
// manufacturers
public Article() {
}
public Article(int id, string nom, decimal prix, int stockActuel, int stockMinimum) {
Id = id;
Nom = nom;
Prix = prix;
StockActuel = stockActuel;
StockMinimum = stockMinimum;
}
}
}
The [Parametres.cs] application implements parameterized requests:
using System;
using System.Data.SqlServerCe;
using System.Text;
using System.Data;
using System.Configuration;
namespace Chap7 {
class Parametres {
static void Main(string[] args) {
// using the configuration file
string connectionString = null;
try {
// connecting chain
connectionString = ConfigurationManager.ConnectionStrings["dbArticlesSqlServerCe"].ConnectionString;
} catch (Exception e) {
Console.WriteLine("Erreur de configuration : {0}", e.Message);
return;
}
// displays
Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);
// create a table of items
Article[] articles = new Article[5];
for (int i = 1; i <= articles.Length; i++) {
articles[i-1] = new Article(0, "article" + i, i * 100, i * 10, i);
}
// handle any exceptions
try {
// delete existing items from the database
ExecuteUpdate(connectionString, "delete from articles");
// table items are displayed
ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
// insert the table of items into the database
InsertArticles(connectionString, articles);
// table items are displayed
ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
} catch (Exception ex) {
// error msg
Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
}
}
// insert table of items
static void InsertArticles(string connectionString, Article[] articles) {
using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
// opening connection
connexion.Open();
// control configuration
string requête = "insert into articles(nom,prix,stockactuel,stockminimum) values(@nom,@prix,@sa,@sm)";
SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
sqlCommand.Parameters.Add("@nom",SqlDbType.NVarChar,30);
sqlCommand.Parameters.Add("@prix", SqlDbType.Money);
sqlCommand.Parameters.Add("@sa", SqlDbType.Int);
sqlCommand.Parameters.Add("@sm", SqlDbType.Int);
// command compilation
sqlCommand.Prepare();
// line insertion
for (int i = 0; i < articles.Length; i++) {
// parameter initialization
sqlCommand.Parameters["@nom"].Value = articles[i].Nom;
sqlCommand.Parameters["@prix"].Value = articles[i].Prix;
sqlCommand.Parameters["@sa"].Value = articles[i].StockActuel;
sqlCommand.Parameters["@sm"].Value = articles[i].StockMinimum;
// request execution
sqlCommand.ExecuteNonQuery();
}
}
}
// execute an update request
static void ExecuteUpdate(string connectionString, string requête) {
...
}
// execute a Select query
static void ExecuteSelect(string connectionString, string requête) {
...
}
// reader display
static void AfficheReader(IDataReader reader) {
...
}
}
The procedure [InsertArticles] on lines 51-75 is new compared to what was seen previously:
- line 51: the procedure receives two parameters:
- the connection chain connectionString which will allow the procedure to connect to the
- an array of objects Article to be added to the Articles database
- line 56: the [Article] object insertion request. It has four parameters:
- @name : article name
- @price : its price
- @its : its current stock
- @sm : its minimum stock
The syntax of this parameterized query is proprietary to SQL Server Compact. We saw in the previous paragraph that with MySQL5, the syntax would be as follows:
With SQL Server Compact, each parameter must be preceded by the @ character. Parameter names are free.
- lines 58-61: define the characteristics of each of the 4 parameters and add them, one by one, to the list of object parameters SqlCeCommand which encapsulates the SQL command to be executed.
We use ici the method [SqlCeCommand].Parameters.Add which has six signatures. We use both below:
Add(string parameterName, SQLDbType type)
adds and configures the parameter named parameterName. This name must be one of those in the configured query parameter: (@name, ...). type designates the SQL type of the column concerned by the parameter. Many types are available, including :
type SQL | c# type | comment |
Int64 | ||
DateTime | ||
Decimal | ||
Double | ||
Int32 | ||
Decimal | ||
String | fixed-length chain | |
String | variable-length string | |
Single |
Add(string parameterName, SQLDbType type, int size)
the third parameter size sets the column size. This information is only useful for certain types SQL, type NVarChar for example.
- line 63: the parameterized request is compiled. We also say that we prepare, hence the name of the method. This operation is not essential. It's there to improve performance. When a SGBD executes a SQL request, it does some optimization work before executing it. A parameterized query is intended to be executed several times with different parameters. The query text, however, remains unchanged. The optimization work can therefore be carried out only once. Some SGBD programs can "prepare" or "compile" parameterized queries. An execution plan is then defined for this query. This is the optimization phase we've been talking about. Once compiled, the query is executed repeatedly, each time with new effective parameters but the same execution plan.
Compilation is not the only advantage of parameterized queries. Let's take the query we studied:
We might want to construct the query text programmatically:
string requête="insert into articles(nom,prix,stockactuel,stockminimum) values('"+nom+"',"+prix+","+sa+","+sm+")";
Above, if (name,price,sa,sm) is ("item1",100,10,1), the previous query becomes :
string requête="insert into articles(nom,prix,stockactuel,stockminimum) values('article1',100,10,1)";
Now if (name,price,sa,sm) is ("item1",100,10,1), the previous query becomes :
string requête="insert into articles(nom,prix,stockactuel,stockminimum) values('l'article1',100,10,1)";
and becomes syntactically incorrect because of the apostrophe in the noun article1. If name comes from a user input, this means we have to check whether the input has no apostrophes, and if it does, to neutralize them. This neutralization is dependent on SGBD. The advantage of the prepared query is that it does this work itself. This facility alone justifies the use of a prepared query.
- lines 65-73: the articles in the table are inserted one by one
- lines 67-70: each of the four query parameters receives its value via its property Value.
- line 72: the now complete insertion request is executed in the usual way.
Here's an example:
- line 3: message after deleting all table rows
- lines 5-7: show that the table is empty
- lines 10-18: show the table after inserting the 5 articles
9.6. Transactions
9.6.1. General
A transaction is a sequence of SQL orders executed "atomically":
- or all operations are successful
- either one of them fails, in which case all the previous ones are cancelled
In the end, the operations of a transaction have either all been successfully applied, or none at all. When the user is in control of the transaction, he validates a transaction with a COMMIT command, or cancels it with a ROLLBACK command.
In our previous examples, we didn't use a transaction. And yet we did, because in a SGBD a SQL order is always executed within a transaction. If the .NET client does not itself start a transaction explicit, the SGBD uses a transaction implicit. There are two common cases:
- each individual SQL order is the subject of a transaction, initiated by the SGBD before the order and closed afterwards. We say we're in autocommit. So it's as if the .NET customer were making transactions for each SQL order.
- the SGBD is not in mode autocommit and starts an implicit transaction on the 1st order SQL that the client .NET issues outside a transaction and lets the client close it. All SQL orders issued by client .NET are then part of the transaction implicit. This can be terminated by various events: the customer closes the connection, starts a new transaction, etc., but on is then in a SGBD-dependent situation. This mode should be avoided.
The default mode is usually set by configuring SGBD. Some SGBD default to the autocommit, others not. by default, SQLServer Compact is in autocommit.
The SQL orders of different users are executed at the same time in transactions that work in parallel. Operations carried out by one transaction can affect those carried out by another. There are four levels of watertightness between the transactions of different users:
- Uncommitted Read
- Committed Read
- Repeatable Read
- Serializable
Uncommitted Read
This isolation mode is also known as "Dirty Read". Here's an example of what can happen in this mode:
- a user U1 starts a transaction on a table T
- a user U2 starts a transaction on the same table T
- user U1 modifies rows in table T but does not yet validate them
- the U2 user "sees" these modifications and makes decisions based on what he sees
- the user cancels the transaction with a ROLLBACK
We can see that in 4, user U2 has made a decision based on data that will later prove to be false.
Committed Read
This isolation mode avoids the previous pitfall. In this mode, user U2 at step 4 will not "see" the modifications made by user U1 to table T. He will only see them after U1 has performed a COMMIT of his transaction.
In this mode, also known as "Unrepeatable Read", the following situations may nevertheless arise:
- a user U1 starts a transaction on a table T
- a user U2 starts a transaction on the same table T
- user U2 performs a SELECT to obtain the average of a column C of the rows in T that satisfy a certain condition
- user U1 modifies (UPDATE) certain values in column C of T and validates them (COMMIT)
- user U2 repeats the same SELECT as in 3. He will discover that the average in column C has changed as a result of the modifications made by U1.
Now user U2 only sees the modifications "validated" by U1. But while he remains in the same transaction, two identical operations 3 and 5 give different results. This situation is known as "Unrepeatable Read". It's an annoying situation for anyone wishing to have a stable image of the T table.
Repeatable Read
In this isolation mode, a user is guaranteed to get the same results for his database readings as long as he remains in the same transaction. He works on a photo on which the modifications made by other transactions, even validated ones, are never reflected. He will only see them when he ends his transaction with a COMMIT or ROLLBACK.
However, this isolation mode is not yet perfect. After operation 3 above, the rows consulted by user U2 are locked. During operation 4, user U1 will not be able to modify (UPDATE) the values in column C of these rows. However, he can add rows (INSERT). If some of the added rows verify the condition tested in 3, operation 5 will give an average different from that found in 3, due to the added rows. These lines are sometimes referred to as ghost lines.
To solve this new problem, we need to switch to "Serializable" isolation.
Serializable
In this isolation mode, transactions are completely isolated from each other. It ensures that the result of two transactions performed simultaneously will give the same result as if they were performed one after the other. To achieve this result, in operation 4, when user U1 wants to add lines that would change the result of user U1's SELECT, he will be prevented from doing so. An error message will tell him that insertion is not possible. It will become possible once user U2 has validated his transaction.
The four SQL levels of transaction isolation are not available in all SGBD. The default isolation level is usually the Committed Read. The desired sealing level for a transaction can be explicitly specified when an explicit transaction is created by a .NET client.
9.6.2. The API transaction management system
A connection implements the IDbConnection presented paragraph 9.3.3. This interface has the following method :
M | starts a transaction. |
This method has two signatures:
- IDbTransaction BeginTransaction() : starts a transaction and returns the IDbTransaction to control it
- IDbTransaction BeginTransaction(IsolationLevel level) : also specifies the level of sealing required for the transaction. level takes its values from the following enumeration:
the transaction can read data written by another transaction which the latter has not yet validated - avoid this | |
the transaction cannot read data written by another transaction that it has not yet validated. However, the data read twice in a row by the transaction may change (not repeatable reads) because another transaction may have modified it in the meantime (the lines read are not locked - only the updated lines are). Furthermore, another transaction may have added lines (ghost lines) that will be included in the second read. | |
lines read by the transaction are locked in the same way as updated lines. This prevents another transaction from modifying them. It does not prevent rows from being added. | |
the tables used by the transaction are locked, preventing new rows from being added by another transaction. Everything happens as if the transaction were alone. Reduces performance as transactions no longer work in parallel. | |
the transaction works on a copy of the data made at time T. Used when the transaction is read-only. Gives the same result as serializable avoiding its cost. |
Once the transaction has been started, it is controlled by the IDbTransaction, an interface with the following P properties and M methods:
Name | Type | Role |
P | the connection IDbConnection which supports the transaction | |
M | validates the transaction - the results of SQL orders issued in the transaction are copied to the database. | |
M | invalidates the transaction - the results of SQL orders issued in the transaction are not copied to the database. |
9.6.3. The sample program
Let's go back to the previous project and take a look at the program [Transactions.cs] :
 |
- in [1], the project.
- in [2], the project is configured to run [Transactions.cs]
The code for [Transactions.cs] is as follows:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlServerCe;
using System.Text;
namespace Chap7 {
class Transactions {
static void Main(string[] args) {
// using the configuration file
string connectionString = null;
try {
// connecting chain
connectionString = ConfigurationManager.ConnectionStrings["dbArticlesSqlServerCe"].ConnectionString;
} catch (Exception e) {
Console.WriteLine("Erreur de configuration : {0}", e.Message);
return;
}
// displays
Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);
// create a table of 2 items with the same name
Article[] articles = new Article[2];
for (int i = 1; i <= articles.Length; i++) {
articles[i - 1] = new Article(0, "article", i * 100, i * 10, i);
}
// handle any exceptions
try {
Console.WriteLine("Insertion sans transaction...");
// the table of items is first inserted into the database without a transaction
ExecuteUpdate(connectionString, "delete from articles");
try {
InsertArticlesOutOfTransaction(connectionString, articles);
} catch (Exception ex) {
// error msg
Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
}
ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
// we do the same thing again, but in a transaction this time
Console.WriteLine("\n\nInsertion dans une transaction...");
ExecuteUpdate(connectionString, "delete from articles");
InsertArticlesInTransaction(connectionString, articles);
ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
} catch (Exception ex) {
// error msg
Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
}
}
// insert item table without transaction
static void InsertArticlesOutOfTransaction(string connectionString, Article[] articles) {
....
}
// insert an array of items into a transaction
static void InsertArticlesInTransaction(string connectionString, Article[] articles) {
....
}
// execute an update request
static void ExecuteUpdate(string connectionString, string requête) {
....
}
// execute a Select query
static void ExecuteSelect(string connectionString, string requête) {
...
}
// reader display
static void AfficheReader(IDataReader reader) {
...
}
}
}
}
- lines 12-19: database connection string SQLServer Ce is read into [App.config]
- lines 25-28: an array of two objects Article is created. These two articles have the same "article" name. Or, the [dbarticles.sdf] database has a uniqueness constraint on its [name] column (cf paragraph 9.3.1). These two articles cannot therefore be present in the database at the same time. The two articles with the name "article" are added to the table articles. So there's going to be a problem, c.a.d. an exception thrown by the SGBD and relayed by its ADO.NET connector. To demonstrate the effect of the transaction, the two articles will be inserted in two different environments:
- first of all without any transaction. Remember ici that, in this case, SQLServer Compact works in autocommit, c.a.d. inserts each SQL order into a transaction implicit. The 1st article will be inserted. The second will not.
- then in a transaction explicit encapsulating the two insertions. Because the second insertion will fail, the first will be defeated. In the end, no insertion will be made.
- line 33: the table articles is emptied
- line 35: insertion of the two articles without an explicit transaction. Because we know that the second insertion will trigger an exception, this is handled by a try / catch
- line 46: table display articles
- lines 44-46: the same sequence is repeated, but this time an explicit transaction is used to perform the insertions. The exception encountered is ici handled by the InsertArticlesInTransaction.
- lines 54-56: the method InsertArticlesOutOfTransaction is the InsertArticles program [Parametres.cs] studied previously.
- lines 64-66: the method ExecuteUpdate is the same as above. The SQL order is executed in an implicit transaction. This is possible ici because we know that in this case, SQLServer Compact works in the autocommit.
- lines 69-71: ditto for the method ExecuteSelect.
The method InsertArticlesInTransaction is as follows:
// insert an array of items into a transaction
static void InsertArticlesInTransaction(string connectionString, Article[] articles) {
using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
// opening connection
connexion.Open();
// control configuration
string requête = "insert into articles(nom,prix,stockactuel,stockminimum) values(@nom,@prix,@sa,@sm)";
SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
sqlCommand.Parameters.Add("@nom", SqlDbType.NVarChar, 30);
sqlCommand.Parameters.Add("@prix", SqlDbType.Money);
sqlCommand.Parameters.Add("@sa", SqlDbType.Int);
sqlCommand.Parameters.Add("@sm", SqlDbType.Int);
// command compilation
sqlCommand.Prepare();
// transaction
SqlCeTransaction transaction = null;
try {
// start of transaction
transaction = connexion.BeginTransaction(IsolationLevel.ReadCommitted);
// the SQL command must be executed in this transaction
sqlCommand.Transaction = transaction;
// line insertion
for (int i = 0; i < articles.Length; i++) {
// parameter initialization
sqlCommand.Parameters["@nom"].Value = articles[i].Nom;
sqlCommand.Parameters["@prix"].Value = articles[i].Prix;
sqlCommand.Parameters["@sa"].Value = articles[i].StockActuel;
sqlCommand.Parameters["@sm"].Value = articles[i].StockMinimum;
// request execution
sqlCommand.ExecuteNonQuery();
}
// validate the transaction
transaction.Commit();
Console.WriteLine("transaction validée...");
} catch {
// we undo the transaction
if (transaction != null)transaction.Rollback();
Console.WriteLine("transaction invalidée...");
}
}
}
We will only detail the differences between it and the InsertArticles of the program [Parametres.cs] studied above:
- line 16: a transaction SqlCeTransaction is declared.
- lines 17, 35: try / catch to handle the exception that will arise at the end of the 2nd insertion
- line 19: the transaction is created. It belongs to the current connection.
- line 21: the SQL command is set in the transaction
- lines 23-31: inserts are made
- line 33: all went well - the transaction has been validated - the insertions will now be definitively integrated into the database.
- line 37: we had a problem. The transaction is defeated if it existed.
Execution gives the following results:
- line 4: displayed by the ExecuteUpdate("delete from articles") - there were no rows in the table
- line 5: the exception caused by the second insertion. The message indicates that the UQ__ARTICLES__0000000000000010 constraint has not been checked. You can find out more by looking at the database properties:
 |
- in [1] in Visual Studio's [Database Explorer] view, we've created a connection [2] to the [dbarticles.sdf] database. This has an index UQ__ARTICLES__0000000000000010. Right-click on the index to access its properties
- in [3,4], we can see that the index UQ__ARTICLES__0000000000000010 corresponds to a uniqueness constraint on column [NOM]
- lines 7-11: table display articles after the two insertions. It is not empty: the 1st article has been inserted.
- line 15: displayed by the ExecuteUpdate("delete from articles") - there was a row in the table
- line 16: message displayed by InsertArticlesInTransaction if the transaction fails.
- lines 18-20: show that no insertion has been made. The Rollback of the transaction has undone the 1st insertion.
9.7. The ExecuteScalar method
9.7.1. Among the methods of the IDbCommand described paragraph 9.3.3, there was the following method:
M | to execute a SQL order Select returns a single result as in : select count(*) from articles. |
We show ici an example of how to use this method. Back to the :
 |
- in [1], the project.
- in [2], the project is configured to run [ExecuteScalar.cs]
The [ExecuteScalar.cs] program is as follows:
...
namespace Chap7 {
class Scalar {
static void Main(string[] args) {
// using the configuration file
string connectionString = null;
...
// displays
Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);
// creation of a 5-item table
Article[] articles = new Article[5];
for (int i = 1; i <= articles.Length; i++) {
articles[i - 1] = new Article(0, "article" + i, i * 100, i * 10, i);
}
// handle any exceptions
try {
// insert the item table into a transaction
ExecuteUpdate(connectionString, "delete from articles");
InsertArticlesInTransaction(connectionString, articles);
ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
// average item prices
decimal prixMoyen = (decimal)ExecuteScalar(connectionString, "select avg(prix) from articles");
Console.WriteLine("Prix moyen des articles={0}", prixMoyen);
// or the number of items
int nbArticles = (int)ExecuteScalar(connectionString, "select count(id) from articles");
Console.WriteLine("Nombre d'articles={0}", nbArticles);
} catch (Exception ex) {
// error msg
Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
}
}
// insert an array of items into a transaction
static void InsertArticlesInTransaction(string connectionString, Article[] articles) {
...
}
// execute an update request
static object ExecuteScalar(string connectionString, string requête) {
using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
// opening connection
connexion.Open();
// request execution
return new SqlCeCommand(requête, connexion).ExecuteScalar();
}
}
// execute an update request
static void ExecuteUpdate(string connectionString, string requête) {
...
}
// execute a Select query
static void ExecuteSelect(string connectionString, string requête) {
...
}
// reader display
static void AfficheReader(IDataReader reader) {
...
}
}
}
- lines 14-17: creation of an array of 5 articles
- line 22: the table articles is emptied
- line 23: filled with the 5 acticles
- line 24: it is displayed
- line 26: asks for the average price of articles
- line 29: requests the number of articles
- line 49: using the method [IDbCommand].ExecuteScalar() to calculate each of these values.
The results are as follows:
Lines 15 and 16 show the two values returned by the ExecuteScalar.
9.8. Sample application - version 7
We take the example application IMPOTS. The latest version was studied in paragraph 7.6. It was the following three-coat application:
 |
- the [ui] layer was a graphical interface [A] and the [dao] layer was a text file [B].
- layer instantiation and integration into the application were handled by Spring.
 |
We modify the [dao] layer so that it fetches its data from a database.
9.8.1. Visit database
The contents of the previous text file [B] are put into a MySQL5 database. We'll show you how to do this:
 |
- [1] :MySQL Administrator has been launched
- [2,3]: in the [Schemata] area, right-click and select option [Create Schema] to create a new database
- [4]: the base will be called [bdimpots]
- [5]: it has been added to the [Schemata] zone bases.
 |
- [6,7]: right-click on the table and select option [Create New Table] to create a table
- [8]: the table will be called [slices]. It will have columns [id, limit, coeffR, coeffN].
- [9,10]: [id] is a primary key of type INTEGER and has the attribute AUTO_INCREMENT [10]: the SGBD will fill this column when rows are added.
- columns [limit, coeffR, coeffN] are of type DOUBLE.
- [11,12]: the new table appears in the [Schema Tables] tab of the database.
 |
- [13,14]: to add data to the table
- [15]: [Query Browser] has been launched
- [16]: data entered and validated for columns [limit, coeffR, coeffN]. Column [id] was filled by SGBD. Validation was performed with [17].
 |
- still in [Query Browser] [18], we run [20] query [19]. This creates a user 'admimpots' password 'mdpimpotsand gives it all the privileges (grant all privileges) on all objects in the database bdimpots (on bdimpots.*). This will allow us to work on the [bdimpots] database with the [admimpots] user rather than the [root] administrator.
9.8.2. The Visual Studio solution
 |
We will follow the same approach as for version 5 of the example application (see paragraph 6.4). We will gradually build the following Visual Studio solution:
 |
- in [1]: the solution ImpotsV7 is made up of three projects, one for each of the application's three layers
- in [2]: the [dao] project in the [dao] layer, which will now use a database
- in [3]: the [metier] project for the [metier] layer. We take ici from version 5's [metier] layer, described in paragraph 6.4.4.
- in [4]: project [ui] of layer [ui]. We take ici the [ui] layer from version 6, described in paragraph 7.6.
We're relying on what we've learned to retrieve two layers that have already been written, the [ui] and [metier] layers. This is made possible by the layered architecture we have chosen. However, we will need the source code for the [ui] and [metier] layers. It is not possible to be satisfied with the DLL of the layers. When, in version 5, the DLL of the [metier] layer was created, it had a dependency on the DLL of the [dao] layer. This dependency was hard-coded in the DLL of the [metier] layer (name of the DLL of the [dao] layer, version, identity token, etc.). For example, version 5's DLL [ImpotsV5-metier.dll] will only work with the DLL [ImpotsV5-dao.dll] with which it was compiled. If the DLL of the [dao] layer is changed, the [metier] layer must be recompiled to create a new DLL. The same applies to the [ui] layer. The [ui] and [metier] layers will therefore not be modified, but will be recompiled to work with the DLL of the new [dao] layer.
9.8.3. The [dao] layer
 |
 |
Project references (see [1] in the project)
- nunit.framework : for NUnit test
- System.Configuration : to use the configuration file [App.config]
- System.Data : because we use a database.
The entities (see [2] in the project)
Classes [TrancheImpot] and [ImpotException] are those of previous versions.
The [dao] layer (see [3] in the project)
The [IImpotDao] interface has not changed:
using Entites;
namespace Dao {
public interface IImpotDao {
// tax brackets
TrancheImpot[] TranchesImpot{get;}
}
}
The [DataBaseImpot] implementation class for this interface is as follows:
using System;
using System.Collections.Generic;
using System.Data.Common;
using Entites;
namespace Dao {
public class DataBaseImpot : IImpotDao {
// tax brackets
private TrancheImpot[] tranchesImpot;
public TrancheImpot[] TranchesImpot { get { return tranchesImpot; } }
// manufacturer
public DataBaseImpot(string factory, string connectionString, string requête) {
// factory: the factory of the target SGBD
// connectionString: connection string to tax bracket base
// handle any exceptions
try {
// we retrieve a generic connector for the SGBD
DbProviderFactory connecteur = DbProviderFactories.GetFactory(factory);
using (DbConnection connexion = connecteur.CreateConnection()) {
// connection configuration
connexion.ConnectionString = connectionString;
// opening connection
connexion.Open();
// configuration Command
DbCommand sqlCommand = connecteur.CreateCommand();
sqlCommand.CommandText = requête;
sqlCommand.Connection = connexion;
// request execution
List<TrancheImpot> listTrancheImpot = new List<TrancheImpot>();
using (DbDataReader reader = sqlCommand.ExecuteReader()) {
while (reader.Read()) {
// a new tax trance is created
listTrancheImpot.Add(new TrancheImpot() { Limite = reader.GetDecimal(0), CoeffR = reader.GetDecimal(1), CoeffN = reader.GetDecimal(2) });
}
}
// put the tax brackets in your instance
tranchesImpot = listTrancheImpot.ToArray();
}
} catch (Exception ex) {
// encapsulate the exception in a ImpotException type
throw new ImpotException("Erreur de lecture des tranches d'impôt", ex) { Code = 101 };
}
}
}
}
- line 7: class [DataBaseImpot] implements interface [IImpotDao].
- line 10: implementation of the [TranchesImpot] interface method. It simply returns a reference to the table of tax brackets from line 9. This table will be constructed by the class constructor.
- line 13: the builder. It uses a generic connector (see paragraph 9.4.5) to evaluate the tax bracket database. The builder receives three parameters:
- the name of the "factory" from which it will request classes to connect to the database, issue SQL commands, and evaluate the result of a Select.
- the connection string it must use to connect to the database
- the order SQL Select he must execute to get the tax brackets.
- line 19: requests a factory connector
- line 20: creates a connection with this connector. It is created but not yet operational
- line 22: the connection string is initialized. You can now connect.
- line 24: connect
- line 26: requests a [DbCommand] object from the connector to execute a SQL command
- line 27: sets SQL command to be executed
- line 28: sets the connection on which to run it
- line 30: a list [listTrancheImpot] of objects of type [TrancheImpot] is created empty.
- line 31: order SQL Select is executed
- lines 32-35: the [DbDataReader] object resulting from Select is used. Each row of the Select result table is used to instantiate an object of type [TrancheImpot], which is added to the [listTrancheImpot] list.
- line 38: the list of objects of type [TrancheImpot] is transferred to the table in line 9.
- lines 40-43: any exception is encapsulated in type [ImpotException] and assigned error code 101 (arbitrary).
The [Test1] test (see [4] in the project)
The [Test1] class simply displays the tax brackets on the screen. This is the class already used in version 5 (paragraph 6.4.3) except for the instruction that instantiates the [dao] layer (line 14).
using System;
using Dao;
using Entites;
using System.Configuration;
namespace Tests {
class Test1 {
static void Main() {
// create the [dao] layer
IImpotDao dao = null;
try {
// layer creation [dao]
dao = new DataBaseImpot(ConfigurationManager.AppSettings["factoryMySql5"], ConfigurationManager.ConnectionStrings["dbImpotsMySql5"].ConnectionString, ConfigurationManager.AppSettings["requete"]);
} catch (ImpotException e) {
// error display
string msg = e.InnerException == null ? null : String.Format(", Exception d'origine : {0}", e.InnerException.Message);
Console.WriteLine("L'erreur suivante s'est produite : [Code={0},Message={1}{2}]", e.Code, e.Message, msg == null ? "" : msg);
// program stop
Environment.Exit(1);
}
// display tax brackets
TrancheImpot[] tranchesImpot = dao.TranchesImpot;
foreach (TrancheImpot t in tranchesImpot) {
Console.WriteLine("{0}:{1}:{2}", t.Limite, t.CoeffR, t.CoeffN);
}
}
}
}
Line 14 uses the following configuration file [App.config]:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbImpotsMySql5" connectionString="Server=localhost;Database=bdimpots;Uid=admimpots;Pwd=mdpimpots;" />
</connectionStrings>
<appSettings>
<add key="requete" value="select limite, coeffr, coeffn from tranches"/>
<add key="factoryMySql5" value="MySql.Data.MySqlClient"/>
</appSettings>
</configuration>
- line 4: database connection string MySQL5. Note that it is the user [admimpots] who will establish the connection.
- line 8: the "factory" for working with SGBD MySQL5
- line 7: query SQL Select to obtain tax brackets.
The project is configured to run [Test1.cs] :
Running the test gives the following results:
The NUnit [NUnit1] test (see [4] in the project)
The unit test [NUnit1] is the one already used in version 5 (paragraph 6.4.3) except for the instruction that instantiates the [dao] layer (line 16).
using System;
using System.Configuration;
using Dao;
using Entites;
using NUnit.Framework;
namespace Tests {
[TestFixture]
public class NUnit1 : AssertionHelper{
// layer [dao] to be tested
private IImpotDao dao;
// manufacturer
public NUnit1() {
// dao] layer initialization
dao = new DataBaseImpot(ConfigurationManager.AppSettings["factoryMySql5"], ConfigurationManager.ConnectionStrings["dbImpotsMySql5"].ConnectionString, ConfigurationManager.AppSettings["requete"]);
}
// test
[Test]
public void ShowTranchesImpot(){
// display tax brackets
TrancheImpot[] tranchesImpot = dao.TranchesImpot;
foreach (TrancheImpot t in tranchesImpot) {
Console.WriteLine("{0}:{1}:{2}", t.Limite, t.CoeffR, t.CoeffN);
}
// some tests
Expect(tranchesImpot.Length,EqualTo(7));
Expect(tranchesImpot[2].Limite,EqualTo(14753).Within(1e-6));
Expect(tranchesImpot[2].CoeffR, EqualTo(0.191).Within(1e-6));
Expect(tranchesImpot[2].CoeffN, EqualTo(1322.92).Within(1e-6));
}
}
}
To run this unit test, the project must be of type [Class Library] :
 |
- in [1]: the nature of the project has been changed
- in [2]: the DLL generated will be called [ImpotsV7-dao.dll]
- in [3]: after project generation (F6), the folder [dao/bin/Release] contains the DLL [ImpotsV7-dao.dll]. It also contains the configuration file [App.config] renamed [name DLL].config. This is standard in Visual Studio.
The DLL [ImpotsV7-dao.dll] is then loaded into the framework NUnit and executed :
 |
- in [1]: tests passed. We now consider the [dao] layer operational. Its DLL contains all the project's classes, including the test classes. These are no longer needed. We rebuild DLL to exclude the test classes.
- in [2]: the [tests] folder is excluded from the project
- in [3]: the new project. This is regenerated by pressing F6 to generate a new DLL. This DLL will be used by the application's [metier] and [ui] layers.
9.8.4. The [ layerjob]
 |
 |
- in [1], the [metier] project became the solution's active project
- in [2]: project references. Note the reference on the DLL of the [dao] layer created earlier. This reference addition procedure was described in version 5, paragraph 6.4.4.
- in [3]: the [metier] layer. This is the version 5 layer, described in paragraph 6.4.4.
The [metier] project is configured to generate a DLL :
 |
- [1]: the project is of the "class library" type
- [2]: project generation will produce DLL [ImpotsV7-metier.dll] [3].
The project is generated (F6).
9.8.5. The [ui] layer
 |
 |
- in [1], the [ui] project became the active project for the solution
- in [2]: project references. Note the references on the DLL of the [dao] and [metier] layers.
- in [3]: the [ui] layer. This is the version 6 layer described in paragraph 7.6.
- in [4], the [App.config] configuration file is similar to that of version 6, differing only in the way the [dao] layer is instantiated by Spring :
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<sectionGroup name="spring">
<section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
<section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
</sectionGroup>
</configSections>
<spring>
<context>
<resource uri="config://spring/objects" />
</context>
<objects xmlns="http://www.springframework.net">
<object name="dao" type="Dao.DataBaseImpot, ImpotsV7-dao">
<constructor-arg index="0" value="MySql.Data.MySqlClient"/>
<constructor-arg index="1" value="Server=localhost;Database=bdimpots;Uid=admimpots;Pwd=mdpimpots;"/>
<constructor-arg index="2" value="select limite, coeffr, coeffn from tranches"/>
</object>
<object name="metier" type="Metier.ImpotMetier, ImpotsV7-metier">
<constructor-arg index="0" ref="dao"/>
</object>
</objects>
</spring>
</configuration>
- lines 11-25: Spring configuration
- lines 15-24: objects instantiated by Spring
- lines 16-20: instantiate layer [dao]
- line 16: layer [dao] is instantiated by class [Dao.DataBaseImpot], which is in DLL [ImpotsV7-Dao]
- lines 17-19: the three parameters (factory of the SGBD used, connection string, SQL request) to be supplied to the [Dao.DataBaseImpot] class constructor
- lines 21-23: [metier] layer instantiation. This is the same configuration as in version 6.
Tests
The [ui] project is configured as follows:
 |
- [1]: the project is of the "Windows Application" type
- [2]: project generation will produce executable [ImpotsV7-ui.exe]
An example is given in [3].
9.8.6. Change database
 |
The [dao] layer above was written with a generic connector and a MySQL5 database. We propose ici to switch to a SQL Server Compact base to show that only the configuration will change.
The base SQL Server Compact will be as follows:
 |
- [1]: the [dbimpots.sdf] database in Visual studio's [DataBase Explorer] view [2]. It was created without a password.
- [3]: the table [data] containing the data. We've deliberately chosen different names for the table and columns from those used with the MySQL5 database, to re-emphasize the importance of putting such details in the configuration file rather than in the code.
- [4]: column [id] is primary key and has attribute Identity : it's the SGBD that will assign its values.
- [5]: contents of table [data].
 |
- [6]: the [dbimpots.sdf] database has been placed in the [ui] project folder and integrated into this project.
- [7]: the [dbimpots.sdf] database will be copied to the project execution folder.
The configuration file [App.config] for the new database is as follows:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<sectionGroup name="spring">
<section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
<section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
</sectionGroup>
</configSections>
<spring>
<context>
<resource uri="config://spring/objects" />
</context>
<objects xmlns="http://www.springframework.net">
<!--
<object name="dao" type="Dao.DataBaseImpot, ImpotsV7-dao">
<constructor-arg index="0" value="MySql.Data.MySqlClient"/>
<constructor-arg index="1" value="Server=localhost;Database=bdimpots;Uid=admimpots;Pwd=mdpimpots;"/>
<constructor-arg index="2" value="select limite, coeffr, coeffn from tranches"/>
</object>
-->
<object name="dao" type="Dao.DataBaseImpot, ImpotsV7-dao">
<constructor-arg index="0" value="System.Data.SqlServerCe.3.5"/>
<constructor-arg index="1" value="Data Source=|DataDirectory|\dbimpots.sdf;" />
<constructor-arg index="2" value="select data1, data2, data3 from data"/>
</object>
<object name="metier" type="Metier.ImpotMetier, ImpotsV7-metier">
<constructor-arg index="0" ref="dao"/>
</object>
</objects>
</spring>
</configuration>
- lines 23-27: configuration of the [dao] layer to use the [dbimpots.sdf] database.
The results are identical to the previous ones. Note the advantage of using a generic connector to make the [dao] layer insensitive to changes in SGBD. We have seen, however, that this connector is not suitable for all situations, particularly those involving parameterized queries. In such cases, there are other solutions such as the one mentioned above, third-party data access frameworks (Spring, iBatis, NHibernate, LINQ, ...).
9.9. To find out more ...
- LINQ is featured in numerous publications, including the book: C# 3.0 in a Nutshell, Joseph and Ben Albahari, published by O'Reilly, already cited in the introduction to this document.
- iBatis is presented in the book: iBatis in Action, Clinton Begin, Manning Editions
- Nhibernate in Action, published by Manning, is scheduled for release in July 2008
Spring, iBatis, NHibernate have reference manuals available on the websites of the various frameworks.