[RESOLVED] Beginner trying to learn SQLite database

[jumper77]

Hi everyone, I've heard about people using lists with databases and would like to learn how to do that. I would also like to use SQLite as the database because SQL Server would be overkill for what I need.

I know it's tough to post a thread like this, because so many people have their own way of doing this. But I'm going to give it a shot.

thanks a bunch.

edit: if SQLite is a bad way to go, I'm cool with that.

edit2: I just read where someone suggested not using a database at all (if it's just a small amount of data). Not sure how you would store the information though. If I knew the format, I could save information in an XML file. I already have a XML file that contains the records I want, so that might be a good way to go.

[Recoil1980]

Well, there are several ways to store data. One way is to store everything in text files. You can move up to encrypt them. XML files are good too, but are tedious to work with IMO. Access DB is another small solution, but has been depreciated using that over the years (even though I still use it a lot for my projects). Moving to SQLite is the next step up, and there are literally hundreds of examples that are easy to find.

It really depends on what you want to eventually use it for. The easiest thing I can suggest is to make a new project for new concepts you want to use so you will get a better grasp of how to handle everything. Then you can plan and port that functionality into a project.

[jumper77]

Hi Recoil, thanks for replying. Quick question about encrypting the text files. I already have an encryption routine, so that's not a big deal. You talk about using Access for a project, I would like to (even though people advise against it). However, I haven't found a version of Access that's free. I used it in shops before, but they already had licensed copies.

Thanks about the POC idea. It might become ugly code, but I would at least understand the basics.

thanks

[Recoil1980]

I missed the question about the encrypted text files. And no, Access is not free, I just have experience with it from school, like 10 years ago, LOL.

If you have experience with Access already though, SQLite would be the best route for a flat-file DB because you already understand how to query with SQL functions.

[Sitten Spynne]

I'd strongly recommend against Access. It's not free, there's some pitfalls getting it to play nice in the presence of x64 machines or versions of Windows newer than XP that vary wildly depending on circumstance. A lot of the world's moved on to SQLite, but Access was the de facto solution for a long time so there's lots of VB tutorials about it, which tends to lead people to believe it's the only solution.

There's some good bickering that could be had about if SQLite is "a good database", but for the purposes of "learning database access" there's nothing that presents a problem.

"Learn to use lists with databases" is a very vague question. I think you'll get the most traction if you try to implement some program that uses a database for storage, then ask questions when stuck or, if you finish, ask for comments about how you designed it.

Database questions are much tougher to field, as they ask the person on the other end to do some setup or you have to distribute a sample database file.

[jumper77]

Hi S, good to hear from you.

There's some good bickering that could be had about if SQLite is "a good database", but for the purposes of "learning database access" there's nothing that presents a problem.

I have read a lot about SQLite and think it might a good way to go. But I know that people who work in an MS environment might not approve.

"Learn to use lists with databases" is a very vague question.

I think you'll get the most traction if you try to implement some program that uses a database for storage, then ask questions when stuck or, if you finish, ask for comments about how you designed it.

Thanks for the suggestion. I'm sure that's a better way to go about this.


And the reason I talked about using Lists with databases is because I can't remember something I read about it. So yes, it's vague to me too. Maybe what I was looking at were classes to deal with databases and using the right tools for storing data inside the software before it's written back to the database.

Database questions are much tougher to field, as they ask the person on the other end to do some setup or you have to distribute a sample database file.

Ok. I understand that. So I will have a go at doing it myself before asking questions.

thanks

[jumper77]

I believe this issue is resolved. Thanks to everyone who helped.

[Sitten Spynne]

I mean, conceptually, there's a lot of things to approach.

Your program talks about its data in terms of the objects that represent the data. There are Person and Invoice and Customer classes, because those are what the code manipulates. I'm going to call these "domain types", that's a common professional term for them.

When we write to a file, we have to find a way to represent those objects, usually as "lines" or "an XML element". To assist with this, we tend to write "in between" code that knows how to turn "an Invoice" into "XML" and vice versa. This is sort of like how France and Spain share a border but do not speak the same language, so to interact they need people who speak both languages.

The database only knows how to talk about tables and rows. When you execute a SQL SELECT statement, what you get back is esentially a giant, comma-separated text file where each line represents a row. If, for every result set, you had to write code to parse that text into the relevant objects, you could. But it would be a pain. So we use a translator. ADO .NET acts as the translator. It interprets the text and returns a DataReader (maybe an IDataReader, I'm not sure.), which is a type that represents the text as many rows separated into columns. It even goes so far as to parse the text values into appropriate .NET data types, when the column's type information allows it. Sadly, since it's a very old .NET API and predates Generics, it operates in terms of Object variables so you have to mind your casts. There are many ADO .NET tools that try to help you further by creating DataSets or DataTables that take the metaphor even further, trying to give you something as convneient as possible.

But, you might notice, none of those constructs are Invoice, or Customer, or any of the objects that our program knows how to deal with. So while ADO .NET takes the burden of a lot of text parsing away from us, we still have to implement code to convert those ADO .NET objects like DataTable and DataReader into the relevant domain types. Many, many programs don't take this extra step. For small enough programs, it's fine. But I find my code works overall better if I do take that extra step.

When I write code, I draw two lines in the conceptual sand:

                             |  In between, I convert from  |
On this side, I only use     |  DB types like DataReader to | On this side, I only use 
domain objects like Invoice. |  and from domain types like  | db types like DataReader.
                             |  Invoice.                    |

Those are high-level concepts to keep in mind, and I want you to get there. But first, you need to learn the basics.

I think your first steps should be to focus on just loading and saving some random objects. Learn how to create a connection. Learn how to execute INSERT, SELECT, UPDATE, and DELETE queries. Learn the difference between INSERT, UPDATE, and how to figure out which you need. Learn about SQL parameters, and get used to using them early, in fact you should endeavor to never write queries without using them.

We call a basic database editing app 'CRUD'. It stands for "Create, Remove, Update, Delete". Ignoring all concerns for architecture, you should work at the above concepts (and ask questions when stuck) until you can write two or three different CRUD applications without needing help from the forums. (It's OK if you have to look up documentation, or re-read the forums. Finding answers ourselves is as good as knowing the answers.) What's an example of a CRUD application? The contact list we did before is one. A check register's a good one. A to-do list is a good example. Practically everything we do involves CRUD in some form. Don't bother with 'fancy', it will slow you down. You're trying to learn, here.

When you can write a functional CRUD application, you're operating at a level where you can start asking questions about writing a good CRUD application.

[jumper77]

Thanks for the help. It's very much appreciated. I will take your suggestions and start just trying to understand then design (loosely) the database. I'll pop up with a question when needed.

getting to work now

[jumper77]

I'm proud to announce that after hours of work, I have SQLite working in Visual Studio. I haven't done much yet, but I can create a database. Everything from here will be smooth sailing compared to setting everything up.

[jumper77]

edit: On Hold for now

Hi everyone, I have just started working with databases and for the most part, I'm clueless. I'm trying to do an INSERT statement and somehow get the results (row with values?).

I did post this in the Database forum, but haven't heard a word in hours.
thanks for looking

Here's the code:

       'Declare the main SQLite data access objects
        Dim objConn As New SQLiteConnection
        Dim objCommand As New SQLiteCommand

 Try
            objConn = New SQLiteConnection(CONNECTION_STR & "New=True;")

            objConn.Open()

            objCommand = objConn.CreateCommand()

            objCommand.CommandText = "Create TABLE customer(ID Int PRIMARY KEY Not NULL,
                firstName Text Not NULL, lastName Text Not NULL, phoneNumber Text Not NULL, Birthday Text Not Null);"
            objCommand.ExecuteNonQuery()

            objCommand = objConn.CreateCommand()
            objCommand.CommandText = "INSERT INTO customer (ID, firstname, lastName, phoneNumber, birthday)
            VALUES(1, 'Paul', 'Jones', '222-6666', 'May 16 1954');"

            objCommand.ExecuteNonQuery()

        Finally
            'close the connection
            If Not IsNothing(objConn) Then
                objConn.Close()
            End If
        End Try

[wes4dbt]

edit: On Hold for now

Hi everyone, I have just started working with databases and for the most part, I'm clueless. I'm trying to do an INSERT statement and somehow get the results (row with values?).

I did post this in the Database forum, but haven't heard a word in hours.
thanks for looking

Here's the code:

       'Declare the main SQLite data access objects
        Dim objConn As New SQLiteConnection
        Dim objCommand As New SQLiteCommand

 Try
            objConn = New SQLiteConnection(CONNECTION_STR & "New=True;")

            objConn.Open()

            objCommand = objConn.CreateCommand()

            objCommand.CommandText = "Create TABLE customer(ID Int PRIMARY KEY Not NULL,
                firstName Text Not NULL, lastName Text Not NULL, phoneNumber Text Not NULL, Birthday Text Not Null);"
            objCommand.ExecuteNonQuery()

            objCommand = objConn.CreateCommand()
            objCommand.CommandText = "INSERT INTO customer (ID, firstname, lastName, phoneNumber, birthday)
            VALUES(1, 'Paul', 'Jones', '222-6666', 'May 16 1954');"

            objCommand.ExecuteNonQuery()

        Finally
            'close the connection
            If Not IsNothing(objConn) Then
                objConn.Close()
            End If
        End Try

Earlier post said you create the database already, why are you creating a table at run time.

I'm trying to do an INSERT statement and somehow get the results (row with values?).

Not sure what that means, do you want to get back the values you just inserted? You already have them. If you want to retrieve data from a database then you would use a SQL "Select" statement.
Here is a post by jmc thats a good way to start. http://www.vbforums.com/showthread.p...ses&highlight=
Don't worry that it's use an SQL database, most of the SQL is the same.

If you search for "visual basic ado net tutorial" there's lots of information

[jumper77]

Hi Wes, good to hear from you.

Earlier post said you create the database already, why are you creating a table at run time.

Actually when I said I created the database, I meant at run time. It's only called if the database does not exist.

Not sure what that means, do you want to get back the values you just inserted? You already have them. If you want to retrieve data from a database then you would use a SQL "Select" statement.
Here is a post by jmc thats a good way to start. http://www.vbforums.com/showthread.p...ses&highlight=
Don't worry that it's use an SQL database, most of the SQL is the same.

If you search for "visual basic ado net tutorial" there's lots of information

I have only just come to think that I should try a select statement outside the the 'CreateDatabase()' sub. I'm thinking (and hoping) that it will work then.

And yea, I did hear from jmc, from my post in the database forum.

And for the last, I'll do more searching for tutorials. But I've been searching all day long for anything you can think of <laughs>.

[wes4dbt]

This should keep you busy for a while. https://msdn.microsoft.com/en-us/library/jj943772.aspx

One bit of advise, START SIMPLE. I've noticed you like to run before you can walk. You'll end up spending less time if you start simple and once you understand the basics, then start building in more complex code.

[jumper77]

Thank you wes, and yes I'm guilty running. Probably has to do with my years of experience before I retired. And yes, I will start simple. I get ahead of myself and forget about 'baby steps'.

I really appreciate your help. Looks like I've go some reading to do

[jumper77]

Damn, just found out that my first form has lost all it's components. That will probably fix the issue.

I've spent enough time on this trying to make it work, and I give up. So I'm asking questions. The code below is taken from a MSDN project where they tell you what code to insert where. But this one has problems. I get an error on every piece of code that's bold. Also, if it helps, this code was taken from the MSDN link that Wes gave me.

And there are two more classes that are doing the same exact thing.

First is InitializeComponent. Error is: x is not declared and may be inaccessible... yada yada
Next error type is on all the buttons. Error is: handles clause requires WithEvents variable

I know these errors are caused by something simple, but I don't know what that is. I've never had an error on a Button Click before.

Any help appreciated.
thanks

Namespace SimpleDataApp
    Partial Public Class Navigation
        Inherits Form
        Public Sub New()
            InitializeComponent()
        End Sub

        ' Open the NewCustomer form as a dialog box, which will return focus to the calling form when it closes.
        Private Sub btnGoToAdd_Click() Handles btnGoToAdd.Click
            Dim frm As Form = New NewCustomer()
            frm.Show()
        End Sub

        ' Open the FillorCancel form as a dialog box.
        Private Sub btnGoToFillOrCancel_Click() Handles btnGoToFillOrCancel.Click
            Dim frm As Form = New FillOrCancel()
            frm.ShowDialog()
        End Sub

        ' Close the application, not just the Navigation form.
        Private Sub btnExit_Click() Handles btnExit.Click
            Me.Close()
        End Sub
    End Class
End Namespace

[dbasnett]

If this is from the link that wes supplied my guess would be that you skipped some steps, like this one https://msdn.microsoft.com/en-us/lib...andaddcontrols . If your project doesn't have three forms named like they point out then you did skip steps.

When MS gives in depth walk-throughs you can't skip any steps. My advice is start over.

[jumper77]

Hi db, I found out what the problem was. For reasons unknown, two of my forms lost all there controls (buttons and fields). But thank God for source control. I checked out my last commit and got two of the forms back. I'm working on the third form now.

I'm sure that's what's causing the issue. Will let you know something when I can. But I will be gone for a while today and may not be able to post again for a while.

[jumper77]

Well, I've got all of the above errors corrected. Moving on to the last part of the UI.

[jumper77]

And db, you have so little faith in me. <sigh> Debugging happens to be something I'm pretty good at. And while I'm not that good at OOP, debugging hasn't changed. So my couple of years experience pays off sometimes.

[dbasnett]

It is not about faith, but what wes mentioned, running before walking. You have a lot of visual studio problems that I don't understand and have never encountered. In 10+ years of writing VB code I can count on one hand how many times I have had major issues with VS. That could be because I write really simple things.

When you get the walk through done your debugging skills will show you what is going on.

[jumper77]

Yes, and I told Wes that he was correct about running before walking. Wes has been very helpful. If fact everyone has been helpful, but the only thing you've said is:

If this is from the link that wes supplied my guess would be that you skipped some steps, like this one https://msdn.microsoft.com/en-us/lib...andaddcontrols . If your project doesn't have three forms named like they point out then you did skip steps.

When MS gives in depth walk-throughs you can't skip any steps. My advice is start over.

What you said doesn't sound like you were trying to help. I won't say what it sounds like because I don't want to respond in kind. But I do appreciate your support.

[dbasnett]

Yes, and I told Wes that he was correct about running before walking. Wes has been very helpful. If fact everyone has been helpful, but the only thing you've said is:

What you said doesn't sound like you were trying to help. I won't say what it sounds like because I don't want to respond in kind...

OK. I'll make sure there is no confusion in the future.

[jumper77]

db, I have a question... (or you to Wes). One of the main points in this exercise was to be able to use SQLite instead of SQL Server. The walk-through I'm working on is made to use SQL Server. SQL Server would require learning something else I don't know about. Is it best just to learn SQL Server while I'm at it?

thanks.

[jumper77]

Guess everyone's out having fun
Looks like I'm going to learn SQL Server.

[szlamany]

Well - that ends up being a question for you and an important one before we all get invested in a thread.

You started this thread with a title about SQLite (I guess from another thread with JMC).

Somehow you got on a MS tut about MS SQL - different product.

The difference between using SQLite and MS SQL are probably minimal.

Installing MS SQL and SSMS (Management Studio) is a whole new kind of pain-in-the-arse.

[Sitten Spynne]

SQL server costs money, requires setting up (at least a local) server instance, and is far more complex. If you walk down that path, I'm mostly going to be unable to help.

On the other hand, for the most part, SQL is just SQL. The main differences between SQLite and MSSQL to a .NET developer will be:

• You use classes that behave the same but have different names, like "SqlConnection" instead of "SqliteConnection".
• You'll have to figure out the new connection string format.
• The placeholders for query parameters will likely be different.

There's a handful of other things, but SELECT, INSERT, UPDATE, and DELETE are basically the same on every DB engine.

[jumper77]

Thanks so much, sz and S. Yes, the main point was to use SQLite. I had it working on another application, but still needed to find out a few things. Wes was trying to help by giving me the link to the database walk-through. But because of my usually "thick" head, I didn't think that it would lead me to SQL Server. I should have known though...

And S, I'm going to go back to the 'little' things like you said. Just learning the normal stuff (select, insert, update, and delete). Setting up SQL Server is not an easy thing and not what I had in mind. SQLite is easy and even easier to deploy (if I were going to do that).

Oh.... I'm using a wrapper (I think that's the right thing to say in this case) called SQLite.Net. It's supposed to be an ADO version of SQLite.

