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

Answer 1

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