C#访问SQLite完整增删改查代码

一 一个控制台示例

using System;
using System.Data.SQLite;
 
namespace SQLiteSamples
{
    class Program
    {
        //数据库连接
        SQLiteConnection m_dbConnection;
 
        static void Main(string[] args)
        {
            Program p = new Program();
        }
 
        public Program()
        {
            createNewDatabase();
            connectToDatabase();
            createTable();
            fillTable();
            printHighscores();
        }
 
        //创建一个空的数据库
        void createNewDatabase()
        {
            SQLiteConnection.CreateFile("MyDatabase.sqlite");
        }
 
        //创建一个连接到指定数据库
        void connectToDatabase()
        {
            m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
            m_dbConnection.Open();
        }
 
        //在指定数据库中创建一个table
        void createTable()
        {
            string sql = "create table highscores (name varchar(20), score int)";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
        }
 
        //插入一些数据
        void fillTable()
        {
            string sql = "insert into highscores (name, score) values ('Me', 3000)";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
 
            sql = "insert into highscores (name, score) values ('Myself', 6000)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
 
            sql = "insert into highscores (name, score) values ('And I', 9001)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
        }
 
        //使用sql查询语句，并显示结果
        void printHighscores()
        {
            string sql = "select * from highscores order by score desc";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
                Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
            Console.ReadLine();
        }
    }
}      

二 完整的增删改查代码

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;
using System.Data.SQLite;
 
namespace jyyggl
{
    public partial class Form1 : Form
    {
        SQLiteConnection m_dbConnection;
        bool isupdate;
 
        public Form1()
        {
            InitializeComponent();
            m_dbConnection = new SQLiteConnection("Data Source=jyyggl.sqlite;Version=3;");
            m_dbConnection.Open();
            isupdate = false;
        }
 
        //添加
        private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "" && textBox2.Text == "" && textBox3.Text == "" && textBox4.Text == "" && textBox5.Text == "" && textBox6.Text == "" && textBox7.Text == "" && textBox8.Text == "")
            {
                MessageBox.Show("没有要添加的内容", "员工添加");
                return;
            }
            else
            {
                string sql = "insert into yggl values (null,'" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "')";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                command.ExecuteNonQuery();
                textBox1.Text = "";
                textBox2.Text = "";
                textBox3.Text = "";
                textBox4.Text = "";
                textBox5.Text = "";
                textBox6.Text = "";
                textBox7.Text = "";
                textBox8.Text = "";
                databind();
            }
        }
 
        private void databind()
        {
            DataTable dt = new DataTable();
            SQLiteDataAdapter slda = new SQLiteDataAdapter("select * from yggl", m_dbConnection);
            DataSet ds = new DataSet();
            slda.Fill(ds);
            dt = ds.Tables[0];
            dataGridView1.DataSource = dt;
 
        }
 
        // 浏览
        private void button1_Click(object sender, EventArgs e)
        {
            databind();
        }
 
        // 查询
        private void button5_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            SQLiteDataAdapter slda = new SQLiteDataAdapter("select * from yggl where name="+"'"+textBox9.Text+"'", m_dbConnection);
            DataSet ds = new DataSet();
            slda.Fill(ds);
            dt = ds.Tables[0];
            dataGridView1.DataSource = dt;
        }
 
        //删除
        private void button3_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count < 1 || dataGridView1.SelectedRows[0].Cells[1].Value == null)
            {
                MessageBox.Show("没有选中行。", "员工管理");
            }
            else
            {
                object oid = dataGridView1.SelectedRows[0].Cells[0].Value;
                if (DialogResult.No == MessageBox.Show("将删除第 " + (dataGridView1.CurrentCell.RowIndex + 1).ToString() + " 行，确定？", "员工管理", MessageBoxButtons.YesNo))
                {
                    return;
                }
                else
                {
                    string sql = "delete from yggl where id=" + oid;
                    SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                    command.ExecuteNonQuery();
                }
                databind();
            }
        }
 
        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            if (isupdate == true && dataGridView1.SelectedRows.Count>=1)
            {
                textBox1.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
                textBox2.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                textBox3.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
                textBox4.Text = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
                textBox5.Text = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
                textBox6.Text = dataGridView1.SelectedRows[0].Cells[6].Value.ToString();
                textBox7.Text = dataGridView1.SelectedRows[0].Cells[7].Value.ToString();
                textBox8.Text = dataGridView1.SelectedRows[0].Cells[8].Value.ToString();
            }
        }
 
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
 
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
            databind();
 
            dataGridView1.Columns[0].Visible = false;
            dataGridView1.Columns[1].HeaderCell.Value = "姓名";
            dataGridView1.Columns[2].HeaderCell.Value = "部门";
            dataGridView1.Columns[3].HeaderCell.Value = "职务";
            dataGridView1.Columns[4].HeaderCell.Value = "性别";
            dataGridView1.Columns[5].HeaderCell.Value = "身份证号";
            dataGridView1.Columns[6].HeaderCell.Value = "学历";
            dataGridView1.Columns[7].HeaderCell.Value = "手机";
            dataGridView1.Columns[8].HeaderCell.Value = "备注";
        }
 
        // 开始更新
        private void button6_Click(object sender, EventArgs e)
        {
            isupdate = true;
            button4.Enabled = true;
            button7.Enabled = true;
            button1.Enabled = false;
            button2.Enabled = false;
            button3.Enabled = false;
            button6.Enabled = false;
            if (dataGridView1.SelectedRows.Count >0)
            {
                dataGridView1.SelectedRows[0].Selected = false;
            }
        }
 
        // 结束更新
        private void button7_Click(object sender, EventArgs e)
        {
            isupdate = false;
            button4.Enabled = false;
            button7.Enabled = false;
 
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox6.Text = "";
            textBox7.Text = "";
            textBox8.Text = "";
 
            button1.Enabled = true;
            button2.Enabled = true;
            button3.Enabled = true;
            button6.Enabled = true;
        }
 
        // 更新
        private void button4_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count < 1 || dataGridView1.SelectedRows[0].Cells[1].Value==null)
            {
                MessageBox.Show("没有选中行。", "员工管理");
            }
            else
            {
                UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'WHERE LastName = 'Wilson'
                object oid = dataGridView1.SelectedRows[0].Cells[0].Value;
                string sql = "update yggl set name = '" + textBox1.Text + "',part='" + textBox2.Text + "',zhiwu='" + textBox3.Text + "',sex='" + textBox4.Text +
                    "',shfzhhao='" + textBox5.Text + "',xueli='" + textBox6.Text + "',shouji='" + textBox7.Text + "',demo='" + textBox8.Text + "'" +
                    "where id=" + oid;
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                command.ExecuteNonQuery();
                databind();
             }              
            
        }
    }
}