界面
; 代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;
namespace SqliteTest
{
public partial class Form1 : Form
{
SQLiteConnection Conn;
public Form1()
{
InitializeComponent();
}
#region 操作库
private void btnOpenDatabase_Click(object sender, EventArgs e)
{
OpenDatabase();
comboBox2.Text = "";
MessageBox.Show("打开成功！");
}
private void btnCloseDatabase_Click(object sender, EventArgs e)
{
if (Conn == null)
{
MessageBox.Show("数据库未打开");
}
else
{
Conn.Close();
MessageBox.Show("关闭成功！");
}
}
private void btnCreateDatabase_Click(object sender, EventArgs e)
{
CreateDatabase();
}
private void OpenDatabase()
{
string FilePath = Application.StartupPath + "\\" + comboBox1.Text + ".db";
try
{
Conn = new SQLiteConnection("Data Source=" + FilePath + ";Version=3;");
Conn.Open();
}
catch (Exception ex)
{
throw new Exception("打开数据库：" + FilePath + "的连接失败：" + ex.Message);
}
}
private void CreateDatabase()
{
string FilePath = Application.StartupPath + "\\" + comboBox1.Text + ".db";
if (!File.Exists(FilePath))
{
SQLiteConnection.CreateFile(FilePath);
}
}
private bool CheckDatabaseIsOpen()
{
if (Conn != null && Conn.State == ConnectionState.Open)
{
return true;
}
else
{
MessageBox.Show("数据库未打开！");
return false;
}
}
#endregion
#region 操作表
private void btnCreateDataTable_Click(object sender, EventArgs e)
{
if (CheckDatabaseIsOpen())
{
CreateDataTable();
}
}
private void btnDelDataTable_Click(object sender, EventArgs e)
{
if (CheckDatabaseIsOpen())
{
DropDataTable();
}
}
private void comboBox2_DropDown(object sender, EventArgs e)
{
if (CheckDatabaseIsOpen())
{
comboBox2.DataSource = QueryTableName();
}
}
private List<string> QueryTableName()
{
List<string> list = new List<string>();
try
{
//string sql = "select * from " + textBox2.Text + " order by score desc";
string sql = "select name from sqlite_master where type='table' order by name";
SQLiteCommand command = new SQLiteCommand(sql, Conn);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
list.Add(reader["name"].ToString());
}
}
catch (Exception ex)
{
throw new Exception("查询数据失败：" + ex.Message);
}
return list;
}
private void CreateDataTable()
{
try
{
string sql = "create table " + comboBox2.Text + " (name varchar(20), score varchar(20))";
SQLiteCommand command = new SQLiteCommand(sql, Conn);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("创建数据表" + comboBox2.Text + "失败：" + ex.Message);
}
MessageBox.Show("创建数据表" + comboBox2.Text + "成功！");
}
private void DropDataTable()
{
try
{
//string sql = "create table " + comboBox2.Text + " (name varchar(20), score varchar(20))";
string sql = "drop table if exists "+comboBox2.Text;
SQLiteCommand command = new SQLiteCommand(sql, Conn);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("删除数据表" + comboBox2.Text + "失败：" + ex.Message);
}
MessageBox.Show("删除数据表" + comboBox2.Text + "成功！");
}
#endregion
#region 根据指定位置拿到数据库的库名
private void comboBox1_DropDown(object sender, EventArgs e)
{
comboBox1.DataSource = GetDatabase();
}
private List<string> GetDatabase()
{
string FilePath = Application.StartupPath + "\\";
//这2行我也不知道它为什么非要用FileInfo，干脆我再声明一个string的好了
List<fileinfo> list = new List<fileinfo>();
List<fileinfo> list2 = GetFile(FilePath, ".db", list);
List<string> list3 = new List<string>();
foreach (FileInfo shpFile in list2)
{
list3.Add(shpFile.Name.Replace(".db",""));
}
return list3;
}
/// <summary>
/// 获得目录下所有文件或指定文件类型文件(包含所有子文件夹)
/// </summary>
/// <param name="path">文件夹路径
/// <param name="extName">扩展名可以多个 例如 .mp3.wma.rm
/// <returns>List<fileinfo></fileinfo></returns>
public static List<fileinfo> GetFile(string path, string extName, List<fileinfo> lst)
{
try
{
string[] dir = Directory.GetDirectories(path); //文件夹列表
DirectoryInfo fdir = new DirectoryInfo(path);
FileInfo[] file = fdir.GetFiles();
//FileInfo[] file = Directory.GetFiles(path); //文件列表
if (file.Length != 0 || dir.Length != 0) //当前目录文件或文件夹不为空
{
foreach (FileInfo f in file) //显示当前目录所有文件
{
if (extName.ToLower().IndexOf(f.Extension.ToLower()) >= 0)
{
lst.Add(f);
}
}
foreach (string d in dir)
{
GetFile(d, extName, lst);//递归
}
}
return lst;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region 增删改查数据
private void btnAddData_Click(object sender, EventArgs e)
{
InsertData();
}
private void btnQueryData_Click(object sender, EventArgs e)
{
QueryData();
}
private void btnDelData_Click(object sender, EventArgs e)
{
DeleteData();
}
private void btnUpdateData_Click(object sender, EventArgs e)
{
UpdateData();
}
private void InsertData()
{
try
{
string sql = "insert into " + comboBox2.Text + " (name, score) values ('" + textBox1.Text + "', '" + textBox2.Text + "')";
SQLiteCommand command = new SQLiteCommand(sql, Conn);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("插入数据：" + textBox1.Text + ":" + textBox2.Text + "失败：" + ex.Message);
}
}
private void DeleteData()
{
try
{
string sql = "delete from " + comboBox2.Text + " where name = " + textBox1.Text;
SQLiteCommand command = new SQLiteCommand(sql, Conn);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("删除数据：" + textBox1.Text + ":" + textBox2.Text + "失败：" + ex.Message);
}
}
private void UpdateData()
{
try
{
string sql = "update " + comboBox2.Text + " set score = '" + textBox2.Text + "' where name='" + textBox1.Text + "'";
SQLiteCommand command = new SQLiteCommand(sql, Conn);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("更新数据：" + textBox1.Text + ":" + textBox2.Text + "失败：" + ex.Message);
}
}
private void QueryData()
{
try
{
string sql = "select * from " + comboBox2.Text + " order by score desc";
SQLiteCommand command = new SQLiteCommand(sql, Conn);
SQLiteDataReader reader = command.ExecuteReader();
richTextBox1.Text = "";
while (reader.Read()){
richTextBox1.AppendText("Name: " + reader["name"] + "\tScore: " + reader["score"] + "\r\n");
}
}
catch (Exception ex)
{
throw new Exception("查询数据失败：" + ex.Message);
}
}
#endregion
}
</fileinfo></fileinfo></string></string></fileinfo></fileinfo></fileinfo></string></string></string></string>
}
Original: https://blog.csdn.net/GoldenLionKing/article/details/125477888
Author: GoldenLionKing
Title: C#访问Sqlite入门之基本增删改查操作
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/816710/
转载文章受原作者版权保护。转载请注明原作者出处！