Home > .NET, SQL Server > XML stored generation with CodeSmith

XML stored generation with CodeSmith

2012/11/09

Microsoft SQL Server has , since the 2000 version, the possibility of to use XML in stored procedures by using the OPENXML keyword.

I love this feature because is very efficient and your code is simplified, especially when you must work with tables having many fields.

For a little sample we can use a simple SQL Server table as

CREATE TABLE [dbo].[Cars](
    [CarID] [int] IDENTITY(1,1) NOT NULL,
    [Model] [varchar](30) NOT NULL,
    [Plate] [varchar](10) NULL,
    [Corporate] [bit] NULL,
    [CheckStamp] [timestamp] NULL,
    [_UserMod] [varchar](50) NULL,
    [_HourMod] [datetime] NULL,
    [_UserAdd] [varchar](50) NULL,
    [_HourAdd] [datetime] NULL,
 CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
(
    [CarID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [IX_Cars] UNIQUE NONCLUSTERED
(
    [Plate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


This is a typical table with the classic fields for the user which has inserted/modified the record, and the checkstamp for the concurrent modification verification.

With an XML for example

<Cars>
	<Model><![CDATA[Saab 9.3]]></Model>
	<Plate><![CDATA[WNF713]]></Plate>
	<Corporate><![CDATA[0]]></Corporate>
	< _UserAdd><![CDATA[Smith]]></ _UserAdd>
	< _UserMod><![CDATA[Smith]]></_UserMod>
</Cars>


We can use a stored procedure for adding as:

CREATE PROCEDURE [dbo].[spAdd_Cars](
    @XMLDoc text,
    @ID int OUTPUT
)
AS
   SET NOCOUNT ON
   DECLARE @intHandleDoc int
   DECLARE @intErr int

   EXECUTE sp_xml_preparedocument @intHandleDoc OUTPUT, @XMLDoc

   BEGIN TRANSACTION
    INSERT INTO Cars(
        Model ,
        Plate ,
        Corporate ,
        _UserAdd
    )
    SELECT
        Model,
        Plate,
        Corporate,
        _UserAdd,
        GETDATE()
    FROM OpenXML(@intHandleDoc, '/Cars', 1)
    WITH(
        Model  varchar(30) '../Cars/Model'  ,
        Plate  varchar(10) '../Cars/Plate'  ,
        Corporate  bit '../Cars/Corporate'  ,
        _UserAdd  varchar(50) '../Cars/_UserAdd'
    )
   SELECT @intErr = @@ERROR,  @ID = SCOPE_IDENTITY()
   EXEC sp_xml_removedocument @intHandleDoc
   IF @intErr > 0
        BEGIN
            RAISERROR('Error in add car.', 18,1)
            ROLLBACK TRANSACTION
            SET @ID = 0
            RETURN
        END

   COMMIT TRANSACTION


And for update:

CREATE PROCEDURE [dbo].[spUpd_Cars](
   @XMLDoc text
)
AS
   SET NOCOUNT ON
   DECLARE @intHandleDoc int
   DECLARE @intErr int
   DECLARE @lngRet int

   EXECUTE sp_xml_preparedocument @intHandleDoc OUTPUT, @XMLDoc

   BEGIN TRANSACTION
    UPDATE Cars SET
        Model = XMLUpdateSet.Model ,
        Plate = XMLUpdateSet.Plate ,
        Corporate = XMLUpdateSet.Corporate ,
        _UserMod = XMLUpdateSet._UserMod ,
        _HourMod = GETDATE()
    FROM(
        SELECT * FROM OpenXML(@intHandleDoc, '/Cars', 1)
        WITH(
            Model  varchar(30) '../Cars/Model'  ,
            Plate  varchar(10) '../Cars/Plate'  ,
            Corporate  bit '../Cars/Corporate'  ,
            _UserMod  varchar(50) '../Cars/_UserMod'  ,
        )
    )
    AS XMLUpdateSet
    WHERE Cars.CarID  = XMLUpdateSet.CarID
    SELECT @intErr = @@ERROR, @lngRet = @@ROWCOUNT
    EXECUTE sp_xml_removedocument @intHandleDoc

    IF @intErr > 0
        BEGIN
            RAISERROR('Error in modify car.', 18,1)
            ROLLBACK TRANSACTION
            RETURN 0
        END
   COMMIT TRANSACTION

   RETURN @lngRet


In both stored the @XMLDoc contains our sample XML.

The stored in sample are using a SQL 2000 compatible syntax , with a more recent SQL is better to use TRY…CATCH.

If you have a table with many fields the activity of fields list writing and data type verification in SQL Management Studio (for the part in OpenXML) could be tedious, prone to errors and time consuming.

Here we can have a big help from CodeSmith.

I’m still using an ancient 2.6 free version , at this moment i don’t feel the need for a more recent version ; but i should try, because the 2.6 version uses the .NET 1.1 framework (!) which could be a nightmare to install on a Windows 7: on my pc after the 1.1 installation it was necessary to reinstall the 4.0 and now is all ok, but i can’t assure that on every pc the things goes well as on mine.

There is another free code generator, MyGeneration, but i never tried this software.

CodeSmith uses an approach similar to ASP.NET pages, you write a template with a server code / code behind approach near identical to ASP.NET, and the result is generated code; instead of aspx pages here we have .cst files.

Here the environment:


CodeSmith provides many useful samples (i show opened the interesting part for DatabaseSchema) , and as in the Code Snippet Manager in Visual Studio you can hook your template directory (for example CodeSmithFolder in image); the Cst files are edited with a SciLite Scintilla Editor.

Very interesting is the native functionality , with specific objects, for database connectivity.

Years ago i wrote a stored generator in Vb6 but for some strange reason the exe does not work in Windows 7, so i wrote this GenXmlStoredFields.cst :

<%@ CodeTemplate Language="C#" Src="TypedDataSet.cst.cs" Inherits="CodeSmith.BaseTemplates.SqlCodeTemplate" TargetLanguage="C#" Description="Generates code for XML stored procedure." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the typed DataSet should contain." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>

// for insert:
INSERT INTO <%= SourceTable.Name %>(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%    if (SourceTable.Columns[i].Name != "_UserMod" && SourceTable.Columns[i].Name != "_HourMod" && SourceTable.Columns[i].Name != "CheckStamp"){%>
         <% if(!(Boolean)SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value) { %>
    <%= SourceTable.Columns[i].Name %> <%if (i < SourceTable.Columns.Count - 1) {%>,<%} %>
         <% } %>
<%    } %>
<% } %>
)
SELECT
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%    if (SourceTable.Columns[i].Name != "_UserMod" && SourceTable.Columns[i].Name != "_HourMod" && SourceTable.Columns[i].Name != "CheckStamp"){%>
         <% if (!(Boolean)SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value) { %>
            <%if (SourceTable.Columns[i].Name != "_HourAdd") {%>
    <%= SourceTable.Columns[i].Name %><%if (i < SourceTable.Columns.Count - 1) {%>,<%} %>
            <%}else{%>
    GETDATE()<%if (i < SourceTable.Columns.Count - 1) {%>,<%} %>
<%            } %>
<%       } %>
<%    } %>
<% } %>
FROM OpenXML(@intHandleDoc, '/<%= SourceTable.Name %>', 1)
WITH(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%    if (SourceTable.Columns[i].Name != "_UserMod" && SourceTable.Columns[i].Name != "_HourMod" && SourceTable.Columns[i].Name != "_HourAdd" && SourceTable.Columns[i].Name != "CheckStamp"){%>
         <% if(!(Boolean)SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value) { %>
    <%= SourceTable.Columns[i].Name %>  <%= GetSqlDeclaration(SourceTable.Columns[i], SourceTable.Name) %>  <%if (i < SourceTable.Columns.Count - 1) {%>,<%} %>
         <% } %>
<%    } %>
<% } %>
)
SELECT @intErr = @@ERROR, @ID = SCOPE_IDENTITY()
EXEC sp_xml_removedocument @intHandleDoc

// for update:
UPDATE <%= SourceTable.Name %> SET
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%    if (SourceTable.Columns[i].Name != "_UserAdd" && SourceTable.Columns[i].Name != "_HourAdd" && SourceTable.Columns[i].Name != "CheckStamp"){%>
         <% if(!(Boolean)SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value) { %>
    <%= SourceTable.Columns[i].Name %> = <%= TestUpdHour(SourceTable.Columns[i].Name) %> <%if (i < SourceTable.Columns.Count - 1) {%>,<%} %>
         <% } %>
<%    } %>
<% } %>
FROM(
   SELECT * FROM OpenXML(@intHandleDoc, '/<%= SourceTable.Name %>', 1)         BEGIN
        RAISERROR('Errore in modifica Mezzo', 18,1)
        ROLLBACK TRANSACTION
        RETURN 0
        END
   WITH(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%    if (SourceTable.Columns[i].Name != "_UserAdd" && SourceTable.Columns[i].Name != "_HourMod" && SourceTable.Columns[i].Name != "_HourAdd" && SourceTable.Columns[i].Name != "CheckStamp"){%>
         <% if(!(Boolean)SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value) { %>
      <%= SourceTable.Columns[i].Name %>  <%= GetSqlDeclaration(SourceTable.Columns[i], SourceTable.Name) %>  <%if (i < SourceTable.Columns.Count - 1) {%>,<%} %>
         <% } %>
<%    } %>
<% } %>
   )
)
AS XMLUpdateSet
WHERE <%= SourceTable.Name %>.<%=GetAutoincrementPkFieldName()%>  = XMLUpdateSet.<%=GetAutoincrementPkFieldName()%>
SELECT @intErr = @@ERROR, @lngRet = @@ROWCOUNT
EXECUTE sp_xml_removedocument @intHandleDoc

