[RESOLVED] Beginner trying to learn SQLite database

Quote:

---

I know your kidding me...

---

No I'm not, my guess is it's some kind of wrapper to change the look of Winforms. Probably would need a lot API's. I only mess with API's when absolutely necessary, you know me, I like to keep it simple.

You've learn some basics on how to Create and work with classes, databases. You could expand on those and create a whole functioning application, like maybe your own personal Contacts program. I know there's lots out there but thats not the point. The point is to create a properly functioning real world application.

Really though, this is a hobby for you, so select something your interested in and would enjoy doing.

Here's a good example for themes. I imagine you've seen how the "Advanced System Care" software looks like? That is one of the finer looking themes. It's REALLY good.

I think your suggestion is perfect. I think I have an idea of what I could do (if it's not too specific). I have friends who do animal rescue... and none of the rescues have any software to keep track of people and animals. For example, there's a number of things that someone who works with rescues do. Some people can transport, others can foster for a few days... etc. There's probably about 5 different things people can do.

Then there's the matter of keeping up with the animals (dogs, cats...). Where are they, are they being boarded at the Vet's. Plus people take pictures of the pets (but not everyone). I'll stop here because I imagine you get the idea. They have been wanting me to write some software for all the rescues and maybe now is a good time with all the good people to help me along.

Another good thing, it's really a specific niche. I don't think anyone has written software for these people.

Think about it for a bit. Most of the work would be the database. They don't need anything fancy, just something to help them keep track of things.

If that's something that interest you and you would enjoy doing, then give it a shot. Try to come up with a clear product definition before you start programming. Like what you want to include, Workers/Owners/Pets/Locations...

Also, you need to start a new Thread when you start having questions or problems.

You're a good man Wes. I will start working on that in the morning. I'm going to take the rest of the night off. But I should have some things to talk about sometime tomorrow. As someone told me, don't get too far ahead before you start asking questions (paraphrase).

tanks

I think that's an excellent idea for a project. It's basically a standard CRUD app with enough different entities and relationships to get you really well practised at all the basics. As a side benefit it'll probably give you lots of opportunities to play with puppies and kittens:D

The one question I'd ask going in: how's your database theory? Do you understand how and why you'd relate one entity to another? Are you happy with how you'll break down your domain into individual entities? This is going to be the most important skill in this project and will be worth brushing up on if you're rusty.

Quote:

---

The one question I'd ask going in: how's your database theory? Do you understand how and why you'd relate one entity to another? Are you happy with how you'll break down your domain into individual entities? This is going to be the most important skill in this project and will be worth brushing up on if you're rusty.

---

Morning Dex. I'm glad you think the project is a good idea. My database theory? It's terrible. Do I understand why I would relate one entity to another? Not much of a clue. I barely have enough sense to do just one table. If this is going to be the most important skill, I need to learn some things (a lot of things).

So, where do I buy a clue :) Do you have a link or anything that might help? I need one of those 'dummies' books. 'Databases for 'Dummies'. I actually like those books because they explain things in plain English. So if you can recommend a source for learning this, it would really help.

Some searches

https://www.google.com/search?q=rdbm...esign+concepts

https://www.google.com/search?q=data...utf-8&oe=utf-8

https://www.google.com/search?q=data...concepts+books

Thanks db. I appreciate the help. Also I would like to ask a few things. Should I create a new thread yet, or wait until I know more about database design and project definition.

If it IS time to create the thread, what do you think the subject line should be? And of course that question is for anyone.

Personally I'm not a fan of posting google searches like that. Might as well post links to LMGTFY (which I hate). None the less, they were good google searches and I'll take the liberty of linking to a couple of the better hits:-

http://www.ntu.edu.sg/home/ehchua/pr...se_design.html
http://www.roma1.infn.it/people/baro...ox-handout.pdf

Basically you start by identifying the main entities in your system (animals, rescuers, vets, skills, resources etc.) and thinking about how they interrelate. E.g. A rescuer has many skills and many rescuers have the same skill. You use foreign keys and/or bridging tables (aka linking entities) to map those relationships. And there a bit of set maths that allow you to decompose your whole domain into a set of interrelated tables.

