Build VB.NET stored procedure calls
auto-magically.
Maintain database code consitancy with a development team of any size
Language: VB.NET, SQL Server
Level: intermediate
by Harvey
Every successful project I have ever worked on has had one common denominator: Database wrappers. I've found it doesn't matter if you have a two-person development team or a team of 30, creating a database wrapper ensures consistent database calls and future compatibility.
While the argument can be made that ADO.NET should be written the same way by every developer on the team, this rarely happens. Additionally, I've worked on a few projects where the client decides to change the backend database -- no, I don't mean changing a table or stored procedure, I'm talking about changing from SQL Server to Oracle. The argument also could be made that ADO.NET will allow a developer to connect to SQL Server or Oracle, but in practice I've never been able to seamlessly change the connection string and just have my application work.
A database wrapper removes the application developer one level from ADO.NET and, because you defined the database wrapper, everyone's code can be forced to look the same.
I'm not going to explain how to create a database wrapper in this article; instead, I'm going to focus on the task of automating the building of stored procedure code. This will provide two benefits: First, we can guarantee all code will conform to the standards you've put in place; second, those lazy developers won't have to write line after line of database code. Yes, developers are lazy! We have to type for a living and we're always (rightfully so) looking for a shortcut.
Before we get started, let's define what our utility will do:- Create a wizard to generate a database connection string
- Pull in all stored procedures from our database
- Generate VB.NET code to call our stored procedure
Step 1 - Building the connection string wizard
Microsoft has done this for us, so let's make use of the .NET version of Microsoft Active Service Connection (MSDADC). After looking around for a minute or two, it will become apparent MS never ported the MSDADC to .NET. That means we have to use the COM version. In a normal .NET application, I would find this unacceptable, but since this is just an in-house developer utility, I'm okay with the idea of using a COM component.
We need to add a reference to MSDASC. To do that, right click on "References" in the Solution Explorer and click "add reference" from the popup menu. Next, click on the COM tab in the Add Reference dialog and double click "Microsoft OLE DB Service Component 1.0 Type Library". While we're in the Add Reference dialog, you might as well add ADO 2.7, as well -- double click "Microsoft ActiveX Data Object 2.7", then click the OK button to add them to our .NET project.
Why would we need ADO 2.7 COM component when .NET has ADO.NET? Because MSDADC can't use the ADO.NET object natively, so we'll need to use the ADO2.7 object just for the wizard.
Building the Wizard Code:
Dim MSDAC As New MSDASC.DataLinks()
Dim adoconn As New ADODB.Connection()
Try
adoconn = MSDAC.PromptNew()
If Not adoconn Is Nothing Then txtDBConn.Text = adoconn.ConnectionString
Finally
'this is COM, make sure you clean up your mess.
Adoconn = nothing
MSDAC = Nothing
End Try
What we're doing here is creating our MSDAC object as our interface into the familiar Data links wizard. Next we create our ADO2.7 Connection, which should look very familiar to all VB6 developers. Now is where the magic happens. Calling MSDAC.PromptNew tells MSDADC to display the database connection wizard dialog and the output from the wizard will be stored in our adoConn (ADO2.7 Connection) object.
Finally, we check that adoConn is valid and populate a textbox (txtDBConn) with our generated database connection string.
Step 2 - Getting a list of Stored Procedures
Fortunately, our most difficult task is the wizard; everything else will be a breeze.
The first thing we need to do is create an OleDBConnection:
Dim cn As New OleDbConnection()
Next, we can set our connection string to the connection string we generated from the wizard code above:
Cn.ConnectionString = txtDBConn.text
Cn.Open
While the utility I built for this example works with SQL Server, it is important to point out we created an OleDBConnection instead of a SQLServer specific .NET connection. Only OleDBConnections will return schema information, which means this example should work with Oracle -- and other databases, as well -- with little or no modification.
Our stored procedure list will be returned to us in a DataTable object using the cn.GetOleDbSchemaTable method, which makes it convenient to assign the datatable to a ComboBox, which I've called cbStoredProcedures.
Here's the full source code for returning all stored procedures from a Database and assigning the returned DataTable row object to a ComboBox:
Dim cn As New OleDbConnection()
cn.ConnectionString = txtDBConn.Text
cn.Open()
'This should be wrapped in a Try..Catch block, removed to simply example
Dim tb As DataTable
tb = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, Nothing)
cbStoredProcedures.DisplayMember = "PROCEDURE_NAME"
cbStoredProcedures.DataSource = tb
cn.Close()
Step 3- Building our store procedure VB.NET code
Building our code is nothing more then concatenating a bunch of strings of data and placing them into a textbox, which later will be copied and pasted in your VB.NET source code.
First we'll need to build a new OleDBConnection to get parameter datatypes, sizes and parameter names. We'll also need a DataRowView object, which will contain the stored procedure name selected by the user in the ComboBox. We'll also need a DataTable and DataRow object and, since we're going to be doing a lot of concatenation, we will be using a string builder object for efficiency.
Here's the code to make our connection to the database and retrieve the user-selected stored procedure:
Dim cn As New OleDbConnection()
Dim SPClean As String
Dim drv As DataRowView
cn.ConnectionString = txtDBConn.Text
cn.Open()
drv = cbStoredProcedures.SelectedItem
SPClean = drv.Row("PROCEDURE_NAME")
Dim tb As DataTable
tb = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedure_Parameters, _
New Object() {Nothing, Nothing, SPClean, Nothing})
The schema will return the stored procedure looking like spw_GetUser;1. We need to strip the ;1 off the end and SPClean will contain our clean procedure name.
'Build our DataRow and StringBuilder objects
Dim row As Data.DataRow
Dim sSP As New System.Text.StringBuilder()
'Create our stored procedure heading information
sSP.Append("Dim DB as DBWrapper = New DBWrapper([ENTER CONNECT STRING])" & _
ControlChars.CrLf & ControlChars.CrLf)
SP.Append("DB.CommandType = CommandType.StoredProcedure" & ControlChars.CrLf)
SP.Append("DB.CommandText = """ & SPClean & """" & ControlChars.CrLf)
SP.Append("With DB.Parameters" & ControlChars.CrLf)
And we're ready to start putting together our parameters. The Row object will contain all the fields to our data and we'll just need the field names to get what we want. Here's the code to generate the concatenated parameter string:
For i = 0 To tb.Rows.Count - 1
row = tb.Rows(i)
If row("PARAMETER_TYPE") <> 4 Then
sSP.Append(vbTab & ".Add(""" & row("PARAMETER_NAME") & """")
sSP.Append(", SqlDBType." & row("TYPE_NAME"))
If IsDBNull(row("CHARACTER_MAXIMUM_LENGTH")) Then
sSP.Append(", 0")
Else
sSP.Append(", " & row("CHARACTER_MAXIMUM_LENGTH"))
End If
If row("PARAMETER_TYPE") = 1 Then
sSP.Append(", ParameterDirection.Input")
Else
sSP.Append(", ParameterDirection.Output")
End If
sSP.Append(", [VALUE])" & vbCrLf)
End If
Next
'We can finish up our string concatenation with our footer information
SP.Append("End With" & vbCrLf & vbCrLf)
SP.Append("DB.Execute([ENTER DB TYPE])")
xtSPList.Text = sSP.ToString
Screen shot of our utility at work (now this is one heck of a time saver).
Creating a utility like this can save hundreds of manhours in typing and debugging. It also guarantees your developers are following your development guidelines and standards.
Hopefully, I've not only given you the basis for a useful utility, but some excellent examples of how to interface with any OLE DB database and extract underlying schema information.
|
