SQLite_test

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;

namespace SQLite_test
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            //Program();
        }

        //数据库连接
        SQLiteConnection m_dbConnection;
        //string FilePath;

        //作废
        public void Program()
        {
            createNewDatabase();
            connectToDatabase();
            createTable();
            fillTable();
            printHighscores();
        }

        //创建一个空的数据库
        void createNewDatabase()
        {
            string FilePath = Application.StartupPath + "\\" + txb_souce.Text + ".sqlite";
            if (!File.Exists(FilePath))
            {
                SQLiteConnection.CreateFile(FilePath);
            }
            else
            {
                MessageBox.Show("数据库已存在");
            }

            //SQLiteConnection.CreateFile("MyDatabase.sqlite");
        }

        //创建一个连接到指定数据库
        void connectToDatabase()
        {
            string FilePath = Application.StartupPath + "\\" + txb_souce.Text + ".sqlite";

            try
            {
                m_dbConnection = new SQLiteConnection("Data Source=" + FilePath + ";Version=3;");
                m_dbConnection.Open();
                lab_status.Text = "连接成功";
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
                throw new Exception("打开数据库：" + FilePath + "的连接失败：" + ex.Message);
            }
            //m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
            //m_dbConnection.Open();

        }

        //在指定数据库中创建一个table
        void createTable()
        {

            string sql = "create table " + txb_lable.Text + " (name varchar(20), score int)";
            //string sql = "create table highscores (name varchar(20), score int)";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();

        }

        //插入一些数据
        void fillTable()
        {
            string name = txb_tag1.Text;
            int score = Convert.ToInt32(txb_tag2.Text);
            string sql = "insert into highscores (name, score) values ('" + name + "'," + score + ")";  //highscores为数据表名称
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);  //同creattable中一样，可以共用
            command.ExecuteNonQuery();  //每个命令需要触发一次
            lab_status.Text = "插入成功";
        }

        //使用sql查询语句，并显示结果
        void printHighscores()
        {
            txb_result.Clear();
            string sql = "select * from highscores order by score desc";  //desc降序，*不限，即读取所有项按score降序排列
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
                txb_result.Text = "Name: " + reader["name"] + "\tScore: " + reader["score"] + "\r\n" + txb_result.Text;
            }
            /*
            SQLiteCommand command = new SQLiteCommand(m_dbConnection); //重载2，单参数，只有连接对象
            command.CommandText = "select name from highscores where score =123";  //另一种写法
            var name = command.ExecuteScalar();
            Console.WriteLine(Name.ToString());
            */
        }

        private void btn_con_Click(object sender, EventArgs e)
        {
            connectToDatabase();
        }

        private void btn_dis_Click(object sender, EventArgs e)
        {
            printHighscores();
        }

        private void btn_lab_Click(object sender, EventArgs e)
        {
            createTable();
        }

        private void btn_newdb_Click(object sender, EventArgs e)
        {
            createNewDatabase();
        }

        private void btn_write_Click(object sender, EventArgs e)
        {
            fillTable();
        }

        //删除数据
        private void btn_delete_Click(object sender, EventArgs e)
        {
            try
            {
                string sql = "delete from " + txb_lable.Text + " where name='" + txb_tag1.Text + "'";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                command.ExecuteNonQuery();
                lab_status.Text = "删除成功";
            }
            catch (Exception ex)
            {
                throw new Exception("删除失败" + ex.Message);
            }
        }

        //查询数据表名
        private void btn_dis_table_Click(object sender, EventArgs e)
        {
            // 获取数据库中的所有表名
            string sqlTableNames = "select name from sqlite_master where type='table' order by name;";
            // 创建命令对象
            SQLiteCommand cmd = new SQLiteCommand(sqlTableNames, m_dbConnection);
            using (SQLiteDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    // 表名
                    txb_result.Text = dr["Name"] +"\r\n";
                }
            }
        }

        //修改数据
        private void btn_modify_Click(object sender, EventArgs e)
        {
            try
            {
                string sql = "update " + txb_lable.Text + " set score = " + Convert.ToInt32(txb_tag2.Text) + " where name='" + txb_tag1.Text + "'";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("更新数据失败" + ex.Message);
            }
        }
    }
}