here is a little script to quiz an SQL Server Db, get a list of the user
created stored procedures and then generate some VB ASPX code to call
it.
May or may not be or use to people.
Pat
<%@ import namespace=3D "System.Data.SqlClient" %>
<%@ Page Language=3D"vb" AutoEventWireup=3D"false"
EnableSessionState=3D"false" EnableViewState=3D"false"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>spCodeBuilder</title>
<meta name=3D"GENERATOR" content=3D"Microsoft Visual Studio.NET 7.0">
<meta name=3D"CODE_LANGUAGE" content=3D"Visual Basic 7.0">
<meta name=3D"vs_defaultClientScript" content=3D"JavaScript">
<meta name=3D"vs_targetSchema"
content=3D"http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING=3D"GridLayout">
<form id=3D"Form1" method=3D"get" runat=3D"server">
<div id=3D"Title" runat=3D"server"> </div>
<p></p>
<div id=3D"DropDown" runat=3D"server"> </div>
<p></p>
<div id=3D"Table" runat=3D"server"> </div>
<p></p>
<div id=3D"Code" runat=3D"server"> </div>
</form>
</body>
</HTML>
<script language=3D"vb" runat=3D"server">
sub pat(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles MyBase.Load
'Change these four settings to allow you to access whichever db
you want
Dim datasourceIP As String =3D "192.168.100.88"
Dim defaultDB As String =3D "st_live"
Dim UserID As String =3D "sa"
Dim Password As String =3D "sa"
'Other Vars
Dim ConnString As String =3D "Data Source=3D" & datasourceIP &
";Initial Catalog=3D" & defaultDB & ";Pooling=3DFalse;User ID=3D" &
UserID & ";Password=3D" & Password
Dim strTitle As String =3D ""
Dim strProcedure As String =3D ""
Dim param As SqlParameter
Dim cmdSPList As SqlCommand
Dim dr As SqlDataReader
dim strDropDownCode as string =3D ""
dim strTableCode as string =3D ""
dim strAspxCode as string =3D ""
Dim strSQL As String =3D "select name from sysobjects where type
=3D 'p' and name not like 'dt%' order by name"
dim strAspX as string =3D ""
dim strcodeHeader as string =3D ""
dim strcodeFooter as string =3D ""
=09
'@@@ Create Title
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'See which mode the page has been called in
If Len(Trim(Request.querystring("sp"))) < 1 Then
'Select Stored procedure mode
strTitle =3D "<h2>Select Stored Procedure from drop down
box</h2>"
strProcedure =3D ""
Else
'Display Selected Stored Procedure Details
strProcedure =3D Trim(Request.QueryString("sp"))
strTitle =3D "<h2>Parameter Details for <EM>'" &
strProcedure & "'</EM> Stored Procedure</h2>"
End If
Title.innerhtml=3D "<b> Database =3D " & defaultdb & "</b>" &
strTitle
Dim conn As New SqlConnection(ConnString)
conn.Open()
'@@@ Create drop down list
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
cmdSPList =3D New SqlCommand(strSQL, conn)
dr =3D cmdSPList.ExecuteReader()
If dr.Read Then
strDropDownCode =3D "<SELECT id=3D'sp' style=3D'WIDTH:
500px' name=3D'sp'>"
strDropDownCode =3D strDropDownCode & "<OPTION value=3D" &
dr("name") & " selected>" & dr("name") & "</OPTION>"
While dr.Read
strDropDownCode =3D strDropDownCode &"<OPTION value=3D"
& dr("name") & ">" & dr("name") & "</OPTION>"
End While
strDropDownCode =3D strDropDownCode &"</SELECT>"
strDropDownCode =3D strDropDownCode & "<input
type=3D'submit' name=3D'Submit' value=3D'Get Details'>"
strDropDownCode =3D strDropDownCode & "<input
type=3D'hidden' name=3D'DB' value=3D'" & defaultDB & "'>"
DropDown.innerhtml =3D strDropDownCode
dr.Close()
End If
dr =3D Nothing
If strProcedure <> "" Then
'@@@ Write out Parameters Table
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Dim cmd As New SqlCommand(strProcedure, conn)
cmd.CommandType =3D cmd.CommandType.StoredProcedure
SqlCommandBuilder.DeriveParameters(cmd)
strTableCode =3D"<table align=3Dcenter border=3D1>
<TR><TD><B>Name</B></TD><TD><B>Type</B></TD><TD><B>Direction</B
></TD><TD>
<B>size</B></TD>"
For Each param In cmd.Parameters
strTableCode =3D strTableCode & "<TR><TD>" &
param.ParameterName & "</TD>"
strTableCode =3D strTableCode & "<TD>" &
GetDataType(param.SqlDbType) & "</TD>"
strTableCode =3D strTableCode & "<TD>" &
GetDirection(param.Direction) & "</TD>"
strTableCode =3D strTableCode & "<TD>" & param.Size &
"</TD></TR>"
strAspX =3D strAspx & "Param =3D cmd.Parameters.Add(" & chr(34) &
param.ParameterName & chr(34) &", SQLDBType." &
GetDataType(param.SqlDbType) & ", " & param.Size & ") <BR>"
strAspX =3D strAspx & "Param.Direction =3D ParameterDirection." &
GetDirection(param.Direction) & "<br>"
strAspX =3D strAspx & "Param.Value =3D<b><i>" &
right(param.ParameterName,len(param.ParameterName) -1) &
"</b></i><br><br>"
Next
strTableCode =3D strTableCode & "</table>"
table.innerhtml =3D strTableCode
cmd.Dispose()
cmd =3D Nothing
conn.Close()
conn =3D Nothing
'@@@ Display AspX Code
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
strCodeHeader =3D "Dim conn As New
system.data.sqlclient.SqlConnection(<b><i>ConnString</i></b>)<BR>"
strCodeHeader =3D strCodeHeader & "Dim cmd As New
SqlCommand(" & chr(34) & strProcedure & chr(34) & ", conn) <br>"
strCodeHeader =3D strCodeHeader & "cmd.CommandType =3D
cmd.CommandType.StoredProcedure <br>"
strCodeHeader =3D strCodeHeader & "Dim param As
system.data.sqlclient.SqlParameter <br><BR>"
strCodeFooter =3D strCodeFooter & "Try <br>"
strCodeFooter =3D strCodeFooter &
" <b>'open the connection and execute the command
<br></b>"
strCodeFooter =3D strCodeFooter &
" conn.Open() <br>"=09
strCodeFooter =3D strCodeFooter &
" cmd.ExecuteNonQuery() <br>"
strCodeFooter =3D strCodeFooter &
" conn.close <br>"
strCodeFooter =3D strCodeFooter &
" param=3Dnothing <br>"
strCodeFooter =3D strCodeFooter &
" cmd=3Dnothing <br>"
strCodeFooter =3D strCodeFooter &
" conn=3Dnothing <br>"
strCodeFooter =3D strCodefooter & "Catch objError As
Exception <br>"
strCodeFooter =3D strCodefooter & " <b>'Do
some error trapping</b><br>"
strCodeFooter =3D strCodeFooter &
" param=3Dnothing <br>"
strCodeFooter =3D strCodeFooter &
" cmd=3Dnothing <br>"
strCodeFooter =3D strCodeFooter &
" conn=3Dnothing <br>"
strCodeFooter =3D strCodefooter & "End Try <br>"
=09
code.innerHTML =3D strCodeHeader & strAspx & strCodefooter
End If
End Sub
shared Function GetDirection(ByVal value As Integer) As String
Select Case value
Case 1
Return ("Input")
Case 2
Return ("Output")
Case 3
Return ("InputOutput")
Case 6
Return ("ReturnValue")
Case Else
Return (value.ToString)
End Select
End Function
shared Function GetDataType(ByVal value As Integer) As String
Select Case value
Case 0
Return ("BigInt")
Case 1
Return ("Binary")
Case 2
Return ("Bit")
Case 3
Return ("Char")
Case 4
Return ("DateTime")
Case 5
Return ("Decimal")
Case 6
Return ("Float")
Case 7
Return ("Image")
Case 8
Return ("Int")
Case 9
Return ("Money")
Case 10
Return ("NChar")
Case 11
Return ("NText")
Case 12
Return ("NVarChar")
Case 13
Return ("Real")
Case 14
Return ("UniqueIdentifier")
Case 15
Return ("SmallDateTime")
Case 16
Return ("SmallInt")
Case 17
Return ("SmallMoney")
Case 18
Return ("Text")
Case 19
Return ("Timestamp")
Case 20
Return ("TinyInt")
Case 21
Return ("VarBinary")
Case 22
Return ("VarChar")
Case 23
Return ("Variant")
Case Else
Return (value.ToString)
End Select
End Function
</script>
View Cart
