Certainly if you ask me
which one is better: Using Microsoft SQL server or MySQL server as a database to
store your application data, I say, of course, Microsoft SQL server as it offers
more features and demonstrates less number of bugs and problems. But what if the
majority of hosting servers provides MySQL server as database system and even
the minority that offers Microsoft SQL server is very expensive. Hence you
haven't any choice but storing and manipulating your data from a MySQL
database.
In the below table, I try
to compare the two products:
| Characteristics |
Microsoft SQL server |
MySQL server |
| Disponibility in the market according to my region
(Tunisia) |
Less offered by the hosting server machines as they almost use
Linux and Unix platforms , moreover, even if they make use of windows server
platform MySQL server is more present than Microsoft SQL server |
It is rare to find a hosting server machine that offers
Microsoft SQL server as a front end database, and even if you find one, it is
more expensive than the equivalent that offers MySQL server |
| Features |
Offers less features |
Offers more features |
| Coherence with .Net application |
Less compatibility and more bugs in spite of the endeavors made
to make easy the connection with .net applications |
The best choice if you are .Net developer is SQL server, it
demonstrates the best performance, easy to establish secure and stable
connections. In addition it offers good security features |
| Simplicity |
It is more simple to deal with MySQL server |
It is less simple to deal with Microsoft SQL
server |
| Connection |
More difficult to connect to MySQL database, the best and the
only way is to use ODBC the 3.51 ODBC connector and the rest, I mean OLEDB
connectors, the recent ODBC connectors versions and event the others tools
represent a lot of bugs and they are not stables |
There are several ways to connect to Microsoft SQL server. The
.Net environment provides special libraries to deal only with SQL server
connections. More over you can use ODBC and even OLEDB for the older Microsoft
SQL server versions. |
Connecting to a
MySQL database is not something difficult, but when visiting several forums you
can remark that the question is frequently posted by a lot of people, I think,
as they are .Net developers, they deal almost all the time with an SQL Server
database to store data, the other database types are not used. I, personally,
saw this problematic in a dozen of forums, English forums, French forums and
German forum too. Always the same question like "how to pass parameter" and
"connect to a MySQL database from a .Net application" can be seen in a
forum.
Therefore, I will provide
more than one method to deal with the issue. In this first article, we will see
the simplest method to deal with the problem. In a second article, I will
provide a second solution. Let's begin the trip!!!
First, if you have MySQL
server already installed on your machine then it is OK, else, if you want to
start from the beginning then you have to install MySQL server first. If you are
a PHP programmer then you will be familiar with this product. In our case, we
are concerned only by MySQL database server, I personally use PhpMyAdmin in
order to create and request data within MySQL format. You can also download
MySQL server directly from www.mysql.com, the MySQL official web site.
Moreover, you can download other kind of management consoles also provided by
the same web site or you can simply Google it, find the suitable management
console for MySQL databases and download it or finally, it is possible to deal
with MySQL database system using what I can say the "ugly" console. But as
a part of this tutorial, I will deal with MySQL via PhpMyAdmin. Anyway, the
installation, the configuration and the creation of MySQL databases are out of
the scope of this tutorial. There are a lot of articles about those topic. If
you are interested in this kind of databases you can simply take a look on
tutorials in www.mysql.com. But for the moment, our unique
concern is how to connect to a MySQL database from .Net environment. In
our case we suppose that we have a database that is called database, using
localhost as server, me as user id and me as password. This database contains a
table called user; this last one contains two fields UserID, and Password which
I have already populated with some data.
Walkthrough:
First, you have to
download the MySQL ODBC connector 3.51 from http://dev.mysql.com/downloads/connector/odbc/3.51.html and then install it. There is a
newer version which is the ODBC connector 5.1 but I don't advise to use it for
the moment because it is not stable, I personally had problems with the ODBC
connector 5.1. After downloading and installing the connector, create a data
source name DSN, to do so follow those steps:
- Go to Start >
Configuration panel > Administration tools > ODBC data
sources
- Select user data sources
tab, then click Add
Figure 1
- Then the below window
appears, then select MySQL 3.51 Driver and click finish
Figure 2
- Add your database
parameters, namely the data source name (The alias used later in the connection
string "database" in this case), the description, the server name (Localhost if
you use a local machine or the server name if you use a distant machine), the
user name (root for example or you can create a new user from within MySQL
Server), the password and finally database name.
- You can test the
connection by clicking Test button, if it is OK then click OK, then click OK
again to
Figure 3
Once the DSN is
configured, move to Visual studio and perform the following tasks:
Create a new Console
application project then add a new class to the new project and name it
ODBCClass, finally implement it as below:
using
System;
using
System.Text;
using
System.Data;
using
System.Data.Odbc;
namespace
MysqlProj_1
{
class
ODBCClass : IDisposable
{
///
<summary>
///
OdbcConnection : This is the connection
///
</summary>
OdbcConnection
oConnection;
///
<summary>
///
OdbcCommand : This is the command
///
</summary>
OdbcCommand
oCommand;
///
<summary>
///
Constructor: This is the constructor
///
</summary>
///
<param
name="DataSourceName">string:
This is the data source name</param>
public
ODBCClass(string DataSourceName )
{
//Instantiate
the connection
oConnection
= new OdbcConnection("Dsn="
+
DataSourceName);
try
{
//Open
the connection
oConnection.Open();
//Notify
the user that the connection is opened
Console.WriteLine("The connection is established with the
database");
}
catch
(OdbcException caught)
{
Console.WriteLine(caught.Message);
Console.Read();
}
}
///
<summary>
///
void: It is used to close the connection if you work within
disconnected
///
mode
///
</summary>
public
void CloseConnection()
{
oConnection.Close();
}
///
<summary>
///
OdbcCommand: This function returns a valid odbc connection
///
</summary>
///
<param
name="Query">string:
This is the SQL query</param>
///
<returns></returns>
public
OdbcCommand GetCommand(string Query)
{
oCommand
= new OdbcCommand();
oCommand.Connection
= oConnection;
oCommand.CommandText
= Query;
return
oCommand;
}
///
<summary>
///
void: This method close the actual connection
///
</summary>
public
void Dispose()
{
oConnection.Close();
}
}
}
If you want to work with
connecting mode then do implement the main method as follows:
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Text;
using
System.Data;
using
System.Data.Odbc;
namespace
MysqlProj_1
{
class Program
{
static void Main(string[]
args)
{
using (ODBCClass o = new
ODBCClass())
{
OdbcCommand oCommand =
o.GetCommand("select * from
user");
OdbcDataReader oReader =
oCommand.ExecuteReader();
while
(oReader.Read())
{
Console.WriteLine(oReader[0] + " " + oReader[1]);
}
Console.Read();
}
}
}
}
Else, if you want to work
with disconnected mode then implement the main method as
follows:
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Text;
using
System.Data;
using
System.Data.Odbc;
namespace
MysqlProj_1
{
class Program
{
static void Main(string[]
args)
{
using (ODBCClass o = new
ODBCClass())
{
OdbcCommand oCommand =
o.GetCommand("select * from
user");
OdbcDataAdapter
oAdapter = new OdbcDataAdapter(oCommand);
DataSet
ds = new DataSet();
oAdapter.Fill(ds);
//TO DO : Make use of the data
set
}
}
}
}
This is one of the two
methods used to connect and deal with a MySQL database. In subsequent articles,
I will expose other techniques to achieve the same task, for instance, you
shouldn't miss the second method to connect via ODBC without using data source
name.
GoodDotneting!!!