C# SQLite使用举例查询数据库所有表所有列，插入表和插入的方法

主窗体类

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 SqlitTest
{
    public partial class Form1 : Form
    {
        static  string DbName = "testc";
        static string CurTable = "表2";
        static string CurColNum = "列1";
        static string filePath = "db";
        public Form1()
        {
            InitializeComponent();
            UpdateDbname();
        }
        private void UpdateDbname()
        {
            string[] files = Directory.GetFiles(filePath, "*.db");
            foreach (string i in files)
            {
                string str = i.Replace($"{filePath}\\","");
                str = str.Replace(".db", "");
               if( !comboBoxDbNameList.Items.Contains(str))
                comboBoxDbNameList.Items.Add(str);
            }
            if (comboBoxDbNameList.Items.Contains(DbName))
            {
                int m = 0;
                foreach (string j in comboBoxDbNameList.Items)
                {
                    if (DbName == j)
                    {
                      //  comboBoxDbNameList.SelectedIndex = m;
                    }
                    m++;
                }
            }
            UpdateTbName2();
        }
        //显示表信息的第一中方法
        private void UpdateTbName()
        {
            DataTable dt = new DataTable();
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "select * from sqlite_master where type = 'table' order by name; ";                 
                    using (SQLiteDataAdapter sda = new SQLiteDataAdapter(command))
                    {

                        sda.Fill(dt);//将数据库中表信息显示
                    }
                    comboBoxTbNameList.Items.Clear();
                    if (dt.Rows.Count > 0)
                    {
                        DataRow[] tbs = dt.Select("type = 'table'");
                        foreach (var tb in tbs)
                        comboBoxTbNameList.Items.Add(tb["name"]);
                    }
                    if (comboBoxTbNameList.Items.Contains(CurTable))
                    {
                        DataTable dt2 = new DataTable();
                        command.CommandText = $"select * from {CurTable}; ";//读取表中元素
                        using (SQLiteDataAdapter sda = new SQLiteDataAdapter(command))
                        {
                            sda.Fill(dt2);
                        }
                        dataGridView1.DataSource = dt2;//将表中所有信息显示
                        int m = 0;
                        foreach (string j in comboBoxTbNameList.Items)
                        {
                            if (CurTable == j)
                            {
                              //  comboBoxTbNameList.SelectedIndex = m;
                            }
                            m++;
                        }
                    }
                    

                }
                connection.Close();
               
            }

           



        }
        //显示表信息的第二种方法
        private void UpdateTbName2()
        {
            DataTable dt = new DataTable();
            List<string> tableNameList = new List<string>();
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
            {
                connection.Open();
                string sqlTableNames = "select name from sqlite_master where type='table' order by name;";
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = sqlTableNames;
                    using (SQLiteDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                         tableNameList.Add((string)dr["Name"]);

                    }
                    comboBoxTbNameList.Items.Clear();
                    if (tableNameList.Count > 0)
                    {
                        foreach (var tb in tableNameList)
                        {
                            comboBoxTbNameList.Items.Add(tb);
                        }
                    }
                    if (comboBoxTbNameList.Items.Contains(CurTable))
                    {
                        DataTable dt2 = new DataTable();
                        command.CommandText = $"select * from {CurTable}; ";//读取表中元素
                        using (SQLiteDataAdapter sda = new SQLiteDataAdapter(command))
                        {
                            sda.Fill(dt2);
                        }
                        dataGridView1.DataSource = dt2;//将表中所有信息显示
                        int m = 0;
                        foreach (string j in comboBoxTbNameList.Items)
                        {
                            if (CurTable == j)
                            {
                                //  comboBoxTbNameList.SelectedIndex = m;
                            }
                            m++;
                        }
                    }
                    else
                        dataGridView1.DataSource = null;


                }
                connection.Close();

            }
            UpdateColmnu();
        }
        //显示列元素的方法
        private void UpdateColmnu( )
        {
            DataTable dt = new DataTable();
            List<string> ColNameList = new List<string>();
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                // 获取表中的所有字段名
                string sqlfieldName = $"Pragma Table_Info( {CurTable}  )"; 
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = sqlfieldName;
                    using (SQLiteDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                            ColNameList.Add((string)dr["Name"]);

                    }
                    comboBoxColmunList.Items.Clear();
                    if (ColNameList.Count > 0)
                    {
                        
                        foreach (var tb in ColNameList)
                                comboBoxColmunList.Items.Add(tb);
                    }
                    if (comboBoxColmunList.Items.Contains(CurColNum))
                    {                                             
                        int m = 0;
                        foreach (string j in comboBoxColmunList.Items)
                        {
                            if (CurColNum == j)
                            {
                             //   comboBoxColmunList.SelectedIndex = m;
                            }
                            m++;
                        }
                    }


                }
                connection.Close();

            }




        }

        private void button1_Click(object sender, EventArgs e)
        {

          string   dbname = textBoxDbName.Text;
            if (dbname == ""|| dbname == "请输入数据库名")
            {
                MessageBox.Show("请输入数据库名");
                return;
            }
            DbName = dbname;
          
            string[] files = Directory.GetFiles(filePath, "*.db");
            foreach(string i in files)
            {
                string str = $"{filePath}\\{dbname}.db";
                if (str == i)
                {
                    MessageBox.Show("已经包含这个数据库");
                    return;
                }
            }
            SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{dbname}.db");
            connection.Open();
            connection.Close();
            
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string mtableName = textBoxTableName.Text;
            if (mtableName == ""|| mtableName == "请输入表名")
            {
                MessageBox.Show("请输入表名");
                return;
            }         
            if (comboBoxTbNameList.Items.Contains(mtableName))
            {
                MessageBox.Show("已经包含该表");
                return;
            }
            CreatCol mform = new CreatCol();
            mform.TopMost = true;
            mform.ShowDialog();
            if (mform.DialogResult == DialogResult.Cancel)
                return;
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    SqLiteHelper mm = new SqLiteHelper(command);
                    SqLiteTable mtable = new SqLiteTable (mtableName);
                    mtable.Columns.Add(mform.mclo);
                    mm.CreateTable(mtable);              
                }
                connection.Close();
            }          
            CurTable = mtableName;
            UpdateDbname();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            CreatCol mform = new CreatCol();
            mform.TopMost = true;
            mform.ShowDialog();
            if (mform.DialogResult == DialogResult.Cancel)
                return;
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    SqLiteHelper mm = new SqLiteHelper(command);                       
                    mm.AddColmnu(CurTable, mform.mclo);
                }
                connection.Close();
            }
            UpdateDbname();
        }

        private void comboBoxDbNameList_SelectedValueChanged(object sender, EventArgs e)
        {
            DbName=  comboBoxDbNameList.Text;
            UpdateDbname();
        }

        private void textBoxTableName_TextChanged(object sender, EventArgs e)
        {

        }

        private void button4_Click(object sender, EventArgs e)
        {
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    string sql = $"INSERT INTO {CurTable}({CurColNum}) VALUES(@ID1) ";
                    command.CommandText = sql;
                    command.Parameters.Add(new SQLiteParameter("ID1", textBoxColValue.Text));
                    command.ExecuteNonQuery();
                }
                connection.Close();
             }
            UpdateDbname();
        }

        private void comboBoxTbNameList_SelectedIndexChanged(object sender, EventArgs e)
        {
            CurTable = comboBoxTbNameList.Text;
            UpdateDbname();
        }

        private void comboBoxColmunList_SelectedIndexChanged(object sender, EventArgs e)
        {
            CurColNum = comboBoxColmunList.Text;
            UpdateDbname();
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
           
        }
    }
}

弹窗窗体类

using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SqlitTest
{
    public partial class CreatCol : Form
    {
        public CreatCol()
        {
            InitializeComponent();
        }
       public   SqLiteColumn mclo;
        public static bool bOk;

        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
            {
                MessageBox.Show("请输入列名");
                this.DialogResult=  DialogResult.Cancel;
                return;
            }
            mclo = new SqLiteColumn(textBox1.Text);
            mclo.PrimaryKey = checkBoxPreKey.Checked;
            mclo.NotNull = checkBoxNotNull.Checked;
            mclo.AutoIncrement = checkBoxAutoIncrease.Checked;
            if(comboBox1.SelectedIndex==0)
            {
                mclo.ColDataType = ColType.Text;

            }
            else if (comboBox1.SelectedIndex ==1)
            {
                mclo.ColDataType = ColType.DateTime;

            }
            bOk = true;

            this.DialogResult = DialogResult.OK;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.Cancel;
        }
    }
}