Schema Field Pattern

Intent

To be able to store, retrieve, and manipulate data schemas dynamically. The means for passing descriptive metadata in an abstracted N- tier architecture.

Problem

Data schemas can be one of the most effective means by which data can be manipulated, stored, passed, or retrieved. They provide the structure and the backbone to what would otherwise be just another data abstraction layer manifested in ADO.NET. But what is the best way to create a schema? Once created, where does the schema go? How do you determine what schema to use? Where are the schemas kept, if anywhere ? These are some of the questions I hope to answer in this chapter. However, the .NET framework has given you many means by which to manipulate schemas and DataSets. You can create from scratch at runtime any structure you want. You can also import any preexisting schema into a DataSet at runtime. You can use one schema containing many tables like a "floating" ERD or you can have one schema for each area of a database used for one particular purpose. You don't even have to know that a schema exists behind the scenes by strictly using ADO.NET against any supported database. The choices are many. So which one is the best? Well, that depends on what you need but here I try and provide the best of all worlds .

To support the Poly Model pattern, you must first have some structure to hold data. The basis of the pattern does not use a physical model for this purpose but the structure must come from somewhere. The only physical structure that will store instance data is the data table I mentioned in the previous section. This table does not contain any business structure, however, and that is where the schemas come in. To store business data, you must first have a schema, its instance data, and a key or two for lookup. To begin with, however, you must first have the schema defined. Once you have your schema or schemas, you need a place to put them. That is where the schema table comes in. As mentioned earlier, the only fields required of this physical table are an ID and the column that will hold the actual schema.

I do recommend a few more fields to add a little more flexibility to your solution, so I've created the schema table shown in Figure 5.4 in SQL Server.

Figure 5.4. Physical model of the schema table as seen from SQL Table Properties.

Schemas do not have to be stored in a database but when you begin to work with several schema versions and types, creating a database table is usually the best option. This even goes for using strongly typed DataSets. You can use strongly typed DataSets to manage each schema at runtime. However, when using multiple versions, you must then manage different code sets, one for each DataSet/schema. Using a database table will simplify your life by providing a place to store your streamed schemas in one place.

Once you have your schema table, you can readily store your schemas, retrieve them at runtime using any version, instantiate your DataSet with the schema structure, and manipulate your data in memory as though you were working with any normal data services layer. Once you have your schema table, you then need to store at least one schema so that it can be retrieved and used within your architecture at runtime. Storing and retrieving schemas is the basis for this pattern and will give you the structure required to create your "floating" ERD in memory. To begin to get a feel for the structure of the Schema Field pattern reference Figure 5.5.

Figure 5.5. The Schema Field pattern class diagram.

Forces

Use the Schema Field pattern when:

• Complex XML must be stored and organized by structure and/or type.

• Using the Poly Model pattern.

• Multiple schemas are used and categorized.

• Unmanaged clients need to pass DataSets into a .NET backbone.

• Using strongly typed DataSets that may contain multiple versions.

Structure

The number of entities in the structure of this pattern is deceiving. The simplicity of the Schema Field pattern lies in the association between the DataServiceFacade, the ModelFactory, and the Poly Model object itself. The PolyModelData class represents generic data access functionality and does not have to be represented exactly as shown. Any data access methods could have been used. However, I believe it is important to abstract those lower level services with that of the PolyModel class. As you proceed deeper into the Poly Model pattern, you will see exactly what I'm referring to.

The Poly Model pattern is more generic than any "typical" data access layer. The Poly Model child class MSSQLPolyModel contains the specific SQL commands used to carry out each request. Only generic ADO wrapper implementations have been placed in the PolyModelData entity. Other than the PolyModel and the MSSQLPolyModel class, there is little reason to have any other business-specific entities that contain SQL as you typically see in most other data access layers. You have just eliminated hours of SQL design and encapsulation that you would normally have at this layer in the architecture. In fact, because most data access layers access numerous physical tables instead of only a few, as the Poly Model does, you typically have SQL spread throughout your classes. Most data access layers may even have one class for every table that it must access with the corresponding SQL command. This is not the case when employing the Poly Model pattern. Most of the SQL can now be centralized in one or two entities. In this case, those entities are the PolyModel class and the MSSQLPolyModel class. This is another advantage of using Poly Models. As far as the Schema Field pattern is concerned , the only SQL used is that SQL needed to carry out the retrieval and storage of the schemas themselves . Later I will show you how this is very similar to the handling of the actual instance data. During normal query and save operations, I will save and retrieve customer information using very generic SQL and utilizing the services of the Schema Field pattern as a starting point.

Consequences

