[RESOLVED] Scope of Datasets and DataAdapters for SQLite

[HopWorksEI]

Greets all,

I am just getting my feet wet again in VS2022 and I decided to take on a project that accesses and updates a SQLite3 database.

I have a simple question about dataset, SQLiteDataAdapter, and dataTable and the scope of these objects when created in a procedure. I decided to forgo the designers since I got into so much trouble with them my last go around, and thought I would use something more lower-level.

So say I have a form with a datagridview on it, and a button I click that fills it with a query result from a table in my SQLite3 database. I already got that working, but now I am trying to find an easy way to process the query results before displaying in a form control. Examples I have seen show instantiating the dataset, datatable, and SQLiteDataAdapter in a procedure. But such objects are something I would like to use across many procedures and functions.

So is it wise and even possible to create these objects globally to be reused and shared? Are they destroyed at the end of a procedure they are created in?

If there is something I am missing here, I'm not asking for it to be done for me. Instead, I would love direction to where I can get a better understanding of the scope and do's and dont's of creating these database interface objects, and the scope to my application as a result.

I hope I was not too vague. I am not necessarily a rookie at this as a whole, but certainly am in this environment.

Thanks for listening!

[wes4dbt]

If all your using is one form then you don't need any objects that are global. If you did need global objects then you would put then in a Module and declare them Public "Public varFoo as String"

From what you've said it sound like all you need is Form level and procedural level objects.

Imports System.Data.SQLite
Public Class Form1
    Private con As New SQLiteConnection(My.Settings.RockCollectionConnectionString)
    Private da As New SQLiteDataAdapter("Select Id, Name from ScientificData", con)
    Private dt As New DataTable
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim varFoo As String
        varFoo = "ello World"
        MessageBox.Show(varFoo)
    End Sub
End Class

da has form level scope which means it can be accessed from anywhere in the form class.

varfoo can only be accessed from within the Button1_click event.

Also, if your only using one datatable then you don't need a datset. Datasets are for holding a group of datatables.

[jmcilhinney]

Imports System.Data.SQLite
Public Class Form1
    Private con As New SQLiteConnection(My.Settings.RockCollectionConnectionString)
    Private da As New SQLiteDataAdapter("Select Id, Name from ScientificData", con)
    Private dt As New DataTable
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim varFoo As String
        varFoo = "ello World"
        MessageBox.Show(varFoo)
    End Sub
End Class

A couple of points on that code. I know that this is just a basic example and not intended to be comprehensive, so these are observations rather than criticisms.

I haven't checked whether the SQLite provider is the same but other ADO.NET providers have a data adapter constructor that takes a SQL query in a String and a connection string, so there's actually no need to create the separate connection object. Also, if you're only going to retrieve data then the data adapter and possibly the DataTable variables can be locals too. If you want to retrieve and save then you'll need a member variable but then you also need the commands to save data. To that end, you should create a command builder with a member variable too, or else configure those commands yourself in the Load event handler.

You may like to follow the CodeBank link in my signature below and check out my Retrieving & Saving Data thread for more complete examples. They are quite old now and could still be improved, but they'll give you a pretty good idea of what you need.

[HopWorksEI]

Also, if your only using one datatable then you don't need a datset. Datasets are for holding a group of datatables.

Actually I will be using numerous tables, so definitely looking at the dataset.

Thanks for the clarification and the code sir, that means a LOT to my project!!!

[HopWorksEI]

Thank you sir for that. Between your response and wes4dbt, I have a better understanding now. This will significantly help my simple project.

Thing is, I got into VS 2022 with the data sources, designers, and ran into so many problems with working with my database. At that time, it was MySQL not SQLite. It's not MS's fault, all mine. But as I am working on a home project and not for a company, I'm not what you would call a "pro" so I am sure I missed something with that scheme's implementation.

I wanted to dumb-down to a simpler level of dealing with my database, like I have in the past with Python and PHP. But I am sure it is faster working with dataTables and datasets, so I keep hitting my head against that block wall. The biggest problem I have always had is scope with objects in a windows form application using VB.NET. What I need to do is find a great book on it and thoroughly read/study up. There are so many offerings though, I cannot decide on what the best one would be. One that covers the best practices. As you all know, the best practices are designed to keep us from chasing our tails with errors that would not be there if we followed the right path.

With that said, I appreciate your help and with giving me an assist with this. THANKS!

[wes4dbt]

What don't don't you understand about scope that wasn't covered in my post? You don't need a book for that.

jmc has several useful links in the footer of his post.

[jmcilhinney]

Actually I will be using numerous tables, so definitely looking at the dataset.

