| Using SQL to Write VB Code for You | | by Pete Nelson | | | Skill level: Beginner | | | First posted: Monday, October 23, 2000 | |  | | | | Overview
For developers doing a lot of interaction with SQL stored procs in COM objects or VB applications, there's usually a lot of code involved to define all the parameters on each stored procedure - define the name, the data type, input or output, size and value. You can cheat and use the Refresh method on the ADO Command object, but it is generally frowned upon as it uses unneccessary execution time and resources.
I Hate Typing
Lets face it - we each spend all day writing code, and I don't like writing code. I like writing functionality, and anything I can use to help me write more functionality, the better. In today's lesson, we're using SQL Query Analyzer and some T-SQL to help generate some Visual Basic code for me.
For example, lets say I have a Customers table with some standard fields - CustomerID, FirstName, LastName, EMail, Birthdate and Notes, with CustomerID being an identity field. A stored proc to create new records in the table would look like this:
create proc sp_CreateNewCustomerRecord
@CustomerID int = null output,
@FirstName varchar(30),
@LastName varchar(30),
@Email varchar(75),
@State char(2),
@Birthdate datetime,
@Notes text
as
Set NoCount On
Insert into Customers(FirstName, LastName, EMail, State, Birthdate, Notes)
Values(@FirstName, @LastName, @EMail, @State, @Birthdate, @Notes)
Select @CustomerID = @@Identity
|
The system tables in SQL contain all the information about everything in your server - databases, tables, columns in tables & stored procs, etc. We're querying two tables: syscolumns (contains column definitions for tables, views and stored procs) and systypes (contains definitions of all the available data types). We use the built-in Object_ID function to get the object ID for the supplied object name. In this case, we pass it sp_CreateNewCustomerRecord.
When I'm developing COM objects to talk to my stored procs, I usually define a Sub or Function with the same parameters as the stored procedure. The following T-SQL code can be used to generate all the parameters on a Sub or Function:
/*** Create parameter list for a VB sub or function ***/
select
'ByVal ' + replace(rtrim(sc.Name), '@', '') + ' as ' +
case st.name
when 'bit' then '
Boolean'
when 'char' then 'String'
when 'datetime' then 'Date'
when 'decimal' then 'Double'
when 'float' then 'Double'
when 'int' then 'Long'
when 'money' then 'Double'
when 'real' then 'Double'
when 'smallint' then 'Integer'
when 'text' then 'String'
when 'tinyint' then 'Integer'
when 'uniqueidentifier' then 'String'
when '
varchar' then 'String'
when 'nvarchar' then 'String'
else st.name
end
+ ', _'
from syscolumns sc inner join systypes st on sc.xtype = st.xtype
where id = object_id('sp_CreateNewCustomerRecord') order by colorder
|
Down at the bottom that code, you'll see "object_id('sp_CreateNewCustomerRecord')". Just change the stored procedure name to give it a try on your own server. Here's the output you'll get. With a few simple edits, you can drop this right into VB!
ByVal CustomerID as Long, _
ByVal FirstName as String, _
ByVal LastName as String, _
ByVal Email as String, _
ByVal State as String, _
ByVal Birthdate as Date, _
ByVal Notes as String, _
(7 row(s) affected)
|
Now here's the code to generate the Parameters on a Command object. In this case, I'm assuming I'll have an object called "cmd" in my VB code:
/*** Create ADODB.Command parameters ***/
select
'cmd.Parameters.Append cmd.CreateParameter("' + rtrim(sc.Name) + '", ' +
case st.name
when 'bit' then 'adBoolean'
when 'char' then 'adChar'
when 'datetime' then 'adDate'
when 'decimal' then 'adDecimal'
when 'float' then 'adDecimal'
when 'int' then 'adInteger'
when 'money' then 'adCurrency'
when 'real' then 'adDecimal'
when 'smallint' then 'adSmallInt'
when 'text' then 'adVarChar'
when 'tinyint' then 'adTinyInt'
when 'uniqueidentifier' then 'adGUID'
when 'varchar' then '
adVarchar'
else st.name
end
+ ', ' +
case sc.isoutparam
when 1 then 'adParamInputOutput'
else 'adParamInput'
end
+ ', ' +
case st.name
when 'char' then IsNull(cast(sc.length as varchar(4)), '')
when 'varchar' then IsNull(cast(sc.length as varchar(4)), '')
when 'text' then '2147483647'
else ''
end
+ ', ' + replace(rtrim(sc.Name), '@', '') + ')'
from syscolumns sc inner join systypes st on sc.xtype = st.xtype
where id = object_id('sp_CreateNewCustomerRecord') order by colorder
|
And here's the output:
cmd.Parameters.Append cmd.Creat
eParameter("@CustomerID", adInteger, adParamInputOutput, , CustomerID)
cmd.Parameters.Append cmd.CreateParameter("@FirstName", adVarchar, adParamInput, 30, FirstName)
cmd.Parameters.Append cmd.CreateParameter("@LastName", adVarchar, adParamInput, 30, LastName)
cmd.Parameters.Append cmd.CreateParameter("@Email", adVarchar, adParamInput, 75, Email)
cmd.Parameters.Append cmd.CreateParameter("@State", adChar, adParamInput, 2, State)
cmd.Parameters.Append cmd.CreateParameter("@Birthdate", adDate, adParamInput, , Birthdate)
cmd.Parameters.Append cmd.CreateParameter("@Notes", adVarChar, adParamInput, 2147483647, Notes)
(7 row(s) affected)
|
Oooh! More code we can copy and paste write into VB! If you use this and the other method to generate the parameters for a Sub or Function, you'll see that we're using the same variable names (last parameter in the cmd.CreateParameter statement).
There you have it, but don't limit yourself to just this. There's lots of ways to get your development tools to write code for you. With a little more programming in VB and SQL, you could write a tool to generate all the stored procs and VB classes you need to interface with a SQL database. Experiment!
|
| | | | |
This document can be found in these Encyclopedia chapters:
º ActiveX Data Objects (ADO)
º Visual Basic
º Microsoft SQL Server
|
|
|
|
Got something to add to this article?
Create a new discussion |
|
|
|
View Article Statistics
Authors...
Edit this article
View Preview Version
|
|
Register Domains
