Sample ASP.NET code to take XCRI CAP 1.2 XML from
database and publish it as a web feed
Having got your XCRI CAP 1.2 XML generated from your database (see Sample SQL
Server 2008 code for extracting XCRI CAP 1.2 XML from a relational database), you will now probably want to
publish it on the web. A best practice could be to set up a scheduled task which
extracts the XML, validates it, and (if passed) writes the XML as a static file
to your webserver, then sends an email notification on the result to your
administrators.
However, if you just want to generate a live feed, here is some code showing
a way to do it using a Microsoft ASP.NET web form, using Visual Basic (VB.NET) code.
The ASP.NET page, save as default.aspx
This is a pretty simple page, which returns a content type of
"application/xml", and holds the XML control that will be filled with the XCRI CAP 1.2
output.
<%@ Page Language="VB" ContentType="application/xml" AutoEventWireup="false" CodeFile="default.aspx.vb" Inherits="courses_xcri_cap_1p2_default" %>
<asp:Xml ID="xmlCoursesCAP1p2" runat="server" />
The ASP.NET Visual Basic code behind file, save as
default.aspx.vb
The main work is done in the code behind file, which sits in the same
directory. The connection to your database should be set in your site/web
application's web.config file, and you should replace
"kelpiecollegeConnectionString" with the name of your connection string. You
should also amend the values of the three parameters as necessary (they will
work for the sample set of
course data on this wiki).
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Xml.XPath
Partial Class courses_xcri_cap_1p2_default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strSchemaName As String = "XCRI CAP 1.2"
Dim strProviderIdentifier As String = "0000X00XX0"
Dim strLanguage As String = "en"
Try
'Set up SQL connection and command which returns XCRI XML, using named connection string from web.config file.
Dim conn As SqlConnection
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("kelpiecollegeConnectionString").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.usp_Course"
cmd.Connection = conn
'Add parameters.
Dim sprSchemaName As SqlParameter = New SqlParameter()
Dim sprProviderIdentifier As SqlParameter = New SqlParameter()
Dim sprLanguage As SqlParameter = New SqlParameter()
sprSchemaName.ParameterName = "@schemaName"
sprSchemaName.SqlDbType = SqlDbType.NVarChar
sprSchemaName.Size = 25
sprSchemaName.Direction = ParameterDirection.Input
sprSchemaName.Value = strSchemaName
sprProviderIdentifier.ParameterName = "@providerIdentifier"
sprProviderIdentifier.SqlDbType = SqlDbType.NVarChar
sprProviderIdentifier.Size = 25
sprProviderIdentifier.Direction = ParameterDirection.Input
sprProviderIdentifier.Value = strProviderIdentifier
sprLanguage.ParameterName = "@language"
sprLanguage.SqlDbType = SqlDbType.NVarChar
sprLanguage.Size = 50
sprLanguage.Direction = ParameterDirection.Input
sprLanguage.Value = strLanguage
cmd.Parameters.Add(sprSchemaName)
cmd.Parameters.Add(sprProviderIdentifier)
cmd.Parameters.Add(sprLanguage)
'Open the connection.
cmd.Connection.Open()
'Get the XML from the stored procedure with an XmlReader and put it into the XML control to be passed to the requester.
Dim xmlr As System.Xml.XmlReader
xmlr = cmd.ExecuteXmlReader()
xmlr.Read()
Dim xpnDoc As New XPathDocument(xmlr)
xmlCoursesCAP1p2.XPathNavigator = xpnDoc.CreateNavigator()
'Dispose of command and connection objects.
cmd.Dispose()
conn.Dispose()
Catch ex As Exception
'Do stuff to handle an exception.
End Try
End Sub
End Class
Now all you need to do is publish these to your website and request the page.
And remember that your webserver account will need EXECUTE permissions on the
stored procedure (dbo.usp_Course).
You could set up caching on this page to avoid hitting the database every
time.
A working demonstration of the web pages built from this code scan be found on the fictional Kelpie College website courses section.
For more information on the standard, see the XCRI Knowledge Base.
This work is licensed under a Creative Commons Attribution 3.0 Unported License.
Back to Code and Examples for XCRI CAP 1.2.
Last updated:
.