Sqlite建立增刪改查

標籤：

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.SQLite;using System.Data.Common;using System.IO;namespace SqliteDemo{    public partial class Form1 : Form    {                public Form1()        {            InitializeComponent();        }                        string sqlitePath = Application.StartupPath + "/test.db";        System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();        System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();        string sql;                /// <summary>        /// 建立資料庫        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnCreat_Click(object sender, EventArgs e)        {            if (File.Exists(sqlitePath))            {                MessageBox.Show("資料庫已經存在","error",MessageBoxButtons.OK,MessageBoxIcon.Error);            }            else            {                try                {                       //建立一個資料庫檔案                    System.Data.SQLite.SQLiteConnection.CreateFile(sqlitePath);                }                catch (Exception ex)                {                    MessageBox.Show("建立資料庫失敗" + ex.ToString());                 }             }         }                private void btnConnect_Click(object sender, EventArgs e)        {            //串連資料庫                        try{                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();                connstr.DataSource = sqlitePath;                //connstr.Password = "admin";//設定密碼，SQLite ADO.NET實現了資料庫密碼保護                conn.ConnectionString = connstr.ToString();                conn.Open();                cmd.Connection = conn;            }                   catch (Exception ex)            {                MessageBox.Show("串連資料庫" + ex.ToString());            }        }        private void btnCreatTable_Click(object sender, EventArgs e)        {            try            {                //建立表                sql = "CREATE TABLE test(username varchar(20),password varchar(20))";                cmd.CommandText = sql;                cmd.Connection = conn;                cmd.ExecuteNonQuery();            }            catch (Exception ex)            {                MessageBox.Show("建立表失敗" + ex.ToString());             }        }        private void btnAdd_Click(object sender, EventArgs e)        {            try            {                sql = "insert into  test (username,password) values (‘" + "123" + "‘,‘" + "456" + "‘)";                cmd.CommandText = sql;                cmd.ExecuteNonQuery();            }            catch (Exception ex)            {                MessageBox.Show("增加失敗" + ex.ToString());            }        }        private void btnSelect_Click(object sender, EventArgs e)        {            try            {                listView1.Items.Clear();                sql = "SELECT * FROM test";                cmd.CommandText = sql;                System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();                while (reader.Read())                {                    ListViewItem lvi = new ListViewItem();                    lvi.Text = (listView1.Items.Count + 1).ToString();                    lvi.SubItems.Add(reader.GetString(0));                    lvi.SubItems.Add(reader.GetString(1));                    listView1.Items.Add(lvi);                }                reader.Close();            }            catch (Exception ex)            {                MessageBox.Show("讀取失敗" + ex.ToString());            }        }        private void btnDeleteAll_Click(object sender, EventArgs e)        {            sql = "delete FROM test";            cmd.CommandText = sql;            cmd.ExecuteNonQuery();        }        private void btnDelete_Click(object sender, EventArgs e)        {            sql = "DELETE FROM test WHERE username = ‘"+textBox1.Text.Trim()+"‘";            cmd.CommandText = sql;            cmd.ExecuteNonQuery();        }          }}

 

Sqlite建立增刪改查