C# - Update SQL table with data from XML file

Question

I'm trying with C# to read data from XML file, and update my SQL table with. But nothing happens.

My XML look like this

<User>
  <Table>
    <userID>535631</userID>
    <Date>2017-12-18</Date>
  </Table>
  <Table>
    <userID>36334</userID>
    <Date>2020-02-03</Date>
  </Table>
  <Table>
    <userID>734563</userID>
    <Date>2020-02-03</Date>
  </Table>
  <Table>
    <userID>6334</userID>
    <Date>2020-02-21</Date>
  </Table>
</User>

And what I tried:

        XmlDocument doc = new XmlDocument();
        doc.Load(@"C:\\temp\\data\\myData.xml");
        XmlNodeList node = doc.SelectNodes("/User/Table");


        string source = ConfigurationManager.ConnectionStrings["JBVRM"].ConnectionString;
        SqlConnection conn = new SqlConnection(source);
        SqlCommand cmd = new SqlCommand();

        foreach (XmlNode xn in node)
        {

            string userID = xn["userID"].InnerText;
            string NewDate= xn["Date"].InnerText;

            SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("UPDATE [dbo].[User] SET Date='"+NewDate+"' WHERE UserID="+ userID , source);

             conn.Close();

            //  Console.WriteLine(userID+" "+Date); // This prints everything very fine. 

        }

Any suggestions what I'm doing wrong? I can print date and userID fint. but my table is not being updated.

SOLUTION Thanks to @Magnetron and @Gnud

            using (SqlConnection connection = new SqlConnection(source))
            {
                using (SqlCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "UPDATE [dbo].[User] SET Date=@Date WHERE userID=@userID";

                    cmd.Parameters.AddWithValue("@Date", xn["Date"].InnerText);
                    cmd.Parameters.AddWithValue("@userID", xn["userID"].InnerText);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
            }

The SqlAdapter Constructor, you're using is for a select command not a update one. — Magnetron
– Magnetron, Commented Mar 26, 2020 at 11:58
Arh OK.. Thank you. I will try to use cmd.CommandText.. or is there any better idea? — user3883376
– user3883376, Commented Mar 26, 2020 at 12:00
Yes, use the CommandText and parameters, instead of trusting the user input, as this make you vulnerable for sql injection. — Magnetron
– Magnetron, Commented Mar 26, 2020 at 12:02
Comment on your solution: Move the outermost using (of the connection) outside the loop. And Open() the connection before the loop. Only connect once. If you create and close the connection inside the loop, you will connect and disconnect once for every row in the XML file. — gnud
– gnud, Commented Mar 26, 2020 at 14:38

gnud · Accepted Answer · 2020-03-26 12:13:21Z

There are a couple of things wrong in your code.

You should always use SQL parameters instead of baking values into the SQL string. This is important. Building SQL strings like you do is a recipe for security holes.
You should really wrap your SqlConnection and your SqlCommand's in using statements. Database connections are resources that you want to free as soon as you are done with them.
You should not use SqlDataAdapter unless you're working with the DataSet or DataTable classes. You're not doing that in your code, and there's no reason to.
You should really not close the database connection inside the loop. Then, the connection will be closed when you try to process the 2nd "table" element from the XML file.

After fixing this, the update loop might look something like this:

using(var conn = new SqlConnection(source))
using(var cmd = conn.CreateCommand()) {
    conn.Open();

    cmd.CommandText = "UPDATE [dbo].[User] SET [Date]=@date WHERE [UserId]=@id";
    cmd.Prepare();

    var date = cmd.CreateParameter();
    date.ParameterName = "@date";
    date.DbType = DbType.Date;

    var id = cmd.CreateParameter();
    id.ParameterName = "@id";
    id.DbType = DbType.Int32;

    cmd.Parameters.Add(date);
    cmd.Parameters.Add(id);

    foreach (XmlNode xn in node)
    {                
            id.Value = int.Parse(xn["userID"].InnerText);
            date.Value = DateTime.ParseExact(xn["Date"].InnerText, "yyyy-MM-dd", CultureInfo.InvariantCulture);

            cmd.ExecuteNonQuery();
    }
}

Collectives™ on Stack Overflow

C# - Update SQL table with data from XML file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related