That's still not necessarily a reason to use a DataSet. It depends what you're using at that time. If you're only displaying data from a single query, even if that query involves multiple tables, then you only need a single DataTable. Even if you are displaying data from multiple tables separately, multiple loose DataTables may still be the better option. A DataSet allows you to pass multiple DataTables around as a unit, e.g. from one form to another, but the biggest benefit is that it allows you to create DataRelations between the DataTables, thus enforcing foreign key constraints from the database. Whether or not that desired or required depends on the situation.

[jmcilhinney]

The biggest problem I have always had is scope with objects in a windows form application using VB.NET.

The fact that you're asking about scope in relation to ADO.NET is part of the problem. Scope is actually a very simple subject and has nothing to do with databases or ADFO.NET. All you really need to understand is that you declare everything - local variables, members, types - with the minimal scope and access level you can in order to use them as required. If you are only using a variable within a single block, e.g. a If block then you declare it within that block. There's no reason to declare it outside because doing so provides no benefit and it does provide the opportunity to do things you shouldn't be doing. If a variable needs to be used within multiple blocks then you declare the variable in the innermost block that contains the blocks that use the variable. If that's two If blocks in the same method then you would declare it at the top level within the method. If it's within two methods then you declare the variable outside both methods, i.e. at the class level. It's one very simple principle and it goes for everything.

[HopWorksEI]

Thank you two for all the input and clarification. I guess I was not very clear about scope. I understand variable scope as it applies to the few languages I know. And I understand somewhat about object scope and class inheritance.
I guess what I was getting at with datasets, dataTables, and dataAdapters is at what level I should create them, are they destroyed if the scope they are in ends, and if it was feasible to create them in a simple procedure to do a few things and have to recreate them with other procedures. Examples I have researched show them created in, say, a button click where the goal is to populate a control. I am looking for a best-practice scenario for something acceptable that populates several controls on a form, and still be available for future form interaction. So from what I have read from you two (jmcilhinney and wes4dbt) I would think it would be acceptable to create these objects at the form class level so they are available to all the controls that will interact with them for as long as the form exists.
I sincerely appreciate your replies! They really helped, and I realize I have a lot to learn about working with these objects and how they affect my interaction with my database. It is intriguing though and definitely a step up from manually running queries against the DB without the buffer as the middleman.
I might have one last question on-topic about database connections, but I am going to work on my project and see how my changes affect the operation of my project before I make a fool of myself asking an obvious question. Have a great day!

[wes4dbt]

This is an update to my first example,

Imports System.Data.SQLite
Public Class Form1
    Private con As New SQLiteConnection(My.Settings.RockCollectionConnectionString)
    Private da As New SQLiteDataAdapter("Select Id, Name from ScientificData", con)
    Private dt As New DataTable
    Private cmdBldr As New SQLiteCommandBuilder(da)
    Private bs As New BindingSource

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        con.Open()

        da.Fill(dt)

        bs.DataSource = dt
        Me.DataGridView1.DataSource = bs

    End Sub

    Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click

        bs.EndEdit()
        da.Update(dt)
    End Sub

End Class

If you want to Add/Edit/Delete the data you retrieve then here is a simple example. You'll notice I use a CommandBuilder to create the necessary commands for the DataAdapter, For this to work your database table must have a Primary Key. Also, I use a bindingsource. This is not necessary but adds some nice built in functionality. You could just set the datagridview datasource to the datatable itself.

This method is acceptable but many don't consider it "best practice". Many think the data layer and the UI layer should be completely separate. But I've never felt the need when the app is fairly small and simple. I don't know what your program is but this is a good way to learn the basics of ADO .Net data objects.

[HopWorksEI]

Thank you Wes4dbt for this. This will certainly give me what I need to expand on. And a lot of things to get my head around, like using my.settings and using the command builder. AND learning about the BindingSource. You have given me a lot to expand on and I certainly appreciate it.
My real-life job is concluded in about an hour, for the week. I cannot WAIT to dive into this and apply it to my project.
My project... by the way, is an interface to be able to CRUD my embedded systems inventory database. I have so many devices, parts, modules, MPU's, etc. etc. I need an app to organize it all and know what I have to work with on future systems. Much of the gear is in storage containers that with the help of the Brother label printers API, has printed labels with QR CODES that I can read with a scanner. This inventory app will allow me to quickly find what I need on a project. And I can add controller software to light up a container that contains what I am looking for. It is a neat system.
Anyway, thank you!!!

[jmcilhinney]

Again, there's nothing special about ADO.NET here. If you only need to use an object in one place, declare the appropriate variable and create the object in that place. If the object supports it, dispose it in that place too. For example, if you're inserting a record to a database using ExecuteNonQuery, the connection and the command objects should be created where you do it, exactly where you declare the local variables to refer to them. In the example in post #10, the same data adapter is being used to retrieve and save data so it needs to be used in multiple methods, so it is created outside all those methods. It's exactly what I said in post #8. Given that the objects should generally be created on the same line that declares the corresponding variable, the scope of the objects matches the scope of the variables.