• Eliminates the need to manage multiple versions of strongly typed DataSet classes as part of the code tree . Schemas do not have to be stored or retrieved. Using a factory, a strongly typed DataSet could be dynamically instantiated and used within the architecture without ever having to go to the database. This presents a problem, however. Future versions may be created after an application has gone to production, and supporting older versions may be necessary. In cases such as these, storing and retrieval schemas with the Schema Field pattern are strongly recommended.

• Provides a means by which to retrieve structured XML in any capacity, including support for unmanaged clients . For unmanaged clients, storing schemas in the database will provide direct access for those clients. The .NET DataSet does not have to be instantiated in this case, and the unmanaged client can treat the schema as any other well- formed XML document. This provides an interop layer for bridging technologies other than .NET. Remember that XML is universal.

• Provides a schema version management framework . As mentioned above, to support client access, older schemas will require some version management. Versioning the schemas while storing them and providing the code to dynamically access them will give the developer a starting point at which to build version management at the schema level.

Implementation

In the implementation that follows , you should notice quite a bit of code to do something as simple as storing and retrieving what amounts to a single field in the database. Do not be disheartened from this. I show most of the code used to perform such an operation so that the remaining patterns of this chapter can be better understood . It is the storing and retrieval of schemas that drives the rest of the PolyModel pattern, and it is crucial to its understanding. That said, the code below is rather self-explanatory and shows how a simple string value can hold the entire structure of your "floating" ERD or database in memory.

Once you have your structure, creating a DataSet from it is rather simple from .NET. The following code snippet (Listing 5.14) shows how to take your schema from memory and generate, or "hydrate" a DataSet from that point:

Listing 5.14 Helper method to create a structure DataSet from an XML schema.

 virtual public void ReadXmlschema(DataSet oDS, string sschema)    {       if(oDS == null)          return;       System.IO.StringReader oReader = null;       oReader = new System.IO.StringReader(sschema);       if(oReader != null)          oDS.ReadXmlschema(oReader);    } 

If you are using other tools besides .NET to send and receive DataSets, you will have to build your own DataSet, using something such as Microsoft's XML DOM to manipulate a schema manually and duplicate what .NET has conveniently placed into the DataSet class for you. In fact, in the application featured in Chapter 6, we've done just that; virtually creating the DataSet class in an unmanaged world. This is one way to appreciate what Microsoft has done with ADO.NET and XML!

The following code will kick-start your ability to apply the Poly Model by first allowing you to generate a schema and save it. For this implementation, I am using only one schema I created in Visual Studio, called Customers . It is the same schema I introduced in the previous section and will be the schema I use throughout this chapter. In the Generate Schema Web method, I simply construct the Customers object (as you will remember from the previous section, this will be generated by Visual Studio) and once the schema is created, I called StoreSchema(). Before calling the StoreSchema function, I insert a dummy blank row so that the returned schema will show the instance data to give you a feel for the structure in the browser. Once in the StoreSchema function, I immediately instantiate a class called PolyModel that acts as a factory for my data provider.

Once I have the PolyModel class, I call StoreSchema(), and I pass the product name, version, description, type, and, of course, the actual DataSet. These are optional but are recommended to give you the most flexibility when adding other schemas to your framework. The product name is the friendly name for the schema and will be used for lookups other than the key. The version can be used to distinguish multiple versions of the same schema. For example, if you anticipate the schema changing slightly and you want to support older schemas, versioning is your friend. Versioning can be tricky. Versioning Schema Field additions will be much simpler than trying to manage schemas whose fields have been deleted. Deleted fields can cause problems for pieces of code expecting them to exist, so be careful. Besides the other obvious parameters of description and DataSet, the type parameter categorizes the schema and again is optional. For example, in the production application featured in Chapter 6, we use three primary types of schemas:

1. Decision schemas ” used for dynamic routing of logic.

2. Lookup schemas ” used in the Schema Indexer pattern explained in this chapter.

3. Product schemas ” used to hold typical instance data, such as the Customers data generated from the schema featured here.

