[RESOLVED] Beginner trying to learn SQLite database

[Sitten Spynne]

rowID is an implementation detail of SQLite. In my opinion, you should still make an Id column for rows that don't have a different way to be uniquely identified. If you don't, it's harder to decide you want to use a different database later. Or, put another way, the SQL you learn won't work quite right on any other database, and you'll have to re-learn a few things later.

If you dig in the documentation, you'll find that "PRIMARY KEY INTEGER" columns are an alias for rowId anyway, so it's not like it's wasting space.

[jumper77]

Hey, thanks for that. Guess I was doing it the right way at first (by luck) but just didn't know it. So I'll go back to using an ID column as primary key.

Now I have another question....
I know I'm just throwing stuff at the wall trying to do this, but can't get it to work. I'm trying to load a table/dataset in the form load event. But there are some issues with it that I didn't see at first. Maybe someone knows about this.

When I add da and ds to the watch list, ds looks fine, but da has an error inside it at 'da.selectCommand'. Here's the error:

+ SelectCommand (System.Data.Common.DbDataAdapter) 'DirectCast(da, System.Data.Common.DbDataAdapter).SelectCommand' threw an exception of type 'System.InvalidCastException' System.Data.Common.DbCommand {System.InvalidCastException}

But... I don't get an error when stepping through the code. The code is below. Does anyone have some ideas?

thanks

       ' connectionstring =  Const connectionString As String = "Data Source=customers.db;Version=3;"
        Dim da As New SQLiteDataAdapter
        Dim dt As New DataTable
        Dim ds As New DataSet
        Dim objConn As New SQLiteConnection
        Dim objCommand As New SQLiteCommand

        objConn = New SQLiteConnection(connectionString)
        objCommand = objConn.CreateCommand()
        da.SelectCommand = New SQLiteCommand("select * from customer", objConn)

        objConn.Open()

        da.Fill(ds)
        dgvCustomer.DataSource = dt

[szlamany]

I just read up on SQLite and this odd RowId that is exposed to users.

https://www.sqlite.org/autoinc.html

Yikes - this is just horrible. Forget it exists! You won't find it elsewhere (like in MS SQL) and the re-use and assignment algorithms scare the heck out of me!

[Sitten Spynne]

Sometimes the debugger isn't very smart, and can confuse itself. One question: why do you call CreateCommand() then never use the command you get back? That's more idiomatic than calling the SQLiteCommand constructor. Other than that, I don't know. I don't use the DataAdapter, but I'm sure someone else might be able to fill you in.

Don't use Load. Use Shown. If an exception's being thrown, it's certain that Load is hiding that.

[Sitten Spynne]

I just read up on SQLite and this odd RowId that is exposed to users.

https://www.sqlite.org/autoinc.html

Yikes - this is just horrible. Forget it exists! You won't find it elsewhere (like in MS SQL) and the re-use and assignment algorithms scare the heck out of me!

Same. SQLite is very much like Access: compared to a "real" database like MSSQL there are quite a few backwards things. It's "good enough" for local client databases that don't want to support multiple concurrent users. Get outside that case, and it starts getting foolish to use SQLite. No one sane would back an e-commerce or banking site with it. But a phone application's data? It's a good fit.

jumper77: be careful reading into implementation details. SQLite is documented as it is because it's fully open, and they want everyone to understand how it's supposed to work. But many, many features are intended only for advanced use cases, such as the above linked page's discussion of re-use and other concepts. The takeaway is supposed to be: "Don't try to guess what rowID will be assigned, instead create a row and ask via query."

Storytime: MSSQL is no saint either. At my last job, we drank the TFS kool-aid. It stores lots of things in MSSQL, among them build errors/warnings as part of continuous integration. It turns out that 300 people submitting builds all day, with failed builds producing many thousands of errors/warnings very quickly overflowed the auto-increment ID column used to store those. Apparently that thought never crossed Microsoft's mind. What happened in response?

The entire TFS system broke. No one could check out code, no one could check in code. MS's solution? Basically a "DROP TABLE; CREATE TABLE..." script. It took four days to complete, and failed twice, so we lost two weeks of development effort.

So do make sure, when choosing auto-increment as an ID column, that you're reasonably certain having several billion rows won't be a common case. If it is, you need a better ID scheme. GUIDs come to mind as a good one.

[jumper77]

Ok, I have read the posts from both of you and it's time to say something I'm thinking. I think that using your own ID field does not make sense. Once I found out the database did it anyway, I was not going to mess with it. Why would you want to add another layer of complexity when it's not necessary?

"Don't try to guess what rowID will be assigned, instead create a row and ask via query."

You say that creating a your own auto-increment field would help you queries. I really don't understand how using your own ID has any benefits because rowID is already there. I mean... what does it buy you? Maybe if you could explain more about why it's a good thing, I could understand better. I'm not trying to fight the issue, just trying to understand it

And about running out of unique row Ids, This is something I will never have to worry about. And the numbers it can use is HUGE!

