« The Enterprise Social Messaging Experiment | Home | City of Men »
Create XML for Simple Viewer from SQL Server in VB.NET
By chris dalby | July 9, 2008
OK, so this is Part 2 of what has become a series. In my previous post, I gave you code that will create the XML file from SQL server data using VB.NET needed to use in Tiltviewer, a rather cool image visualisation using flash.
So these same guys also do something called simple viewer. Today I got around to creating the code needed to generate the XML file for it. Why this is different to the TiltViewer XML format is beyond me. Anyways.
Again, this is not a tutorial, I am simply sharing the code. So leave a comment if you want help, but this basically shows you the correct syntax for generating your XML file using vb.NET from SQL Server using a stored procedure.
I simply put this code into the backend file xml.aspx.vb. Then I run the file and the XML file is generated from the SQL server database. Oh, you’ll need to add an extra tag at the top of the xml file according to your needs. I didn’t bother doing this, I just copied and pasted from the sample file as it seemed far easier at the time 
Imports System.IO
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Data
Partial Class visual2_XML
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim filename As String = “G:\websites\htdocs\test\XML\gallery.xml”
Dim mywriter As System.Xml.XmlTextWriter
‘declare variables
Dim strPhoto As String = “imgs/img.jpg”
‘declare the db connection stuff
‘ Create Instance of Connection and Command Object
Dim connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings(”ConnectionString”))
Dim command As SqlCommand = New SqlCommand(”GetProductsInCategory”, connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue(”@CategoryID”, “74″)
‘ extract details
connection.Open()
Dim customerReader As SqlDataReader = command.ExecuteReader()
‘declare the xml stuff
Dim writer As XmlTextWriter = Nothing
writer = New System.Xml.XmlTextWriter(filename, Nothing)
‘Use indenting for readability.
writer.Formatting = Formatting.Indented
‘Write the XML delcaration.
writer.WriteStartDocument()
‘Write a root element.
writer.WriteStartElement(”simpleviewergallery”)
While customerReader.Read
writer.WriteStartElement(”image”)
‘ Loop
‘Write the title.
writer.WriteElementString(”filename”, customerReader(”ImagePath”))
writer.WriteElementString(”caption”, customerReader(”Name”))
‘Write the close tag for the root element.
writer.WriteEndElement()
End While
‘close db
connection.Close()
writer.WriteEndDocument()
‘Write the XML to file and close the writer.
writer.Flush()
writer.Close()
‘Load the file into an XmlDocument to ensure well formed XML.
Dim doc As New XmlDocument()
‘Preserve white space for readability.
doc.PreserveWhitespace = True
Try
Label1.Text = “This seemed to work.”
Catch ex As Exception
Label1.Text = ex.Message
End Try
End Sub
End Class
Topics: Coding, Tech Watch |