[.paul.]

Again, there's nothing special about ADO.NET here. If you only need to use an object in one place, declare the appropriate variable and create the object in that place. If the object supports it, dispose it in that place too. For example, if you're inserting a record to a database using ExecuteNonQuery, the connection and the command objects should be created where you do it, exactly where you declare the local variables to refer to them. In the example in post #10, the same data adapter is being used to retrieve and save data so it needs to be used in multiple methods, so it is created outside all those methods. It's exactly what I said in post #8. Given that the objects should generally be created on the same line that declares the corresponding variable, the scope of the objects matches the scope of the variables.

I think the question is... If you create a DataTable in a Button_Click handler, will the data stay where you've set a DataSource?
Scope is scope, for any Variable or Object. If you need the Variable or Object throughout the Class, you declare it at Class level.

[HopWorksEI]

I know this thread is "solved" but I wanted to add that the time the three of you spent replying to my question really got the ball rolling for me. Especially the replies from jmcilhinney and wes4dbt. I guess my problem was that I was looking at all the database objects as separate entities that existed like a separate process. Persistent and ever-present. I realize now that this was really silly and I am not sure how I was led down that road. And although I have not fully embraced the command builder, I certainly am having a fun time accessing multiple tables in my database and using that data in my windows forms controls. My three projects (budget, home media, and inventory) are all cruising along nicely!

I just wanted to throw out an additional thanks for the push without the usual condescending rhetoric for a rookie that has issues with the terminology. The usual handicaps a hack has that has not learned this in school. Thing is, knowledge is power, especially when writing something for one's self. Getting input from accomplished greats like you guys really helps pave that road, and I am sincerely appreciative.

Thanks again!

[wes4dbt]

And although I have not fully embraced the command builder

A DataAdapter has three methods it uses to push the information back to the database, InsertCommand, UpdateCommand, DeleteCommand. The CommandBuilder creates the code for each of those methods. You don't have to use a CommandBuilder, it's just saves some coding. Here is an example of coding those methods manually.

This codes those methods for a DataAdapter called collectionAdapter

    Private Sub SetUpCollectionAdapter()
        Dim collectionInsertCommand As New SQLiteCommand("INSERT INTO Collection (GemId, VendorID, PurchaseDate) VALUES (@GemId, @VendorID, @PurchaseDate)", con)
        Dim collectionUpdateCommand As New SQLiteCommand("UPDATE Collection SET  GemID = @GemId, VendorID = @VendorID, PurchaseDate = @PurchaseDate WHERE Id = @Id", con)
        Dim collectionDeleteCommand As New SQLiteCommand("DELETE FROM Collection WHERE Id = @Id", con)

        With collectionInsertCommand.Parameters
            .Add("@GemId", DbType.Int32, 50, "GemID")
            .Add("@VendorID", DbType.Int32, 0, "VendorID")
            .Add("@PurchaseDate", DbType.Date, 0, "PurchaseDate")
        End With

        With collectionUpdateCommand.Parameters
            .Add("@GemId", DbType.Int32, 50, "GemID")
            .Add("@VendorID", DbType.Int32, 0, "VendorID")
            .Add("@PurchaseDate", DbType.Date, 0, "PurchaseDate")
            .Add("@ID", DbType.Int32, 0, "ID")
        End With

        With collectionDeleteCommand.Parameters
            .Add("@Id", DbType.Int32, 0, "Id")
        End With

        'Now add the commands to the dataadapter
        With collectionAdapter
            .InsertCommand = collectionInsertCommand
            .UpdateCommand = collectionUpdateCommand
            .DeleteCommand = collectionDeleteCommand
        End With
    End Sub

These methods will be used when you call the Update method. collectionAdapter.Update(yourData)

[HopWorksEI]

A DataAdapter has three methods it uses to push the information back to the database

Thank you sir for that code offering. I used command builders during my last try a couple of years ago with the assist of some sort of designer. I believe it was VS2019. Anyway, I cannot remember the special circumstance that applied, maybe it was allowing me to get the auto increment ID from a record that was added. But the designer took me down a rabbit hole that opened up all sorts of other issues. Not fully understanding the process, and having VS holding my hand, I failed to rectify those sprouted issues.
This code helps me to understand the process however, with the command builder. The benefit of using it will be applied much better now as I better understand the process it assists with. And of course, the code you posted will help! Thank you!!

Now I have been struggling with DBNULL for queries that do not yield any rows. I believe I have it solved however. This first of 3 projects deal with my home media, where Kodi's database is tapped for the data. It is completely informational, being used to look for duplicate instances over 4 NAS resources, improperly named media, and streaming characteristics for comparison. No edits to this database will happen with this first of three projects. The budget and inventory versions will deal heavily with database CRUD interaction however, so again, this code will be a great help!

Thanks again, for everything!