Transferring data from SQL Server database to MySql database

SheoNarayan
Posted by in ASP.NET category on for Intermediate level | Views : 12708 red flag

Hi, The other day I was facing problem while migrating my Sql Server database tables into MySql database. I tried to google it but couldn't find any great solution that can do it through code easily.

Thought to share this simple code to all of you.
Introduction

My problem was that I had a Sql Server database at my webserver and I had to migrate its data for any reason to my MySql database table that had the same table structure as the Sql Server had. As I didn't had enough permission on Server to use DTS or other type of services to directly transfer my data to MySql so I had one option left that is to write a code that can get all data from Sql Server and transfer into MySql database.

Prerequisites

In order to use following function, you must have same Database table structure into both Sql Server and MySql database. If you have different structure then you may have to slightly play with the columns of the DataTable inside DataSet.

Function To Transfer data from Sql Server to MySql


/// <summary>

/// Tutorials for transferring data from SqlSrver to MySql
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void TransferSqlServerDataInToMySql(object sender, EventArgs e)
{
string SqlConnStr = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ToString();
string MySqlConnStr = ConfigurationManager.AppSettings["MySqlConnectionString"].ToString();

DataSet SqldSet = new DataSet(); // SqlServer Dataset that holds Sql Server data
DataSet MySqldSet = new DataSet(); //MySql dataset that will be used to push data into MySql database

try
{
// SqlServer - get myTables data from Sql Server
using (SqlConnection conn = new SqlConnection(SqlConnStr))
{
conn.Open();
using (SqlDataAdapter dAd = new SqlDataAdapter("select * from myTables", conn))
{
dAd.Fill(SqldSet, "myTables");
}
}

lblMessage.Text += "Sql table myTables: " + SqldSet.Tables[0].Rows.Count.ToString() + " records found";

// Connect to MySql database and have as there is no data into MySql table yet so just get the schema into DataSet
using (MySqlConnection conn = new MySqlConnection(MySqlConnStr))
{
conn.Open();
using (MySqlDataAdapter dAd = new MySqlDataAdapter("select * from myTables", conn))
{

dAd.Fill(MySqldSet, "myTables"); // Got the empty table of MySql
// Loop through all rows of Sql server data table and add into MySql dataset
foreach (DataRow row in SqldSet.Tables["myTables"].Rows)
{
MySqldSet.Tables[0].NewRow();
MySqldSet.Tables[0].Rows.Add(row.ItemArray);
}
// Now we have all rows of Sql Server into MySql server dataset
// Create a command builder to update MySql dataset
MySqlCommandBuilder cmd = new MySqlCommandBuilder(dAd);
// Following update command will push all added rows into MySql dataset to database
dAd.Update(MySqldSet, "myTables"); // We are done !!!

}
}

lblMessage.Text += "<br />MySql myTables: " + MySqldSet.Tables[0].Rows.Count.ToString() + " records found <hr />";
}
catch (Exception ee)
{
lblMessage.Text = ee.Message.ToString();
}
}


In the above function, I have take connection string of my Sql Server database as well as MySql database. I have declared two DataSet to hold data from Sql Server and update data into MySql server respectively.

Now, I am getting all records from my Sql Server database table (myTables) and holding it into SqldSet dataset. I am rows count message in the lblMessage just to know that I have data into my dataset. Now in the following code, I have opened MySql database and executing the select statement of my table. As I don't have any record into MySql database now so it will just give me the structure of the table into my MySql dataset.

Now I have looped through all the rows of the Sql Server dataset table (that hold my Sql Server data) and added it into my MySql server dataset (MySqldSet). Then I attached a MySqlCommandBuilder to the DataAdapter and finally run dAd.Update(MySqldSet, "myTables). that will update all the added rows of MySql dataset into the database.

Just for our satisfaction, I have again written the rows count into the label so I know that same number of records have been added into the MySql database that I had got from Sql Server database.

Conclusion

The conclusion is that by using the Update method of DataAdapter we can do transferring data from virtually any database to another database. It takes just a little code.

Hope above function will help someone, if you have any question or feedback, please feel free to provide below.

Happy Coding !!!
Page copy protected against web site content infringement by Copyscape

About the Author

SheoNarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Pemex44 on: 11/26/2010 | Points: 25
Sheo,

Your code works great, but I wonder if this can be done with a non-empty mysql table. It's not really necessary to update the data, I just want to be getting the latest snapshot of the sql server table every certain amount of minutes so I guess doing an erase on the receiving table is ok.

Thanks in advance.

-Francisco

Login to post response

Comment using Facebook(Author doesn't get notification)