Home > Uncategorized > Moving from MsSQL to MySQL

Moving from MsSQL to MySQL

I’ve decided to start moving some data from my MsSQL database to a MySQL one. Why?, because I’d like my MsSQL database to be used for my main websites, and the MySQL one used for less important ones. Those where the data is bulky but the performance isn’t so important. – Since my MySQL db is nowhere near my webserver.

Anyway, I was surprised how easy it was to migrate,

This is the code I use for MsSQL:

public DataSet ExecuteDataSet(string sql)
{
DataSet ds = new DataSet();
try
{

OleDbConnection DSN = new OleDbConnection(connectionString);
DSN.Open();
OleDbCommand Database = new OleDbCommand(sql,DSN);
OleDbDataAdapter Adapter = new OleDbDataAdapter(Database);
Adapter.Fill(ds,”sql”);
DSN.Close();
}
catch(Exception ex)
{
logError(ex,sql);
}
return ds;
}

Just replace OleDb with MySql and you get

public DataSet ExecuteMySqlDataSet(string sql)
{
DataSet ds = new DataSet();
try
{

MySqlConnection DSN = new MySqlConnection(MySqlConnectionString);
DSN.Open();
MySqlCommand Database = new MySqlCommand(sql, DSN);
MySqlDataAdapter Adapter = new MySqlDataAdapter(Database);
Adapter.Fill(ds, “sql”);
DSN.Close();
}
catch (Exception ex)
{
logError(ex, sql);
}
return ds;
}

And then include using MySql.Data.MySqlClient;

A few tweaks to the SQL, so instead of select top 10 * … it’s select * … limit 10, instead of order by newid() it’s order by rand(), and the table names are case sensitive.

I hit some wierd bug where it didn’t recognise column names with umlauts, but I just worked around that instead of fixing it. – Anyway, here’s the end-result: http://www.listofbanks.info/GermanBanks.aspx

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: