C#操作SQLite数据库方法小结(创建,连接,插入,查询,删除等)

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

创建数据库

要创建一个SQLite数据库，我们需要使用System.Data.SQLite.Core库中的SQLiteConnection类。例如，要创建一个名为“myDatabase.db”的数据库，可以使用以下代码：

using System.Data.SQLite;

string connectionString = "Data Source=myDatabase.db;Version=3;";
SQLiteConnection.CreateFile("myDatabase.db");

连接到数据库

要连接到SQLite数据库，我们需要使用SQLiteConnection对象。我们需要提供数据库的连接字符串。例如，以下代码连接到名为“myDatabase.db”的数据库：

using System.Data.SQLite;

string connectionString = "Data Source=myDatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();

插入数据

要将数据插入SQLite数据库，我们需要使用SQLiteCommand对象。例如，以下代码向“users”表中插入一条新记录：

using System.Data.SQLite;

string connectionString = "Data Source=myDatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();

string sql = "INSERT INTO users (name, age) VALUES ('John Doe', 30);";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();

查询数据

要查询SQLite数据库中的数据，我们需要使用SQLiteCommand对象和SQLiteDataReader对象。例如，以下代码查询“users”表中的所有记录：

using System.Data.SQLite;

string connectionString = "Data Source=myDatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();

string sql = "SELECT * FROM users;";
SQLiteCommand command = new SQLiteCommand(sql, connection);

SQLiteDataReader reader = command.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);
}

删除数据

要从SQLite数据库中删除数据，我们需要使用SQLiteCommand对象。例如，以下代码删除名为“John Doe”的记录：

using System.Data.SQLite;

string connectionString = "Data Source=myDatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();

string sql = "DELETE FROM users WHERE name = 'John Doe';";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();

示例

示例一

下面是一个完整的示例，演示如何创建一个名为“myDatabase.db”的数据库，以及向“users”表中插入一些记录，并查询它们。

using System;
using System.Data.SQLite;

namespace SQLiteDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=myDatabase.db;Version=3;";
            SQLiteConnection.CreateFile("myDatabase.db");

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string createTableSql = "CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR(50), age INTEGER);";
                SQLiteCommand createTableCommand = new SQLiteCommand(createTableSql, connection);
                createTableCommand.ExecuteNonQuery();

                string insertSql = "INSERT INTO users (name, age) VALUES ('John Doe', 30);";
                SQLiteCommand insertCommand = new SQLiteCommand(insertSql, connection);
                insertCommand.ExecuteNonQuery();

                insertSql = "INSERT INTO users (name, age) VALUES ('Jane Doe', 25);";
                insertCommand = new SQLiteCommand(insertSql, connection);
                insertCommand.ExecuteNonQuery();

                string selectSql = "SELECT * FROM users;";
                SQLiteCommand selectCommand = new SQLiteCommand(selectSql, connection);

                SQLiteDataReader reader = selectCommand.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);
                }
            }
        }
    }
}

示例二

下面是一个示例，演示如何从SQLite数据库中删除记录。

using System.Data.SQLite;

string connectionString = "Data Source=myDatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();

string sql = "DELETE FROM users WHERE name = 'John Doe';";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();