mysql中SQL Server的openxml和merge语句的任意替代 [英] Any alternate of openxml and merge statement of SQL server in mysql

问题描述

我有数千种XML格式的项目。它包含ItemID和ItemName。我的要求是，我需要将所有记录插入到ItemMaster表中的数据库中。如果这些项中的任何一个已经存在，则不应该插入它而应该更新ItemMaster表中的ModifiedOn列。目前我正在使用SQL Server，在临时表，OpenXML和Merge语句的帮助下，我能够在存储过程中成功完成。



我的问题是我有使用存储过程在MySQL中完成。不需要循环。我不知道怎么做，因为我对MySQL没有更多的了解。



我尝试了什么：



我的SQL Server程序是：

I have thousands of items in XML format. It consists ItemID and ItemName. My requirement is, I need to insert all the records into database in ItemMaster table. If any of these items is already exists then it should not be inserted rather it should update the ModifiedOn column in ItemMaster table. Currently I am using SQL Server and with the help of temporary table,OpenXML and Merge statement I am able to do it successfully in stored procedure.

My problem is I have do it in MySQL by using stored procedure. Looping is not required. I have no idea how to do it as I don't have much more knowledge of MySQL.

What I have tried:

My SQL Server procedure is :

declare @XML xml =null,@XMLDOC Int

declare @TempTable Table(ItemID int, ItemName nvarchar(50))

EXEC sp_xml_preparedocument @XMLDOC output,
						
INSERT INTO @TempTable(ItemID, ItemName)
	SELECT UOMID from openXML (@XMLDOC,'NewDataSet/Table1',2)
					  with (ItemID int, ItemName nvarchar(50))

EXEC sp_xml_removedocument @XMLDOC	

MERGE ItemMaster as Target
Using(select ItemID,ItemName FROM @TempTable) as Source
ON(Target.Item_ID=Source.ItemID)
WHEN MATCHED THEN
	Update set Target.Item_ModifiedOn=GETDATE()
WHEN NOT MATCHED THEN
	INSERT(ItemName,Item_CreatedOn) 
	Values(Source.ItemName,GETDATE());

推荐答案

你可以在c＃.net itse中做到这一点如果是这样的话

首先你将下载可以通过金块包找到的MYSQL库。



2.获取MySQL数据库信息，例如用户名，密码，主机等以及所需的任何其他信息。然后制作这样的东西，它将允许你连接到服务器

这是一个例子：

You can do this in c#.net itself like this
Firstly you will have download the MYSQL library which can be found through the nugget packages.

2. Get the MySQL db information e.g. username, password, host etc. and any other info required. Then make something like this which will allow you to connect to the server
Here is an example:

MySqlConnection makeConnection()
{
    try
    {
        //connection information
        server = "localhost";
        database = "dbname";
        uid = "user";
        dbPassword = "password";
        string connectionString = "SERVER=" + server + ";" + "DATABASE=" +
        database + ";" + "UID=" + uid + ";" + "PASSWORD=" + dbPassword + ";";
        //connect to the database
        connection = new MySqlConnection();
        connection.ConnectionString = connectionString;
        connection.Open();
        return connection;
    }
    catch (MySqlException e)
    {
        //if anything goes wrong
        MessageBox.Show(@"Something has gone wrong here (Your own message here if you want)");
        Console.WriteLine(e.ToString());
        return connection;
    }
}

3.您需要检查数据是否在数据库中。

你是怎么做的？

遍历每个值并检查它是否在数据库中。

这是一个例子：

3. You will need to check if the data is on the database.
How do you do it?
Loop through each value and check if it is in the database.
Here is an example:

//checking each value in the database
MySqlConnection conn = makeConnection(); //using the connection method above
string[] values = new string[] { "string1", "string2", "string3", "string4", "string5", };
for (int i = 0; i < values.Length; i++)
{
    //.ExecuteScalar is used only to get single values
    string checkQuery = "SELECT col1 FROM table1 WHERE col1 = '" + values[i] + "'";
    MySqlCommand checkCommand = new MySqlCommand(checkQuery, conn);
    string checkValue = checkCommand.ExecuteScalar().ToString();
    if(checkValue == values[i])
    {
        //.ExecuteNonQuery only used for updating values inserting values etc.
        //things that don't need to return a value.
        string updateColumnQuery = "UPDATE table1 SET col2 = 'already here'";
        MySqlCommand updateColumnCommand = new MySqlCommand(updateColumnQuery, conn);
        updateColumnCommand.ExecuteNonQuery();
    }
    else
    {
        //Insert new value
        string insertColumnQuery = "INSERT INTO table1 (col1) VALUES('" 
+ values[i] +"')";
        MySqlCommand insertColumnCommand = new MySqlCommand(insertColumnQuery, conn);
        insertColumnCommand.ExecuteNonQuery();
    }
}

希望这有帮助，我自己没有尝试过代码，但根据我的建议我认为这段代码应该有效。只记得获取正确的数据库信息，否则可能会产生很多错误。我第一次使用MySQL c＃库时发生了这种情况。希望你的最终工作能够完成。

Rohit Pai，C＃，HTML，CSS，JS，PHP，（Python仍然不完整）

Hope this helps, I haven't tried the code myself but as per my suggestion I think that this code should work. Just remember to get the correct db information otherwise it could be giving lots of errors. That happened to me the first time I was using the MySQL c# library. Hope your final work gets completed.
Rohit Pai, C#, HTML, CSS, JS, PHP, (Python still incomplete)

这篇关于mysql中SQL Server的openxml和merge语句的任意替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！