Reading Spring.NET Configuration from a Database

Whilst recently working on a Spring.NET aware web application we required a way of centrally storing our Spring configuration. In this post we present an IResource implementation that reads Spring configuration from a SQLServer. We also show how we used the Schema Collection feature in SQL Server to ensure the configuration is schematically sound.

Database Setup

The first thing to do is setup the schema collection in the database. The easiest way I found for doing this to load the spring-objects-1.3.xsd file into an editor and replace all occurrences of ‘ with ‘’ (single quote with single quote single quote). Then execute the following SQL.

Figure 1.
CREATE XML SCHEMA COLLECTION [dbo].[SpringObjectSchemaCollection] AS
N'<PASTE CONTENTS OF SPRING-OBJECTS-1.3.XSD>'

If intend to use the Spring Validation framework you can add the spring-validation-1.3.xsd file to the schema collection again you should open the file in a text editor and replace all occurrences of ‘ with ‘’ (single quote with single quote single quote). Then execute the following SQL.

Figure 2.
ALTER XML SCHEMA COLLECTION [dbo].[SpringObjectSchemaCollection] ADD
N'<PASTE CONTENT OF SPRING-VALIDATION-1.3.XSD>'

Now that we have our schema collection we are in a position to create a table to hold our spring configuration. This table can be customised to store extra attributes e.g. change control etc. However we will keep it simple. The following SQL create a table called spring.Descriptors with an XML column whose content is governed by the schema collection we just created. Our table is created in a database schema called spring. You can create this using the CREATE SCHEMA SQL statement.

Figure 3.
CREATE TABLE [spring].[Descriptors](
    [fileName] [nvarchar](256) NOT NULL CONSTRAINT [DF_Descriptors_fileName]  DEFAULT (''),
    [attached] [bit] NOT NULL CONSTRAINT [DF_Descriptors_attached]  DEFAULT ((0)),
    [Objects] [xml](CONTENT [dbo].[SpringObjectSchemaCollection]) NOT NULL,
CONSTRAINT [PK_Descriptors] PRIMARY KEY CLUSTERED
(
    [fileName] ASC
  )WITH (PAD_INDEX= OFF,
       STATISTICS_NORECOMPUTE  = OFF,
       IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS  = ON,
       ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

We have out table and our schema collection so we will now create a simple spring configuration entry into the table. You can experiment with this however SQL Server will only accept spring configuration that conforms to the schemata we configured.

Figure 4.
INSERT INTO spring.Descriptors
VALUES('objects.config', 1,
N'<objects xmlns="http://www.springframework.net">
  <object id="Message" type="string">
    <constructor-arg value="Hello World !!!" />
  </object>
</objects>');

 

Spring Web App Configuration

Next we need to configure our web application. We are assuming that the base Spring.NET configuration has been done following the documentation i.e. the httpHandlers and httpModules.

As we are using a custom IResource implementation we need to prepare our configuration section. In Figure 5 we specify the resourceHandlers section and its corresponding handler.

Figure 5.
    <configSections>
    <sectionGroup name="spring">
      <section name="context" type="Spring.Context.Support.WebContextHandler, Spring.Web"/>
      <section name="resourceHandlers" type="Spring.Context.Support.ResourceHandlersSectionHandler, Spring.Core" />
      <section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core"/>
    </sectionGroup>
  </configSections>

Now that we the configuration section handlers are described we can define out custom resource handler.

Figure 6.
  <spring>
    <resourceHandlers>
      <handler protocol="db" type="WebServices.Configuration.DbResource, WebServices" />
    </resourceHandlers>
    <context>
      <resource uri="db://GatewayDBConnectionString/spring.Descriptors/objects?attached=1" />
     </context>
  </spring>

The handler element associates the “db” protocol with our custom handler. In the context section the resource element is how we invoke the custom handler. The handler is passed a Uri to describe the location of the spring configuration to read. Our custom handler will interpret this as follows

db://[ConnectionStringName]/[TableName]/[ColumnName]?Criteria

IResource Implementation

Our custom handler is initialised with a Uri as described in the previous section. The handler will do the following:

  • Identify a ConnectionString in the connectionString configuration section using the hostname portion of the Uri
  • Use the DbProviderFactories object to get an instance of the database provider specified by the connectionString provider attribute
  • Get a Connection and Command object and craft a SQL SELECT statement using the TableName, ColumnName and Criteria specified in the Uri.
  • Open a DataReader and stitch the resulting rows together into an Xml document hosted by a MemoryStream
  • Seek the start of the memory stream and return it.

Figure 7.
using System;
using Spring.Core.IO;
using System.Xml;
using System.IO;
using System.Configuration;
using System.Data.Common;

namespace WebServices.Configuration
{
    public class DbResource : IResource
    {
        Uri _uri;
        public DbResource(string uri)
        {            
            _uri = new Uri(uri);
        }

        #region IResource Members

        public IResource CreateRelative(string relativePath)
        {
            throw new NotImplementedException();
        }

        public string Description
        {
            get { return ""; }
        }

        public bool Exists
        {
            get { throw new NotImplementedException(); }
        }

        public System.IO.FileInfo File
        {
            get { throw new NotImplementedException(); }
        }

        public bool IsOpen
        {
            get { throw new NotImplementedException(); }
        }

        public Uri Uri
        {
            get { throw new NotImplementedException(); }
        }

        #endregion

        #region IInputStreamSource Members

        public System.IO.Stream InputStream
        {
            get
            {
                DbProviderFactory dbProvider = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[_uri.Host].ProviderName);
                DbConnection dbConnection = dbProvider.CreateConnection();
                dbConnection.ConnectionString = ConfigurationManager.ConnectionStrings[_uri.Host].ConnectionString;
                dbConnection.Open();
                DbCommand dbCommand = dbProvider.CreateCommand();
                dbCommand.CommandType = System.Data.CommandType.Text;
                dbCommand.CommandText = "SELECT " + _uri.Segments[2].Replace("/", "")
                                      + " FROM " + _uri.Segments[1].Replace("/", "")
                                      + (string.IsNullOrEmpty(_uri.Query) ? "" : " WHERE " + _uri.Query.Replace("?", ""));
                dbCommand.Connection = dbConnection;                
                DbDataReader ddr = dbCommand.ExecuteReader();
                            
                MemoryStream ms = new MemoryStream();
                XmlWriter xwtr = XmlTextWriter.Create(ms);
                xwtr.WriteStartDocument();
                xwtr.WriteStartElement("objects", "http://www.springframework.net");
                xwtr.WriteAttributeString("xmlns", "v", null, "http://www.springframework.net/validation");
                while (ddr.Read())
                {                    
                    TextReader txr = new StringReader(ddr.GetValue(0) as string);
                    XmlReader xrdr = XmlTextReader.Create(txr);                    
                    if (xrdr.Read())
                    {
                        xwtr.WriteRaw(xrdr.ReadInnerXml());
                    }
                }
                xwtr.WriteEndElement();
                xwtr.WriteEndDocument();
                xwtr.Flush();
                xwtr.Close();
                ms.Seek(0, SeekOrigin.Begin);

                return ms;
            }
        }
        #endregion
    }
}

We’re all done.

Conslusion

We have shown how you can store valid Spring configuration centrally in a database. Using this along with an application to maintain the spring.Descriptors table make for a powerful flexible facility for web application development. 

Tagged with: , , ,
Posted in Spring.NET, SqlServer

Leave a Reply

Your email address will not be published.