C# .NET - insert xml data in database

Asked By goldy gupta on 05-Jul-11 02:40 AM

Helo to all i am making windows application in c#...I am saving id,username and password in Xml...This is my xml

<record>
<customtable id="2116418199" username="golds" password="sodala" />
<customtable id="880628076" username="kshama" password="adarsh nagar" />
</record>

and my .cs page is like this

private void button1_Click(object sender, EventArgs e)

{

XmlDocument xx = new XmlDocument();

xx.Load("E:\\xmldesk\\desktopxml\\XMLFile1.xml");

XmlNode xn1 = xx.CreateElement("customtable");

XmlAttribute attr = xx.CreateAttribute("id");

randomgenration();

attr.Value = RandomNumber.ToString();

xn1.Attributes.Append(attr);

attr = xx.CreateAttribute("username");

attr.Value = textBox1.Text;

xn1.Attributes.Append(attr);

attr = xx.CreateAttribute("password");

attr.Value = textBox2.Text;

xn1.Attributes.Append(attr);

xx.SelectSingleNode("record").AppendChild(xn1);

xx.Save("E:\\xmldesk\\desktopxml\\XMLFile1.xml");

}

Now i have three field in my database id,username and password..

on another button click the data of xml should save in my local database..how it will be possible plz tell

Reena Jain replied to goldy gupta on 05-Jul-11 02:41 AM

hi,

ASP.NET allows you to easily convert the data form a database into an Xml document. It provides the XMLDataDocument class for this. The data loaded in XMLDataDocument can be manipulated using the W3C DOM.
try this

using System.Data.SqlClient;

using System.Xml;

//Create a dataset

Dataset ds = new Dataset();

//Create a connection string.

String sqlconnect = "Persist Security Info=False;User ID=sa;Initial Catalog=WebShoppe;Data Source=IRDTEST-D190;";

//Create a connection object to connect to the web shoppe database

try

{

SqlConnection nwconnect = new SqlConnection(sqlconnect);

//Create a command string to select all the customers in the customerDetails table

String scommand = "Select * form customerImages";

//Create an adapter to load the dataset

SqlDataAdapter da = new SqlDataAdapter(scommand,nwconnect);

//Fill the dataset

da.Fill(ds,"customerDetails");

}

catch

{

Label1.Text = "Error while connecting to database";

}

XmlDataDocument doc = new XmlDataDocument(ds);

Xml1.Document = doc;

doc.Save(MapPath("Customers.xml"));//This is where we are saving the data in an XML file Customers.xml

The above code will display the contents not in a tabular format, since no style sheets are attached. If you want the data to be displayed in some particular format, make your XSL file as myfile.xsl and add the following three lines of code to the above written code:

XslTransform t = new XslTransform();

