CODEWITHSUNDEEP
c#xmllinq
user2888402
user2888402

Reputation: 77

XML to database and vice-versa

I can insert my XML easily into my database table, but i follow this exhausting manner as seen in my down code using LINK, which is perfectly tested. But, I wonder if I can find a way to read all my XML descendants elements of "Level" node, using iteration of all child tagnames, because when I make any change to my XML file, I would have to change my LINK code once again,and usually i'll face some errors when i use this exhausting manner. Please help improve this code:

try
{
    XElement d = XElement.Parse(richTextBox1.Text.ToString());

        var people = (from Level in d.Descendants("Level")
                      select new
                      {
                          ID = Convert.ToInt32(Level.Element("ID").Value),
                          Day1 = Level.Element("Day1").Value,
                          Day2 = Level.Element("Day2").Value,
                          Day3 = Level.Element("Day3").Value,
                          Day4 = Level.Element("Day4").Value,
                          Day5 = Level.Element("Day5").Value,
                          Day6 = Level.Element("Day6").Value,
                          Day7 = Level.Element("Day7").Value
                      }).ToList();
        foreach (var item in people)
        {
            //Insert and Update                    
            datacommand1.CommandText = "Insert Into MyTable(ID,Day1,Day2,Day3,Day4,Day5,Day6,Day7) values(" + item.ID + ","  + "','" + item.Day1 + "','" + item.Day2 + "','" + item.Day3 + "','" + item.Day4 + "','" + item.Day5 + "','" + item.Day6 + "','" + item.Day7 + "')";
            datacommand1.ExecuteNonQuery();                        
        }                    
}

my XML file seems like that:

<level>
    <id> 101 </id>
    <Day1> task 1</Day1>
    <Day2> task 2</Day2>
    <Day3> task 3</Day3>
    <Day4> task 4</Day4>
    <Day5> task 5</Day5>
    <Day6> task 6</Day6>
    <Day7> task7 </Day7>

</level>

Upvotes: 1

Views: 179

Answers (1)

Alejandro
Alejandro

Reputation: 7813

Instead of declaring one variable for each tag in the file, you may want to try to iterate over all them, extracting the name and the value for each one and composing the SQL statement at runtime from those, no matter what they are. Try something like this:

IEnumerable<XElement> items = d.Descendants("level").Elements();
string names = string.Empty;
string values = string.Empty;
foreach (XElement item in items)
{
    names += item.Name + ",";
    values += "@" + item.Name + ",";
    IDbDataParameter parameter = datacommand1.CreateParameter();
    parameter.ParameterName = "@" + item.Name;
    parameter.DbType = DbType.String;
    parameter.Value = item.Value;
    datacommand1.Parameters.Add(parameter);
}
datacommand1.CommandText = "INSERT INTO MyTable (" + names.Substring(names.Length - 1) + ") VALUES (" + values.Substring(values.Length - 1) + ");";
datacommand1.ExecuteNonQuery();

This builds the command on the fly, based on the XML structure, and fills its parameters with the data there. But doing so relies on the fact that the table structure will be exactly the same as in the file, and still needs manual schema updating when the structure changes, but as long as they're in sync it should be fine.

EDIT

About datatypes, there are 2 choices I can think of. Leave as it is, sending everything as strings no matter what, and rely on the DB engine to parse, validate and turn those into numbers (depending on what DB you're using, that may be possible or not, but I guess it's not rare to see). Or modify the code to separate the special fields apart from the loop (and excluding from it) and add those one by one, specifying their types accordingly. This is easy to do if the numeric columns are fixed, like the ID, and everything else is text.

Upvotes: 1

Related Questions