SQLite的简单用法示例

2016-01-11 16:18:50  访问(1805) 赞(0) 踩(0)

using System;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Data.SQLite;

namespace SqliteDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 创建SQLite数据库
        /// </summary>
        protected void CreateDB()
        {
            string folderName = Application.StartupPath;

            if (folderName.Length == 0)
            {
                MessageBox.Show("请选择生成目录。");
             
                return;
            }

            string fileName = "s.db";

            if (fileName.Length == 0)
            {
                MessageBox.Show("请选择文件名。"); 
                return;
            }

            string thePwd = "";

            if (!Directory.Exists(folderName))
                Directory.CreateDirectory(folderName);

            string datasource = "";

            if (folderName.EndsWith("\\") || folderName.EndsWith("/"))
                datasource = folderName + fileName;
            else
                datasource = folderName + "\\" + fileName;

            // FileSlowXFunctions.CreateDirByFileName(datasource);

            System.Data.SQLite.SQLiteConnection.CreateFile(datasource);

            //连接数据库
            System.Data.SQLite.SQLiteConnection conn
                =
                new System.Data.SQLite.SQLiteConnection();

            System.Data.SQLite.SQLiteConnectionStringBuilder connstr
                =
                new System.Data.SQLite.SQLiteConnectionStringBuilder();

            connstr.DataSource = datasource;

            if (thePwd.Length > 0)
                connstr.Password = thePwd;//设置密码SQLite ADO.NET实现了数据库密码保护

            conn.ConnectionString = connstr.ToString();
            conn.Open();
            ////创建表
            //System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            //string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
            //cmd.CommandText = sql;
            //cmd.Connection = conn;
            //cmd.ExecuteNonQuery();
            ////插入数据
            //sql = "INSERT INTO test VALUES('a','b')";
            //cmd.CommandText = sql;
            //cmd.ExecuteNonQuery();
            ////取出数据
            //sql = "SELECT * FROM test";
            //cmd.CommandText = sql;
            //System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
            //StringBuilder sb = new StringBuilder();


            //while (reader.Read())
            //{
            //    sb.Append("username:").Append(reader.GetString(0)).Append("\n")
            //    .Append("password:").Append(reader.GetString(1));
            //}
            conn.Close();
            conn.Dispose();

            MessageBoxShowYesNoCancel(datasource, folderName);
        }


        /// <summary>
        /// 保存成功后的提示操作
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="dirName"></param>
        public void MessageBoxShowYesNoCancel(string fileName, string dirName)
        {
            DialogResult dg = MessageBox.Show(
                       "保存成功，是否复制文件或打开目录（点No）？", "选择操作", MessageBoxButtons.YesNoCancel);

            if (dg == DialogResult.Cancel)
                return;

            if (dg == DialogResult.No)
            {
                System.Diagnostics.Process.Start("explorer.exe", dirName);

                return;
            }

            if (dg == DialogResult.Yes)
            {

                System.Collections.Specialized.StringCollection stringCollectionValue
                    = new System.Collections.Specialized.StringCollection();

                stringCollectionValue.Add(fileName);

                Clipboard.SetFileDropList(stringCollectionValue);
            }
        }


        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                CreateDB();
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }
        }

        protected void CreateTable()
        {
            string sql = @"CREATE TABLE UTB_DEMO(
   ID INT PRIMARY KEY     NOT NULL,
   TheName        VARCHAR(50)    NOT NULL
);";

            string dbConnection = "Data Source=" + Application.StartupPath + "\\s.db";
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                mycommand.ExecuteNonQuery();
                mycommand.Dispose();
                mycommand = null;
                cnn.Close();
                cnn.Dispose();
                cnn = null;
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                CreateTable();
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }
        }



        protected void InsertData()
        {
            int pk = int.Parse(DateTime.Now.ToString("HHmmss"));

            string sql = @"insert into UTB_DEMO (id,thename) values (" + pk + ",'名称" + DateTime.Now.ToString("yyyyMMddHHmmss") + "')";


            string dbConnection = "Data Source=" + Application.StartupPath + "\\s.db";
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                mycommand.ExecuteNonQuery();
                mycommand.Dispose();
                mycommand = null;
                cnn.Close();
                cnn.Dispose();
                cnn = null;
                MessageBox.Show("OK");

            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }

        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                InsertData();
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }
        }

        protected void LoadData()
        {
            
            string sql = @"select * from UTB_DEMO order by id asc ";


            string dbConnection = "Data Source=" + Application.StartupPath + "\\s.db";
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                SQLiteDataAdapter da = new SQLiteDataAdapter(mycommand);

                DataSet ds = new DataSet();
                da.Fill(ds, "table_data");

                da.Dispose();
                da = null;

                mycommand.Dispose();
                mycommand = null;
                cnn.Close();
                cnn.Dispose();
                cnn = null;

                if (ds != null)
                {
                    dataGridView1.DataSource = ds.Tables[0];
                     
                }
                MessageBox.Show("OK");

            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }

        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                LoadData();
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }
        }
    }
}

上一条：

下一条：

相关评论

发表评论

类型：	留言 建议 疑问
内容：
	(Alt+Enter)