Solved

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

Posted on 2014-10-24
2
419 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

630 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