And S, I can't believe you said "Real" database. I would call a "real" database the one that fits the needs of the application being built. I'm not a fan of SQL Server and I've seen it fall on it's face too many times. As I told you before, in one shop I worked at, we tried to make MSSQL work for about 6 months, only to realize that it would never work for our application. We tossed it out the door and used another database much like SQLite

That's bizarre about running out of ID numbers. Were you trying to build your own source control software? Just curious. And YES, in that situation, I think a GUID might be a good way to go.

Note: I'll be leaving the house in a little while to take care of some business out of town, but should be back in a few hours.
Thanks guys

[szlamany]

It's really rather simple.

This RowId internal value is kind of unique to SQLite from what I can see.

Seems this is an accepted fact as if you create a simple INTEGER PRIMARY KEY field on a table in SQLite it uses the RowId internal value.

With that said if you want to use an ascending sequential integer id # for a primary key make a field.

That way if you port to another SQL database you can use this same common practice of making a primary key field.

If you make reference to the internal RowId value without using a normal field you are making yourself married to SQLite.

The link I sent you said that AutoIncrement in SQLite is to be avoided. That advise seems like bad advise. Without AutoIncrement there is a possibility that a "deleted" RowID will be re-used on a future new record. This is bad in my opinion - very, very, very bad.

That is what made me say to forget RowId even exists.

In SQLite it will use it for you when you make a simple PK of integer type.

And I would suggest that you carefully consider the AutoIncrement keyword in SQLite. I find re-use of keys from deleted records to be a deal breaker.

[wes4dbt]

Seems like a lot of extra steps, this is how I would do it

        Try
            Using con As New OleDbConnection(My.Settings.ContactsConnectionString), da As New OleDbDataAdapter("select * from contacts", con), dt As New DataTable
                da.Fill(dt)
                Me.DataGridView2.DataSource = dt
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

A DataAdapter will open the connection for you if it's not open. On more complex things, especially in loops, I do open the connection first because it helps the speed.

Also, especially in the form load event, always put your code in a Try/Catch block. Sometimes Form Load will ignore errors and keep on running without letting you know.

[jumper77]

Thanks sz for making things clear concerning IDs and auto-increment. Portability is a good enough reason to do it that way. Sounds like good practice.

And thanks Wes. Lot of good information. I have never heard of errors being skipped during the form_load event. And when I can remember, I usually put things in the shown event.

Thanks for the code too. It's good to know. Right now I'm just trying to get things to even work But good practice/knowledge is always a good thing to hear about. And I'm going to try it right now.

Thanks to both of you.

[Sitten Spynne]

I agree with szlamany's #47, though I'm not quite as leery of reused auto-increments, but this also comes from some assumptions I'm making.

It's only going to happen around an overflow scenario, and it's only going to use IDs that aren't currently being used. In order to be burned by it, you'd need to query a large set of rows and cache them while the 'last' ID is close to overflow. Meanwhile, some other process would have to delete a handful of rows. Then, you decide to add a few new rows. The database reuses one of the deleted IDs. Then, you update your cached records and submit an UPDATE. Whoops, you just overwrote some data by re-introducing a deleted row.

I think you have to do some really dumb things to get in that scenario. SQLite is bad at doing the things you'd be asking it to do. If you are caching data for that long, or accessing the database from multiple processes, or you routinely expect to reach row counts of 4 billion, no sane developer would suggest SQLite.

All of this falls way outside the parameters of an application that should be using SQLite. It's good for single-user applications that generate dozens or hundreds of rows. The most intensive program I've used with it generates a little south of one million rows per day. It will take it 11 years to overflow at this rate, and it never caches data in memory so it won't even notice the reuse. I don't make any assumptions about the ID. I'm pretty sure it won't notice when it overflows.

You have to be doing some really stupid things with SQLite to get burned by this feature. Those things might not be so stupid with MSSQL. That's why MSSQL makes stronger guarantees: it satisfies requirements that MSSQL will never require.

And S, I can't believe you said "Real" database. I would call a "real" database the one that fits the needs of the application being built.

I don't like it either, but SQLite is only one notch better than Access, and I could never bring myself to call Access a "real" database.

See the above. There's plenty of common database scenarios where SQLite's developers have intentionally decided to omit some safety because they don't want SQLite to be anything more than a simple solution for a local file database. It is extremely important to understand that when I say "MSSQL is too complex for a tutorial" that "for a tutorial" part is the important part of the phrase. I wouldn't use SQLite to back a website, or anything else that can have multiple concurrent users. But you can learn a lot of lessons with SQLite that make learning the nuances of MSSQL/postgres/mysql much easier.

That's bizarre about running out of ID numbers. Were you trying to build your own source control software?

No, this was an unaddressed issue in Microsoft's TFS, the only source control system so bloated and convoluted it uses MSSQL to store its data.

[szlamany]

