Solved

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

Posted on 2014-10-24
2
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 75

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 34

Author Closing Comment

by:Mike Eghtebas
ID: 40402752
Thank you.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

759 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