C#访问Sqlite入门之基本增删改查操作

界面

代码

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>
    /// <param name="extName">扩展名可以多个 例如 .mp3.wma.rm</param>
    /// <returns>List<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
}

}