It wasn't just the re-use of deleted PK's - it's that it's using a different algorithm when you use Autoincrement keyword. Who knows how those algorithms will change over time. The Autoincrement seems to force the behaviors of assignments and gaps closer to MS SQL (maybe that's the intent).

I also have problems with IDENTITY keys in MS SQL - as gaps are ugly if the intent is a sequential ascending value (which Identity does not guarantee for you).

But this is kind of off-topic now...

[Sitten Spynne]

Yeah. I think we're both more or less on the same page as to whether SQLite is extremely robust. It's more like "good enough", and for that sacrifice you get easier configuration.

Let's talk about databases again. This is going to be a disappointing lesson. But each of these is a building block. This is one of the blocks that goes at the bottom, so skip it at your peril.

Lesson One - Connections, Or The Lesson That Does Nothing

First, let's make sure we're on board with setting up SQLite in our project. If you're using MSSQL, you're on your own, though there's probably some similar steps, and it's a little easier because MS generally installs it for you.

One way to set it up is to download the SQLite DLL from the SQLite website, put it somewhere on your hard drive, and make sure you reference that DLL. That's probably what you're doing now. Want to keep doing it? Skip ahead. There's a slightly more convenient way.

Somewhere around VS 2013, a 'package manager' called NuGet was adopted from the open-source world by Microsoft. Since Microsoft adopted it, it suddenly lost all its cooties and everyone in the world but VB developers considered it OK. What does a package manager do? It lets someone who develops a library like SQLite set up an automatic way to add the right DLLs and references to a .NET project. You can use NuGet from the command line, and MS did invest some work in PowerShell commandlets that make it nice and easy. You can also right-click in your project and get the same thing done. A benefit to using NuGet is if you're using it, you can send your project file to someone else without the SQLite DLLs, and when they open/build it the package will magically be installed. This is very convenient if, say, you plan on uploading a zipped project to a forum that doesn't like you to include DLLs in zip files. It also means you can guarantee everyone using your project has the right version of the DLL, another common source of frustration on forums.

So I just made a new console project. If I right-click the project, in the menu I see a "Manage NuGet Packages..." item. I click that. I click on the "Browse..." tab, and I search for "SQLite". Now, you get presented with many options. You do NOT want the one named "SQLite .NET". That is a sort of ORM for SQLite that is useful in some cases, but will be absolutely useless if we want to learn how ADO .NET works. It'd be like "learning to ride a bike" by "riding a bus". The right one is called "System.Data.SQLite" and has about 700k downloads as of right now. I clicked it, then clicked the "Install" button that appeared in the summary. This summoned a dialog that told me which DLLs were being added. It's adding support for Entity Framework, and some other things I don't care to use. If I were really picky, I could find a SQLite package that doesn't come with all of that. I don't feel like being really picky, we'll deal with some extra dependencies right now.

Time passes. DLLs are downloaded. References are added. When the dust settles, I have a project ready to use SQLite. Joy. Let's actually talk about connections, now.

The real part
SQLite's types live in the namespace System.Data.SQLite, if you're using the same library as me. MSSQL uses something else, probably just System.Data. If you try using some of my code and it complains it doesn't know what the type is, make sure you've added "Imports System.Data.SQLite" at the top. If you do that and it complains it doesn't know what that is, make sure you added references/the package properly. If you're using MSSQL, do something different.

ADO .NET tries to be an abstraction of database operations. So for every type we want to use, there's an Interface or MustInherit class that represents the concept. The benefit to you is, in theory, if you write a lot of code that uses SQLite, you won't have to make many changes to convert that program to use MSSQL. I can say from experience that promise is mostly kept, though it's a big deal to change database engines. I'm going to talk about the interfaces first, then point out the types that implement them.

In ADO .NET, you need to have a "connection" to a database to use it, even if it isn't a networked database. Connections are represented by types that derive from IDbConnection. SQLite named theirs SQLiteConnection, MSSQL names its own SqlConnection, and other databases each have their own connection. Every connection implements IDbConnection, so they all have the same methods and properties. These are the most important:

• The ConnectionString property represents a string used to configure the connection. Each database has its own format for connection strings. It usually includes a path or address to the file/server, username/password information if needed, and other things you may or may not care about.
• Open() has to be called before you use the connection. What it does is database-specific, and you'll get reminded via exceptions if you forget to Open() your connection.
• Close() closes the connection. Dispose() exists as an analog of Close(), though they aren't guaranteed to do the same thing. Because stupid. Just keep in mind you'd better be manually calling Close() or, more commonly, using a Using statement.
• CreateCommand() creates a 'command object' associated with the connection. I'm not talking about those in this lesson. But remember it's there.

If we want to create a connection, we have to call a constructor. To construct a SQLiteConnection, we're going to need a connection string. Hm. It turns out there's a handy reference at Connection Strings dot com. You can see a lot of options there. You can, on your own, go read about them and play with them. All we care about is the "Basic" connection string. It is a semi-colon delimited set of 'name=value' pairs. It expects a path to a file for "Data Source", and after that it expects "Version=3". If it were MSSQL, we'd need to specify an IP address, maybe a username and password, etc.

Armed with that, it's easy to get a connection ready. This is how my humble tutorial application begins its life:

Imports System.Data.SQLite

Module Module1

    Private Const ConnectionString As String = "Data Source = test.db; Version = 3;"

    Sub Main()
        Using connection As New SQLiteConnection(ConnectionString)
            connection.Open()
        End Using
    End Sub

End Module

It does nothing! If it works. You might be asking what kind of an idiot makes a tutorial that does nothing. Well, it's more complicated than that.

If you can build and run this, you win! You learned about all you need to know about making connection objects, and you can properly create a .NET project that depends on SQLite. If anything goes wrong, you did something wrong! You need to figure out what went wrong and fix it. I recommend just starting a new project, and following the steps again. The reason I stopped here is so we can make sure there's something to start from. If you don't get this far, then everything beyond will fail. Plus, also, my lunch break is over.

The next thing to do: create a table. We'll have to learn about the command object to do that, and the three ways it can be used to submit queries. Then, I'll spend a little time on each type of command we know. When that's been covered, it's time to talk about how to make life easier with helper code. And, if you really twist my arm, I might even talk about DataAdapter.

[jumper77]

That's ok Sz, I don't consider that off-topic.

Somewhere around VS 2013, a 'package manager' called NuGet was adopted from the open-source world by Microsoft. Since Microsoft adopted it, it suddenly lost all its cooties and everyone in the world but VB developers considered it OK. What does a package manager do? It lets someone who develops a library like SQLite set up an automatic way to add the right DLLs and references to a .NET project. You can use NuGet from the command line, and MS did invest some work in PowerShell commandlets that make it nice and easy. You can also right-click in your project and get the same thing done. A benefit to using NuGet is if you're using it, you can send your project file to someone else without the SQLite DLLs, and when they open/build it the package will magically be installed. This is very convenient if, say, you plan on uploading a zipped project to a forum that doesn't like you to include DLLs in zip files. It also means you can guarantee everyone using your project has the right version of the DLL, another common source of frustration on forums.

Sounds like a really cool thing, but I've been trying to install several NuGet packages for SQLite, and it always fails. I guess I must be doing something wrong because it even failed on getting the NuGet Core. All of the NuGet packages that I've tried to install fail with the same error:
'Unable to find package...'

PM> Install-Package NuGet.Core
Install-Package : Unable to find package 'NuGet.Core'
At line:1 char:1
+ Install-Package NuGet.Core
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: ( [Install-Package], Exception
+ FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PackageManagement.PowerShellCmdlets.InstallPackageCommand

PM> Install-Package System.Data.SQLite.Core
Install-Package : Unable to find package 'System.Data.SQLite.Core'
At line:1 char:1
+ Install-Package System.Data.SQLite.Core
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: ( [Install-Package], Exception
+ FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PackageManagement.PowerShellCmdlets.InstallPackageCommand

PM>

You get the idea. Do you know what may be going wrong? I've installed other things before and they worked. Maybe it's caused by an update I did about a month ago. I installed: Update 2 of VS.

About System.Data.SQLite... I can't use it because I'm using something else. It's different, here's the import statement:
Imports Finisar.SQLite

I'm guessing you might 'fuss' about this one because the project is now closed. I found it by looking for something that was .Net specific. However, it would probably be a good thing to use something still supported. I can try system.data.sqlite, but it will take a bit to find out how to use it. Some things work without the Finisar reference, but some things don't. So I will have to adjust to new syntax.

Guess I've said enough for now. I'm going to see about using the import you suggested.

[jumper77]

Hey!! It worked right out of the box and I didn't have to change a thing. Big time cool

[Sitten Spynne]

Ha. Sounds like the other one followed the ADO .NET interfaces. It might've even worked with my lessons with some tweaks. If you figured out how to get System.Data.SQLite working, that's fine. I'd stick with that for now. I can't tell you what the heck is wrong with NuGet based on the errors you got, but it sounds like you found another way around. NuGet is nice. When it works.

[jumper77]

I think it 'would' work with whatever you write, just because I didn't have to change 1 single thing. So I'm guessing they must be compatible. Plus I've looked at your code and it seems the same to me (I think).

And about NuGet, when it was working before, it wouldn't load some things, but it never got to the point of not loading 'anything'. And yes it's nice. I have used it on other projects before I came to the forum.

[jumper77]

Here's another 'Hey!'... I've been working so much on the code that I never let the main form come up. Well, I just did and OMG. The dataGridView came up with all the columns and everything. I've never done that before and it looks neat

Sorry... just had to jump up and down for a minute....

[jumper77]

Also sorry for all the posts, but... now that it works, I need to ask something. How do I implement the auto-increment ID in an INSERT command? I don't get it yet.

thanks

[Sitten Spynne]

Also sorry for all the posts, but... now that it works, I need to ask something. How do I implement the auto-increment ID in an INSERT command? I don't get it yet.

thanks

Re-read lesson 0. That table has a PRIMARY KEY INTEGER column, which auto-increments. Lesson 0 has a tutorial about INSERT queries. Thus, it also talks about how you handle auto-incrementing columns with INSERT queries.

Today's lesson will sort of cover it too, as we'll get to working with commands. But it will take me much longer to type that post, and if you can't type an INSERT query at the SQLite console, you will have a harder time understanding VB code that uses it.

[jumper77]

You are correct about Lesson 0. I just found out I've been doing it right after all. I have verified that the ID is auto incremented in the DB table.

So sorry for the question. If I had waited just a little longer before asking, I would have found the answer (but of course there's no way of knowing that).

thanks

[Sitten Spynne]

In lesson 0, I talked about SQLite and SQL. In lesson 1, I cracked my knuckles and made sure my SQLite project worked. Now it's time to actually start talking about using SQLite.

Lesson 2 - Commands

There's a method on the connection object, CreateCommand(), that I glossed over. It's time to show off what it does.

Commands are the ADO .NET concept of 'a thing that can execute SQL'. In order to execute any SQL, you have to create a command, configure it, and make the relevant method call.

IDbConnection.CreateCommand() returns an object of type IDbCommand. This object has several properties and methods, these are the most important:

• Connection is the associated connection.
• CommandText is the text for the SQL query that will be executed.
• Parameters is a collection of SQL parameters and values for the query. That's for a future lesson, but very important.
• CreateParameter() is used to create parameters.
• ExecuteNonQuery(), ExecuteReader(), and ExecuteScalar() are the subject of this lesson!

Each database-specific ADO .NET implementation will provide its own types. For example, the SQLite implementation uses SqliteCommand, the MSSQL implementation uses SqlCommand, etc. Note that the connection's CreateCommand() returns an IDbCommand, not the specific type, so you usually have to cast with CType() after calling it.

Note that IDbCommand is IDisposable, so you should always make sure to call Dispose() or use a Using statement.

Why are there three 'Execute' methods? Well, different SQL queries do different things, but you can't make an Execute() method that returns different types that makes a lick of sense. So each command has a purpose that tends to match up with a certain kind of SQL query.

ExecuteScalar - Returns one value.
Some SQL queries don't return rows as results, they instead return a single value. This value is called a "scalar". For example, SQL has a function called 'count()' that will count the rows returned by a query and return that.

sqlite> select * from customers;
2|Alice|Programmer|555-555-5555
3|Twilight|Sparkle|555-555-5555
sqlite> SELECT count() FROM customers;
2

See how it just returns the number 2? That's why ExecuteScalar() returns an object. It takes some trial and error to figure out what kind of a number comes back. SQLite seems to like returning a Long, and I screw up and try to cast it to Integer. Do note that the documentation points out there is some tricky behavior around 'null':

• If no results are returned at all, ExecuteScalar() will return Nothing.
• If a result is returned, and that result is null, ExecuteScalar() will return DBNull.Value.

This gets newbies and veterans all the time. The database does not use Nothing to represent null, and we've learned that it means many things in VB anyway. Be very, very careful that when you compare database values, and you expect null, you use DBNull.Value.

So, how would we use this in code? It's pretty simple. You need a connection. You use it to create a command. You set the command text, then call ExecuteNonQuery(), casting to the result you expect and praying that's what you get.

Using countCommand As SQLiteCommand = connection.CreateCommand()
    countCommand.CommandText = "SELECT count(*) FROM customers"
    Dim result As Long = CLng(countCommand.ExecuteScalar())
    Console.WriteLine(result)
End Using

Don't forget to Open() the connection!

ExecuteNonQuery()
Some SQL queries don't really return results at all, instead they do things to the database. INSERT, UPDATE, and DELETE come to mind. Those are the commands ExecuteNonQuery() can represent. ExecuteNonQuery() returns an Integer, and that Integer usually indicates the number of rows that were affected by the query. That can be a decent sanity check: if you execute a DELETE you think should delete 10 rows, and 20 get deleted, you know you screwed up. If, for whatever reason, it doesn't get a result, it returns -1.

Using it is just like wiht ExecuteScalar():

Using deleteCommand As SQLiteCommand = connection.CreateCommand()
    deleteCommand.CommandText = "DELETE FROM customers WHERE id = 3"
    Dim result As Integer = deleteCommand.ExecuteNonQuery()
    Console.WriteLine(result) ' Probably 1 or 0.
End Using

ExecuteReader()
This is one I'd love to make its own lesson, but it really feels wrong stopping short of here. When you make a SELECT query that returns rows, ADO .NET can't know in advance what kind of objects you want to put that data in. So the best it can do is return some object that represents the rows and columns of a table and let you sort it out. You may be thinking, "Aha! DataTable!", but that's thinking a little too far ahead.

IDbCommand.ExecuteReader() returns an object of type IDataReader. This object has several important methods and properties:

• FieldCount tells you how many columns are in the current row.
• Item(Integer) and Item(String) are 'indexers', that let you treat it like an array or Dictionary.
• Lots of Get???() methods exist, including a more general GetValue(). These all return the value for the column with the index you give them, either as an Object or the type the method is named after.
• IsDBNull() tells you if a column contains DBNull.Value, which seems silly until you realize GetInt32() and several other Get methods can't return it.
• Read() tries to move to the next row. If it runs out of rows, it returns False. If there is a row, it returns True.

That's a lot to keep in mind! But it's simple once you've used it a time or two.

IDataReader is IDisposable, I think you know what that means by now. I think you can also guess that, depending on which ADO .NET provider you're using, you might get SqliteDataReader, SqlDataReader, etc.

I can't just paste a code example here, you have to think about how to use this. The general pattern is you get your IDataReader from ExecuteReader(), then start a While loop that ends when IDataReader.Read() returns False. Inside the loop, you use the indexers or Get methods to get the values out of each row's columns. Odds are those values are bound for an object, but they don't have to be.

It looks a little like this, if you can imagine a Customer class:

Using connection As New SQLiteConnection(ConnectionString)
    Dim selectQuery As String = "SELECT id, firstName, lastName FROM customers"
    connection.Open()
    Using selectCommand As SQLiteCommand = connection.CreateCommand()
        selectCommand.CommandText = selectQuery

        Using reader As SQLiteDataReader = selectCommand.ExecuteReader()
            While reader.Read()
                Dim thisCustomer As New Customer()
                thisCustomer.Id = CInt(reader("id"))
                thisCustomer.FirstName = reader("firstName").ToString()
                thisCustomer.LastName = reader("lastName").ToString()

                Console.WriteLine("This is {0} {1}, with Id {2}.",
                                  thisCustomer.FirstName, thisCustomer.LastName, thisCustomer.Id)
            End While
        End Using

    End Using
End Using

I included more code there, because it's important to know what columns you are working with. You either have to memorize indexes (which makes editing the command a pain) or make sure you spell column names right. They might be case sensitive, so consider using constants to eliminate mistakes.

It probably seems tedious. It sort of is. Slowly, as we learn more concepts, I want to start talking about "how to make this less terrible" as opposed to "how to make this work". There's no time like the present. Do not get in the habit of scattering database code throughout your program. Whether you are using DataAdapter or IDataReader, you should make sure database code is in a special place.

So let's show off a bit of code that demonstrates all of the concepts put together. This is what my submission for a 'challenge' to write an app demonstrating these concepts would look like. It will create a database (deleting one if it already exists), then add a few rows. It will update some of the rows, delete some rows, printing the contents of the database along the way. Pay close attention to how the database code is separated. Note how, instead of returning things like IDataReader, I return objects like Customer? This is an important way to hide ugliness. Good developers do this almost every chance they get.

You keep getting hung up on "How do I know the ID if it's auto-incremented?" This project shows one solution: When I call GetAll(), I get all of the data in the database as a Customer object. Each Customer object has an Id property that represents its database Id. This can go pear-shaped in a hurry if there are multiple users accessing a database at the same time. We're not worried about that right now, and don't need the ridiculously sophisticated solution.

Someone is going to criticize this and say I hit the database too many times, or that I could cache connections, or that I could keep the Customers in memory. This is true, but it's also a tutorial. I felt like the code was easier to follow when I did these 'bad' things. When writing database code for real, we do want to minimize connections, especially when those connections are made over a network. But when we're hacking out our initial functionality, it's OK to be a little sloppy so long as we actually make the time to clean it up. I find I don't tend to do that, so in 'real' code I try to pay attention.

Look it over. Change it. Enhance it. Break it wide open then put it back together. The next lesson will be easier: parameters are just lipstick on this.

*edit*
Ha. I didn't actually use ExecuteScalar(). Whoops. I don't tend to use it all that much, but it could fit, in a few places. For example, I could do a "SELECT count()" in GetAll() to pre-initialize the array. That'd save on some memory allocations. Oh well.

[jumper77]

Ok, just to regroup here... The code below works and shows the use (although not the best) of the ID field. I'm hard coding the ID but just wanted to show that it worked

I've been told that there's an example coming up soon with a much better way to handle the ID field. I anxiously await

    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',
                             'First Name' Text Not NULL, 'Last Name' Text Not NULL, 
                             Address Text Not NULL, 'Phone Number' Text Not Null);"
                        .ExecuteNonQuery()

                        .CommandText = "INSERT INTO customer ('ID',
                          'First Name', 'Last Name',
                           'Address', 'Phone Number') VALUES('1','Tom','Jones',
                          'Sin City','555.4444');"
                        .ExecuteNonQuery()

                        .CommandText = "INSERT INTO customer ('ID', 
                            'First Name', 'Last Name', 'Address', 'Phone Number') 
                            VALUES('2','Baby Face','Johnson','Jackson Wy', '555.4242');"
                        .ExecuteNonQuery()

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

[szlamany]

Your CREATE TABLE has a problem.

The first field is just called ID - without a data type and without the keywords that would make it the primary key.

[jumper77]

Thanks Sz, before I try to apply that, is this how it should be done?
'ID as integer'

[szlamany]

I do not do SQLite - but I can see your AS syntax is not in use in the CREATE TABLE you have in the code.

Regardless - you should test a CREATE table in the management tool and make sure you understand it.

I thought sitten has a post here with exactly how tables get created in SQLite.

[Sitten Spynne]

Lesson zero.

Heck. Let's make a table of contents, and pretend I didn't retroactively add it.

Table of Contents

• Lesson 0: SQL/SQLite introduction. CREATE TABLE, SELECT, INSERT, UPDATE, DELETE. Link to the syntax pages/SQLite documentation.
• Lesson 1: NuGet, setup, IDbConnection.
• Lesson 2: IDbCommand, focus on ExecuteNonQuery(), ExecuteReader(), ExecuteScalar(). Attached example of a working application with a layer of abstraction.

[jumper77]

Thanks again for 0
And thank you Sz

edit: I'm on it... head down

[Sitten Spynne]

To review:

CREATE TABLE customers (
  Id INTEGER PRIMARY KEY,
  FirstName TEXT,
  LastName TEXT,
  PhoneNumber TEXT);

I keep typing INTEGER PRIMARY KEY because that's important. Without that part, 'Id' is just "a column", has no special meaning, and if you don't provide a value it has null as a value. SQL is just like VB: you can't leave words out of your program because you don't want to type them. There's nothing magic about the column name 'Id'. I tend to use it because it's obvious, and I expect if I tell someone "this is the Id column" they'll assume it's a primary key, or at least unique.

INSERT INTO customers (FirstName, LastName, PhoneNumber) 
    VALUES ('John', 'Smith', '555-555-5555');

This is an INSERT query into that table. Note how there is no 'Id' column? Because it is INTEGER PRIMARY KEY, I'm not supposed to provide a value for it.

UPDATE customers SET PhoneNumber = '555-123-4567' WHERE Id='1';

This is an UPDATE query for that table. 'Id' is used here, because if we don't specify which rows are being updated then all rows will be updated. So it's generally normal to use Id here, but you have to know the right Id.

DELETE is very similar to UPDATE. SELECT only incidentally needs the Id. I'm not really sure why there is so much focus on the Id. You seem to believe it's some SQLite-specific concept. rowId, and how it relates to this one type of column, is a SQLite-specific thing. But ask szlamany:

Hey, szlamany, do you use primary key columns in MSSQL? Are they typically integers? Do they autoincrement?

If you want to talk SQL, then we can get into a deep discussion of what a primary key is and the functions it serves. But that is not a VB discussion, and I can't lie: everything I pretend I know will be lifted from some documentation somewhere.

[jumper77]

Thanks S, I'm not going to reply a lot because I need to keep my head down for a while and read your post and learn how to do this. I'm a little frustrated because I've been stuck on this part about ID's for far too long.

I'll get it though... promise

[jumper77]

Hah! I think I have it now. I know you've been beating it into my head S, but as I always say... it's pretty thick. I just got this to work about a minute ago:

And 'believe it or not', I have been reading the documentation over and over about everything. Don't want you thinking I haven't.

.CommandText = "Create TABLE customer('Id' INTEGER PRIMARY KEY ASC, 
        'First Name' Text Not NULL, 'Last Name' Text Not NULL, 
         Address Text Not NULL, 'Phone Number' Text Not Null);"

         .ExecuteNonQuery()

