SQL Server and MySQL Database Models in a C#.NET Application - Part 1

Develop a C#.NET web application using multiple database using SQL Server 2005/2008 and MySQL 5.1. I got the requirements from one of my client, web application should support both SQL Server and MySQL database. Where ever my client want to install the application to go with either any one of SQL Server or MySQL database setup.

Based on the above my client multiple database models requirement, i architect the application with one class file to connect both database. So i decided to keep maintain same name(tables, indexes, stored procedures, triggers, cursors, views and functions) for both database. I used more stored proceduresfor the transactions and to produce the same result set.

In the programming side, it is easy to maintain the whole application. If any additions or modifications, just changed in one class file, that is taking care of the entire application database transactions.

I created a one class file, that will contain all the database global variables, properties, functions and objects. It contain both MS SQL Server and MySQL namespace, classes(SqlConnection, MySqlConnection, SqlCommand, MySqlCommand, SqlDataAdapter, MySqlDataAdapter, SqlParameter, and MySqlParameter), and connection string.

Then i created common functions to connect both database, methods for parameters to pass in, out and in-out values, retrieve record set list, method for insert, update and delete record and finally method for to read return values.

How do i differentiate between two database?
How does it work?
How does an application interact between two database?

It is very simple concept, i just use the '#define directive', to select the database type.

#define MS_SQLSERVER
#define MY_SQL

To depend upon the client database software, the '#define directive' will be commented by either one. If the client use the MS SQL Server database, then the MySQL will be commented, so all the MS SQL Server classes, methods, and codes are activated. MySQL part of the code will be grayed (i.e., inactivated). See the below code

#define MS_SQLSERVER
//#define MY_SQL

If the client use the MySQL database, then the MS SQL Server will be commented, so all the MySQL classes, methods, and codes are activated. MS SQL Server part of the code will be grayed (i.e., inactivated). See the below code

//#define MS_SQLSERVER
#define MY_SQL

namespace declaration code

#if MS_SQLSERVER
  using System.Data.SqlClient;
#else
  using MySql.Data.MySqlClient;
#endif

If MySQL is defined, then the MS SQL Server namespace, using System.Data.SqlClient; line will be grayed, not activated and vice verse.

Global variables declaration code

#if MS_SQLSERVER
  public string CONNECTION_STRING = System.Configuration.ConfigurationManager.AppSettings["MSSQL_ConnectionString"].ToString();
#else
  public string CONNECTION_STRING = System.Configuration.ConfigurationManager.AppSettings["MYSQL_ConnectionString"].ToString();
#endif

Both the connection string values are configured in the web.config file.

<appSettings>
  <add key="MYSQL_CS" value="server=localhost;database=dnsdb;uid=dnsuid;Password=dnspwd;Allow User Variables=true"/>
  <add key="MSSQL_CS" value="Server=DNS-SVR,1433;PASSWORD=dnspwd;UID=dnsuid;Database=dnsdb;"/>
</appSettings>

Member Variables declaration code

private string sProcedureName;
private DataSet dsList = new DataSet();
#if MS_SQLSERVER
  private SqlConnection sqlConn = new SqlConnection();
  private SqlCommand sqlCmd = new SqlCommand();
  private SqlDataAdapter sqlDAList = new SqlDataAdapter();
#else
  private MySqlConnection sqlConn = new MySqlConnection();
  private MySqlCommand sqlCmd = new MySqlCommand();
  private MySqlDataAdapter sqlDAList = new MySqlDataAdapter();
#endif

If MS SQL Server is defined, then SqlConnection(), SqlCommand(), and SqlDataAdapter() classes are activated.

Continue in Part 2