SQLite Basics
The first -- and most important -- thing about sqlite is obviously the namespace: "using System.Data;". Make sure to add that first, and reference it if you need to.
The engine you will be using for executing SQLite commands is Dzienny's DBInterface class.
The biggest part of coding has to do with the datatypes. In SQLite there are similar ones to C#, but there are also some not available in the .NET 3.5 framework.
Note: There are many other datatypes and datatype configurations, but these should be enough to do plenty.
Integers
- TINYINT : 0 - 255
SMALLINT : +-32,767
MEDIUMINT : +- 8,388,608
INT : +- 2,147,483,648
BIGINT : +- 9,223,372,036,854,775,808
- CHAR : best for values that will always be the same length e.g. cat, bat, poo, dog, etc
VARCHAR : best for values that vary e.g. cat, werewolf, watermelon, hi
- BOOL : 0 = false and 1 = true
This is really all you need to begin using SQLite.
Let's practice them.
Let's say we wanted to create a database slot for a player's pet.
This is what we would do.
- Code: Select all
DBInterface.ExecuteQuery("CREATE TABLE if not exists `"+p.name+"pets`(name VARCHAR(12), hunger TINYINT, happy BOOL);");
So let's analyze what has been laid out.
- Certain words must be CAPITALIZED or else it will mean something else to SQLite.
The ` character symbolizes the table's name.
Unlike C#, the name of a datatype comes first
SQLite strings have parenthesis depicting the max chars available
TINYINT and BOOL cannot have parenthesis because it is a set length already
The command ends with ;, but that is not true for all commands
Ok, well now we have a table for a player's pet.
Let's put it to use.
How about we actually insert something.
- Code: Select all
DBInterface.ExecuteQuery("INSERT INTO `"+p.name+"pets` (name, hunger, happy) VALUES ('deathmaster900', 0, 1);
Nothing too different.
- You can see that when defining a string, you need to put ' around the value or else it will return an error.
VALUES must be put between variables and values
Not all variables need to be defined, you could also do (name, happy) VALUES ('deathmaster900',1);
That's a lot so far, but a vital part of databases is updating them.
Looks like deathmaster900 has unfortunately become unhappy with us ):.
- Code: Select all
DBInterface.ExecuteQuery("UPDATE `"+p.name+"pets` SET name='happyhugs39', happy=1, hunger=0 WHERE happy=0 AND name='deathmaster900'
We have a new pet and some new usages
- Update statements must always use the SET qualifier to begin updating.
Commas seperate update values, and not all values have to be updated
The WHERE statement searches through the pets for a specific pet
the AND statement shouldn't be hard to figure out.
Plus, no semicolon ;
Now, if you wanted to do something with pets, you could select it.
- Code: Select all
using(DataTable table = DBInterface.fillData("SELECT * FROM `"+p.name+"pets` WHERE happy=1")
{
p.SendMessage("Unhappy pets");
foreach(var row in table.Rows)
{
p.SendMessage(row["name"]);
}
}
New Stuff:
- The using clause is pretty much a DataTable that disposes itself when done
SELECT * FROM is standard usage for find
The code creates a new datatable that contains the pets in rows
To get information from a row you use brackets [] with quotes "" and the variable name
So I have no doubt this will be very helpful to a vast majority of people!
