locked
save and read xml file into linq database table into column which is xml data type using datacontext and insertonsubmit and submitchaghes function RRS feed

  • Question

  • User1708223868 posted

    I have this sql table Which I using in Datacontext in aplication

       CREATE TABLE dbo.Txml(
       brRacuna int NULL,
       korisnickoime varchar(50) NULL,
       xmldata xml NULL
       )

    I have xml file which have this look

         <ProizvodUkorpi>
          <proizvod>
           <naziv>fff</naziv>
         <kolicina>2</kolicina>
          <cena>567</cena>
           <iznos>1134</iznos>
           </proizvod>
         <proizvod>
          <naziv>cam</naziv>
          <kolicina>1</kolicina>
            <cena>678</cena>
           <iznos>678</iznos>
            </proizvod>
           <proizvod>
          <naziv>alarm</naziv>
          <kolicina>2</kolicina>
          <cena>3456</cena>
          <iznos>6912</iznos>
          </proizvod>
          </ProizvodUkorpi>

    I want to save and read this xml file into linq database table into column xmldata which is xml data type

    How I can do that using datacontext and fuction insertonsubmit and submitchanges, I hope someone can help me

    Wednesday, July 30, 2014 12:38 PM

Answers

  • User-271186128 posted

    Hi nmiroslav72,

    According to you description, I think you want to read xml file and then save it into LINQ database. Here is a sample, you could refer to it:

    My XML File:

    <?xml version="1.0" encoding="utf-8" standalone="yes"?>
    <!--Jason's xml-->
    <People>
      <Person id="1">
        <Name>Joe</Name>
        <Age>35</Age>
        <Job>Manager</Job>
      </Person>
      <Person id="2">
        <Name>Jason</Name>
        <Age>18</Age>
        <Job>Software Engineer</Job>
      </Person>
      <Person id="3">
        <Name>Lisa</Name>
        <Age>53</Age>
        <Job>Bakery Owner</Job>
      </Person>
      <Person id="4">
        <Name>Mary</Name>
        <Age>90</Age>
        <Job>Nurse</Job>
      </Person>
    </People>

    Code in page (.aspx)

            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>

    Code in page (.aspx.cs)

            protected void Button1_Click(object sender, EventArgs e)
            {
                //read xml file using LINQ
                var names = (from person in XDocument.Load(@"D:\People.xml").Descendants("Person")
                             select new
                             {
                                 ID = person.Attribute("id").Value,
                                 Name = person.Element("Name").Value,
                                 Age = person.Element("Age").Value,
                                 Job = person.Element("Job").Value
                             }).ToList();
    
                MyTestDBDataContext context = new MyTestDBDataContext();
                People pp;
                
                //Insert into Linq Database
                foreach (var item in names)
                {
                    pp = new People();
                    pp.Id = Convert.ToInt32(item.ID);
                    pp.Name = item.Name;
                    pp.Job = item.Job;
                    pp.Age = Convert.ToInt32(item.Age);
    
                    context.Peoples.InsertOnSubmit(pp);
    
                }
    
                context.SubmitChanges();
    
                var query = from people in context.Peoples
                            select people;
                GridView1.DataSource = query.ToList();
                GridView1.DataBind();
            }

    Here are some good articles, you could refer to them:

    LINQ To Xml: http://www.dreamincode.net/forums/topic/218979-linq-to-xml/

    Basic Queries (LINQ to XML): http://msdn.microsoft.com/en-us/library/bb943906.aspx

    Making and Submitting Data Changes (LINQ To SQL): http://msdn.microsoft.com/en-us/library/bb882674(v=vs.110).aspx

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 5, 2014 5:57 AM

All replies

  • User-1614460396 posted

    create a stored procedure up_TxmlAddUpdate, use this stored procedure as the insert/update method in your datacontext. make sure the @xmldata a parameter type is nvarchar(max), when you insert to the table  cast your nvarchar to xml.

    CAST(@xmldata AS XML)
    Thursday, July 31, 2014 1:55 PM
  • User-271186128 posted

    Hi nmiroslav72,

    According to you description, I think you want to read xml file and then save it into LINQ database. Here is a sample, you could refer to it:

    My XML File:

    <?xml version="1.0" encoding="utf-8" standalone="yes"?>
    <!--Jason's xml-->
    <People>
      <Person id="1">
        <Name>Joe</Name>
        <Age>35</Age>
        <Job>Manager</Job>
      </Person>
      <Person id="2">
        <Name>Jason</Name>
        <Age>18</Age>
        <Job>Software Engineer</Job>
      </Person>
      <Person id="3">
        <Name>Lisa</Name>
        <Age>53</Age>
        <Job>Bakery Owner</Job>
      </Person>
      <Person id="4">
        <Name>Mary</Name>
        <Age>90</Age>
        <Job>Nurse</Job>
      </Person>
    </People>

    Code in page (.aspx)

            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>

    Code in page (.aspx.cs)

            protected void Button1_Click(object sender, EventArgs e)
            {
                //read xml file using LINQ
                var names = (from person in XDocument.Load(@"D:\People.xml").Descendants("Person")
                             select new
                             {
                                 ID = person.Attribute("id").Value,
                                 Name = person.Element("Name").Value,
                                 Age = person.Element("Age").Value,
                                 Job = person.Element("Job").Value
                             }).ToList();
    
                MyTestDBDataContext context = new MyTestDBDataContext();
                People pp;
                
                //Insert into Linq Database
                foreach (var item in names)
                {
                    pp = new People();
                    pp.Id = Convert.ToInt32(item.ID);
                    pp.Name = item.Name;
                    pp.Job = item.Job;
                    pp.Age = Convert.ToInt32(item.Age);
    
                    context.Peoples.InsertOnSubmit(pp);
    
                }
    
                context.SubmitChanges();
    
                var query = from people in context.Peoples
                            select people;
                GridView1.DataSource = query.ToList();
                GridView1.DataBind();
            }

    Here are some good articles, you could refer to them:

    LINQ To Xml: http://www.dreamincode.net/forums/topic/218979-linq-to-xml/

    Basic Queries (LINQ to XML): http://msdn.microsoft.com/en-us/library/bb943906.aspx

    Making and Submitting Data Changes (LINQ To SQL): http://msdn.microsoft.com/en-us/library/bb882674(v=vs.110).aspx

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 5, 2014 5:57 AM