C#操作SQLite数据库方法小结

关于C#操作SQLite数据库方法小结，我可以提供以下攻略：

1. 安装SQLite

在使用SQLite之前，需要先安装SQLite数据库，在Windows上可以从官方网站下载SQLite安装程序进行安装。

2. 创建SQLite数据库

为了进行SQLite数据库的操作，需要使用以下命令创建连接并打开数据库：

SQLiteConnection conn = new SQLiteConnection("Data Source=test.db;Version=3;");
conn.Open();

该命令将会打开test.db数据库，并返回一个SQLiteConnection连接对象。

3. 创建表格

接着，使用以下命令创建表格：

string query = "CREATE TABLE IF NOT EXISTS customers (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT);";
SQLiteCommand cmd = new SQLiteCommand(query, conn);
cmd.ExecuteNonQuery();

该命令创建了一个名为"customers"的表格，并包含三个字段：id，name和email。

4. 插入数据

以下命令将向"customers"表格中插入数据：

string query = "INSERT INTO customers (name, email) VALUES (@name, @email)";
SQLiteCommand cmd = new SQLiteCommand(query, conn);
cmd.Parameters.AddWithValue("@name", "John Doe");
cmd.Parameters.AddWithValue("@email", "[e-mail]");
cmd.ExecuteNonQuery();

该命令向"customers"表格中插入一条数据，其中"name"列为"John Doe"，"email"列为"[e-mail]"。

5. 查询数据

要查询"customers"表格中所有的数据可以使用以下代码：

string query = "SELECT * FROM customers";
SQLiteCommand cmd = new SQLiteCommand(query, conn);
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    int id = reader.GetInt32(0);
    string name = reader.GetString(1);
    string email = reader.GetString(2);
}

该命令将会返回"customers"表格中所有的数据，包括id，name和email列的数据。

示例说明

以下是两个示例说明：

示例1

在这个例子中，我们将通过创建连接、创建表格、插入数据、查询数据等操作，创建一个简单的"students"表格，并将两个学生信息插入到该表格中:

using System.Data.SQLite;
using System;

namespace SQLiteTest
{
    class Program
    {
        static void Main(string[] args)
        {
            SQLiteConnection conn = new SQLiteConnection("Data Source=students.db;Version=3;");
            conn.Open();

            string query = "CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER);";
            SQLiteCommand cmd = new SQLiteCommand(query, conn);
            cmd.ExecuteNonQuery();

            query = "INSERT INTO students (name, age) VALUES (@name, @age)";
            cmd = new SQLiteCommand(query, conn);
            cmd.Parameters.AddWithValue("@name", "Tom");
            cmd.Parameters.AddWithValue("@age", 18);
            cmd.ExecuteNonQuery();

            cmd = new SQLiteCommand(query, conn);
            cmd.Parameters.AddWithValue("@name", "Jessie");
            cmd.Parameters.AddWithValue("@age", 20);
            cmd.ExecuteNonQuery();

            query = "SELECT * FROM students";
            cmd = new SQLiteCommand(query, conn);
            SQLiteDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string name = reader.GetString(1);
                int age = reader.GetInt32(2);
                Console.WriteLine("ID: {0}, Name: {1}, Age: {2}", id, name, age);
            }

            conn.Close();
        }
    }
}

示例2

在这个例子中，我们将逐步创建一个可以管理学生信息的控制台应用程序：

using System.Data.SQLite;
using System;

namespace SQLiteTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Welcome to Student Management System!");

            string menu = "1. Show all students\n2. Add a student\n3. Exit\nEnter your choice:";
            bool exit = false;

            SQLiteConnection conn = new SQLiteConnection("Data Source=students.db;Version=3;");
            conn.Open();

            string query = "CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER);";
            SQLiteCommand cmd = new SQLiteCommand(query, conn);
            cmd.ExecuteNonQuery();

            while (!exit)
            {
                Console.Write(menu);
                string choice = Console.ReadLine();

                switch (choice)
                {
                    case "1":
                        query = "SELECT * FROM students";
                        cmd = new SQLiteCommand(query, conn);
                        SQLiteDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            int id = reader.GetInt32(0);
                            string name = reader.GetString(1);
                            int age = reader.GetInt32(2);
                            Console.WriteLine("ID: {0}, Name: {1}, Age: {2}", id, name, age);
                        }
                        break;
                    case "2":
                        Console.Write("Enter student name:");
                        string name = Console.ReadLine();
                        Console.Write("Enter student age:");
                        int age = int.Parse(Console.ReadLine());
                        query = "INSERT INTO students (name, age) VALUES (@name, @age)";
                        cmd = new SQLiteCommand(query, conn);
                        cmd.Parameters.AddWithValue("@name", name);
                        cmd.Parameters.AddWithValue("@age", age);
                        cmd.ExecuteNonQuery();
                        break;
                    case "3":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("Invalid choice! Please enter a valid choice.");
                        break;
                }
            }

            conn.Close();
        }
    }
}

该程序将提供一个菜单，允许用户查看所有已注册的学生并添加新学生。