Calling Stored Procedures with Lots of Parameters

Question

0.00/5 (No votes)

See more:

Hey guys I have this piece of code for running a Stored Procedure, and I was wondering if there is a way of cutting code like:

uPar = .CreateParameter("@PropertyID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput)
      .Parameters.Append(uPar)
      .Parameters("@PropertyID").Value = Val(lblPropertyIDValue.Text)

As I have a lot of stored procedures in my system this takes a lot of time, my boss seems to think there could be a way to possible cut this down to one line of code and make it a lot easier to read and write, though I'm not sure what way to go about this.

Hope someone can help.

Thanks

Public Function SaveProperty() As Boolean
    '** Save Current Personal Data Record
 
    ' Error Checking
    On Error GoTo Err_SaveProperty
 
    ' Dimension Local Variables
    Dim uRecSnap As ADODB.Recordset
    Dim uPar As ADODB.Parameter
 
    ' Check For Open Connection
    If uDBase Is Nothing Then
        OpenConnection()
        bConnection = True
    End If
 
    ' Run Stored Procedure - Save Property Record
    uCommand = New ADODB.Command
    With uCommand
        .ActiveConnection = uDBase
        .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
        .CommandTimeout = 0
        uPar = .CreateParameter("@PropertyID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput)
        .Parameters.Append(uPar)
        .Parameters("@PropertyID").Value = Val(lblPropertyIDValue.Text)
        uPar = .CreateParameter("@PropertyManager", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 60)
        .Parameters.Append(uPar)
        .Parameters("@PropertyManager").Value = cmbPropertyManager.Text
        uPar = .CreateParameter("@AddressLine1", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@AddressLine1").Value = txtAddress1.Text
        uPar = .CreateParameter("@AddressLine2", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@AddressLine2").Value = txtAddress2.Text
        uPar = .CreateParameter("@AddressLine3", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 20)
        .Parameters.Append(uPar)
        .Parameters("@AddressLine3").Value = txtAddress2.Text
        uPar = .CreateParameter("@Town", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@Town").Value = txtTown.Text
        uPar = .CreateParameter("@PostCode", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@PostCode").Value = txtPostCode.Text
        uPar = .CreateParameter("@Availabilty", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput)
        .Parameters.Append(uPar)
        .Parameters("@Availabilty").Value = chkAvailable.Checked
        uPar = .CreateParameter("@Available", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@Available").Value = dtpAvailable.Text
        uPar = .CreateParameter("@Factored", ADODB.DataTypeEnum.adTinyInt, ADODB.ParameterDirectionEnum.adParamInput)
        .Parameters.Append(uPar)
        .Parameters("@Factored").Value = -chkFactored.Checked
        uPar = .CreateParameter("@FactorsName", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 20)
        .Parameters.Append(uPar)
        .Parameters("@FactorsName").Value = txtFactorName.Text
        uPar = .CreateParameter("@FactorsEmail", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@FactorsEmail").Value = txtFactorsEmail.Text
        uPar = .CreateParameter("@PropertyBuilt", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@PropertyBuilt").Value = dtpPropertyBuilt.Text
        uPar = .CreateParameter("@PropertyValue", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput)
        .Parameters.Append(uPar)
        .Parameters("@PropertyValue").Value = txtPropertyValue.Text
        uPar = .CreateParameter("@MimimumFee", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@MimimumFee").Value = txtMinimumFee.Text
        uPar = .CreateParameter("@Commission", ADODB.DataTypeEnum.adTinyInt, ADODB.ParameterDirectionEnum.adParamInput)
        .Parameters.Append(uPar)
        .Parameters("@Commission").Value = -txtCommision.Text
        uPar = .CreateParameter("@CostSuthorisationAmount", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 20)
        .Parameters.Append(uPar)
        .Parameters("@CostSuthorisationAmount").Value = txtCostAuthorisationAmount.Text
        uPar = .CreateParameter("@Vacant", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@Vacant").Value = chkVacant.Checked
        uPar = .CreateParameter("@VacantDate", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30)
        .Parameters.Append(uPar)
        .Parameters("@VacantDate").Value = dtpVacant.Text
        uPar = .CreateParameter("@StartingRent", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput)
        .Parameters.Append(uPar)
        .Parameters("@StartingRent").Value = txtStartingRent.Text
 
        .CommandText = "PropertyMaster_SaveRecord"
 
        .Execute()
    End With
 
    ' Close Connection
    uRecSnap = Nothing
    uCommand = Nothing
    If bConnection Then CloseConnection()
    SaveProperty = True
 
 Err_SaveProperty:
    If Err.Number <> 0 Then
        sErrDescription = Err.Description
        WriteAuditLogRecord("clsProperty", "SaveProperty", "Error", sErrDescription)
        SaveProperty = False
    End If
 
End Function

Posted 23-Jan-15 2:24am

Rich1e1990

Add a Solution

Comments

ZurdoDev 23-Jan-15 8:26am

We just put #region around the parameters section so it can be collapsed. One way or another you have to add all the parameters. There's no way around that.

ZurdoDev 23-Jan-15 8:27am

Also, do not repost. I just saw your other question.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2015-01-23T02:44:00

One solution could be to store all these parameter information in some form of XML. Then at run time you can load this XML in memory and then have a function which creates parameter by reading this xml. This will definitely reduce your code base significantly but it will increase your memory overhead