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,
Shared Sub writeSQLQueryToFileAsXML(ByVal connString As String, ByVal query As String, ByValfilename As String)
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:
vbc
XMLGenFromSQLDbPB.vb.
Or
vbc /r:System.dll,System.Data.dll,System.Xml.dll XMLGenFromSQLDbPB.vb.
This will return you
with the location where it had saved the XML file.You can specify it in the
String s.
Source Code:
Imports System
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports System.IO
Namespace WriteXML
Public Class WriteXML
Shared Sub 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.
'*******************************************************************
Dim outputFileName As String =
"C:/myXmlData" '
".xml" will be appended.
Dim connString As String =
"user id=sa;password=password;" + "Database=northwind;server=(local);"
Dim sqlQueryString As String =
"SELECT * FROM Suppliers"
' Here's the
meat of the demonstration.
writeSQLQueryToFileAsXML(connString, sqlQueryString, outputFileName)
Console.WriteLine("Wrote query results to {0}", outputFileName)
End Sub 'Main
Shared Sub writeSQLQueryToFileAsXML(ByVal connString As String, ByVal query As String, ByValfilename As String)
Dim myConn As New SqlConnection(connString)
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(query,
myConn)
' Build the
DataSet
Dim myDs As New DataSet
adapter.Fill(myDs)
Dim myFs As FileStream
= Nothing
' 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()
End Sub 'writeSQLQueryToFileAsXML
End Class 'WriteXML
'***************************************************
End Namespace 'WriteXML
' 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()
'***************************************************