Solved

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

Posted on 2014-10-24
4
560 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 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 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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 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

14 Experts available now in Live!

Get 1:1 Help Now