edit: please tell me this is right <sigh>

[Sitten Spynne]

I don't know if the ASC is strictly required, but I say leave it! I think I saw something, somewhere, about DESC doing something special. I think it's just better to learn what this kind of primary key looks like and stick with it until there's a reason to use something else.

[jumper77]

Thank goodness. I'll try it without the ASC just to see. I'm sure it doesn't need it, but I want to be sure. I had an image that I was going to upload to show what the end result was in the program, but I can't get the attach files thing to work.

[jumper77]

Just tried it without ASC and can't tell any difference.

No more posts for the night, have to cut grass in a minute, plus that will give me more time afterward to do more things with the project.

[wes4dbt]

I don't want to slow progress but after reading a little about SQLLite and what I consider "different" behavior, I think maybe SQLExpress may be easier to learn. It at least use the same conventions as SQL and very simular to an Access database. As far as I know it's still free. Maybe something to consider for future projects. Would probably be easier to get help, you don't see very many SQLLite posts on this forum. Lots of SQL, SQLExpress, MySql, Access.

[Sitten Spynne]

That has been a constant gripe in this thread. Here's my deal.

I don't use SQL Express. I don't plan on it. I don't have room in my career for Microsoft-only solutions.

If someone else can write a guide to:

• Setting up a SQL Express project.
• Deploying that project to another machine.
• Why it even matters for a one-table application doing no-join SELECT queries.

