Solved

improve user designed class, combine methods.. vb.net

Posted on 2014-10-24
4
536 Views
Last Modified: 2014-10-26
The following btnSave_Clcik uses SqlControl class to:

1. Insert a new record using "spAddCustomer".
2. Saving existing customer data edits using "Update tblCustomer ...."

In the SqlControl class, there are similar methods to handle item 1 and 2 described above.

Question: Could you possibly combine the following routines in SqlControl ?

1. Combine the methods starting at line 56 "ExecQuery()" and line 92 "ExecSP()"
2. Combine the methods starting at line 131 "AddPram()" and line 140 "AddSpPram()"

It will be nice to have defaule CommandType.Text in the class so the user doesn't have to specify command type using a query.
Code In Form Class:
' USE SqlControl CLASS IN THIS FORM FOR DATA HANDLING
Private SQL As New SqlControl 
'--------------------------
' SAVE EXSITING CUSTOMER EDITS OR NEW CUSTOMER (WITH OR WITHOUT AN ORDER)
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

   ' WHEN NEW CUSTOMER, bolSaveNew IS TRUE
   If bolSaveNew  Then  
	
      Add_SpParameters()
      Insert_SpParamValues()
      SQL.ExecSP("spAddCustomer", CommandType.StoredProcedure)

   Else
      Add_Parameters()
      SQL.ExecSP("Update ....")
   End If

 End Sub
'============================

Public Class SqlControl

    ' DECLRATIONS
#Region "DECLRATIONS"

    ' CONNECTION STRING
    Dim xmlReader As New XmlTextReader("csWSCGSoftware.xml")
    Dim strCnn As String = xmlReader.ReadElementString("connection")

    ' CONNECTION 
    Private cnn As SqlConnection = New SqlConnection(strCnn)

    ' COMMAND
    Private cmd As SqlCommand = New SqlCommand

    ' DATA ADAPTER
    Public da As SqlDataAdapter

    ' DATASET
    Public ds As DataSet

    ' QUERY PARAMETERS
    Public params As New List(Of SqlParameter)

    ' QUERY STAT
    Public RecordCount As Integer

    ' TO CALL DATA CONTROL EXCEPTIONS ANYWHERE
    Public exception As String

#End Region

    ' 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

    ' EXECUTE THE STORED PROCEDURE
    Public Sub ExecSP(query As String, cmdType As CommandType)

        RecordCount = 0

        Try
            cnn.Open()

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

            ' ADD COMMAND TYPE
            cmd.CommandType = cmdType

            ' 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

        End Try

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

    End Sub

    ' ADD QUERY PARAMETERS
    Public Sub AddPram(name As String, value As Object)

        Dim newPram As New SqlParameter(name, value)

        params.Add(newPram)

    End Sub

    ' ADD SP PARAMETERS
    Public Sub AddSpPram(name As String, value As Object, int As Int32)

        Dim newSpPram As New SqlParameter(name, value, int)

        params.Add(newSpPram)

    End Sub

End Class

Open in new window

0
Comment
Question by:Mike Eghtebas
  • 2
4 Comments
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 200 total points
ID: 40404044
You're on the right way. Abstraction and decoupling is a code architecture pattern.

There are some possible ways to go on from here:

Using the Entity Framework. This is the most comfortable way of handling the low-level database tasks.
Another possible way would be handling those tasks using the MVC or MVVM pattern.

The basic idea is that your UI code does not need anything to know about the database below.

In your concrete case, your UI code needs to know about stored procedures and how tables and sprocs work together. Here you should introduce a class which forms your business logic. So that your UI code uses only this class.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40404240
Hi Ste5an,

Why am I getting error executing line 5?

'ExecuteNoneQuery' is not a member of 'System.Data.SqlClient.SqlCommand'

          
If cmd.CommandText.Substring(0, 6) = "Select" Then
       RecordCount = da.Fill(ds)
 Else
    RecordCount = cmd.ExecuteNoneQuery()
End If

Open in new window


My imports are:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlCommand
Imports System.Xml
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 300 total points
ID: 40404280
ExecuteNoneQuery should be ExecuteNonQuery
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40404821
Just a comment about your code:

Please use explicit names for classes, members and variables. This is cheap and improves readability to a point where inline comments are almost no longer necessary.

Also read BOL for the classes you're using. Each class implementing IDisposable should be explicitly freed as early as possible. This is the use-case for the Using statement.

On a general approach make you execution methods simpler to use. Return at least success or failure directly:

Imports System.Data.SqlClient

Public Class SqlControl

    Dim strCnn As String = "connection"
    Private connection As SqlConnection = New SqlConnection(strCnn)
    Private parameters As New List(Of SqlParameter)
    Public RecordCount As Integer
    Public ExceptionMessage As String

    Public Function ExecuteQuery(query As String) As Boolean

        ExecuteQuery = False
        RecordCount = 0
        Try
            connection.Open()
            Using command = New SqlCommand(query, connection)
                parameters.ForEach(Sub(x) command.Parameters.Add(x))
                parameters.Clear()
                command.ExecuteNonQuery()
                Using dataSet = New DataSet, dataAdapter = New SqlDataAdapter(command)
                    RecordCount = dataAdapter.Fill(dataSet)
                End Using
            End Using

            connection.Close()
            ExecuteQuery = True
        Catch exception As Exception
            ExceptionMessage = exception.Message
        End Try

        If connection.State = ConnectionState.Open Then
            connection.Close()
        End If

    End Function

    Public Function ExecuteStoredProcedure(storedProcedure As String) As Boolean

        ExecuteStoredProcedure = False
        RecordCount = 0
        Try
            connection.Open()
            Using command = New SqlCommand(storedProcedure, connection)
                command.CommandType = CommandType.StoredProcedure
                parameters.ForEach(Sub(x) command.Parameters.Add(x))
                parameters.Clear()
                Using dataSet = New DataSet, dataAdapter = New SqlDataAdapter(command)
                    RecordCount = dataAdapter.Fill(dataSet)
                End Using
            End Using

            connection.Close()
            ExecuteStoredProcedure = True
        Catch exception As Exception
            ExceptionMessage = exception.Message
        End Try

        If connection.State = ConnectionState.Open Then
            connection.Close()
        End If

    End Function

    Public Sub AddParameter(parameterName As String, value As Object)

        parameters.Add(New SqlParameter(parameterName, value))

    End Sub

    Public Sub AddParameter(parameterName As String, dbType As SqlDbType, size As Integer)

        parameters.Add(New SqlParameter(parameterName, dbType, size))

    End Sub

End Class

Open in new window


But this kind of generalization is normally not really useful. Cause it reduces the possible use-cases. E.g. a stored procedure does not return a records affected value per se. It directly returns always an integer which can be used for transporting the execution status. It also can return one or more result sets. All this is hidden by your general approach.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

16 Experts available now in Live!

Get 1:1 Help Now