Listing 5.15 Used to generate and optionally save a schema to the database SCHEMA table.

 [WebMethod]    public DataSet Generateschema(          string sProduct, string sVersion,          string sDescription, bool bStore)       {          Customers dsCustomers = null;          try          {             dsCustomers = new Customers();             Customers.CustomerRow oRow =       dsCustomers.Customer.NewCustomerRow();    // solely for testing        oRow.CustomerID = "123";             oRow.CompanyName = "The eTier Group";             oRow.ContactName = "Christian Thilmany";             oRow.Phone = "713-555-2343";       dsCustomers.Customer.AddCustomerRow(oRow);             dsCustomers.Customer.AcceptChanges();             if (bStore)                Storeschema(sProduct, sVersion,       sDescription, dsCustomers);          }          catch(Exception ex)          {             throw new       BaseException(       Utilities.BuildErrorMessage(       "Exception thrown",       ex.Message), true);          }             return dsCustomers;          } 

Listing 5.16 Helper method to save the passed-in schema to the database using the Poly Model object.

 [WebMethod] public DataSet Storeschema(string sProduct, string sVersion, string sDescription, DataSet ds) {    try    {    ModelFactory.CreatePolyModel().Storeschema(    sProduct, sVersion,    DTPConstants.SCHEMA_TYPE.PRODUCT, sDescription, ds);    }    catch (Exception ex)    {       throw new    BaseException(    Utilities.BuildErrorMessage(    "Exception thrown",    ex.Message), true);    }    return null; } 

The following StoreSchema method is called from the previous Web method, and it simply delegates to my specific provider's DBStoreSchema method. This only provides another level of abstraction for my implementation.

Listing 5.17 Generic StoreSchema method from the PolyModel class.

 public virtual bool Storeschema(string sName, string sVersion, DTPConstants.SCHEMA_TYPE eType, string sDescription, DataSet oschema)    {      bool bResult = false;      try      {         Profiler.BeginInstrumentation();         // Check argument         . . .         int iNumTries = 0;         StringBuilder sb = null;         while(iNumTries < DTPConstants.MAX_DB_ATTEMPTS)         {            // Write the schema to the database            bResult = DBStoreschema(sName,      sVersion, eType, sDescription, oschema);            if(bResult)            {               // See if we are in a retry loop               if(iNumTries > 0)               {                   . . .               }               return true;}    . . .    iNumTries++;          }          . . .          return false;       }       . . .       return false;    } 

The heart of the implementation lies within the DBStoreSchema method below. Looking through the code, you should immediate recognize that this is like any other ordinary ADO save routine, however. The only difference is I am using a stored procedure called sp_DNPatternsUpdateschema to update the schema table with my schema. Up to this point, many of the original parameters have been passed inward, and now it's time to extract the schema itself and update the database with it. Two things should be pointed out here. The following snippet uses another factory to create a provider specific to SQL Server. This "Data Factory" is a very clean way to support multiple providers on the back end in case you are using Oracle. The following code is used to actually create the SQL Server provider and connect to the database. How you implement this piece is up to you.

Listing 5.18 Using a provider factory to create the appropriate Model object.

 PolyModelData oDB = null;    oDB =       ModelFactory.CreatePolyModelData(       "sp_DNPatternsUpdateschema",       CommandType.StoredProcedure);    if(oDB == null)       return false; 

The second snippet worth pointing out is how to extract the schema from the DataSet. Fortunately for you, .NET makes it easy. Here I call WriteXml(), which is shown in Listing 5.19. WriteXml() uses a StringWriter class to pass into the DataSet's overloaded version of WriteXml and simply tell it to write the schema only. That's it for generating and saving schemas!

Listing 5.19 Extracts the schema from PolyModelData object.

 string sxml = string.Empty;    sxml = oDB.WriteXml(oschema,       XmlWriteMode.Writeschema);    if(sxml.Length == 0)       return false;    oInParm5.Value = sxml; 

Below is the DBStoreschema method from the MSSQLPolyModel class:

Listing 5.20 Shows the use of a store procedure to store the schema field.

 public override bool DBStoreschema(string sschemaName, string sVersion, DTPConstants.SCHEMA_TYPE eType, string sDescription, DataSet oschema)    {       try       {          Profiler.BeginInstrumentation();          // Check inputs          if(sschemaName.Length == 0)             return false;          // Check argument          if(eType == DTPConstants.SCHEMA_TYPE.UNKNOWN)             return false;          // Check argument          if(sVersion.Length == 0)             return false;          // Check argument          if(oschema == null)             return false;          // Check argument          string sDescription2 = sDescription;          if(sDescription.Length == 0)             sDescription2 = "Default Description";          PolyModelData oDB = null;          oDB =       ModelFactory.CreatePolyModelData(       "sp_DNPatternsUpdateschema",       CommandType.StoredProcedure);          if(oDB == null)             return false;          // Bind input stored procedure parameter          SqlParameter oInParm1 = null;          oInParm1 = (SqlParameter)       oDB.AddDataParameter("@Name",       SqlDbType.VarChar, 64,       ParameterDirection.Input);          if(oInParm1 == null)             return false;          oInParm1.Value = sschemaName;          SqlParameter oInParm2 = null;          oInParm2 = (SqlParameter)       oDB.AddDataParameter("@Version",       SqlDbType.VarChar, 64,       ParameterDirection.Input);          if(oInParm2 == null)             return false;          oInParm2.Value = sVersion;          SqlParameter oInParm3 = null;          oInParm3 = (SqlParameter)       oDB.AddDataParameter("@schemaType",       SqlDbType.BigInt,       ParameterDirection.Input);          if(oInParm3 == null)             return false;          oInParm3.Value = (long) eType;          // Bind input stored procedure parameter          SqlParameter oInParm4 = null;          oInParm4 = (SqlParameter)       oDB.AddDataParameter("@Description",       SqlDbType.VarChar, 255,       ParameterDirection.Input);          if(oInParm4 == null)             return false;          oInParm4.Value = sDescription2;          // Bind input stored procedure parameter          SqlParameter oInParm5 = null;          oInParm5 = (SqlParameter)       oDB.AddDataParameter("@xmlData",       SqlDbType.Text, ParameterDirection.Input);          if(oInParm5 == null)             return false;          string sxml = string.Empty;          sxml = oDB.WriteXml(oschema,       XmlWriteMode.Writeschema);          if(sxml.Length == 0)             return false;          oInParm5.Value = sxml;          SqlParameter oOutParm = null;          oOutParm = (SqlParameter)       oDB.AddDataParameter("@RowCount",       SqlDbType.Int, ParameterDirection.ReturnValue);          if(oOutParm == null)             return false;          // Execute the stored procedure          int iRowCount = 0;          iRowCount = oDB.ExecuteNonQuery();          // Get the result of the stored procedure          int iCount = 0;          iCount =       Convert.ToInt32(       oDB.GetDataParameterValue(       "@RowCount"));          if(iCount == 1)             return true;       }       catch(SqlException e)       {          LogSQLException(e);       }       catch(Exception e)       {          Trace.WriteLineIf(       Config.TraceError, e.Message);       }       finally       {          . . .       }       return false;    } 

Listing 5.21 Serializes, in memory, a string representation of the schema stored in the DataSet passed.

 virtual public string WriteXml(       DataSet oDS, XmlWriteMode eMode) {    if(oDS == null)       return string.Empty;    System.IO.StringWriter oSW = null;    oSW = new System.IO.StringWriter();    if(oSW == null)       return string.Empty;    oDS.WriteXml(oSW, eMode);    return oSW.ToString(); } 

Now you should have a Customers schema saved. So how do you retrieve and populate a DataSet? Like storing schemas, retrievals are self-explanatory and start with a driver method called GetSchema() . This method creates a Poly Model object as before, calls StoreSchema() from the Poly Model object, and uses a simple SQL command to retrieve the schema from the database. Once the schema has been returned as a string, it can be traversed manually from any unmanaged client or simply used to construct a DataSet.

Listing 5.22 Web service driver for retrieving schemas from the database.

 [WebMethod]       public DataSet Getschema(string sschemaName,       string sVersion, DTPConstants.SCHEMA_TYPE eType)       {          PolyModelData oDB =       ModelFactory.CreatePolyModelData(); oDB.ReadXmlschema(ModelFactory.CreatePolyModel()       .DBGetschema(sschemaName,       sVersion, eType));          return oDB.Data;       } 

Listing 5.23 Poly Model method for retrieving schemas from the database using straight SQL.

 public override string DBGetschema( string sschemaName, string sVersion, DTPConstants.SCHEMA_TYPE eType)    {       try       {          Profiler.BeginInstrumentation();          // Check inputs          if(sschemaName.Length == 0)             return string.Empty;          if(sVersion.Length == 0)             return string.Empty;    if(eType == DTPConstants.SCHEMA_TYPE.UNKNOWN)             return string.Empty;          StringBuilder sbSQL = new StringBuilder();          // Define the query to execute              sbSQL.Append("execute          //sp_executesql ");       sbSQL.Append("N'SELECT xml_DATA FROM" +       "dbo.[SCHEMA] WHERE NAME = @schemaName" +       "AND VERSION = @schemaVersion AND" + "SCHEMA_TYPE = @schemaType', ");                sbSQL.Append("N'@schemaName VARCHAR(65), " +       "@schemaVersion VARCHAR(32), " + "@schemaType BIGINT', ");       sbSQL.AppendFormat("@schemaName ='{0}', " +       "@schemaVersion = '{1}', @schemaType = "       + "{2} ", sschemaName,       sVersion, Convert.ToInt32(eType));          return GetschemaBySQL(sbSQL.ToString());       }       . . .       return string.Empty;    } 

This section contained quite bit of code but that is exactly the heart of this pattern, which borders more on implementation than design. The Schema Field pattern is an implementation solution but also the process pattern for saving and retrieving the actual bits and bytes of the schema to a persistent store.

Related Patterns

• Schema Indexer (Thilmany)

• Poly Model (Thilmany)

• Delegate (GoF)

• Abstract Schema (Thilmany)

• Factory Method (GoF)

• Abstract Packet (Thilmany)