新建控制台项目
安装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);
}
}
}
}