Solved

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Optimize the query 5 43
Passing data between Forms 3 20
Where is this file? 3 26
Install IIS7.5 on Windows Sever 2012 R2 4 24
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 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