My suggestion would be to have a read through those articles and see how you get on. If you think it makes enough sense to take an initial stab at a design, put one together and post it in the database forum asking for opinions. We'll pull it apart, question it and generally help you refine it into something workable.

Thanks for the links. I will for sure check them out. And about the database forum, do they allow complete idiots to ask questions :)

Yep. We allow complete idiots to answer them too:bigyello:

edit>Just in case I'm not being clear I mean this sub forum. (I wouldn't want to accidentally send you off to SQL Server Central. They'd eat you alive). The Database Development sub forum is part of VB Forums and you'll find all the same members posting.

I downloaded the PDF first and it's REALLY good. It starts out with basic information, AND it's in plain English! I have no doubt that it's going to be one of the most important references I'll have.

Hey Jumper, where were your rescuer friends when this little fella needed them?:bigyello:

I love it.. great story :)

I would be willing to work some on back end if in SQL Server. Table design stored procedure. things along those line. I am not interested in learning SQLLite

Thanks Gary, but I need to use SQLite. SQL Server is 'way' overkill for what I'm doing. But I appreciate your offer :)

edit: Also the whole point of it is for me to understand databases.

Writing a Windows Forms theme engine would be a monumental task. Pick something smaller. Or learn WPF, where you can measure the effort of restyling the entire thing in weeks instead of years. I would wager, for any random competent developer, you could start from scratch and be writing iOS applications with Xamarin Forms before you'd be scratching the surface of a WinForms theme engine that doesn't crash 50% of the apps that use it.

I... don't really know where this thread went, but I'm sensing there's not a great need for me to try my hand at a DGV/DataAdapter application? That'd take me a lot of time, and if there's going to be a different topic (or someone mentioning MSSQL again every 10 posts) that's probably a better use of time.

Quote:

---

I... don't really know where this thread went, but I'm sensing there's not a great need for me to try my hand at a DGV/DataAdapter application? That'd take me a lot of time, and if there's going to be a different topic (or someone mentioning MSSQL again every 10 posts) that's probably a better use of time.

---

I'm not 'exactly' sure what you mean by the above. The direction we are going is to make a small but real world application. We're sticking with SQLite. It's also going to have multiple tables so I can understand how to make them work together.

We would love to have you join in, if you would like. In my mind, I thought it might give you a break from me driving you crazy :) But your knowledge and theory would be great to have. I guess I'm saying... If you would like to take a vacation from me <grin>. Take it. If not, I think it would be great to have you in the thread. Your choice my friend.

edit: And yes it's going to be a gridview and adapter type program

Quote:

---

And yes it's going to be a gridview and adapter type program

---

I'm going to suggest you don't do this for your first CRUD app. Or at least, don't just use the drag and drop wizards in Visual Studio. Instead, use the ADO objects from code. Instantiate connections and commands. Learn about parameters and so forth. This will give you a much better understanding of the mechanics of what's really going on. The wizards are great, but they're even better when you understand what lies beneath.

That's ok Dex, I didn't know there "was" a Wizard. Also, I was using SQLite's version of ADO. There almost the same, except for a few things. Also, the only thing I did that was tied to the gridview was something Wes showed me about deleting rows. My weakest part is parameters, but Sitten was trying to help me with them. I only did one small thing, but I had his code to help me out. So yep, no wizards.

Another thing, in the PDF you gave me the link to... It wasn't very long before I was in over my head. I don't think I can learn anymore without some help and asking questions. So what should I do?

Ask questions! Just quote something you don't understand, and type a little bit about what you think it means, or why it confuses you.

Also, yes, don't use the blasted wizards. Part of the reason I hated ADO for a long time is because when I first got started, I had a bad mentor who only knew how to use the wizards. This meant, as far as I knew, everything happened by "magic" and if I couldn't figure out how to drag-and-drop my way to what I wanted I assumed it was impossible.

It was only something like 2 years ago when I was forced into a project that needed a database and had to look at ADO .NET again that I realized how wrong that was. It's not that hard, conceptually, to write ADO .NET code. The wizards make it look very hard because they don't know which set of super-advanced features you want, so they enable ALL OF THEM and it gets bewildering fast. If you sit down and figure out which things you need to learn and only learn the advanced things as you need them, suddenly it all gets clearer.