So I'm going back to square one. Take my time, and do baby steps (instead of running). Thanks to you both for putting me back on track.

And Wes and db, thank you for helping. I really appreciate it. Don't think any of the message is intended to put you in a bad light, because I did learn some things

thanks to everyone...

[wes4dbt]

The walkthrough was meant to show you the important basic elements of working with a database. It doesn't matter if it's a SQL db or a SQLLite db.

I my opinion you need to learn these basic tasks,

1. How to connect to a database
2. How to Add records
3. How to Update records
4. How to Delete records
5. How to Retrieve records

I've written database applications in BBX, dBaseIII, Clipper, C+, VB3-VB6, VB .Net and it always come down to understanding those 5 elements.

[jumper77]

Hi wes, you are correct about the things I need to learn. I'm working on the things you point out right now, but I've gone back to SQLite. I think throwing SQL Server in the mix was a bit much for what I wanted to learn. Plus I'm using SQLite.NET which is an ADO version of it.

I AM taking this seriously and will work on 1 through 5 for a while. Just that, is enough to keep me busy for quite some time.

And Wow... you were back in the days I was. I never expected to see dBaseIII or Clipper mentioned on the forum. When I was in college, I used to work in the computer lab. At night I would help students with their homework, and that included dBaseIII and dBaseIV.

