. Storing XML to a Database Field

Problem

You need to store XML to a field in a database.

Solution

Store the contents of the InnerXml of the XmlDocument to the database. You can later load this into an empty XmlDocument with LoadXml( ) .

The schema of table TBL0804 used in this solution is shown in Table 8-4.

Table 8-4. TBL0804 schema

Column name

Data type

Length

Allow nulls?

Id

int

4

No

XmlField

nvarchar

4000

Yes

The sample code contains five event handlers:

Form.Load

Sets up the DataTable that contains the text field, XmlField , containing the XML and the DataAdapter for the table.

Write Button.Click

Adds or updates a record in the table with the Id and XmlField entered by the user .

Read Button.Click

Loads the XML for the specified Id into an XmlDocument and displays it on the form in the XmlField text box.

Sample Button.Click

Generates sample XML data from the Orders table in Northwind and displays it on the form in the XmlField text box.

Clear Button.Click

Clears the contents of the Id and XmlField text boxes on the form.

The C# code is shown in Example 8-7.

Example 8-7. File: StoreXmlFieldForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

private DataTable dt;
private SqlDataAdapter da;

private const String TABLENAME = "TBL0804";

// . . . 

private void StoreXmlFieldForm_Load(object sender, System.EventArgs e)
{
 String selectText = "SELECT Id, XmlField FROM " + TABLENAME;
 String insertText = "INSERT " + TABLENAME + " (Id, XmlField) " + 
 "VALUES (@Id, @XmlField)";
 String updateText = "UPDATE " + TABLENAME + " " +
 "SET XmlField = @XmlField " +
 "WHERE Id = @Id";

 // Create the data adapter.
 da = new SqlDataAdapter(selectText,
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);

 da.UpdateCommand = new SqlCommand(updateText,
 da.SelectCommand.Connection);
 da.UpdateCommand.CommandType = CommandType.Text;
 da.UpdateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
 da.UpdateCommand.Parameters.Add("@XmlField", SqlDbType.NText, 0,
 "XmlField");

 da.InsertCommand = new SqlCommand(insertText,
 da.SelectCommand.Connection);
 da.InsertCommand.CommandType = CommandType.Text;
 da.InsertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
 da.InsertCommand.Parameters.Add("@XmlField", SqlDbType.NText, 0,
 "XmlField");

 dt = new DataTable( );
 da.FillSchema(dt, SchemaType.Source);
 da.Fill(dt);
}

private void writeButton_Click(object sender, System.EventArgs e)
{
 // Load the ID into variable and text box into XmlDoc.
 int id = 0;
 XmlDocument xmlDoc = new XmlDocument( );
 try
 {
 id = Int32.Parse(idTextBox.Text);
 xmlDoc.LoadXml(xmlTextBox.Text);
 }
 catch(Exception ex)
 {
 MessageBox.Show("ERROR: " + ex.Message);
 return;
 }

 // Find the row with the ID entered.
 DataRow row = dt.Rows.Find(new object[] {id});
 if(row != null)
 // For an existing row, update the XmlField.
 row["XmlField"] = xmlDoc.InnerXml;
 else
 // For a new row, add the row with the ID and XmlField.
 dt.Rows.Add(new object[] {id, xmlDoc.InnerXml});

 // Update the database using the DataAdapter.
 da.Update(dt);
}

private void readButton_Click(object sender, System.EventArgs e)
{
 // Load the ID into variable from text box.
 int id = 0;
 try
 {
 id = Int32.Parse(idTextBox.Text);
 }
 catch(Exception ex)
 {
 MessageBox.Show("ERROR: " + ex.Message);
 return;
 }

 // Find the row with the ID entered.
 DataRow row = dt.Rows.Find(new object[] {id});
 if(row != null)
 {
 // If found, load the XML column value from the row.
 XmlDocument xmlDoc = new XmlDocument( );
 xmlDoc.LoadXml(row["XmlField"].ToString( ));

 // Display the XML.
 xmlTextBox.Text = xmlDoc.InnerXml;
 }
 else
 xmlTextBox.Text = "Record not found for Id = " + id;

}

private void sampleXmlButton_Click(object sender, System.EventArgs e)
{
 DataSet ds = new DataSet( );
 
 // Fill the Categories table and add it to the DataSet.
 SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 3 * FROM Orders",
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
 da.FillSchema(ds, SchemaType.Source, "Orders");
 da.Fill(ds, "Orders");

 // Write the XML for the DataSet to a StringWriter, and output.
 StringWriter sw = new StringWriter( );
 ds.WriteXml(sw, XmlWriteMode.WriteSchema);
 xmlTextBox.Text = sw.ToString( );
 ds.Dispose( );

 idTextBox.Clear( );
}

private void clearButton_Click(object sender, System.EventArgs e)
{
 idTextBox.Clear( );
 xmlTextBox.Clear( );
}

Discussion

The solution demonstrates how to store XML data in a text field of a database table and subsequently read it into an XmlDocument using the LoadXml( ) method. Standard database access techniques using a DataAdapter and DataTable are used.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax



ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net