.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).