Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Use XSLT to Write a Simple Stored Procedure Generator-3 : Page 3


advertisement

.NET Code

All you need the .NET code to do is go into the database, get a list of tables, and then get a list of columns for every table and apply an XSLT file to generate those SP boilerplates. The trick is to get the data from SQL Server in XML. One of the ways to do that is to use the MS SQLXML component, which builds XML on the client side and returns an XmlReader object quickly and efficiently.

First of all, get a list of the tables in question with the following function:

Private Shared Function GetTables(ByVal xCS As String) As XmlReader Dim strCom As String = "select * from sys.objects where type='U' and is_ms_shipped=0 for xml raw" Dim sqlCom As New SqlXmlCommand(xCS) sqlCom.CommandText = strCom : sqlCom.ClientSideXml = True sqlCom.CommandType = SqlXmlCommandType.Sql : sqlCom.RootTag = "tables" Return sqlCom.ExecuteXmlReader End Function



The function will return XML data that looks like this:

<tables> <row name="tDog" object_id="117575457" schema_id="1"parent_object_id="0" type="U " type_desc="USER_TABLE" _
create_date="2005-12-04T21:11:48.530" modify_date="2005-12-04T21:18:09.403" is_ms_shipped="0" _
is_published="0" is_schema_published="0"/> <row name="tOwner" object_id="165575628" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" _
create_date="2005-12-04T21:14:38.810" modify_date="2005-12-04T21:17:49.403" is_ms_shipped="0" _
is_published="0" is_schema_published="0"/> <row name="tAnimalOfficer" object_id="277576027" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" _
create_date="2005-12-04T21:18:09.310" modify_date="2005-12-04T21:18:09.403" is_ms_shipped="0" _
is_published="0" is_schema_published="0"/> <row name="sysdiagrams" object_id="2089058478" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" _
create_date="2005-12-04T21:06:54.200" modify_date="2005-12-04T21:06:54.207" is_ms_shipped="0" _
is_published="0" is_schema_published="0"/> </tables>

You produce a list of columns in a similar way:

Private Shared Function GetColumns(ByVal xCS As String, ByRef TableName As String) As XmlReader Dim strCom As String = "select * from information_schema.columns where table_name='" + TableName + "' order by ordinal_position for xml raw" Dim sqlCom As New SqlXmlCommand(xCS) sqlCom.CommandText = strCom : sqlCom.ClientSideXml = True sqlCom.CommandType = SqlXmlCommandType.Sql : sqlCom.RootTag = "columns" Return sqlCom.ExecuteXmlReader End Function

The function returns the following:

<columns> <row TABLE_CATALOG="DevX" TABLE_SCHEMA="dbo" TABLE_NAME="tAnimalOfficer" COLUMN_NAME="OfficerID" ORDINAL_POSITION="1" _
IS_NULLABLE="NO" DATA_TYPE="nvarchar" CHARACTER_MAXIMUM_LENGTH="255" CHARACTER_OCTET_LENGTH="510" CHARACTER_SET_NAME="UNICODE" _
COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS"/> <row TABLE_CATALOG="DevX" TABLE_SCHEMA="dbo" TABLE_NAME="tAnimalOfficer" COLUMN_NAME="Name" ORDINAL_POSITION="2" _
IS_NULLABLE="YES" DATA_TYPE="nvarchar" CHARACTER_MAXIMUM_LENGTH="255" CHARACTER_OCTET_LENGTH="510" CHARACTER_SET_NAME="UNICODE" _
COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS"/> <row TABLE_CATALOG="DevX" TABLE_SCHEMA="dbo" TABLE_NAME="tAnimalOfficer" COLUMN_NAME="StationName" ORDINAL_POSITION="3" _
IS_NULLABLE="YES" DATA_TYPE="nvarchar" CHARACTER_MAXIMUM_LENGTH="255" CHARACTER_OCTET_LENGTH="510" CHARACTER_SET_NAME="UNICODE" _
COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS"/> <row TABLE_CATALOG="DevX" TABLE_SCHEMA="dbo" TABLE_NAME="tAnimalOfficer" COLUMN_NAME="TerritorialAuthorityID" ORDINAL_POSITION="4" _
IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0"/> </columns>

Glue these two functions together with this code:

Public Shared Function GenratedSPs(ByRef XsltFileName As String, ByRef DbConnectionString As String) As String Dim oStringWriter As New IO.StringWriter oStringWriter.NewLine = vbCrLf Dim xslDoc As New Xml.Xsl.XslTransform Dim xslResolver As New Xml.XmlUrlResolver xslDoc.Load(XsltFileName) Dim xTables As Xml.XmlReader = GetTables(DbConnectionString) Do While xTables.Read If xTables.LocalName = "row" Then Dim sTableName As String = xTables.GetAttribute("name") Dim xColumns As Xml.XmlReader = GetColumns(DbConnectionString, sTableName) ' Dim xC As New Xml.XPath.XPathDocument(xColumns) Dim stOut As New IO.StringWriter xslDoc.Transform(xC, Nothing, stOut, xslResolver) oStringWriter.Write(stOut.ToString) oStringWriter.WriteLine() End If Loop oStringWriter.Flush() Return oStringWriter.ToString End Function

The string returned by this last function contains the text for all the SPs it has generated. You will need to paste it in your MS SQL Management Studio Query Designer, check it, modify it where needed, and then run it. This is still a bit of manual work, but just think how much time it would take to type all those parameters and declarations from scratch.

The sample XSLT file included with the full source code download for this article is designed to deal with the XML formats shown above and produce simple SP boilerplates as described in the SP Design Model section.

Customization

The .NET code is unlikely to need any customization—it does what it does. The actual SP text is in the XSLT. So, if you prefer a different SP boilerplate, you need to look into the XSLT file.

Limitations

Keep in mind that this is only a 10-Minute Solution (even if I did spend half a day getting it all going), so has its limitations:
  • Not all SQL data types are used by the XSLT.
  • The very first field in the table is considered to be the Primary Key.
  • No composite keys are considered.
  • No special constraints are considered (e.g., usually you cannot do an insert into an identity column).



Max Voskob is a freelance solution architect specializing in areas of Web-applications, semantic integration, AI and XML standards with 15 years in IT and telecommunications. Max lives in New Zealand and works with the rest of the world from his home office. His customers range from large and small IT companies to NZ and Australian government agencies.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap