[RESOLVED] Beginner trying to learn SQLite database

[jumper77]

SS, I need to ask about something that's puzzling me. Because you use what's below in your code, I tried to find some references to it (IEnumerator, IEnumerable) Here's the first thing I found. I tried searching through your code, but found 'none' of this.

First, we need to define a class to implement IEnumerable. Here's a start ...

Public Class Parties
Implements IEnumerator, IEnumerable

Public Function GetEnumerator() _
As IEnumerator _
Implements IEnumerable.GetEnumerator
Return CType(Me, IEnumerator)
End Function

This satisfies the requirement of IEnumerable because it implements GetEnumerator. How does it do that? It simply uses the CType operator to change the class itself into an IEnumerator and returns it.

Would you mind filling in some blanks? On the first sentence of the quote, it says to first create a class, but I saw none of that in your code. Of course it must be because you don't 'have to'.

Can you help me put these pieces together?

thanks

[Sitten Spynne]

I don't think that's a good idea. Earlier you posted a form with a datagridview and a couple of button. Why take a step backwards. Your already able to display data in a dgv. I think it would be easier for you and us to review your program results that way.

I mean,
[snip]
Boom.... Done

There's some tensions tugging RE: that decision. You're not wrong, and at some point I've got to start posting DataGridViews.

The console is easy to understand. I can put an entire console application in one copy/paste or one attached .vb file. Then someone can see all the code, laid bare, and see how it's put together.

WinForms projects, especially ones with DataAdapters, are not quite so easy. I can't post just one file, it has to be a zipped project. Or, I have to take the time to detail the 5 or 6 properties that always need to be set on the DGV, and then the 5 or 6 event handlers I might want to register, and that's not even counting the 10 or 15 properties and events you might want to use in various cases. It's remarkably complex, and very hard for new users to peek at and figure out how to reproduce. And Heaven help you if you're using the old .NET 1.x era "typed datasets" generated by code. I had my fun trying to move those around from machine to machine. I didn't touch a database again for something like 6 years.

But the console is really only useful for "this is how you put all the pieces together for a DataAdapter". Obviously few people want to make and use console applications. I wager it's easier to learn DataGridView by itself, rather than try to learn both DataGridView and DataAdapter at the same time. My wager comes from "that's how I learned it, it stunk, and I wish I'd taken my time because I think I might have written some more successful applications."

That said, working with DataAdapter from the console's painful enough I can only predict using it for a handful of more posts. It's convenient, but for magic to happen we need DataGridView and Windows Forms data binding.

[Sitten Spynne]

SS, I need to ask about something that's puzzling me. Because you use what's below in your code, I tried to find some references to it (IEnumerator, IEnumerable) Here's the first thing I found. I tried searching through your code, but found 'none' of this.



Would you mind filling in some blanks? On the first sentence of the quote, it says to first create a class, but I saw none of that in your code. Of course it must be because you don't 'have to'.

Can you help me put these pieces together?

thanks

I don't understand this question, what that block of text means, where it's from, or what you're asking me. That article looks very old, because modern code sort of shuns IEnumerable for IEnumerable(Of T). Long story. Don't get me started.

IEnumerable(Of T) is an interface in .NET that just means "this is a collection you can iterate". Other programmers call it "The Visitor Pattern", but Microsoft doesn't want their developers to be able to talk to other people, I guess, so they called it "enumeration".

There is a very formal version of the pattern that you need to know if you ever want to write your own collection type. That will not be needed at all in anything I'm expecting to talk about. In a nutshell:

Consider the numbers 1-5. That's an "enumerable collection of integers", or an IEnumerable(Of Integer).

IEnumerable(Of T) is an interface that defines one function: GetEnumerator() returns an IEnumerator(Of T). What's an enumerator?

Now consider how we count the numbers 1-5. You start with a closed fist. You extend one finger, and say "one". You extend another and say "two", and so on. Your hand, in this case, is an IEnumerator(Of Integer), because it knows how to iterate over the numbers 1-5, and it tells you when it's finished (it runs out of fingers.)

I didn't do any of the stuff in the post because you don't typically write your own collection type. It was much more common in .NET 1.x, because there were no generics and ArrayList/HashSet were a pain in the butt without them. Many .NET types already implement IEnumerable(Of T): arrays, List(Of T), Dictionary(Of TKey, TValue), etc. Almost every collection you find implements IEnumerable(Of T).

If I spend much more time on the topic, we have to implement one, and honestly I think it's extra work.

My opinion is whatever article you're reading looks like .NET from 2002-2004, and back then we had to do a lot of painful stuff we consider stupid today. A lot of what it says about DataAdapter probably still applies. But a lot of what it tells you to do may not be necessary in 2016.

[jumper77]

I don't think that's a good idea. Earlier you posted a form with a datagridview and a couple of button. Why take a step backwards. Your already able to display data in a dgv. I think it would be easier for you and us to review your program results that way.

