Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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

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
Mike Eghtebas
Asked:
Mike Eghtebas
1 Solution
 
käµfm³d 👽Commented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now