Peter Bosch is the Principal of Highpoint Software Systems, L.L.C., where he works to help companies reap the benefits of leading edge development. This avocation dates back to 1991 when he taught C and C++ to a group at Lockheed Martin (then GE Aerospace) migrating from Ada, and continued in 1996 when he was a technical lead on a group that brought Java scalability, flexibility and portability to GE Medical Systems' MRI group. He is now working on several contracts, and looking for the next great opportunity to help an organization evolve into the C# and .Net spaces. [Pete Bosch] He can be reached at [email protected].
Description
This sample shows how you can obtain a Dataset from (in this case) a SQL Server database, and then write it out to an XML Document. As an "Extra Added Bonus(tm)", it can show you how to write the schema as well. The SQL Server database in question is the venerable and useful Northwind database.
The significant parameters are all declared at the beginning as strings, so that you can easily debug and tweak for your own usage. In this section, we create a connection string, a query string (in standard SQL) and specify a target filename.
Then, we call the function,
static void writeSQLQueryToFileAsXML(string connString,string query,string filename);
This function first gets a connection, creates an adapter and specifies the connection and command to run through it, then obtains a dataset from that adapter.
This function then shows how you can write that DataSet out to a stream (in this case, a FileStream) as an XML document through the DataSet member, public void WriteXml(Stream). And if you've got some extra time on your hands, you can even uncomment the code in the last section, to write the DataSet's SCHEMA to a file!
An interesting corollary to this example would be to stream the DataSet's XML out to an HTTPResponse's HTTPWriter.outputStream, but that is both the subject of another sample, and also perhaps more easily done through native SQL Server capabilities.
Note that the XML that is written is output as a fragment, that is, there is no element, <?xml version="1.0"?> at the beginning, nor is there a unifying, single root node. While you will have to add both of these, this is really a sensible choice. First of all, only you, and your particular application context, can say what is the right name for the root node - is it <DelinquentCustomers>, or <GoldStarCustomers>? Secondly, it may be a node or set of nodes buried down inside a much larger document, such as a <Contacts> node under one of several <Salesman> nodes. Get the picture?
Compiling and Running the code:
csc XMLGenFromSQLDbPB.cs
Or
csc /r:System.dll,System.Data.dll,System.Xml.dll XMLGenFromSQLDbPB.cs
This will return you with the location where it had saved the XML file.You can specify it in the String s.
Source Code:
using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.IO;
namespace WriteXML
{
public class WriteXML
{
static void Main()
{
/*************************************************************/
// NOTE : YOU WILL NEED TO HAVE SQL SERVER (or MSDE) AVAILABLE, AND
// YOU WILL NEED THE NORTHWIND DATABASE INSTALLED IN THE SQL SERVER
// INSTANCE IN ORDER FOR THIS TO WORK.
//
// MODIFY THE FOLLOWING CONNECTION STRING AND QUERY STRING TO RUN
// THIS SAMPLE AGAINST A DIFFERENT DATABASE AND/OR QUERY.
/*************************************************************/
string outputFileName = "C:/myXmlData"; // ".xml" will be appended.
string connString = "user id=sa;password=password;" + "Database=northwind;server=(local);";
string sqlQueryString = "SELECT * FROM Suppliers";
// Here's the meat of the demonstration.
writeSQLQueryToFileAsXML(connString,sqlQueryString,outputFileName);
Console.WriteLine("Wrote query results to {0}",outputFileName);
}
static void writeSQLQueryToFileAsXML(string connString,string query,string filename)
{
SqlConnection myConn = new SqlConnection(connString);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(query,myConn);
// Build the DataSet
DataSet myDs = new DataSet();
adapter.Fill(myDs);
FileStream myFs = null;
// Get a FileStream object
myFs = new FileStream(filename+".xml",FileMode.OpenOrCreate,FileAccess.Write);
// Apply the WriteXml method to write an XML document
myDs.WriteXml(myFs);
// It is always good housekeeping to close a file.
myFs.Close();
/****************************************************/
// Uncomment the following code if you also want to
// dump the DataSet's schema to a file...
/****************************************************
// Get a FileStream object
myFs = new FileStream(filename+"_Schema.xml",
FileMode.OpenOrCreate,
FileAccess.Write);
myDs.WriteXmlSchema(myFs);
// It is always good housekeeping to close a file.
myFs.Close();
***************************************************/
}
}
}
This article is updated to RTM by Peter Bosch.