That would influence me to try and write my future articles with more of a tilt towards SQL Express.

But right now, the only written statements are that I should totally be using it. I'm not convinced. And so far I'm the only person writing tutorials. And so far, I'm the least qualified person in the thread.

So help me write. SQLite is what I know. I'm not going to try and teach what I don't know. If you don't want to help write, I can't help you.

[wes4dbt]

SS,

That has been a constant gripe in this thread

I don't know if your off your meds or what? I'm the only one who has mentioned SQLExpress in this thread and I mentioned it once in the previous post.

But right now, the only written statements are that I should totally be using it

I never said that, all I said was

I think maybe SQLExpress may be easier to learn

Maybe something to consider for future projects

But don't worry, I won't in this thread anymore. I'll leave you alone to play with your mental blocks.

[Sitten Spynne]

I'm not trying to chase you out. It could be some other threads that are blurring together. It seems every time I mention SQLite someone breaks out in hives. It's a bit frustrating, considering how widespread it is in my work.

It's not perfect, and there's plenty of places where I totally agree SQL Express does things better. But because my work hasn't led me to use SQL Express so far (most of my programs end up running on Linux/Android/iOS, and before that I did zero database work since about 2005), I'm literally ignorant about it. If it's easy to set up out of the box, I'd love to know, because the Windows community tends to treat anything non-MS like it has cooties. If it's easy to deploy, I'll raise a dang beer to it because MSSQL was a nightmare to deploy with an application back in 2004. The story went something like, "You should use merge modules, but we're not publishing merge modules for MSSQL." Thanks, MS. I had to hand-edit an MSI custom action in ORCA to launch the installer.