t.Load(MapPath(("myfile.xsl"));

Xml1.Transform = t;

hope this will help you

Venkat K replied to goldy gupta on 05-Jul-11 02:44 AM

For Importing XML data into a SQL Server table:

DataSet reportData = new DataSet();
reportData.ReadXml(Server.MapPath(”report.xml”));

SqlConnection connection = new SqlConnection(”CONNECTION STRING”);
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = “report_table”;

//if your DB col names don’t match your XML element names 100%
//then relate the source XML elements (1st param) with the destination DB cols
sbc.ColumnMappings.Add(”campaign”, “campaign_id”);
sbc.ColumnMappings.Add(”cost”, “cost_USD”);

connection.Open();

//table 4 is the main table in this dataset
sbc.WriteToServer(reportData.Tables[4]);
connection.Close();

Thanks

Anoop S replied to goldy gupta on 05-Jul-11 02:49 AM

for inserting xml data to databse 
DECLARE @xml XML;
SELECT @xml = BulkColumn
FROM OPENROWSET (BULK 'C:\database\xmldtd\yahoostore.xml', SINGLE_BLOB)
Product
--Uncomment the next line to INSERT these values into a table
--INSERT INTO yourtable (id, description, url) -- ... --Add the remaining
columns here
SELECT x.value('@id', 'varchar(32)'),
x.value('Description', 'varchar(1024)'),
x.value('Url', 'varchar(1024)')
--... --Add the remaining columns here
FROM @xml.nodes('/Products/Product') x;
Unfortunately the XML data type isn't as complex an undertaking as all that
OPENXML stuff, and you don't have to remember to manually deallocate the
memory it uses, but you shouldn't have a problem adding the remaining
x.value(..., ...) method calls.  Enjoy.

Ravi S replied to goldy gupta on 05-Jul-11 02:59 AM

If Xml document is not quite large I would choose XmlDocument, here is sample code:

        string fileName = Server.MapPath("test83.xml"); 
        XmlDocument doc = new XmlDocument(); 
        doc.Load(fileName); 
 
        XmlNodeList nodes = doc.SelectNodes("//product"); 
 
        for (int i = 0; i < nodes.Count; i++) 
            for (int j = 0; j < nodes[i].ChildNodes.Count; j++) 
            { 
                Response.Write(nodes[i].ChildNodes[j].Name + " value="); 
                Response.Write(nodes[i].ChildNodes[j].InnerText); 
                Response.Write((" 
")); 
            }

TSN ... replied to goldy gupta on 05-Jul-11 03:05 AM

hi,...

here is the sample USing Stored procedure to import data from XMl...

// Move using statements to top of your file

using System;

using System.Data;

using System.Data.SqlClient;

using System.IO;

// End of code to move

//


        private void InsertXmlCustomersUsingStoredProcedure()

        {

            String sXML;   // Will hold XML data



            // (YOU MUST CHANGE THE CONNECTION STRING TO MATCH YOUR SYSTEM)

            String sDatabaseConnectionString = @"Data Source=SERVERNAME;Initial Catalog=XMLTest;Integrated Security=True";

            //

            // (YOU MUST CHANGE THE PATH TO YOUR SYSTEMS DRIVE:\PATH)

            String sXMLFile = @"D:\XMLData\Customers.XML";

                 

            try

            {

                // Instanciate a new Sql Connection.  

                using (SqlConnection oConn = new SqlConnection(sDatabaseConnectionString))

                {

                    // Open the Connection to the database

                    oConn.Open();


                    // Use a StreamReader to read the XML File  

                    using (StreamReader reader = new StreamReader(sXMLFile))

                    {

                        // Read to the end of the XML File and store in sXML variable

                        sXML = reader.ReadToEnd();

                    }


                    // Create the Command object from the open connection

                    using (SqlCommand oCommand = oConn.CreateCommand())

                    {

                        // set the CommandText property of the SqlCommand object to stored procedure's name

                        oCommand.CommandText = "CustomersBulkInsertXML";


                        // set the CommandType property of the SqlCommand object to CommandType.StoredProcedure

                        oCommand.CommandType = CommandType.StoredProcedure;


                        // Add the DeviceID input parameter and set its properties.

                        IDataParameter oPrm = oCommand.CreateParameter();

                        oPrm.ParameterName = "@Customers";

                        oPrm.DbType = DbType.String;

                        oPrm.Direction = ParameterDirection.Input;

                        oPrm.Value = sXML;


                        // Add the new parameter to the command's parameter Array

                        oCommand.Parameters.Add(oPrm);


                        // Execute the query

                        int iRetCount = oCommand.ExecuteNonQuery();

                    }

                }

            }


            // Catch any SQL errors first 

            catch (SqlException ex)

            {

                MessageBox.Show("Sql Error: " + ex.Message);

            }


            // Was not a SQL error, so handle the Exception

            catch (Exception ex)

            {

                MessageBox.Show("Sql] Error: " + ex.Message);

            }

        }

asnd stored proceure is..

   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=CREATE&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=PROC&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 CustomersBulkInsertXML @Customers http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=nText&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99


   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=AS&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99

   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=DECLARE&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 @hDoc http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=int&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99

   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=exec&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=sp_xml_preparedocument&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 @hDoc http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=OUTPUT&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99, @Customers

 

   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=Insert&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=Into&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 Customers

   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=SELECT&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 ID, 

      FirstName, 

      LastName, 

      DOB,  

      Address, 

      City, 

      State, 

      Zip 

 

   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=FROM&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 OPENXML (@hDoc, '/Customers/Customer',1)

 

   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=WITH&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 (ID http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=int&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99, 

     FirstName http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=char&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99(15), 

     LastName http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=char&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99(15), 

     DOB http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=char&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99(15), 

     Address http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=char&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99(40), 

     City http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=char&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99(40), 

     State http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=char&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99(2), 

     Zip http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=char&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 (9)) XMLItems

 

   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=EXEC&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=sp_xml_removedocument&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99 @hDoc

   http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=GO&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99

 
for more details..
http://www.buffington.com/Blog/post/2010/02/02/Inserting-XML-Data-using-SQL-Server-Stored-Procedure.aspx

hope this helps you...

goldy gupta replied to Venkat K on 06-Jul-11 05:06 AM

thanks very much it works well.

Venkat K replied to goldy gupta on 06-Jul-11 06:14 AM

wc :)