Solved

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

Posted on 2014-10-24
4
612 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
[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
4 Comments
 
LVL 33

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 34

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 33

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

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 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