操作SQLite数据库（C#）
SQLite是一款轻量级的关系型数据库，它不需要安装数据库服务器，只需要在应用程序中引用相关的库文件即可。在C#中，我们可以使用SQLite来操作数据库，实现数据的存储和管理。
本文将详细讲解如何在C#中操作SQLite，包括数据库连接、插入、更新、删除、查询等基本操作。
1. 引用SQLite库文件
我们可以通过Nuget包管理器来引用SQLite库文件，在Visual Studio中右键点击项目名称，选择“管理NuGet程序包”，在搜索框中输入“SQLite”，选择“System.Data.SQLite.Core”进行安装。
2. 创建数据库连接
使用SQLite操作数据库时，需要先创建数据库连接对象。可以使用以下代码创建连接对象：
using System.Data.SQLite;
string connectionString = "Data Source=mydatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
其中，mydatabase.db为数据库文件名，如果文件不存在，则会自动创建一个新的数据库文件；Version=3表示使用SQLite 3版本的数据库。
3. 执行SQL语句
在创建连接对象后，可以使用SQLiteCommand对象执行SQL语句。以下是一个示例：
using System.Data.SQLite;
string connectionString = "Data Source=mydatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();
string sql = "CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
connection.Close();
以上代码创建了一个名为“students”的表，包含三个字段：id（自增长的整数）、name（字符串类型）、age（整数类型）。
4. 插入数据
可以使用以下代码向上面创建的“students”表中插入一条数据：
using System.Data.SQLite;
string connectionString = "Data Source=mydatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();
string sql = "INSERT INTO students (name, age) VALUES ('Tom', 20)";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
connection.Close();
5. 更新数据
可以使用以下代码更新“students”表中id为1的数据：
using System.Data.SQLite;
string connectionString = "Data Source=mydatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();
string sql = "UPDATE students SET name='Jerry', age=22 WHERE id=1";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
connection.Close();
6. 删除数据
可以使用以下代码删除“students”表中id为1的数据：
using System.Data.SQLite;
string connectionString = "Data Source=mydatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();
string sql = "DELETE FROM students WHERE id=1";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
connection.Close();
7. 查询数据
可以使用以下代码查询“students”表中所有数据：
using System.Data.SQLite;
string connectionString = "Data Source=mydatabase.db;Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
connection.Open();
string sql = "SELECT * FROM students";
SQLiteCommand command = new SQLiteCommand(sql, connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader["id"]);
string name = reader["name"].ToString();
int age = Convert.ToInt32(reader["age"]);
}
connection.Close();
以上代码查询“students”表中的所有数据，并依次读取每条数据的id、name和age字段。
示例1：使用SQLite实现简单的账户管理系统
我们可以使用SQLite实现一个简单的账户管理系统，包含注册、登录和修改密码功能。
using System;
using System.Data.SQLite;
public class User
{
public int Id { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public void Save()
{
string connectionString = "Data Source=mydatabase.db;Version=3;";
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = $"INSERT INTO users (username, password) VALUES ('{Username}', '{Password}')";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
}
}
public static User FindByUsernameAndPassword(string username, string password)
{
string connectionString = "Data Source=mydatabase.db;Version=3;";
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = $"SELECT * FROM users WHERE username='{username}' AND password='{password}'";
SQLiteCommand command = new SQLiteCommand(sql, connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
User user = new User
{
Id = Convert.ToInt32(reader["id"]),
Username = reader["username"].ToString(),
Password = reader["password"].ToString()
};
return user;
}
return null;
}
}
public static void UpdatePassword(int id, string newPassword)
{
string connectionString = "Data Source=mydatabase.db;Version=3;";
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = $"UPDATE users SET password='{newPassword}' WHERE id={id}";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
}
}
}
// 注册新用户
User user = new User
{
UserName = "Tom",
Password = "123456"
};
user.Save();
// 登录
User loginUser = User.FindByUsernameAndPassword("Tom", "123456");
// 修改密码
User.UpdatePassword(loginUser.Id, "654321");
以上代码创建了一个User类来管理数据库中的users表。其中，Save方法用于新增用户，FindByUsernameAndPassword方法用于根据用户名和密码查找用户，UpdatePassword方法用于修改用户密码。
示例2：使用SQLite实现银行转账系统
我们可以使用SQLite实现一个简单的银行转账系统，包含转账、查看余额和查看交易记录功能。
using System;
using System.Data.SQLite;
public class Account
{
public int Id { get; set; }
public string AccountNumber { get; set; }
public decimal Balance { get; set; }
public void Save()
{
string connectionString = "Data Source=mydatabase.db;Version=3;";
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = $"INSERT INTO accounts (account_number, balance) VALUES ('{AccountNumber}', {Balance})";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
}
}
public static Account FindByAccountNumber(string accountNumber)
{
string connectionString = "Data Source=mydatabase.db;Version=3;";
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = $"SELECT * FROM accounts WHERE account_number='{accountNumber}'";
SQLiteCommand command = new SQLiteCommand(sql, connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Account account = new Account
{
Id = Convert.ToInt32(reader["id"]),
AccountNumber = reader["account_number"].ToString(),
Balance = Convert.ToDecimal(reader["balance"])
};
return account;
}
return null;
}
}
public void Transfer(Account target, decimal amount)
{
if (Balance < amount)
{
Console.WriteLine("余额不足！");
return;
}
string connectionString = "Data Source=mydatabase.db;Version=3;";
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
SQLiteTransaction transaction = connection.BeginTransaction();
try
{
Balance -= amount;
string sql1 = $"UPDATE accounts SET balance={Balance} WHERE id={Id}";
SQLiteCommand command1 = new SQLiteCommand(sql1, connection);
command1.ExecuteNonQuery();
target.Balance += amount;
string sql2 = $"UPDATE accounts SET balance={target.Balance} WHERE id={target.Id}";
SQLiteCommand command2 = new SQLiteCommand(sql2, connection);
command2.ExecuteNonQuery();
transaction.Commit();
string sql3 = $"INSERT INTO transactions (from_account, to_account, amount) VALUES ({Id}, {target.Id}, {amount})";
SQLiteCommand command3 = new SQLiteCommand(sql3, connection);
command3.ExecuteNonQuery();
Console.WriteLine($"转账成功，转出{amount:N2}元，余额{Balance:N2}元");
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine($"转账失败：{ex.Message}");
}
}
}
public void ShowBalance()
{
Console.WriteLine($"账户余额为{Balance:N2}元");
}
public void ShowTransactions()
{
string connectionString = "Data Source=mydatabase.db;Version=3;";
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = $"SELECT * FROM transactions WHERE from_account={Id} OR to_account={Id}";
SQLiteCommand command = new SQLiteCommand(sql, connection);
SQLiteDataReader reader = command.ExecuteReader();
Console.WriteLine($"账户交易记录：");
while (reader.Read())
{
int fromAccount = Convert.ToInt32(reader["from_account"]);
int toAccount = Convert.ToInt32(reader["to_account"]);
decimal amount = Convert.ToDecimal(reader["amount"]);
DateTime createTime = Convert.ToDateTime(reader["create_time"]);
if (fromAccount == Id)
{
Console.WriteLine($"转出{amount:N2}元，到账户{toAccount}，时间：{createTime}");
}
else
{
Console.WriteLine($"收入{amount:N2}元，来自账户{fromAccount}，时间：{createTime}");
}
}
}
}
}
// 创建账户
Account account1 = new Account
{
AccountNumber = "10001",
Balance = 10000
};
account1.Save();
Account account2 = new Account
{
AccountNumber = "10002",
Balance = 20000
};
account2.Save();
// 转账
account1.Transfer(account2, 5000);
// 查看余额
account1.ShowBalance();
account2.ShowBalance();
// 查看交易记录
account1.ShowTransactions();
account2.ShowTransactions();
以上代码创建了一个Account类来管理数据库中的accounts表和transactions表。其中，Save方法用于新增账户，FindByAccountNumber方法用于根据账号查找账户，Transfer方法用于转账，ShowBalance方法用于查看余额，ShowTransactions方法用于查看交易记录。
总结
使用SQLite操作数据库可以方便快捷地实现数据的存储和管理。本文介绍了在C#中如何使用SQLite，包括创建连接、执行SQL语句、插入、更新、删除、查询数据等基本操作。我们也通过两个示例，了解了如何使用SQLite实现简单的账户管理系统和银行转账系统。
本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：C#操作SQLite方法实例详解 - Python技术站
微信扫一扫 
支付宝扫一扫 