温馨提示:本文翻译自stackoverflow.com,查看原文请点击:c# - how to set two connection string MySqlCommand object argument?

c# - 如何设置两个连接字符串MySqlCommand对象的自变量?

发布于 2020-03-29 21:38:38

我想创建一个基于迁移的可视化基本应用程序,用于将MySQL数据库表迁移到MS SQL Server 2014,这样一键单击即可轻松实现,它将开始迁移数据,无论您要迁移哪个数据库

这是我编写的主要代码,但是当我想在MySqlCommand参数中连接两个连接时,怎么可能

 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());
            }


        }

现在主要的问题在这里

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

有什么解决办法吗?

查看更多

提问者
him bhavsar
被浏览
19
7,105 2020-02-01 00:08

经过一番失败后,我终于获得了成功的解决方案

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());
    }
}