ado.net 操作sqlite

新建控制台项目

安装nuget包Microsoft.Data.Sqlite

数据库名字和链接

string dbName = "test.db";
SqliteConnection? connection = null;
try
{
    //创建链接
    connection = new SqliteConnection($"Data Source={dbName}");
    //打开链接
    connection.Open();
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}
finally
{
    //使用完关闭
    connection?.Close();
}
Console.WriteLine("执行执行完毕");
Console.ReadKey();

创建表

/// <summary>
/// 创建表
/// </summary>
static void CreateTable(SqliteConnection connection)
{
    var studentTable = @"CREATE TABLE IF NOT EXISTS Student(Id INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT NOT NULL,Age INTEGER)";
    using (var command = new SqliteCommand(studentTable, connection))
    {
        var status = command.ExecuteNonQuery();
        Console.WriteLine($"创建学生表结果:{status}");
    }
}

/// <summary>
/// 查询数据库中的表
/// </summary>
/// <param name="connection"></param>
static void QueryTables(SqliteConnection connection)
{
    var querySql = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';";
    using (var command = new SqliteCommand(querySql, connection))
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"表名:{reader.GetString(0)}");
        }
    }
}

增加数据

/// <summary>
/// 插入数据
/// </summary>
/// <param name="connection"></param>
static void InsertData(SqliteConnection connection)
{
    string insertSql = @"INSERT INTO Student (Name, Age) VALUES (@Name, @Age)";

    using (var command = new SqliteCommand(insertSql, connection))
    {
        command.Parameters.AddWithValue("@Name", "小明");
        command.Parameters.AddWithValue("@Age", 25);
        command.ExecuteNonQuery();
        command.Parameters["@Name"].Value = "小红";
        command.Parameters["@Age"].Value = 22;
        command.ExecuteNonQuery();
        Console.WriteLine("插入成功");
    }
}

查询数据

/// <summary>
/// 查询数据
/// </summary>
/// <param name="connection"></param>
static void QueryData(SqliteConnection connection)
{
    var querySql = "Select Id,Name,Age FROM Student;";
    using (var command = new SqliteCommand(querySql, connection))
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"Id:{reader.GetInt32(0)},姓名:{reader.GetString(1)}，年龄:{reader.GetInt32(2)}");
        }
    }
}

修改数据

/// <summary>
/// 更新数据
/// </summary>
/// <param name="connection"></param>
static void UpdateData(SqliteConnection connection)
{
    string updateSql = @"UPDATE Student SET Age=@Age WHERE Id=@Id";

    using (var command = new SqliteCommand(updateSql, connection))
    {
        command.Parameters.AddWithValue("@Id", 1);
        command.Parameters.AddWithValue("@Age", 31);
        command.ExecuteNonQuery();
        //查询结果
        QueryData(connection);
    }
}

删除数据

/// <summary>
/// 删除数据
/// </summary>
/// <param name="connection"></param>
static void DeleteData(SqliteConnection connection)
{
    string deleteSql = @"DELETE FROM Student WHERE Id=@Id";

    using (var command = new SqliteCommand(deleteSql, connection))
    {
        command.Parameters.AddWithValue("@Id", 1);
        command.ExecuteNonQuery();
        //查询结果
        QueryData(connection);
    }
}

完整代码如下

using Microsoft.Data.Sqlite;

namespace AdoNetSqliteStu
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string dbName = "test.db";
            SqliteConnection? connection = null;
            try
            {
                //创建链接
                connection = new SqliteConnection($"Data Source={dbName}");
                //打开链接
                connection.Open();
                //创建表
                CreateTable(connection);
                QueryTables(connection);
                //插入数据
                InsertData(connection);
                //查询数据
                QueryData(connection);
                //更新数据
                UpdateData(connection);
                //删除数据
                DeleteData(connection);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //使用完关闭
                connection?.Close();
            }
            Console.WriteLine("执行执行完毕");
            Console.ReadKey();
        }

        /// <summary>
        /// 创建表
        /// </summary>
        static void CreateTable(SqliteConnection connection)
        {
            var studentTable = @"CREATE TABLE IF NOT EXISTS Student(Id INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT NOT NULL,Age INTEGER)";
            using (var command = new SqliteCommand(studentTable, connection))
            {
                var status = command.ExecuteNonQuery();
                Console.WriteLine($"创建学生表结果:{status}");
            }
        }

        /// <summary>
        /// 查询数据库中的表
        /// </summary>
        /// <param name="connection"></param>
        static void QueryTables(SqliteConnection connection)
        {
            var querySql = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';";
            using (var command = new SqliteCommand(querySql, connection))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"表名:{reader.GetString(0)}");
                }
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="connection"></param>
        static void InsertData(SqliteConnection connection)
        {
            string insertSql = @"INSERT INTO Student (Name, Age) VALUES (@Name, @Age)";

            using (var command = new SqliteCommand(insertSql, connection))
            {
                command.Parameters.AddWithValue("@Name", "小明");
                command.Parameters.AddWithValue("@Age", 25);
                command.ExecuteNonQuery();
                command.Parameters["@Name"].Value = "小红";
                command.Parameters["@Age"].Value = 22;
                command.ExecuteNonQuery();
                Console.WriteLine("插入成功");
            }
        }
    
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="connection"></param>
        static void QueryData(SqliteConnection connection)
        {
            var querySql = "Select Id,Name,Age FROM Student;";
            using (var command = new SqliteCommand(querySql, connection))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"Id:{reader.GetInt32(0)},姓名:{reader.GetString(1)}，年龄:{reader.GetInt32(2)}");
                }
            }
        }
        
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="connection"></param>
        static void UpdateData(SqliteConnection connection)
        {
            string updateSql = @"UPDATE Student SET Age=@Age WHERE Id=@Id";

            using (var command = new SqliteCommand(updateSql, connection))
            {
                command.Parameters.AddWithValue("@Id", 1);
                command.Parameters.AddWithValue("@Age", 31);
                command.ExecuteNonQuery();
                //查询结果
                QueryData(connection);
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="connection"></param>
        static void DeleteData(SqliteConnection connection)
        {
            string deleteSql = @"DELETE FROM Student WHERE Id=@Id";

            using (var command = new SqliteCommand(deleteSql, connection))
            {
                command.Parameters.AddWithValue("@Id", 1);
                command.ExecuteNonQuery();
                //查询结果
                QueryData(connection);
            }
        }
    }
}