[szlamany]

2. How to Add records
3. How to Update records
4. How to Delete records
5. How to Retrieve records

Commonly referred to as CRUD - create, read, update and delete

[Sitten Spynne]

It should also sound like familiar advice:

I think your first steps should be to focus on just loading and saving some random objects. Learn how to create a connection. Learn how to execute INSERT, SELECT, UPDATE, and DELETE queries. Learn the difference between INSERT, UPDATE, and how to figure out which you need. Learn about SQL parameters, and get used to using them early, in fact you should endeavor to never write queries without using them.

[szlamany]

SQL parameters are a MUST- 100% of the time - don't skimp on this one...

Since it is a security concern, being able to say you use ONLY SQL parameters in a program - 100% of the time - is a good thing. Red flag otherwise...

My database classes actually query the DB for the parameters of SPROCS at runtime - nothing is known in advance or hardwired in code.

[jumper77]

Hi guys, I see you both approve of using parameters. I haven't read anything about it, but I've seen it mentioned. I'm glad you caught me early on this too. I just now opened a database and used INSERT, and for the first time, it worked Before I didn't know if it worked at all because the SQLite admin program doesn't show data. Only tables and fields.

But I just now learned how to use the command-line query tool and can verify that the values ARE in the database. So I'm going to check out parameters. May have some questions about that too because I have no idea of how to use it.

