Click here to Skip to main content
13,254,754 members (52,194 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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());
Posted 7-Aug-17 0:52am
Updated 7-Aug-17 4:03am
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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


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)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.171114.1 | Last Updated 7 Aug 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100