Click here to Skip to main content
15,868,340 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have noticed repetitious code when I want to preform a DB function

I have used a helper function in JavaFX and created a openDBUtil Class so I only needed to call the openDBUtil to preform DB functions

I am trying to do the same thing in VB.Net by using a Module because of the inter-dependence of one statement on another this is not working so well

The question is this even possible?

If it is possible how would I code this concept?
My Sub that works as it is now posted below and the code in the Module
YES I HAVE THE IMPORTS
Imports System.Data.SqlClient
Imports System.Data.SQLite


What I have tried:

Private Sub PopulateDGV()

    Dim str2 As String
    Dim s1 As Integer
    Dim dbName As String = "Word.db"
    Dim conn As New SQLiteConnection("Data Source =" & dbName & ";Version=3;")
    Dim valuesList As ArrayList = New ArrayList()
    'openDB() Public Sub in ModuleOne

    'Read from the database
    Dim cmd As SQLiteCommand = New SQLiteCommand("Select * FROM ParentTable", conn)
    conn.Open()
    Dim rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader

    'Set Design of the DataGridView
    dgvOne.DefaultCellStyle.Font = New Font("Tahoma", 10)
    dgvOne.ColumnCount = 2
    dgvOne.Columns(0).Width = 60
    dgvOne.Columns(1).Width = 420
    'To Set Col Header Size Mode = Enabled
    'To Set Col Header Default Cell Styles DO in Properties
    dgvOne.ColumnHeadersHeight = 34

    'DGV Header Names
    dgvOne.Columns(0).Name = "PID"
    dgvOne.Columns(1).Name = "Entry Data"
    dgvOne.Columns(0).SortMode = DataGridViewColumnSortMode.NotSortable
    dgvOne.Columns(1).SortMode = DataGridViewColumnSortMode.NotSortable

    'Read From DB Table add to DGV row
    While rdr.Read()
        valuesList.Add(rdr(1)).ToString()
        lbOne.Items.Add(rdr(1)).ToString()
        s1 = rdr(0).ToString
        str2 = rdr(1)
        dgvOne.Rows.Add(s1, str2)
    End While

    'Add Blank rows to DGV
    For iA = 1 To 4
        dgvOne.Rows.Add(" ")
    Next

    rdr.Close()
    conn.Close()

End Sub


Module ModuleOne

    Public Sub openDB()
        Static dbName As String = "Word.db"
        Static conn As New SQLiteConnection("Data Source =" & dbName & ";Version=3;")
        Static cmd As SQLiteCommand = New SQLiteCommand("Select * FROM ParentTable", conn)
        conn.Open()
        Static rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
        'conn.Close()
    End Sub

End Module


Improved code from OriginialGiff
Private Sub GetDB()
    Dim str2 As String
    Dim s1 As Integer
    'Dim strConnect As String
    'strConnect = "Data Source={0};Version=3;"
    Dim dbName As String = "Word.db"
    Using con As New SQLiteConnection("Data Source =" & dbName & ";Version=3;")
        'Using con As New SqlConnection(strConnect)
        con.Open()
        'Using cmd As New SqlCommand("SELECT * FROM ParentTable", con)
        Using cmd As SQLiteCommand = New SQLiteCommand("Select * FROM ParentTable", con)
            'Using reader As SqlDataReader = cmd.ExecuteReader
            Using reader As SQLite.SQLiteDataReader = cmd.ExecuteReader
                While reader.Read()
                    s1 = reader(0).ToString
                    str2 = reader(1)
                    lbOne.Items.Add(s1 & " " & str2)

                    'Dim id__1 As Integer = CInt(reader("iD"))
                    'Dim desc As String = DirectCast(reader("description"), String)
                    'lbOne.Items.Add(desc.ToString())
                    'Console.WriteLine("ID: {0}" & vbLf & "    {1}", desc)
                End While
            End Using
        End Using
    End Using
End Sub
Posted
Updated 15-Jul-20 7:25am
v2
Comments
Garth J Lancaster 15-Jul-20 2:25am    
You Said "this is not working so well" - could you please use Improve question to indicate 'why not' - it may help people know what you need, it is not clear to me exactly what you're asking for

