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