I'm also rigging the game. I'm focusing on stuff SQLite does well. If you try to do advanced things with it, it's going to fall on its face and I wouldn't recommend it for those things. All I want to do is teach how to do CRUD, and for simple CRUD SQLite's plenty sufficient. I'm a bit peeved that I've had 4 or 5 people yell at me for using SQLite, because 4 or 5 years ago it was me saying "Ugh don't use Access for this" with everybody screaming "TOOT TOOT, ACCESS IS ENTERPRISE READY, DON'T LISTEN TO THE C# GUY". SQLite has made me raise my eyebrows as much as Access ever has, but I don't have to buy Office to deploy SQLite so I forgive its flaws.

Seriously. If someone writes out how to get SQL Server Express in a project, I'll do the extra work to make examples that work with it. Or if someone else wants to transliterate what I'm writing into SQLSE I'll be fine with that too. But I've got about 1,200 pages of Xamarin documentation I should be reading instead of working on this, so please forgive me if I don't sidestep to spend a day learning how to set up a new database engine. You've spent that day. I bet you could teach me in an hour. If you teach me, and I like SQL Express, we both win.

Also I take great offense to the phrase "off your meds", if you had a close relationship with people who had depression you'd be ashamed.

[jumper77]

Come on boys. Let's play nice. Ok, back to our regularly scheduled program... I finally 'get' the idea behind Primary Key and Auto-Increment. If I'm wrong, just tell me. When I create a database table, I define one column as Primary Key. Primary Key will turn into an Auto-Increment automatically if you don't reference it anymore.

