Solved

revise a class to handle stored proc execution... vb.net

Posted on 2014-10-24
2
390 Views
Last Modified: 2014-10-24
The SqlControl class below is used to fill a combo-box, as an example:
  ' POPULATE cboOrder
    Private Sub GetcboOrder()

        SQL.ExecQuery("SELECT SoftwareID, Title From tblSoftware")

        ' CLEAR THE COMBO-BOX
        cboOrder.Items.Clear()

        ' POPULATE COMBO-BOX WITH THE ONLY TABLE IN THE DATASET
        cboOrder.DataSource = SQL.ds.Tables(0)

        ' IDENTIFY DISPLAYMEMBER AND VALUEMEMBER
        cboOrder.DisplayMember = "Title"
        cboOrder.ValueMember = "SoftwareID"

        ' SET THE COMBO-BOX DEFAULT SETTING
        cboOrder.SelectedIndex = -1

        If SQL.exception <> "" Then

            ' REPORT ERROR
            MessageBox.Show(SQL.exception)

        End If

    End Sub

Open in new window

Because CommandType Text is the default, this code works fine.

Question: How can I add CommandType to both the code above and to the class below so I can use it for stored proce as well as slq statement I am using now.

In the class, the default CommandType could be initialized to Text. This way, there will be no need to set it in code when running a sql script.


    ' EXECUTE THE QUERY
    Public Sub ExecQuery(query As String)

        RecordCount = 0

        Try
            cnn.Open()

            ' CREATE QUERY
            cmd = New SqlCommand(query, cnn)

            ' LOAD PARAMETERS TO SQLCOMMAND
            params.ForEach(Sub(x) cmd.Parameters.Add(x))

            ' CLEAR PARAMETERS
            params.Clear()

            ' EXECUTE COMMAND AND FILL DATASET
            ds = New DataSet
            da = New SqlDataAdapter(cmd)
            RecordCount = da.Fill(ds)

            cnn.Close()
        Catch ex As Exception
            ' CAPTURE ERROR
            exception = ex.Message
        Finally

        End Try

        ' MAKE SURE THE CONNECTION IS CLOSED
        If cnn.State = ConnectionState.Open Then cnn.Close()

    End Sub

Open in new window

0
Comment
Question by:Mike Eghtebas
2 Comments
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 40402736
Because CpmmandType Text is the default, this code works fine.
You should never rely on the default values of a class that you yourself did not write. A default value is an implementation detail, and implementation details change. Relying on today's default value means that if it changes with an update to the Framework, you'll have to remember all of the places that you used relied on that value...and then go change them. You should be explicit in what you want to use from a library that is not yours (which I'm sure you'll agree:  SqlCommand was not written by you!).

I would add a second parameter and let the caller tell you what they are sending.

e.g.

' EXECUTE THE QUERY
Public Sub ExecQuery(query As String, CommandType type)

    RecordCount = 0

    Try
        cnn.Open()

        ' CREATE QUERY
        cmd = New SqlCommand(query, cnn)

        cmd.CommandType = type;
        
...

Open in new window

0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 40402752
Thank you.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now