asked on

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

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

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

ASKER

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

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

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.