sz, I had to look up SPROCS (plus add it to my spell checker). I remember a long time ago that ads for a db person always mentioned stored procedures as a must for getting hired. I'm guessing it's still that way.

Once I get through all the CRUD (makes it sound like it came from a Linux person), I'll checkout parameters and SPROCS.
Plus, I'm all for not repeating code.

[Sitten Spynne]

I think this time I'm going to take a different approach to see how it goes. I'll still answer questions. But occasionally, I'm going to pick one step of the process and explain it as thoroughly as I see fit. And if a question comes up about that topic again, I'm going to link to that post if I feel like the answer is contained within. This time there's no challenges, other than "If you really learn what this thread will be about, you can write any application that uses databases, albeit perhaps with some effort."

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.
• Lesson 3: IDbParameter, parameters in general, stern advice to only use parameters from now on.

Lesson Zero - The one that's currently off-topic.
For the first topic, let's make sure you're familiar with what a database is, or, more specifically, a "relational database", which is how most SQL databases call themselves. Lacking these fundamentals makes it sort of moot to figure out how to use VB .NET to access the database.

No one's asked this question. No one's suggested this discussion. I think it's important, because if you can't write SELECT, INSERT, UPDATE, and DELETE queries then you won't get very far with ADO .NET. If you can't fire up a command-line SQLite client to peek at your tables, it will be harder to debug. (Optional: find a GUI client, but you can't really escape having to write SQL.) And I don't know exactly how much you know about these things, so I think a whirlwhind tour of the very basic concepts of SQL will do someone, somewhere, some good. Don't want to read it? Move on and wait until I actually talk about ADO .NET.

So for this part, you don't need Visual Studio. You can play along if you have some tool that lets you edit/manipulate a SQLite database. But I'm using the command-line client that comes with most SQLite installs. If it's on your PATH, you should be able to just type 'sqlite3' plus a filename to get busy. Try that out. If it doesn't work, make sure you have a sqlite3.exe somewhere on your machine. If you don't, you should probably get the SQLite Tools for Windows from the downloads page and make sure the right folder's on your path. Or, if you have a GUI tool you like that can execute SQL, use that.

It also helps to have the syntax page ready at all times. SQL is generally the same in all databases, but sometimes there are tiny differences.

All databases are fancy ways to store data in files. Relational databases represent data as tables. Tables are made of columns and rows. A 'column' represents some property of a data item, and usually has a type. A 'row' represents an entire data item. This is analagous to objects in OOP: a table is like a list of some class type. The columns represent the properties of the class. The rows represent instances of the object.

In SQLite, 'type' is a very loose concept. A column can have a type, but SQLite doesn't get too angry if you put the wrong type of data in the column. So even if you define a column as numeric, you can put a String inside of it. This can be really convenient if you're in a hurry and prototyping. It can also be a disaster later in a project. Thankfully, ADO .NET's Object-focused API helps us remember we can't trust the data types of columns. (This is one of the few times I'll praise this decision.) But do remember: the onus is on YOU to make sure you put the right data in the right column.

To create a table, we use a CREATE TABLE SQL statement. It generally asks you to define a name for the table, then a list of columns with types and any 'constraints'. The types that matter, for now, are INTEGER, REAL, and TEXT, and you can imagine what they stand for. Two other things can matter:

• AUTOINCREMENT means an INTEGER column will be automatically be given a value one greater than the last value that was assigned.
• PRIMARY KEY means a column represents the 'index' of the row, it must be unique.
• INTEGER PRIMARY KEY is automatically AUTOINCREMENT, so it's actually a syntax error to try and make AUTOINCREMENT INTEGER PRIMARY KEY.

Those two usually go together, because an AUTOINCREMENT INTEGER column is going to be unique.

So here's a table for some imaginary Customer type with a first name, last name, and phone number field:

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

The Id column is important: if we don't have a way to uniquely define which row we're talking about, editing or deleting them can get ambiguous. I think it's legal to have two contacts with the same name and/or phone number, so none of the other columns are good for uniquely identifying a row. When that's the case, we make an ID column. That has implications in our code, but I'm not talking about code yet.

To put data into the table, we use an INSERT query. The syntax for this query is a little janky, as is most SQL. It goes something like:

INSERT INTO table_name (column_list) VALUES (value_list)

The column list is a comma-separated list of column names. The value list is a comma-separated list of values in the same order as the column list. You don't have to list every column in the table, though the rules for that are a little more complex than this tutorial. Let's say we want to add "John Smith" with phone number "555-555-5555" into the table. That requires:

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

Note it's best to surround string values with ''. Where's the Id column? Well, it's an INTEGER PRIMARY KEY. That means it's auto-incremented and the value is assigned automatically. So we don't have to include it. This is only true for auto-incremented columns. (Or some other cases I won't discuss.)

To check if it worked, we have to learn how to SELECT data. The syntax for this statement is very complex, but what you need to know right now is:

SELECT column_list FROM table_name

The column list is just like it was in INSERT. You can use * as a shortcut for 'all columns'. I don't like doing that, it can make the code end of the equation more difficult. I suggest not making it a habit in code, but right now it can be harmless. Just remember, you don't always have to fetch all columns. Here's how I can check my customer was added:

sqlite> SELECT Id, FirstName, LastName, PhoneNumber FROM customers;
1|John|Smith|555-555-5555

This shows each column's value, and that the ID for this row is 1.

I'm going to add some more rows, then show you the next most important thing about SELECT: the WHERE clause. Add some more yourself. Here's my current data set:

sqlite> select * from customers;
1|John|Smith|555-555-5555
2|Alice|Programmer|555-555-1234
3|Twilight|Sparkle|555-555-5554

Now let's say I only want to get the customer record for "people who have the first name John". You can stick, on the end of a SELECT statement, a 'WHERE' clause that represents an expression. If the expression evaluates to True for a row, that row will be in the results. There are a lot of expressions to learn, that's part of the "learning SQL" part of working with databases. Here's some examples:

sqlite> SELECT PhoneNumber FROM customers WHERE FirstName='John';
555-555-5555
sqlite> SELECT Id FROM customers WHERE LastName='Sparkle';
3
sqlite> SELECT PhoneNumber FROM customers WHERE PhoneNumber LIKE '555-%';
555-555-5555
555-555-1234
555-555-5554
sqlite> SELECT PhoneNumber FROM customers WHERE PhoneNumber LIKE '555-%' AND FirstName = 'Twilight';
555-555-5554

Note that there's a chance of getting anything between 0 and the entire table's worth of results. That is important to remember. Also note that you can have multiple expressions joined with AND/OR.

So we have CREATE TABLE, INSERT, and SELECT. What if "John Smith" changes his phone number? We can't use INSERT, because that'd create a new row. In this case, we need to use the UPDATE command, and this is where the Id finally becomes important. It will look similar to what we've already done.

The syntax for UPDATE is:

UPDATE table_name SET column_sets WHERE expression

The 'column_sets' itself is a comma-separated list of:

column_name = expression

And an expression is, well, an expression.

You can read this in English as, "In this table, update these columns to these values for any row that matches the WHERE expression." Getting the expression right is important: if more than one row matches, all will be updated. That's usually not what you want. So, usually, the Id is used as part of the WHERE expression. For demonstration, using the last data set:

sqlite> select * from customers;
1|John|Smith|555-555-5555
2|Alice|Programmer|555-555-1234
3|Twilight|Sparkle|555-555-5554

sqlite> UPDATE customers SET PhoneNumber = '555-555-5555' WHERE PhoneNumber LIKE '555-%';
sqlite> select * from customers;
1|John|Smith|555-555-5555
2|Alice|Programmer|555-555-5555
3|Twilight|Sparkle|555-555-5555

sqlite> UPDATE customers SET PhoneNumber = '555-123-4567' WHERE Id='1';
sqlite> select * from customers;
1|John|Smith|555-123-4567
2|Alice|Programmer|555-555-5555
3|Twilight|Sparkle|555-555-5555

This demonstrates you can update multiple rows, and that narrowing them down with Id is possible. This is one reason we try really hard to make sure every row has SOME way we can uniquely identify it.

Now, let's say we want to forget about John Smith and remove him from our life. That is the job of the DELETE statement. Its syntax should look pretty familiar:

DELETE FROM table_name WHERE expression

As you can imagine, this is pretty dangerous if you aren't sure that the expression refers to JUST the row you want. Again, an Id column of some sort tends to be used to make sure we only get the rows we want. When debugging or using the console, I like to 'dry run' by using a SELECT with the same WHERE clause before I delete a row.

sqlite> SELECT * FROM customers WHERE Id = 1;
1|John|Smith|555-123-4567
sqlite> DELETE FROM customers WHERE Id = 1;
sqlite> SELECT * FROM customers;
2|Alice|Programmer|555-555-5555
3|Twilight|Sparkle|555-555-5555

That's a whirlwind tour of creating tables, INSERT, SELECT, UPDATE, and DELETE. Those five things represent 90% of the database interactions you will have. If you're trying to do something with a DB, step one is figuring out the SQL query that corresponds to what you want to do. Things do get a bit more complex if the data types are more complex, but for right now I think messing with the JOIN clause for SELECT is a bit too advanced.

Before writing much ADO .NET, you ought to be comfortable with using the command-line tool or some GUI tool to run these queries against a database. The GUI tools are more useful in general, but all ADO .NET code has to execute these SQL queries. If you can't write one yourself, you won't be able to write code that does it.

So the next logical topic is discussing how ADO .NET enables doing those things. That's for another day, I've spent too long already. And someone's going to fuss at me for going "too basic" or being "too whirlwind".

So, important takeaways in bullet form:

• Databases are like fancy files.
• Each table stores one 'type' of data.
• Table 'columns' represent individual properties of data items.
• Table 'rows' represent each data item, and consist of values for each table column.
• SQLite is pretty loose with typing, and supports INTEGER, REAL, and TEXT most importantly.
• PRIMARY KEY columns must be unique for every row, and by default are Integers that auto-increment and do not need to be assigned.
• Most tables will want to have a PRIMARY KEY column, or some other way to uniquely identify specific rows.
• You use a CREATE TABLE query to create tables, though you don't tend to do that as much as other things.
• You use an INSERT query to add a row to a table.
• You use an UPDATE query to change the values in the column(s) of a particular row.
• You use a DELETE query to remove a particular row.
• You use a SELECT query to find rows that match some criteria.

Master those tidbits, and ADO .NET won't seem so hard. Many of these things are also true for MSSQL, mysql, or any other relational database.

[Sitten Spynne]

Oh, also. SQLite doesn't have Stored Procedures (SPROCS). So for that leg, if you want to take it, you'll have to have an MSSQL/mysql/etc. instance laying around.

I'm not stupid enough to call them 'unimportant', but for most applications that should use SQLite it's not a big deal that it doesn't have them. The two important things stored procedures introduce are better performance (because they're in-process with the server) and better security (because the query cannot be tampered with by client-side means.) Applications that need the performance tend to have dozens of other reasons why SQLite is completely inappropriate.