<script runat="template">
    public string TestUpdHour(string colName)
    {
        if(colName == "_HourMod"){
            return "GETDATE()";
        }else{
            return "XMLUpdateSet." + colName;
        }
    }

    public string GetAutoincrementPkFieldName()
    {
        for (int i = 0; i < SourceTable.Columns.Count; i++) {
            if((Boolean)SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value) {
                return SourceTable.Columns[i].Name;
            }
        }
        return "__PKNOTFOUND__";
    }

    public string GetSqlDeclaration(ColumnSchema column, string tableName)
    {
        switch (column.NativeType)
        {
            case "bigint": return "bigint" + " '../" + tableName + "/" + column.Name + "'";
            case "binary": return "binary" + " '../" + tableName + "/" + column.Name + "'";  // Must be verified
            case "bit": return "bit" + " '../" + tableName + "/" + column.Name + "'";
            case "char":
                {
                    return "char(" + column.Size.ToString() + ") '../" + tableName + "/" + column.Name + "'";
                }
            case "datetime": return "datetime" + " '../" + tableName + "/" + column.Name + "'";
            case "decimal":
                {
                    return "decimal(" + column.Precision.ToString() + "," + column.Scale.ToString() + ") '../" + tableName + "/" + column.Name + "'";
                }
            case "float": return "float" + " '../" + tableName + "/" + column.Name + "'";
            case "image": return "image" + " '../" + tableName + "/" + column.Name + "'"; // Must be verified
            case "int": return "int" + " '../" + tableName + "/" + column.Name + "'";
            case "money": return "money" + " '../" + tableName + "/" + column.Name + "'";
            case "nchar":
                {
                    return "nchar(" + column.Size.ToString() + ") '../" + tableName + "/" + column.Name + "'";
                }
            case "ntext": return "ntext" + " '../" + tableName + "/" + column.Name + "'";
            case "numeric":
                {
                    return "decimal(" + column.Precision.ToString() + "," + column.Scale.ToString() + ") '../" + tableName + "/" + column.Name + "'";
                }
            case "nvarchar":
                {
                    return "nvarchar(" + column.Size.ToString() + ") '../" + tableName + "/" + column.Name + "'";
                }
            case "real": return "real" + " '../" + tableName + "/" + column.Name + "'";
            case "smalldatetime": return "smalldatetime" + " '../" + tableName + "/" + column.Name + "'";
            case "smallint": return "smallint" + " '../" + tableName + "/" + column.Name + "'";
            case "smallmoney": return "smallmoney" + " '../" + tableName + "/" + column.Name + "'";
            case "sql_variant": return "variant" + " '../" + tableName + "/" + column.Name + "'";
            case "sysname":
                {
                    return "nchar(" + column.Size.ToString() + ") '../" + tableName + "/" + column.Name + "'";
                }
            case "text": return "text" + " '../" + tableName + "/" + column.Name + "'";
            case "timestamp": return "timestamp" + " '../" + tableName + "/" + column.Name + "'";
            case "tinyint": return "tinyint" + " '../" + tableName + "/" + column.Name + "'";
            case "uniqueidentifier": return "uniqueidentifier" + " '../" + tableName + "/" + column.Name + "'";  // Must be verified
            case "varbinary": return "varbinary" + " '../" + tableName + "/" + column.Name + "'";  // Must be verified
            case "varchar":
                {
                    return "varchar(" + column.Size.ToString() + ") '../" + tableName + "/" + column.Name + "'";
                }
            default: return "__UNKNOWN__" + column.NativeType;
        }
    }

</script>


Please note the script runat=”template”.

The code is not the better one that could be written, but i think is a good start point.

For example the test <%if (i < SourceTable.Columns.Count – 1) {%>,<%} %> works correctly only if , in our sample, the excluded fields are not the last field in structure, otherwise there will be a not needed comma : but is just a template .

By right clicking the file you can execute the template


In code we have defined a SchemaExplorer.TableSchema parameter , so we can explore every reachable SQL Server and select a table:




And then launch by clicking Generate:


And the code is generated, ready for to be copied and pasted in SQL Management Studio.


The CodeSmith site is full of documentation, for example about the ColumnSchema used in my sample see this page

Advertisements
Categories: .NET, SQL Server
%d bloggers like this: