界面
代码
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
{
List
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 FilePath = Application.StartupPath + "\\";
//这2行我也不知道它为什么非要用FileInfo，干脆我再声明一个string的好了
List
List
List
foreach (FileInfo shpFile in list2)
{
list3.Add(shpFile.Name.Replace(".db",""));
}
return list3;
}
///
/// 获得目录下所有文件或指定文件类型文件(包含所有子文件夹)
///
/// 文件夹路径
/// 扩展名可以多个 例如 .mp3.wma.rm
///
public static List
{
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
}
}
发表评论