All of the web applications i've been building lately have their data stored in SQL Server. We updated and pull our data into the application via stored procedures. Many times, the process of creating the T-SQL to create the procedure and writing the VBScript code to access it can be tedious and repetitive. Therefore i took it upon my self to come up with a solution that would automatically generate the T-SQL and VBScript thereby saving me a lot of time. My cSQLHelper class is the result.
Most of the magic happens when we query the special INFORMATION_SCHEMA views in SQL server. These views provide valuable information such as the columns and their data types/sizes for a given table. They can also tell you about the parameters of a stored procedure. They contain much of the same information as the system tables but are generally considered safer to query. Here is a query that looks at INFORMATION_SCHEMA.COLUMNS to get all the columns in a given table:
--Get Listing of table fields
SELECT c.column_name as fld_name, c.ordinal_position as fld_order, c.data_type as data_type, c.CHARACTER_MAXIMUM_LENGTH as data_length, c.NUMERIC_PRECISION as numeric_precision, c.NUMERIC_SCALE as numeric_scale, CASE is_nullable WHEN 'YES' THEN 1 ELSE 0 END is_nullable, COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') as is_identity,
(SELECT Count(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.TABLE_CATALOG = kcu.TABLE_CATALOG
AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
AND tc.TABLE_NAME = kcu.TABLE_NAME
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND (tc.TABLE_NAME = C.table_name)
AND (kcu.COLUMN_NAME = C.column_name)
) as is_key
FROM information_schema.columns c
WHERE table_name=?
The SQL got a bit bulky there, but it would have been straight forward had it not been for the subquery to see if each column is part of the key. A similar query is in place to get the parameters for a stored procedure only in that case we're referencing INFORMATION_SCHEMA.PARAMETERS. When i get the results from this query, i load it into an XML object. Why XML you ask? Because this way i can format it for a variety of different purposes via an XSL transformation. Here is what you can expect to be loaded into the class's XML object.
'list of databases
<databases>
<database>DatabaseName</database>
</databases>
'list of tables
<tables>
<table>TableName</table>
</tables>
'list of procedures
<procedures>
<procedure>ProcedureName</procedure>
</procedures>
'table definition
<table name="">
<field name="" data_type="" data_type_class="" data_type_ado="" data_length=""
numeric_precision="" numeric_scale="" is_nullable="" is_key="" is_identity=""/>
</table>
'procedure definition
<procedure name="">
<param name="" data_type="" data_type_class="" data_type_ado="" data_length=""
numeric_precision="" numeric_scale="" is_output="" is_result="" />
</procedure>
You can then craft XSL transformations to turn that information into something you can use. I've supplied a few sample XSL sheets, but the nice thing is that you can easily customize them if you have your own coding standards you need everything to follow. There are many opportunities for improvement if you play with your own sheets. The sample sheets them selves are not pretty, but that's mostly because i played with new-lines in order to get the best looking output.
Here is some sample code that will spit out VBScript code with a properly initialized ADO.Connection object to access the "usp_addItem" stored procedure from the "db_orders" database.
Dim oSQLHelper, sVBCode
Set oSQLHelper=new cSQLHelper
oSQLHelper.ConnectionString="DRIVER={SQL SERVER}; Server=sql1; UID=sa; PWD=password"
oSQLHelper.Connect
If oSQLHelper.errorCode=0 then
oSQLHelper.LoadProcedureDefinition "db_orders", "usp_addItem"
If oSQLHelper.errorCode=0 then
sVBCode = oSQLHelper.ApplyStyleSheet(Server.MapPath("xsl_code_vbscript.xsl"))
If oSQLHelper.errorCode=0 then
'print code in text area for easy copy/paste
Response.write "<textarea rows=""10"" cols=""75"">" & sVBCode & "</textarea>"
Else
Response.write oSQLHelper.lastErrorMessage
End If
Else
Response.write oSQLHelper.lastErrorMessage
End If
else
Response.write oSQLHelper.lastErrorMessage
end if
oSQLHelper.Disconnect
Set oSQLHelper=Nothing
Or maybe you want to stream an XML document with a list of all the table in a database to your browser. (I use this when using a client side XMLHttpRequest object in JavaScript.)
Dim oSQLHelper
Set oSQLHelper=new cSQLHelper
oSQLHelper.ConnectionString="DRIVER={SQL SERVER}; Server=SQL1; UID=sa; PWD=password"
oSQLHelper.Connect
oSQLHelper.LoadTableList "db_orders"
'now stream to browser
Response.ContentType="text/xml"
oSQLHelper.getXML.save Response
oSQLHelper.Disconnect
Set oSQLHelper=Nothing