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();
             }              
            
        }
    }
}
           

配合datagridview控件使用；

System.Data.SQLite.dll下載下傳，

http://pan.baidu.com/s/1i4L6FkT