Warm tip: This article is reproduced from stackoverflow.com, please click
c# database-migration mysql sql-server data-migration

how to set two connection string MySqlCommand object argument?

发布于 2020-03-29 21:00:04

I want to create Migration based visual basic application for migrating MySQL database table to MS SQL Server 2014 so that can be easy through one click event on one click it will start migrating data whatever database you want to migrate

here is my main code that I've scripted but when i want to connect two connection in MySqlCommand argument how it is possible

 private void Start_Click(object sender, EventArgs e)
        {
            //MS SQL Connection server
            string connectionString;
            SqlConnection MSSQL;

            connectionString = @"Data Source=DESKTOP-IF12M9R;Initial Catalog=utility;User ID=sa;Password=mysql";
            MSSQL = new SqlConnection(connectionString);

            MSSQL.Open();
            MessageBox.Show("Connection Open of MS SQL !");

            //MySQL server Connectivity
            string connStr = "server=localhost;user=root;database=utility;port=3306;password=''";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                MessageBox.Show("Connecting to MySQL...");

                conn.Open();
                MessageBox.Show("Connection open of MySQL!");

                string sql = "SELECT * FROM userial";
                MessageBox.Show(sql);
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    MessageBox.Show(rdr[0] + " -- " + rdr[1] + " -- " + rdr[2] + " -- " + rdr[3]);
                }
                rdr.Close();


                string mytoms = "IF EXISTS (SELECT * FROM sys.objects so JOIN sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name = N'userial'  AND sc.name = N'utility'  AND type in (N'U')) BEGIN DECLARE @drop_statement nvarchar(500) DECLARE drop_cursor CURSOR FOR SELECT 'alter table ' + quotename(schema_name(ob.schema_id)) + '.' + quotename(object_name(ob.object_id)) + ' drop constraint ' + quotename(fk.name) FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id WHERE fk.referenced_object_id = ( SELECT so.object_id FROM sys.objects so JOIN sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name = N'userial'  AND sc.name = N'utility'  AND type in (N'U')) OPEN drop_cursor FETCH NEXT FROM drop_cursor INTO @drop_statement WHILE @@FETCH_STATUS = 0 BEGIN EXEC(@drop_statement) FETCH NEXT FROM drop_cursor INTO @drop_statement END CLOSE drop_cursor DEALLOCATE drop_cursor DROP TABLE[utility].[userial] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [utility].[userial] ([SerialID] int NOT NULL, [SerialNo] varchar(50)  NOT NULL, [DateTime] datetime2(0)  NOT NULL, [Result] varchar(4)  NOT NULL WITH(DATA_COMPRESSION = NONE) GO IF EXISTS(SELECT* FROM sys.objects so JOIN sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name = N'PK_userial_SerialID'  AND sc.name = N'utility'  AND type in (N'PK')) ALTER TABLE[utility].[userial] DROP CONSTRAINT[PK_userial_SerialID] GO ALTER TABLE[utility].[userial] ADD CONSTRAINT[PK_userial_SerialID] PRIMARY KEY CLUSTERED([SerialID] ASC) GO ALTER TABLE[utility].[userial] ADD DEFAULT getdate() FOR[DateTime] GO";
                MessageBox.Show(mytoms);
                MySqlCommand cmd1 = new MySqlCommand(mytoms , conn, MSSQL);
                MySqlDataReader rdr1 = cmd1.ExecuteReader();

                while (rdr1.Read())
                {
                    MessageBox.Show(rdr1[0] + " -- " + rdr1[1] + " -- " + rdr1[2] + " -- " + rdr1[3]);
                }
                rdr.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }


        }

now the main problem is here

MySqlCommand cmd1 = new MySqlCommand(mytoms , conn, MSSQL);

is there any solution ?

Questioner
him bhavsar
Viewed
22
7,105 2020-02-01 00:08

After some failure finally I got Success for this solution

public partial class Form1 : Form
{
    //MS SQL Connection server
    public string connectionString;
    public SqlConnection MSSQL;

    //MySQL server Connectivity
    public string connStr;
    public MySqlConnection conn;
    private void Start_Click(object sender, EventArgs e)
    {
    try
    {
        //Connection Process of MS SQL Server
        connectionString = @"Data Source='your source name';Initial Catalog='database name';User ID='username';Password='password'";
        MSSQL = new SqlConnection(connectionString);
        //opening connection of MS SQL Server
        MSSQL.Open();
        MessageBox.Show("Connection Open of MS SQL !");


        //Connection Process of MySQL Server
        connStr = "server='server name';user='username';database='database name';port='port';password='password'";
        conn = new MySqlConnection(connStr);
        //opening connection of MySQL Server
        conn.Open();
        MessageBox.Show("Connection open of MySQL!");
    }
    catch (Exception ex)
    {
         Console.WriteLine(ex.ToString());
    }
}