In general, yes, it should be possible - take a look here for example https://www.codeproject.com/Articles/1210189/Using-SQLite-in-Csharp-VB-Net ... if you want a quick critique on 'ModuleOne', ok ..
1) ModuleOne is a bad name and not indicative of it's purpose - vs 'sqliteDBModule' for example (I forget the VB 'case' conventions)
2) openDB does too much - it should take maybe the name/path to the db, and return a db connection or error (or even throw an exception), that's it - one purpose
3) you could extend your Sqlite 'Module' to implement openDB, closeDB, executeSQL etc etc, but of course I'd say consider what's out there already and re-use/extend it
4) in addition to (3), I'd say, think of 'layering'/'abstractions' .. at the bottom-most layer, you have a sqlite database module that does only low level things with ANY database. 'On Top' of that layer, you then build Module(s) that are specific to the data you wish to handle - One Module per database or table maybe

Does this help or get you any closer to what you need ?

Edit - Also useful, https://github.com/praeclarum/sqlite-net, https://blog.maskalik.com/asp-net/sqlite-simple-database-with-dapper/

There are so many things wrong with this approach: the "open" method does a select which creates a DataReader on the connection, rendering the connection unusable until the reader is closed - and since the "outside world" can't access the reader at all, the connection can't be used anyway.
There is no way to enforce closing and disposing of the scarce resources used: the connection, the command, and the reader are all objects which need to be disposed properly or your app will run out fairly quickly.
The Db name and table name are hard coded into a module, which means it can't be reused.
The DGV fill would be better done from a DataAdapter than a DataReader, and the DataTable used as the DataSource.
The SELECT assumes column order, as so does the DGV fill code. Don't use SELECT * - list the columns you need.
The SELECT collects all columns, and that's wasteful if there are any columns you don;t need this time round. Again, list the columns you want rather than using SELECT *
Once your queries get more complex, you either have to write incredibly flexible module methods, or leave yourself wide open to SQL Injection, which can damage or destroy your DB.
You can't generally "modularize" DB access to the level you want as you'd need new - identical - module methods for each table and query, and most DBs have a lot of tables!

Do it in your method when you need it, and use Using statements to control disposal when items go out of scope:
VB
Using con As New SqlConnection(strConnect)
	con.Open()
	Using cmd As New SqlCommand("SELECT iD, description FROM myTable", con)
		Using reader As SqlDataReader = cmd.ExecuteReader()
			While reader.Read()
				Dim id__1 As Integer = CInt(reader("iD"))
				Dim desc As String = DirectCast(reader("description"), String)
				Console.WriteLine("ID: {0}" & vbLf & "    {1}", iD, desc)
			End While
		End Using
	End Using
End Using
 
Share this answer
 
Comments
Choroid 15-Jul-20 13:22pm    
By incorporating "Using" resources disposed YES better idea I knew that just had fit implementing
DGV fill I wanted to manually manage the Adding to the DGV I did write code that used a reader and datatable
ParentTable only has two values PID and Name
While this project only will only Insert and Create the two table I get what you are saying NOT a good idea "modularize"
I will post improved code the commented out code GUESS not a good idea to paste code ? I do not know how to get around Db hard coded name and table?
After thought by implementing "Using" do I still need to con.Close & reader.Close?
Thank You for the reply that supplied a lot of learning and valuable advice
If you want to move/export the part of your code responsible for exchange data with the database, you have to create set of procedures/functions in this module. At least:
1. Function GetDataTable(qry, qry_parameters) As DataTable - grabs data from database based on passed query and returns DataTable object
2. Function InsertData(qry, qry_parameters) As Integer - inserts data and returns the number of rows affected
3. Function UpdateData(qry, qry_parameters) As Integer - updates data and returns the number of rows updated
4. Function DeleteData(qry, qry_parameters) As Integer- deletes data and returns the number of rows deleted

You may also want to create separate functions for connection open and close.

I'd strongly recommend to read this:
Creating a Data Access Layer (VB) | Microsoft Docs[^]
Creating a Business Logic Layer (VB) | Microsoft Docs[^]
 
Share this answer
 
Comments
Choroid 15-Jul-20 13:31pm    
Thanks for the advice and links looks like I can use the Module not sure I will just got lazy writing the same code on different forms

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900