Here's what I mean:

  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,'First Name' Text Not NULL,
                             'Last Name' Text Not NULL, Address Text Not NULL, 
                             'Phone Number' Text Not Null);"

                        .ExecuteNonQuery()

                        .CommandText = "INSERT INTO customer ('First Name', 'Last Name', 
                               'Address', 'Phone Number')
                               VALUES('Tom','Jones','Sin City','555.4444');"

                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    'Do something here.
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

Plus, here's the code that works with a window that allows the user to enter a new record/row.

   Public Sub addNewCustomer(firstName As String, lastName As String, 
                 address As String, phoneNumber As String)

        Using objConn As New SQLiteConnection(connectionString)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "INSERT INTO customer ('First Name', 'Last Name', 
                              'Address', 'Phone Number') VALUES('" & firstName & "','"
                               & lastName & "','" & address & "','" & phoneNumber & "');"

                        .ExecuteNonQuery()
                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
        MainDB.dgvCustomer.Refresh()
    End Sub

So I'm glad I got this through my head. Many thanks to Sz and SS.

[GaryMazzone]

I would suggest one thing. Yes you can create field names with spaces in them. Second NEVER EVER create field names with spaces in them. I am a DBA and would turn any code for development back to the developer if they sent me tables with fields containing spaces in them. They are hard to work with and a mess to remember that they need special consideration to use. If you want to display the field name to a user with that space then alias the column in your select statement:

Select FirstName as 'First Name', LastName AS 'Last Name' from usernames

[jumper77]

Hi Gary, let me look at this for just a minute and I'll post a reply. And thanks for the help.