Hi Wes, I apologize, but I missed your post. I've had to reconfigure my email software so I would get proper notice when someone replies to the thread.

But, thank you for noticing the GridView form. I always struggle with myself about posting UI stuff. I want people to see what I'm doing, but something makes me think it's not a good thing to do. Not really sure why.

So if nobody minds, I'll go back to the form project. It's as up to date as the console version. Plus, the WinForm version is much easier to test with just a click of a button.

Thanks for the post and thanks for sharing that. I feel better that I did before

[jumper77]

That said, working with DataAdapter from the console's painful enough I can only predict using it for a handful of more posts. It's convenient, but for magic to happen we need DataGridView and Windows Forms data binding.

Before I read this post, I talked with Wes about it being easier to understand if it's a WinForm project. If there are only a few more posts using a console, then I'll stick with the UI code. Unless I misunderstand what you're saying.

Plus I'm really looking forward to data binding. I've heard it mentioned for years, but never had a reason to check it out.

[jumper77]

I don't understand this question, what that block of text means, where it's from, or what you're asking me. That article looks very old, because modern code sort of shuns IEnumerable for IEnumerable(Of T). Long story. Don't get me started.

Ok, I won't get you started but here's the link to the article:
Article

And for the rest of your message... again, it was a big help. I'm not diving too deep though so I can continue with the project. If it's not needed now, I can come back to it.

[Sitten Spynne]

My only complaint is UI projects are impossible to remote-debug. You can paste your whole code file, but it's only half of the story. The other half is in the designer code that sets all the DGV properties. And if it's complex enough I need to run it, you have to zip the whole project (remembering to remove the /bin and /obj directories) and upload it. Then, when I get it, the first thing I have to do is look over each property of the DGV, and each registered event handler, THEN go look at the code and see if you changed any of those in code.

It may sound trivial. I just counted roughly 190 properties on DataGridView, each of them related to at least one of something like 20 features. I counted more than 500 methods, with I can't tell how many available to override for extensibility. There's about 170 events. When I open a GUI project with a DataGridView, I have to go investigate all of those properties and event handlers to start getting a feel for what's going on.

Or, I can copy/paste an entire console application. Kablam, instant tweakable project. It creates types. It works with types. It calls Console.WriteLine() a lot. You can even it them and watch it run live at http://dotnetfiddle.net. (Except that doesn't support any database packages sooooo yeah.)

Again, it's screaming into the void. Once you know enough about database interaction, GUI is the only exciting topic left.

[jumper77]

When I open a GUI project with a DataGridView, I have to go investigate all of those properties and event handlers to start getting a feel for what's going on.

No problem at all. I'll stick with the console app if or until you think we should go the other way. I still have it loaded right now, so there's nothing I need to do. The only thing I have left to do at the moment is figure out how to test it in a console project.

edit: I don't mean to say that I understand everything at this point, because I don't. There's still much to learn. Also, I didn't realize how much you had to go through with a form app.

[Sitten Spynne]

Use what you're comfortable with. A better way to phrase what I'm saying is this:

If I'm trying to teach some concept about some thing that doesn't need UI, I'm using a console application to reduce clutter and keep the focus on the concept.

But there are so many needs for CRUD apps in the world, WinForms has a ton of facilities for them. I can't talk about DataGridView without a Form. So when I get there, I'll go to the trouble of setting up a UI. And in that case, while it's "trouble" to explain what I did in the designer, the whole point of the 'lesson' will be that "trouble", so it's not worth complaining about.

[jumper77]

I think we should stay with the console app. It's simple, plus it's easy to focus on what you're learning. A UI only slows the process because half the time you're fighting with the controls and other things that are just not necessary. And I would rather spend more time on the things I want to learn.

And now I have something to throw back at you There really is no need to have the UI/DataGrid in this lesson. As long as I understand the mechanisms behind the UI implementation, I can always learn the UI parts later on. But I'm guessing that you've already put some time in already working with the DataGrid (and maybe even having fun), so if you want to keep that as part of the project, that's ok with me.

We could have dueling DataGrids

edit: I already know how to refresh the DataGrid so it shows the information you just inserted or updated. Plus I have alternating colors on my rows already.

[jumper77]

