Insert XML data in database

Question

In my code i have converted SVG(XML) file into C# objects after converting it i want to insert the XML data in database but is unable to insert

Below is the code

 try
        {
            string uri = @"C:\Users\iqra\Desktop\SVGXMLToJsonApp\SVGXMLToJsonApp\File\3rect.svg";
            XmlSerializer serializer = new XmlSerializer(typeof(Svg));
            using (TextReader reader = new StreamReader(uri))
            {
                Svg result = (Svg)serializer.Deserialize(reader);
                //Console.WriteLine(result);
                List List2 = new List();
                string con = "Data Source=.;Initial Catalog=FloorPlan;Integrated Security=True";
                using (SqlConnection conn = new SqlConnection(con))
                {
                    string query = "select * from Stand_Details";
                    SqlCommand cmd1 = new SqlCommand(query, conn);
                    SqlDataReader dr;
                    conn.Open();
                    dr = cmd1.ExecuteReader();
                    while (dr.Read())
                    {
                        Rect newItem = new Rect();
                        newItem.Id = dr["Id"] == DBNull.Value ? null : dr["Id"].ToString();
                        newItem.X = dr["x"] == DBNull.Value ? null : dr["x"].ToString();
                        newItem.Y = dr["y"] == DBNull.Value ? null : dr["y"].ToString();
                        newItem.Class = dr["Class"] == DBNull.Value ? null : dr["Class"].ToString();
                        newItem.Height = dr["Height"] == DBNull.Value ? null : dr["Height"].ToString();
                        newItem.Width = dr["Width"] == DBNull.Value ? null : dr["Width"].ToString();
                        List2.Add(newItem);
                        conn.Close();
                    }
                }

                List thirdlist = new List();
                foreach (var item1 in result)
                {
                    bool isMatch = false;
                    foreach (var item2 in List2)
                    {
                        //if (List1.SequenceEqual(List2))
                        if (item1.Id == item2.Id && item1.X == item2.X && item1.Y == item2.Y && item1.Class == item2.Class && item1.Height == item2.Height && item1.Width == item2.Width)
                        {
                            isMatch = true;
                            Console.WriteLine("Record Exist");
                        }
                        else
                        {
                            try
                            {
                                SqlConnection cnn = new SqlConnection(con);
                                cnn.Open();
                                string update = "SET ANSI_WARNINGS  OFF;Update Stand_Details set Id=@Id,x=@x,y=@y,Class=@Class,Height=@Height,Width=@Width where Id=@Id";
                                using (SqlCommand cmd = new SqlCommand(update, cnn))
                                {
                                    //Loop through the and get of parameter values
                                    cmd.CommandType = CommandType.Text;
                                    cmd.Parameters.AddWithValue("@Id", item1.Id ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@x", item1.X ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@y", item1.Y ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@Class", item1.Class ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@Height", item1.Height ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@Width", item1.Width ?? (object)DBNull.Value);
                                    cmd.ExecuteNonQuery();
                                    isMatch = true;
                                }
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine(ex);
                            }
                            break;
                        }
                    }
                    if (!isMatch)
                    {
                        thirdlist.Add(item1);
                    }
                }
                foreach (Rect p in thirdlist)
                {
                    try
                    {
                        //Create SQL conection to your database here
                        using (SqlConnection cnn = new SqlConnection(con))
                        {
                           // Open your connection
                                cnn.Open();
                                //Change the table name here
                                string sql = "SET ANSI_WARNINGS  OFF;INSERT INTO Stand_Details(Id,Class, Width, Height,x,y) VALUES (@Id,@Class, @Width, @Height,@x,@y)";
                                // Create the Command and Parameter objects.
                                using (SqlCommand cmd = new SqlCommand(sql, cnn))
                                {
                                    //Loop through the and get of parameter values
                                    cmd.CommandType = CommandType.Text;
                                    cmd.Parameters.AddWithValue("@Id", p.Id ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@Class", p.Class ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@Width", p.Width ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@Height", p.Height ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@x", p.X ?? (object)DBNull.Value);
                                    cmd.Parameters.AddWithValue("@y", p.Y ?? (object)DBNull.Value);
                                    //Execute the query
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                catch (Exception ex)
                    {
                        string errorMsg = ex.Message.ToString();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }

Below is my class

    [XmlRoot(ElementName = "style", Namespace = "http://www.w3.org/2000/svg")]
public class Style
{
    [XmlAttribute(AttributeName = "type")]
    public string Type { get; set; }
    [XmlText]
    public string Text { get; set; }
}

[XmlRoot(ElementName = "rect", Namespace = "http://www.w3.org/2000/svg")]
public class Rect
{
    [XmlAttribute(AttributeName = "id")]
    public string Id { get; set; }
    [XmlAttribute(AttributeName = "x")]
    public string X { get; set; }
    [XmlAttribute(AttributeName = "y")]
    public string Y { get; set; }
    [XmlAttribute(AttributeName = "class")]
    public string Class { get; set; }
    [XmlAttribute(AttributeName = "width")]
    public string Width { get; set; }
    [XmlAttribute(AttributeName = "height")]
    public string Height { get; set; }
}

[XmlRoot(ElementName = "g", Namespace = "http://www.w3.org/2000/svg")]
public class G
{
    [XmlElement(ElementName = "rect", Namespace = "http://www.w3.org/2000/svg")]
    public Rect Rect { get; set; }
    [XmlAttribute(AttributeName = "id")]
    public string Id { get; set; }
    [XmlElement(ElementName = "text", Namespace = "http://www.w3.org/2000/svg")]
    public Text Text { get; set; }
}

[XmlRoot(ElementName = "text", Namespace = "http://www.w3.org/2000/svg")]
public class Text
{
    [XmlAttribute(AttributeName = "id")]
    public string Id { get; set; }
    [XmlAttribute(AttributeName = "transform")]
    public string Transform { get; set; }
    [XmlAttribute(AttributeName = "class")]
    public string Class { get; set; }
    [XmlText]
    public string Text1 { get; set; }
}

[XmlRoot(ElementName = "svg", Namespace = "http://www.w3.org/2000/svg")]
public class Svg
{
    [XmlElement(ElementName = "style", Namespace = "http://www.w3.org/2000/svg")]
    public Style Style { get; set; }
    [XmlAttribute(AttributeName = "style")]
    public string _Style { get; set; }
    [XmlElement(ElementName = "g", Namespace = "http://www.w3.org/2000/svg")]
    public List G { get; set; }
    [XmlAttribute(AttributeName = "version")]
    public string Version { get; set; }
    [XmlAttribute(AttributeName = "id")]
    public string Id { get; set; }
    [XmlAttribute(AttributeName = "xmlns")]
    public string Xmlns { get; set; }
    [XmlAttribute(AttributeName = "xlink", Namespace = "http://www.w3.org/2000/xmlns/")]
    public string Xlink { get; set; }
    [XmlAttribute(AttributeName = "x")]
    public string X { get; set; }
    [XmlAttribute(AttributeName = "y")]
    public string Y { get; set; }
    [XmlAttribute(AttributeName = "viewBox")]
    public string ViewBox { get; set; }
    [XmlAttribute(AttributeName = "space", Namespace = "http://www.w3.org/XML/1998/namespace")]
    public string Space { get; set; }
}

and below is the XML data that i want to insert in database

Written code for insert and update but not able to access result variable in loop

foreach (var item1 in result)

giving error as "Severity Code Description Project File Line Suppression State Error CS1579 foreach statement cannot operate on variables of type 'Svg' because 'Svg' does not contain a public instance definition for 'GetEnumerator' SVGXMLToJsonApp C:\Users\iqra\Desktop\SVGXMLToJsonApp\SVGXMLToJsonApp\Program.cs 58 Active"

Tried the article u have given but not able to insert it in database because the method is simple. Let me explain you how i want to insert data in DB Below is my columns in DB to store data as given below

1st Row data:- Rect_Id (idrect3), x(251.4) ,y(-0.3) ,rect_class(st0) ,height(300.2) ,weight(866) ,text(Nano Tech) ,transform(matrix(1 0 0 1 515.7997 166.1773)), Text_Id(idnano), text_class(st1 st2 st3)

2nd Row data:- Rect_Id (idrect2), x(248.6) ,y(366.5) ,rect_class(st0) ,height(400.2) ,weight(500.3) ,text(Migalo) ,transform(matrix(1 0 0 1 420.2463 553.5321)), Text_Id(idmigalo), text_class(st4 st2 st5)

3rd Row data:- Rect_Id (idrect1), x(811.4) ,y(364.2) ,rect_class(st0) ,height(404.1) ,weight(300.2) ,text(Prime) ,transform(matrix(1 0 0 1 883.9615 567.5667)), Text_Id(idprime), text_class(st6 st2 st5)

Insert XML data in database

Answers (1)

Related Questions