Here I will convert SQL Server to
XML and back from XML to SQL Server. I will be using the "
bookstore.xml" file to read and write. I made some modifications to for this example:
Create a
Bookstore database. The XML nodes, attributes, sibling nodes and child nodes must be represented in one database record.
Example:
Books Table
RecordID - type int (identity column)
Genre - type varchar
ISBN - type varchar
PublicationDate - type date
Title - type varchar
AuthorFirstName - type varchar
AuthorLastname - type varchar
Price - type int
Create a basic form with 2 buttons with labels to trigger the read and write actions.
Once the Bookstore database has been created with a "books" table, add some sample data to the first row.
Add the "bookstore.xml" file to the project and make a copy of it with the name "bookstore1.xml". Create the code to process the "Write XML to Database" button.
Example:
private void btnWriteXMLtoDatabase_Click(object sender, EventArgs e)
{
// Create an XmlReader
//using (XmlReader reader = XmlReader.Create(new StringReader(xmlString)))
using (XmlReader reader = XmlReader.Create(@"C:\Users\ken\Documents\Visual Studio 2010\Projects\XMLtoSQLserver\XMLtoSQLserver\books.xml"))
{
reader.ReadToFollowing("book");
reader.MoveToFirstAttribute();
strGenre = reader.Value;
reader.MoveToNextAttribute();
strPubDate = reader.Value;
datPubDate = DateTime.Parse(strPubDate); //convert to date format
reader.MoveToNextAttribute();
strISBN = reader.Value;
reader.ReadToFollowing("title");
strTitle = reader.ReadInnerXml();
reader.ReadToFollowing("first-name");
strAuthorFirstName = reader.ReadInnerXml();
reader.ReadToFollowing("last-name");
strAuthorLastName = reader.ReadInnerXml();
}
//Output to database
try
{
conn.ConnectionString = @"Data Source=KEN-HP\SQLSERVER2008R2;Initial Catalog=Bookstore;Integrated Security=True";
conn.Open();
string queryStmt = "INSERT INTO Books(Genre, PublicationDate, ISBN, Title, AuthorFirstName, AuthorLastName)" +
"VALUES(@Genre, @PublicationDate, @ISBN, @Title, @AuthorFirstName, @AuthorLastName)";
using (SqlCommand _cmd = new SqlCommand(queryStmt, conn))
{
_cmd.Parameters.Add("@Genre", SqlDbType.NVarChar, 50);
_cmd.Parameters.Add("@PublicationDate", SqlDbType.DateTime, 50);
_cmd.Parameters.Add("@ISBN", SqlDbType.NVarChar, 50);
_cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 50);
_cmd.Parameters.Add("@AuthorFirstName", SqlDbType.NVarChar, 50);
_cmd.Parameters.Add("@AuthorLastName", SqlDbType.NVarChar, 50);
_cmd.Parameters["@Genre"].Value = strGenre;
_cmd.Parameters["@PublicationDate"].Value = datPubDate;
_cmd.Parameters["@ISBN"].Value = strISBN;
_cmd.Parameters["@Title"].Value = strTitle;
_cmd.Parameters["@AuthorFirstName"].Value = strAuthorFirstName;
_cmd.Parameters["@AuthorLastName"].Value = strAuthorLastName;
_cmd.ExecuteNonQuery();
}
}// end try
catch (Exception ex)
{
MessageBox.Show("Failed to insert to Books Table");
}
finally
{
conn.Close();
}
lblXMLtoDatabase.Visible = true;
}// end btnWriteXMLtoDatabase_Click
Create code to write from the database to XML.
Example:
private void btnWriteToXML_Click(object sender, EventArgs e)
{
conn.ConnectionString = @"Data Source=KEN-HP\SQLSERVER2008R2;Initial Catalog=Bookstore;Integrated Security=True";
//********************READ*********************************
try
{
conn.Open();
SqlCommand command = new SqlCommand(
"SELECT * FROM Books;",
conn);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
strGenre = (string)reader[1];
datPubDate = (System.DateTime)reader[2];
strPubDate = datPubDate.ToString();
strISBN = (string)reader[3];
strTitle = (string)reader[4];
strAuthorFirstName = (string)reader[5];
strAuthorLastName = (string)reader[6];
// Open an XML document.
System.Xml.XmlDocument myXmlDocument = new System.Xml.XmlDocument();
myXmlDocument.Load(@"C:\Users\ken\Documents\Visual Studio 2010\Projects\XMLtoSQLserver\XMLtoSQLserver\books1.xml");
System.Xml.XmlNode myXmlNodeFirstBook = myXmlDocument.DocumentElement.FirstChild;
// Create a Book element and populate its attributes
System.Xml.XmlElement XmlElementMyBook = myXmlDocument.CreateElement("book");
XmlElementMyBook.SetAttribute("genre", strGenre);
XmlElementMyBook.SetAttribute("publicationdate", strPubDate);
XmlElementMyBook.SetAttribute("ISBN", strISBN);
// Insert the new element into the XML tree under Catalog
myXmlDocument.DocumentElement.InsertBefore(XmlElementMyBook, myXmlNodeFirstBook);
// Create a new child of the book element
System.Xml.XmlElement myXmlElement2 = myXmlDocument.CreateElement("title");
myXmlElement2.InnerText = strTitle;
// Insert the new element under the node we created
XmlElementMyBook.AppendChild(myXmlElement2);
// Create a new child of the book element
System.Xml.XmlElement myXmlElement3 = myXmlDocument.CreateElement("author");
XmlElementMyBook.AppendChild(myXmlElement3);
// Create new child of the author element
System.Xml.XmlElement myXmlElement4 = myXmlDocument.CreateElement("first-name");
myXmlElement4.InnerText = strAuthorFirstName;
myXmlElement3.AppendChild(myXmlElement4);
// Create new child of the author element
System.Xml.XmlElement myXmlElement5 = myXmlDocument.CreateElement("last-name");
myXmlElement5.InnerText = strAuthorLastName;
myXmlElement3.AppendChild(myXmlElement5);
myXmlDocument.Save(@"C:\Users\ken\Documents\Visual Studio 2010\Projects\XMLtoSQLserver\XMLtoSQLserver\books1.xml");
}// end while
}
else
{
Console.WriteLine("No rows found");
}// end if (reader.HasRows)
reader.Close();
}// end try
catch (Exception ex)
{
MessageBox.Show("Failed to connect to Books Table");
}
finally
{
conn.Close();
}
//********************END READ******************************
lblDataWrittenToXML.Visible = true;
}// end btnWriteToXML_Click
This example is very basic so you will need to manually add , remove and view data from your sample XML files and database.
Click
((here)) for the project code.