Now, back to work. There's something I don't understand going on after I tried to use your INSERT sub. The sub now makes much more sense to me (but I've only been working on it today). I'll include the relevant code, but let me see if I can describe it first.

In my app, I have 4 fields, not 2, so I altered your code to deal with the extra fields. When the program hits the Insert sub, I'm not getting the results I'm expecting. When assigning parameters a .value, the first name is correct, but lastname, address, and phoneNumber all have the value of phoneNumber?? It's probably my fault though.

So, here's where the code starts:

    Sub Main()
        Const databaseName As String = "customers.db"
        Dim dbcustomer As New List(Of Customer)()
        Dim customer As New Customer
        Dim db As New CustomerDatabase(databaseName)

        If Not Exists(databaseName) Then
            db.CreateDatabase()
            'db.Reset()
        End If

        customer.FirstName = "Joe"
        customer.LastName = "bob"
        customer.address = "20 west main street"
        customer.phoneNumber = "444.1212"

        dbcustomer.Add(customer)

        db.Insert(dbcustomer)
    End Sub

Next it's sent to the Insert sub:

    Public Sub Insert(ByVal customers As IEnumerable(Of Customer))
        ' Lesson 3: the query is converted to use parameters.
        Dim insertStatement As String = "INSERT INTO customer (firstName, lastname, address, phoneNumber) VALUES (:firstName, :lastName, :address, :phoneNumber)"

        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                command.CommandText = insertStatement
                command.Parameters.Add(New SQLiteParameter("firstName"))
                command.Parameters.Add(New SQLiteParameter("lastName"))
                command.Parameters.Add(New SQLiteParameter("address"))
                command.Parameters.Add(New SQLiteParameter("phoneNumber"))

                For Each customer In customers
                    command.Parameters("firstName").Value = customer.FirstName
                    command.Parameters("lastName").Value = customer.LastName
                    command.Parameters("lastName").Value = customer.address
                    command.Parameters("lastName").Value = customer.phoneNumber

                    If command.ExecuteNonQuery() = -1 Then
                        Console.WriteLine("INSERT failed!")
                    End If
                Next
            End Using
        End Using
    End Sub

And as I said,
firstname.value = first name
lastname.value = phoneNumber
address.value = phoneNumber
phoneNumber.value = phoneNumber

There's a pattern there, but I didn't expect last name to be phoneNumber. I thought the 2 parameters I added would be affected, not one of yours.

edit: Almost forgot, I'm using my sub for creating the database if not found.

    'Creates New Database if not found
    Public Sub CreateDatabase()
        Using objConn As New SQLiteConnection(__connectionStringCreateDatabase)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "Create TABLE customer('Row Count' INTEGER PRIMARY KEY,'FirstName' Text Not NULL, 'LastName' Text Not NULL, Address Text Not NULL, 'PhoneNumber' Text Not Null);"
                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

[Sitten Spynne]

See if you find something fishy about these lines of code:

                    command.Parameters("firstName").Value = customer.FirstName
                    command.Parameters("lastName").Value = customer.LastName
                    command.Parameters("lastName").Value = customer.address
                    command.Parameters("lastName").Value = customer.phoneNumber

Hint: copy/pasting can save a lot of time. But you have to make sure you actually update everything.

I once saw a study where a guy wrote a bot to analyze thousands of GitHub projects for copy/paste errors. The fascinating finding was not how many he found, but how dramatic the probability swayed towards the last line being the error.

In this case, it's more than the last line.

[jumper77]

Crap (he says hanging his head in shame). I was looking at the values on the right, not the one's in the middle. Well, that post was for nothing.

And yes, it's more than one.

I'll make the changes and then see what happens,
thanks a bunch. Will let you know how it turns out (in a few minutes).

[jumper77]

That was quick. Probably took 30 seconds to get the result. Everybody is happy now and there are NO errors (except in my brain).
Thanks for the help and slap upside the head.

[jumper77]

Before I spend too much time with this issue, I'm going to ask a question.

• First I insert a new row in customers database
• Then I call a function to update the record. It changes the last name in the record.
• Before doing the ExecuteNonQurey, the value contains the correct last name to be changed.
• After ExecuteNonQurey, the last name is not changed.

this is because ID always has the value of 0, and I'm not sure why. So here's the create database and calling code:

   'Creates New Database if not found
    Public Sub CreateDatabase()
        Using objConn As New SQLiteConnection(__connectionStringCreateDatabase)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "Create TABLE customer('Id' INTEGER PRIMARY KEY,'FirstName' Text Not NULL, 'LastName' Text Not NULL, Address Text Not NULL, 'PhoneNumber' Text Not Null);"
                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

Module Module1
    Const databaseName As String = "customers.db"
    Private objConn As New SQLiteConnection
    Private objCommand As New SQLiteCommand
    Private dbcustomers As New List(Of Customer)()
    Private updateCustomer As New Customer
    Private db As New CustomerDatabase(databaseName)
    Private conString As New CustomerDatabase(connectionString)

    Sub Main()
        Dim customer As New Customer

        If Not Exists(databaseName) Then
            db.CreateDatabase()
        End If

        TestInsert()
        TestUpdate()
    End Sub

    Sub TestUpdate()
        Dim customer As New Customer

        With Customer
            .FirstName = "Joe"
            .LastName = "bob"
            .address = "20 west main street"
            .phoneNumber = "444.1212"
        End With

        db.Update(customer)
    End Sub

    Public Sub TestInsert()
        Dim customer As New Customer

        With customer
            .FirstName = "Joe"
            .LastName = "Pendalton"
            .address = "20 west main street"
            .phoneNumber = "444.1212"
        End With

        dbcustomers.Add(customer)
        db.Insert(dbcustomers)
    End Sub

Now, the Insert and Update subs:

   Public Sub Insert(ByVal customers As IEnumerable(Of Customer))
        ' Lesson 3: the query is converted to use parameters.
        Dim insertStatement As String = "INSERT INTO customer (firstName, lastname, address, phoneNumber) VALUES (:firstName, :lastName, :address, :phoneNumber)"

        Try
            Using connection As SQLiteConnection = CreateConnection()
                Using command As SQLiteCommand = connection.CreateCommand()
                    command.CommandText = insertStatement
                    command.Parameters.Add(New SQLiteParameter("firstName"))
                    command.Parameters.Add(New SQLiteParameter("lastName"))
                    command.Parameters.Add(New SQLiteParameter("address"))
                    command.Parameters.Add(New SQLiteParameter("phoneNumber"))

                    For Each customer In customers
                        command.Parameters("firstName").Value = customer.FirstName
                        command.Parameters("lastName").Value = customer.LastName
                        command.Parameters("address").Value = customer.address
                        command.Parameters("phoneNumber").Value = customer.phoneNumber

                        command.ExecuteNonQuery()
                        'If command.ExecuteNonQuery() = -1 Then
                        '    Console.WriteLine("INSERT failed!")
                        'End If
                    Next
                End Using
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub Update(ByVal customer As Customer)
        Dim updateQuery As String = "UPDATE customer SET " &
        "firstName = :firstName, lastName = :lastName, address = :address, phoneNumber = :phoneNumber WHERE id = id"

        Try
            Using connection As SQLiteConnection = CreateConnection()
                Using command As SQLiteCommand = connection.CreateCommand()
                    command.CommandText = updateQuery
                    command.Parameters.Add(New SQLiteParameter("firstName", customer.FirstName))
                    command.Parameters.Add(New SQLiteParameter("lastName", customer.LastName))
                    command.Parameters.Add(New SQLiteParameter("address", customer.address))
                    command.Parameters.Add(New SQLiteParameter("phoneNumber", customer.phoneNumber))
                    command.Parameters.Add(New SQLiteParameter("id", customer.Id))

                    command.ExecuteNonQuery()

                    'If command.ExecuteNonQuery() = -1 Then
                    '    Console.WriteLine("An UPDATE failed!")
                    'End If
                End Using
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

Anyone have an idea of what be doing on? I can't seem to see it.

edit: The ID field should auto-increment, but it's not.

[szlamany]

You have to check your terminology - you do not put new rows in a database. A database contains tables (amongst other objects) and it is the table that receives a new row.

INSERT will put a new row in a table.

There is no need for any other ACTION QUERY after this - INSERT does just this simple task of putting a new row in table.

UPDATE will alter an existing row in a table.

ExecuteNonQuery is a method that takes an ACTION QUERY and performs it.

Have you played a lot in a query window hand typing any INSERT's or UPDATE's?? And of course lots of SELECT's so you can see the changes done by the INSERT's and UPDATE's??

You need to get familiar - really familiar - with the DB operation. I think you are in a tough spot writing code without a stronger foundation in the data side.

[jumper77]

You have to check your terminology - you do not put new rows in a database. A database contains tables (amongst other objects) and it is the table that receives a new row.

I will try to get better with the terminology.

There is no need for any other ACTION QUERY after this - INSERT does just this simple task of putting a new row in table.

UPDATE will alter an existing row in a table.

ExecuteNonQuery is a method that takes an ACTION QUERY and performs it.

I did not know that I did not need to use ExecuteNonQuery after the INSERT, thanks

Have you played a lot in a query window hand typing any INSERT's or UPDATE's?? And of course lots of SELECT's so you can see the changes done by the INSERT's and UPDATE's??

You need to get familiar - really familiar - with the DB operation. I think you are in a tough spot writing code without a stronger foundation in the data side.

Yes I have be doing some queries. But most of the are simple SELECT queries. I have not tried too many inserts and updates, but will start doing it more.

I understand that I need a better foundation. It's the reason I started the thread. I also know that I hardly no anything about databases because I only worked with 'one' my whole career before I retired.

I really don't mean this to be rude, but I think sometimes people don't remember what it was like when they first started learning about databases and didn't know a thing. Well, that's exactly where I am.

But I do thank you for your help, Sz. I know you are VERY good with databases and I appreciate your insights.

[dbasnett]

...Have you played a lot in a query window hand typing any INSERT's or UPDATE's?? And of course lots of SELECT's so you can see the changes done by the INSERT's and UPDATE's??...

I have this real love / hate relationship with DB's. I do what you suggested, using the query window, before I write a single line of code. A couple of months ago I got access to a DB Admin person. She speaks DB better than I do VB. A real eye opener in how much I don't know.

[jumper77]

I think a lot of people have that love/hate thing for databases.

Okay, I'm now able to do an update at the command-line. The one thing you must have (in the queries I did) is the ID value. I could change the first name in a row if I knew the ID. Which brings me to something that I've wondered about all along... In code, how do you know what the 'right' ID is?

edit: I can visualize how you can do it with a GridView because you have access to all of the information in a 'selected' row.

edit: if you don't know, I'm doing a console app

[wes4dbt]

I think a lot of people have that love/hate thing for databases.

Okay, I'm now able to do an update at the command-line. The one thing you must have (in the queries I did) is the ID value. I could change the first name in a row if I knew the ID. Which brings me to something that I've wondered about all along... In code, how do you know what the 'right' ID is?

edit: I can visualize how you can do it with a GridView because you have access to all of the information in a 'selected' row.

edit: if you don't know, I'm doing a console app

If your updating a row then you would have already retrieved the row from the database using a Select statement.

If you want to update customer '12345', Unique Identifier

select * from Customers Where ID='12345'"
make updates
Update .... Where ID = '1234'

If you don't know the ID to begin with then it's more complicated.

Select * from Customers Where LastName = 'Smith'
This could bring back multiple rows, so you need to cycle thru them
For Each row as Datarow in yourDataTable.Rows
    If "this is the Smith I'm looking for" Then
        row("address") = "new address"
        Update ... Where ID = row("ID").ToString
    End If

This is just a quick example, you could use parameters

[szlamany]

I did not know that I did not need to use ExecuteNonQuery after the INSERT, thanks

I did not say this - I actually kind of said the opposite.

ExecuteNonQuery is a .Net method.

UPDATE is a SQL Action statement.

INSERT is a SQL Action statement.

You execute these statements against a database by using ExecuteNonQuery.

You see - if you play a real lot in a management tool with doing an INSERT followed by a SELECT - each time hitting what probably looks like a little RUN button to do the execute - you will get a better understanding of the fact that you are simply throwing TEXT STATEMENTS at a parser who will do your command against the database.

ExecuteNonQuery is just the method used by a RUN button.

The context of the query window is what goes into CommandText.

But again - I have to say that using brand new .Net methods that you have no experience with leaves you in the spot of not knowing if the SQL is wrong or the CODE executing the SQL is wrong.

That's a burden you can eliminate by simply playing in some SQL query window.

[edit] matter of fact I see you are debating using a grid view or making a console app. Why not create your own little management tool - put a simply text box for entering some SQL. A Run button to do the EXECUTE - and a result window to show the results. This would make you a SQL expert without stretching into a bunch of new .Net areas.

Then once you have that mastered - SQL Expert yourself - then do a grid or something more complicated.

Just a suggestion - I don't want to step on sitten's toes here...[/edit]

[Sitten Spynne]

wes4dbt is right:

If you want to UPDATE, you have to know the ID. Sometimes, like in a contact list program, you might've loaded the whole list into memory. Then you know every ID. Other times, like in a huge customer records system, you don't.

Think about how a program like this would work, though. People don't memorize IDs, they memorize things like customer names. So suppose Alice is an employee updating a record for the customer "Firestone". She'd do something like this:

• Type "Firestone" into some "customer name" search box.
• This would execute a SELECT query to list all customers with the name "Firestone". The SELECT would get the name and probably the ID.
• The results are displayed on the screen. She double-clicks one.
• This tells the program which customer she wants to edit. A new SELECT is issued, using the ID to single out a row. This SELECT gathers ALL of the details for the customer.
• She messes with the form that displays the data, then clicks "Save".
• This issues an UPDATE with the new information, using the ID that was fetched in step 2.

This is how most CRUD software tends to work. A user asks for some records, and internally you keep track of the IDs. When the user edits/updates records, you use that ID to tell the database which rows to modify.

So the workflow is almost always:

10 SELECT
20 (maybe) DETAILED SELECT
30 INSERT or UPDATE or DELETE
40 GOTO 10

DataAdapter follows a similar pattern with different words.

You give it a SELECT statement that picks out a lot of rows and their data (and also INSERT, UPDATE, and DELETE as needed.) This is sort of like the "detailed SELECT". Then you call Fill() to populate a DataSet with those rows and display them to the user somehow. The user makes edits, and you update the rows. At some point, you call AcceptChanges() on the DataSet, then Update() on the DataAdapter. This causes it to issue the correct INSERT, UPDATE, and DELETE queries on your behalf.

[jumper77]

If your updating a row then you would have already retrieved the row from the database using a Select statement.

If you want to update customer '12345', Unique Identifier

select * from Customers Where ID='12345'"
make updates
Update .... Where ID = '1234'

Wes, are you saying that you UPDATE a row where the ID is one less than than the SELECT query? And if so, is it because IDs are not zero based? (don't mean to be too dumb)
_________________________________________________________
SZ:

That's a burden you can eliminate by simply playing in some SQL query window.

[edit] matter of fact I see you are debating using a grid view or making a console app. Why not create your own little management tool - put a simply text box for entering some SQL. A Run button to do the EXECUTE - and a result window to show the results. This would make you a SQL expert without stretching into a bunch of new .Net areas.

I wasn't exactly deciding on whether to use a console or form app. I was considering which one would be better as a learning tool. I already have the form app with a DataGrid on it. All the form does is load the database into the GridView when it first comes up.

I was leaning towards the console app because that eliminated the need to learn a lot about the GridView. But I'm also thinking that a form app would be more of a 'real world' type project, and you would have access to all the information based on user selection.
_________________________________________________________

DataAdapter follows a similar pattern with different words.

You give it a SELECT statement that picks out a lot of rows and their data (and also INSERT, UPDATE, and DELETE as needed.) This is sort of like the "detailed SELECT". Then you call Fill() to populate a DataSet with those rows and display them to the user somehow. The user makes edits, and you update the rows. At some point, you call AcceptChanges() on the DataSet, then Update() on the DataAdapter. This causes it to issue the correct INSERT, UPDATE, and DELETE queries on your behalf.

Good information. I played with DataAdapter and DataSets in the very beginning. But just a tiny bit.

It's appearing that I must go back to my form app at this time. I don't think I can anymore without it. It needs to be a real world application. Not as complicated of course, but enough where I can get information from the DataGrid. I need to have access to the Grid to implement what we've been working on.

Now: I hope everyone is having a great day and life is treating you good

[wes4dbt]

Wes, are you saying that you UPDATE a row where the ID is one less than than the SELECT query? And if so, is it because IDs are not zero based?

No, sorry that was just a typo. You use the same ID for Select/Update/Delete

[jumper77]

No problem my friend... You had me wondering.

Thanks

[szlamany]

Wes, are you saying that you UPDATE a row where the ID is one less than than the SELECT query? And if so, is it because IDs are not zero based? (don't mean to be too dumb)

This question leads me to believe that you still have a "my data is a variable" kind of association.

Data in a table is just like data in a spreadsheet (s/s). The idea of a primary key is to have one column in the s/s with a unique value for each row.

It makes logical sense to people like us to use a sequential number - with no duplicates (to satisfy the unique requirement).

Now why is it unique?

So that when you UPDATE a row it's going to always find the correct single row to update.

Since it's not an array of data - there is no off-by-one or starting at zero 'ness even remotely possible.

So with that said - you could have SSN as the unique value if you were doing a human resources type of system. Matter of fact our entire health care network (Anthem BC/BS-type world) still uses the SSN as the primary key identifier for there data.

Database design people of today realize that's a bad idea - since SSN's can be wrong and need to be adjusted - they make a poor unique id to identity Joe Smith.

So - just to reinforce this.

A table of data is a matrix - just like a spreadsheet - with rows and columns. You design each column to fill a particular need in describing the record of data in the domain of that table (a "customer" or a "patient" or whatever that table models in real life).

Now - once you get that matrix of data into your code you might be tempted to refer to the first row as #1 (or #0) - but that's only meaningful on your program side.

The "data" side - where the UPDATE is going to actually happen - only knows about each cell in each row of the spreadsheet that we are calling a table here.

[jumper77]

This question leads me to believe that you still have a "my data is a variable" kind of association.

I guess I do. I guess because what in to in code is manipulate data using variable names. It would be hard to think otherwise. But I do understand that it's real data that's being stored in variables. Tangible data. Other than the spreadsheet example. Can you give me the correct way to think about the data?

Data in a table is just like data in a spreadsheet (s/s). The idea of a primary key is to have one column in the s/s with a unique value for each row.

Now - once you get that matrix of data into your code you might be tempted to refer to the first row as #1 (or #0) - but that's only meaningful on your program side.

The "data" side - where the UPDATE is going to actually happen - only knows about each cell in each row of the spreadsheet that we are calling a table here.

I understand what you mean about referring to the first row being 0 or 1. But I really only thought of it when I was working on the admin tool. Since the 'unique' ID was always 0 (in the code), I tested the queries WHERE the ID was 1,2,3..... but in code, I think of it as a means to get the correct row.

There are many things I need to learn, and there's no way to understand it all in a single thread. Even if I had all the tools I needed in my brain, using the correct tools is usually not enough. There has to be an effort to make the 'correct' way of doing things implemented in a real life setting.

Let me know if you have that 'way' of looking at things that will help me understand things more.

[jumper77]

Sz. I just thought of a question I wanted to ask. Do you 'ever' use a global piece of code to reference the database?

[jumper77]

I would also like to ask something else... I use the code below to make the DataGrivd update itself. The code is something that I pieced together. But is it a good way to do it?

I imagine there's a much better way to do this.

    Public Sub RefreshData()
        'makes the gridview update after SQL updates, inserts, etc.
        Using objConn As New SQLiteConnection(connectionString),
               da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
            da.Fill(dt)
            Me.dgvCustomer.DataSource = dt
        End Using
    End Sub

[szlamany]

I'm not sure I see how that last post does any update - it's a SELECT to get data from the database into client code side.

As for you other question - I do not write traditional database applications. I've always created what would be called a 4GL - fourth generation language. When I ask for a grid of data I get back metadata first that tells my 4GL how to build the grid - what to call the columns - headings, width - special edit capabilities for cells. Actually before this metadata is more general meta data on how the screen itself is going to handle this record - maybe MODIFY is disabled because it's an historical record - stuff like that.

So in a way that's a much more global piece of code. It doesn't matter if I'm showing vendors to be maintained in a financial package, participants in a guardian ad-litem social workers scenario - bids for contractors to download for purchasing departments. Every single one of those user experiences is using a single routine to serve out the data to the GUI.

4GL's make me happy - I've done this on 4 different hardware/software platforms since 1980

Each one lasted a decade plus!

Had to write out own database engines until we started using SYBASE (what MS purchased to make MS SQL decades ago - MS SQL is NOT a Bill Gates product!)

[Sitten Spynne]

Do you 'ever' use a global piece of code to reference the database?

I'll intercept, but I think szlamany will agree. This is a weasely question.

The right way to write .NET code is to write classes that either instantiate the objects they need or ask for them via parameters and properties. Programmers have been preaching the evils of globals/static for longer than I've been alive.

Sometimes, it's easier to just dump all your objects into properties on a Module. Then you don't have to worry about passing them around. "Aha," you'll chuckle, "I showed those fancy C# guys, I'm going to be done in half the time!" Sooner or later, you get in a bind. Something puts the object in an odd state, and since the Module property's referenced from umpteen different places, the only way you can figure out where to put breakpoints involves "Find References" and trial and error. It's much easier to manage objects with lifetimes if you pass instances around between instances.

I wouldn't say I 'never' use global database code. I don't think ADO .NET is a good fit for it, considering Command objects tend to have a lifetime of "one method call" and they're the workhorses. Maybe a globally-available connection. In a very small project. Where I'm going to be the only developer. But debugging's just easier if you stop leaning on Modules.

What I would much rather do, if I had a burning urge to use a Module, is write a Module that manages its own connection/command/data reader objects and returns types like Customer so the things calling it don't even know a database is there. For example, think about the difference between System.IO.File.ReadAllText() and using a System.IO.StreamReader. ReadAllText() is probably using a StreamReader inside, but it's easier to use ReadAllText() when you don't care about the details.

But I still don't like it. Static code cannot participate in a pattern called Inversion of Control that is critical for implementing unit tests. Whatever I make static, I can't test. So I don't make static code.

[jumper77]

Had to write out own database engines until we started using SYBASE (what MS purchased to make MS SQL decades ago - MS SQL is NOT a Bill Gates product!)

love it, love it, love it!!! I have been posting this for a while and no one ever commented on it. When I said I only worked on 1 database? It was SYBASE. I also mentioned how MS 'mysteriously' came out with SQL Server not long after that You're a good egg, I think I like you

Sorry... where was I...........

As for you other question - I do not write traditional database applications. I've always created what would be called a 4GL - fourth generation language. When I ask for a grid of data I get back metadata first that tells my 4GL how to build the grid - what to call the columns - headings, width - special edit capabilities for cells. Actually before this metadata is more general meta data on how the screen itself is going to handle this record - maybe MODIFY is disabled because it's an historical record - stuff like that.

That sounds like a great way to go. And if you make your own 4GL, you should almost be able to do anything. The fact that you can use it as is in different product applications is really good. That has to be the ultimate in code reuse for databases.

[jumper77]

This is a weasely question.

??????? is that taking a shot?

I do understand that globals are not a good thing to do. I've known that from the days of C coding. But I have seen people in C++ shops that thought just 'one' object was so important that they used it as static/global. I don't share this thinking. If I try to do it, I already know that it is WRONG. I won't say I've never used one, but they were only a crutch to lean on until I understood what was going on and how to solve the issue without using globals.

[jumper77]

post edited for content

[szlamany]

I took your "global" reference to more or less mean "a single location for that logic" - as would be a class in modern .Net OOP jargon. Not using a module or something global/static.

Sooner or later, you get in a bind. Something puts the object in an odd state, and since the Module property's referenced from umpteen different places, the only way you can figure out where to put breakpoints involves "Find References" and trial and error. It's much easier to manage objects with lifetimes if you pass instances around between instances.

Such a huge difference in requirements between client side code and backend methods.

When you write web applications - so you have a browser doing an AJAX (asynchronous) request to a server - you are asking the server to RUN a SINGLE FUNCTION.

That function will start and when it finishes it deliver a payload back to the browser.

So you aren't building up a class with references here and there for some long period of time.

You are actually doing something 180 degees opposite - you are wanting something to start and finish as fast as possible.

You can have dozens of people hitting the SAVE button at the same time in browsers all over carnation.

Goal is to load bare minimum logic into place - process request - leave the function.

There is no meaningful FORM LOAD moment where you instantiate a class and build it's layers and works it's methods.

It's - "I was asked to deliver a single recordset for this table with this primary key" - what do I have to do to accomplish that?

Since we have dozens of possible CONCURRENT requests we better make it multi-threaded (a nasty requirement of web backends) - and we better make it fast as possible.

When you have a traditional WIN FORM - you might create a class at the start of the app - do initial loads of all vendor names, for instance - and then be used for the duration of the UI running.

With a backend web method that server starts a thread for each request from each browser! Your program is running 50 times at once - all in the same memory space - but just once function (or different ones as browser requests flow in).

When you have a backend web method - if you want to make something "remembered" from one "request" to another "request" you have only one place to store it. So now all your "global" knowledge is put in a single place so having a class to hide and manage it doesn't have the same meaning as it would in this case. And you better not put too much into this "global - remembered" space (whatever too much is!)

Sure you keep your logic clean and singular - that's always a requirement of coding. If you code the same thing twice in two different place you deserve the pain you get back from that.

Mendhak told me a long time ago that I was destined for web programming - I could not imagine leaving win forms at that time.

Now I have this crazy freedom - my same backend .Net web methods talk to browser and Android apps that I write.

It has changed my life.

That and this second bottle of chianti...

[jumper77]

____

[jumper77]

It has changed my life.

That and this second bottle of chianti...

______________________________

[jumper77]

Sz, when I said I did one database project, I didn't say but it was web based (sort of). No where near what you're doing by a long shot. But it still was fun. (here goes the old man again...) It was back in the day where there were no cell phones. Everybody used pagers. I wrote an email server for a company in Seattle. It would take emails, examine them and send it to peoples pagers, but the email still went to their company account. It also had to strip off any attachments before sending it to the pager, but keep the attachments for their regular mail account.

They wanted to have it so their people out in the field could respond to emails or know they needed to call the office. I was a fun project

[szlamany]

I always liked those kinds of projects - a bit of hardware and a bit of software and it looks so good when shown at the end!

[jumper77]

This is my last code of the night (thank goodness). Because of going back to the form app, the way I was doing things needed to be altered. I took Sitten's example of an insert and modified it to insert a new row into the database. I need to know if this looks ok or not. And if you're wondering, yes it works I worry that it's done in such a way that would make it harder on me as time goes by. Or make me think I should have done it another way... let me know what you think.

first, the sub itself:

    Public Sub addNewCustomer(customers As List(Of Customer), firstName As String, lastName As String, address As String, phoneNumber As String)

        Dim insertStatement As String = "INSERT INTO customer (firstName, lastname, address, phoneNumber) 
                 VALUES('" & ":" & firstName & "','" & ":" & lastName & "','" & ":" & address & "','" & ":" & phoneNumber & "');"

        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                With command
                    .CommandText = insertStatement
                    .Parameters.Add(New SQLiteParameter("firstName"))
                    .Parameters.Add(New SQLiteParameter("lastName"))
                    .Parameters.Add(New SQLiteParameter("address"))
                    .Parameters.Add(New SQLiteParameter("phoneNumber"))
                End With

                For Each customer In customers
                    With command
                        .Parameters("firstName").Value = customer.FirstName
                        .Parameters("lastName").Value = customer.LastName
                        .Parameters("address").Value = customer.address
                        .Parameters("phoneNumber").Value = customer.phoneNumber
                    End With

                    command.ExecuteNonQuery() 
                Next
            End Using
        End Using
    End Sub

And here's the calling method

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles btnAdd.Click

        Dim db As New CustomerDatabase(databaseName)

        If txtFirstName.Text = "" Then
            MsgBox("First Name cannot be blank")
            Return
        ElseIf txtLastName.Text = "" Then
            MsgBox("Last Name cannot be blank")
            Return
        ElseIf txtAddress.Text = "" Then
            MsgBox("Address cannot be blank")
            Return
        Else
            If txtPhoneNumber.Text = "" Then
                MsgBox("Phone number cannot be blank")
                Return
            End If
        End If

        'build a customer object
        Dim customer As New Customer
        With customer
            .FirstName = txtFirstName.Text
            .LastName = txtLastName.Text
            .address = txtAddress.Text
            .phoneNumber = txtPhoneNumber.Text
        End With

        'add customer object to List(of Customer)()
        dbcustomers.Add(customer)

        db.addNewCustomer(dbcustomers, txtFirstName.Text, txtLastName.Text, txtAddress.Text, txtPhoneNumber.Text)
        MainDB.RefreshData()

        Close()
    End Sub