The security is a concern, but I think the idea is that a SQLite database is local, so someone who tampers with their client will only be shooting their own foot off. In short: since SQLite doesn't support remote connections, it won't provide either of the benefits of SPROCS, so they are not implemented.

Parameters are part of CRUD, but be mindful you don't run before you walk. Parameters are important, but easiest to learn if you're already proficient with working in their absence. And working without them for a little while helps you understand the reasons why they are so gosh darn helpful.

When/if I make it to individual kinds of queries, I'll talk about them in general first, then point out how parameters fit in.

[szlamany]

Another reason that SPROCS enhance security is that you only allow EXECUTE permission on SPROCS to your users.

Users never need SELECT or INSERT or UPDATE or DELETE access to tables - as long as you are 100% SPROC.

[Sitten Spynne]

I'm not going to argue they're unimportant. But I don't have experience with them so I wouldn't feel right writing a tutorial about them. My understanding is, aside from the administration concerns, they're roughly the same as writing parameterized SQL queries. So if you want to supplement anything I say with "This is how you'd do it with stored procedures", I'm not going to gripe. I don't think I could explain how to use them. I just kind of know what they are and that the projects I've written haven't needed a SQL engine that supports them. (Which is more a quirk of the applications I write than a general condemnation, most phone apps don't directly know they're using a database.)

[jumper77]

TONS of great stuff guys. And S, this is 'my' thread and you can be as simple or complex as you want it to be. I just hope you save complex for later

No way to respond to anything without making a HUGE post, so just know I agree with all that you and sz are saying. I think one of the biggest things is getting used to the command-line tool and use it to practice queries. I think that might help me more than anything right now. And spending time with it, certainly has no bad effects.

So, I'm off to the first step of knowing what a 'relational' database is (for the 100th time). Meaning I've done it many times before but I'll be darned if I can spit out it's meaning right now

So...... guess I'm off to battle now. Will have questions later.
thanks

[jumper77]

Had my first chance to verify something S said. A rowID IS automatically inserted when creating a database. At first I didn't know that and put an ID field into the query. Not needed, so I modified the software's creating of a database to not include the ID field. It was not apparent that a rowID had been created though. I had to do a query to show rowID to be able to see it. But I downloaded a different Admin software and it shows the rowID by default.

thanks S