· 7 years ago · Oct 26, 2018, 09:32 PM
1using System;
2using System.Data.SQLite;
3
4namespace DbStarter
5{
6 class Program
7 {
8 static void Main(string[] args)
9 {
10 // Open connection to the database. See https://www.connectionstrings.com/sqlite/ for connection string syntax.
11 // SQLiteConnection class is from a 3rd party System.Data.SQLite package: https://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki
12 using (var connection = new SQLiteConnection("Data Source=./my-database.db3;Version=3;"))
13 {
14 // open a connection, this will create my-database.db3 in the working directory on the first run,
15 // or will open an existing database if the file is already there
16 //
17 connection.Open();
18
19 // create a command, commands allow you to execute SQL queries against the database
20 // see https://docs.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand?view=netcore-2.1 for details
21 using (var command = connection.CreateCommand())
22 {
23 // create a table, this is SQLite syntax territory, so use https://www.sqlite.org/lang.html for reference
24 // on how to create these strings
25 command.CommandText = @"CREATE TABLE IF NOT EXISTS people(
26 id INTEGER PRIMARY KEY,
27 first_name TEXT NOT NULL,
28 age INTEGER NOT NULL DEFAULT 0)";
29
30 // each command has to be executed in order to take effect
31 // `ExecuteNonQuery` allows to execute a command, which affects the database (creates or modifies tables,
32 // creates or modifies actual data) but doesn't query data
33 command.ExecuteNonQuery();
34 }
35
36 // at line 38 connection will be closed, database lock and other resources released
37 // it happens because of `using` statement on line 12: https://docs.microsoft.com/en-us/dotnet/standard/garbage-collection/using-objects for details
38 }
39
40 // do some stuff in your app
41
42 // Open a connection again. You can also just keep an old connection constantly alive, it's up to you.
43 using (var connection = new SQLiteConnection("Data Source=./my-database.db3;Version=3;"))
44 {
45 connection.Open();
46 using (var command = connection.CreateCommand())
47 {
48 // inserting data, especially strings, like that is bad practice you should use parameters
49 // see https://stackoverflow.com/questions/8234971/adding-parameters-to-idbcommand for an example
50 command.CommandText = "INSERT INTO people VALUES(0, 'Dude', 29), (1, 'Person', 30)";
51 // again, this command doesn't query for data, so `ExecuteNonQuery` is the way to go
52 command.ExecuteNonQuery();
53
54 // you can reuse the same command object instead of creating new commands
55 // it's also up to you, don't forget to clear command parameters if you reuse the same command
56 command.CommandText = "SELECT COUNT(*) FROM people";
57
58 // `ExecuteScalar` runs the query and returns a single scalar value, it's perfect for
59 // aggregations or other cases when you expect just a single value being returned
60 var peopleCount = (long)command.ExecuteScalar();
61 Console.WriteLine($"There's {peopleCount} people in the database.");
62
63 // let's get actual rows from the table
64 command.CommandText = "SELECT id, first_name, age FROM people";
65
66 // ExecuteReader is what you use when you need to query for rows of data
67 // notice `using` bit, it's a godd idea to close reader when you're done, `using` does that for you
68 using (var reader = command.ExecuteReader())
69 {
70 // reader works like an iterator, it points at a single row at a time,
71 // and you have to manually advance it forward with `NextResult`
72 while (reader.Read())
73 {
74 // there are multiple ways of getting data out of DataReader, subscript is one of them
75 // see https://docs.microsoft.com/en-us/dotnet/api/system.data.common.dbdatareader?redirectedfrom=MSDN&view=netcore-2.1 for details
76 // note, that it returns `object` and you have to cast it correctly yourself
77 var id = (long)reader["id"];
78 var firstName = (string)reader["first_name"];
79 var age = (long)reader["age"];
80
81 Console.WriteLine($"Person #{id}: {firstName}, {age} years old.");
82 }
83 }
84 }
85 }
86 }
87 }
88}