Transferring data from SQL Server database to MySql database

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 !!!

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.