As I said in another post, DataAdapter has a few hundred members. Each set of four or five represents some feature, so it's safe to say it's got at least 50 features. But if you only care about CRUD, there's only 2 or 3 features you care about, that narrows it down to 10 or 15 properties/methods.

That's what I try to shoot for in my big tutorial style posts: "These are the only properties/methods you need to care about, if we do something that needs others I will introduce them then". But it takes a lot of writing to do that. And it takes a lot of thinking to do that.

You mentioned you were still confused about parameters. I'll try again.

Think about the 'game' Mad Libs. It presents you with a story that has many blanks, and a prompt for what kind of word goes in the blank. You ask your friends for the words, then read out the story with those words in it. Sometimes it's funny, because the stories usually create potential for weird combinations.

Parameters are like that. You take a SQL query that wants something specific:

Quote:

---

SELECT firstName, lastName FROM contacts WHERE contactId = 3

---

That SELECT query will return 1 or 0 rows. It can only ever return a row if there is one with the 'contactId' column set to 3. If we want to give it the 'mad libs' treatment, and use this one query to select rows with many different Ids, we need to make a "blank" for the integer ID value. We do that with a parameter:

Quote:

---

SELECT firstName, lastName, FROM contacts WHERE contactId = @contactId

---

This query won't work on its own. If you type it into a SQL console, you'll probably get an error. The '@contactId' part is the "blank" in the query. In order to execute this query with a Command object, you have to crate a Parameter object for a parameter named "contactId" with the value that you want. This is just like saying, "To read a Mad Libs story, you have to pick a word for every blank and use that word instead of saying 'blank'."

Here is some code that demonstrates:

Code:

---

Dim insertStatement As String = "INSERT INTO customers (firstName, lastname) VALUES (@firstName, @lastName)"

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"))

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

            command.ExecuteNonQuery()
        Next
    End Using
End Using

---

The SQL query is an INSERT with two parameters: 'firstName' and 'lastName'. Two parameter objects are added to the Command object shortly after it is created. Then, in a loop, for each 'customer' that will be INSERTed, the two parameters are given a value, and the command is executed. When that happens, the values in each Parameter object are substituted into each 'blank'.

Quote:

---

Ask questions!

---

+1 to that. I've watched your threads enough to know you won't be afraid to ask questions. You repeatedly throw yourself in waaaay out of your depth and manage to tread water long enough to learn to swim so you'll be fine with this too.

Database theory is tricksy at first. Some people look at it and say "well, duh! That's obvious". Others seem to spend a long time running up blind alleays before the penny drops but drop it does and when it does it seems obvious. So just try putting a couple of related tables together (say pets and owners), post what it looks like and we'll use it as a jumping off point. You're probably best off moving over to the database section at this point though.;)

Ok Dex. I'll do that. I guess the title is going to be, DB Project? Don't really know what to call it. Do you?

Sitten, I like the "fill in the blank" concept. Easier to understand and stick with you. Not sure if "confused" is the word, it's more like it's hazy. I 'almost' have it, but need to code a few times to take the haze away.

I was about to use your code like a template. I added a function to do something I needed to do and because of your code. It worked out good. The one thing I'm sticking with is using the colon (:) instead of @. The colon looks so much better, and the at sign just screams at you (smile).

I'm going to read your post a few more times to try to make things stick.

Hi everyone. The new thread is now open in the Database forum.

Content Edited for Stupidity

You edited your 7:00 post at 9:00 and ignored the 2 posts prior to 9:00 and come back here and ask for someone to post - that in and of itself it rather confusing dude.

In the AM I would be more then happy to help you build your table design. 6:00 am EST usually my latest at desk moment - could be even 2 hours earlier depending on what development might be going on.

In my own personal world, tomorrow I have to figure out how to feed "real column names" into a grid layout so when a user edits a cell it knows what the real field name is. The column headings are all over the place and not matching field names...

Thanks Sz I didn't realize that on the posts. Probably because I did not refresh the window. I apologize. Have a great evening.