Tip Easy SELECT, INSERT, UPDATE, DELETE

[Budius]

I'm fairly new to the whole object oriantation thing and as such I've been mostly been using it as if it were not; (the fact that the whole project is developed by me doesn't help). But as I'm getting more into it, and understanding it, I guess I'm getting to the point of actually creating my classes to make something I can re-use across multiple projects.

I've realised that even thou I do some damn complex select statements, every single database query I've done the past 3 years (vb.net or vb6) were the big 4 !
Therefore, nothing more natural than a nice sweet smooth easy to use Class to do it for me.

So this thread is only to share my Class with you guys, re-use it at your will, and for the more experienced ones, please.. tips, suggestions of changes, criticisms, ... all welcome!

I've tried to do it nicely comment, including those nice pop-ups when you're typing to use its methods or properties.

It's basically 2 methods and 2 properties, and the constructor
- you have to pass the connection string to the constructor
- Query method to send a SELECT statement to the database, writes the returned values in the provided datatable and returns True or False for success/fail
- InsertUpdateDelete method that (as the name say) executes any of those non-query statements and return True or False for success/fail
- ErrorMessage property that holds the error message from any failed method
- NumberOfRowsAffected property that holds the number of rows affected or returned by the last method


I guess it doesn't get simpler than that, if it is what you need .

usage example:

        Dim TestDB As New DB("Password=ui;Persist Security Info=True;User ID=ui;Initial Catalog=MyInitialCatalog;Data Source=MyServerName")

        If TestDB.InsertUpdateDelete("INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3)") Then
            MessageBox.Show(TestDB.NumberOfRowsAffected & " rows were modified")
        Else
            MessageBox.Show(TestDB.ErrorMessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

        Dim DT As New DataTable
        If TestDB.Query("SELECT * FROM table_name", DT) Then
            MessageBox.Show(TestDB.NumberOfRowsAffected & " rows were retrieved")
        Else
            MessageBox.Show(TestDB.ErrorMessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

and here is my DB class:

Option Explicit On
Imports System.Data.SqlClient

''' <summary>
''' Provides easy to use functions to perform the 4 basic SQL operations (SELECT, INSERT, UPDATE, DELETE). Create by Budius 2010.
''' </summary>
''' <remarks></remarks>
Public Class DB
    Private ConnStr As String
    Private ErrMessage As String = ""
    Private AffectedRows As Integer = 0

    ''' <summary>
    ''' Gets the message from the last error
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>It will return "" no errors happen</remarks>
    Public ReadOnly Property ErrorMessage As String
        Get
            Return ErrMessage
        End Get
    End Property
    ''' <summary>
    ''' Gets the number of rows affected by the last query
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>It will return -1 case the last query had errors</remarks>
    Public ReadOnly Property NumberOfRowsAffected As Integer
        Get
            Return AffectedRows
        End Get
    End Property

    ''' <summary>
    ''' </summary>
    ''' <param name="ConnectionString">Connection String to the SQL server (e.g. User ID=ui;Password=ui;Persist Security Info=True;Initial Catalog=MyInitialCatalog;Data Source=MyDataServerName)</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal ConnectionString As String)
        ConnStr = ConnectionString
    End Sub


    ''' <summary>
    ''' Query the database and place the result into the provided datatable
    ''' </summary>
    ''' <param name="QueryStr">SQL query string</param>
    ''' <param name="DT">Datatable to receive the result</param>
    ''' <returns>Returns the number of rows returned by the query</returns>
    ''' <remarks>Returns -1 case an error occurs</remarks>
    Function Query(ByVal QueryStr As String, ByRef DT As DataTable) As Boolean
        AffectedRows = -1
        Try
            Dim Conn As New SqlConnection
            Conn.ConnectionString = ConnStr
            Dim Adapter As New SqlDataAdapter(QueryStr, ConnStr)
            ' Clear the table
            DT.Clear()
            DT.Reset()
            ' Opens the connection
            Conn.Open()
            ' Execute the Query
            AffectedRows = Adapter.Fill(DT)
            ' Closes connection
            Conn.Close()
            ' Dispose from resources
            Adapter.Dispose()
            Conn.Dispose()
        Catch ex As Exception
            ErrMessage = ex.Message
            'MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        If AffectedRows = -1 Then
            Query = False
        Else
            Query = True
        End If

    End Function
    ''' <summary>
    ''' Inserts, updates or deletes values onto/from the database using the SQL statement provided and return the number of rows affected.
    ''' </summary>
    ''' <param name="SQLStatement">INSERT, UPDATE or DELETE SQL statement to be executed onto the database</param>
    ''' <returns>Returns the number of rows affected by the SQL statement</returns>
    ''' <remarks>Returns -1 case an error occurs</remarks>
    Function InsertUpdateDelete(ByVal SQLStatement As String) As Integer
        AffectedRows = -1
        Try
            Dim Conn As New SqlConnection
            Conn.ConnectionString = ConnStr
            Dim Cmd As New SqlCommand(SQLStatement, Conn)
            Cmd.Connection.Open()
            AffectedRows = Cmd.ExecuteNonQuery()
            Cmd.Connection.Close()
            Cmd.Dispose()
            Conn.Dispose()
        Catch ex As Exception
            ErrMessage = ex.Message
            'MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        If AffectedRows = -1 Then
            InsertUpdateDelete = False
        Else
            InsertUpdateDelete = True
        End If
    End Function

End Class

 

[cjard]

I'd urge anyone reading this thread to instead use the provided tools within Visual Studio (tools that Microsoft spent thousands of manhours and dollars developing) to properly generate a type-specific data access layer for their application.. Not to pour water on your efforts, Budius but you too should be using the modern tools at your disposal to write the best code possible.. This small utility class isnt really an example of that

For an entire suite of tutorials dedicated to database manipulation, written by Microsoft, see the DWx links in my singature where x is the major version of your .NET framework

 

[Budius]

yeah... I really wished those wizard based stuff worked on the express version.
I really had to do everything manually, so I just find the generic case that allows me to re-use stuff easier.

After the comment I really searched for the button to edit the topic and delete it, but I guess I can't edit the 1st topic of the thread.
So if any moderator have a few seconds to spare, feel free to delete it. I'll carry on using it, cause it's the best tool I have available instead of re-typing the same code again and again.

thanks for the heads-up.

 

[JohnH]

Putting input values in the query string is a big no-no, look into using command parameters at least

VB Express db wizards work best with SQL Server Express or Compact.

I saw some place in your code you create a connection object, open and close it, but never use it run a command. For the adapter you provided the connection string, so adapter creates its own connection object and uses that. Either way you fix it don't open and close the connection when using the adapter, it manages this itself.
When you do create a connection object, why not use the New SqlConnection(String) constructor?
An addition to the class could be to include adapter based IUD method, using the adapters Update method.
Also perhaps SqlCommandBuilder class is new to you, it could be something you find useful.

 

[Budius]

VB Express db wizards work best with SQL Server Express or Compact.

here in the company, they have a few full DB, cause it's used by other systems, but actual programming it's basically just me (control and automation), so they didn't care to buy the full version.

About the adapter, I've used in one of the projects and didn't had time to include in this class (maybe on V2), because it wouldn't be immediately useful on any other of my codes.
About the other suggestions and comments: Thanks a lot, I'll take a look and have some further reading about it.

 

[Budius]

I saw some place in your code you create a connection object, open and close it, but never use it run a command. For the adapter you provided the connection string, so adapter creates its own connection object and uses that. Either way you fix it don't open and close the connection when using the adapter, it manages this itself.
When you do create a connection object, why not use the New SqlConnection(String) constructor?

Cool, I changed both connections to:

            Dim Conn As New SqlConnection(ConnStr)

and use the New constructor passing the connection for both SqlCommand and SqlDataAdapter.
I'm not messing with opening and closing connection for the data adapter any more. I probably made some confusion with the different examples I read.

reading the MSDN I got one more question regarding the SqlCommand.ExecuteNonQuery method
from MSDN:

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub

they do open, but do not close the connection... ??? why is that?

thanks ; )

 

[JohnH]

The command doesn't open/close for you as adapter do, so here code must open.
The connection object is the target of the Using block, which calls Dispose automatically, and Dispose/Close methods has same functionality for connection object.