C# Sqlit应用举例 判断表、字段是否存在的方法，新增、删除、重命名列

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Data.SQLite; using System.Windows.Forms; namespace TestSQLite { public class SqliteHelper { //连接字符串 private static string str ="Data source="+ AppDomain.CurrentDomain.BaseDirectory + "\\DATA.db"; /// <summary> /// 增删改 /// 20180723 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">sql参数</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string sql, params SQLiteParameter[] param) { try { using (SQLiteConnection con = new SQLiteConnection(str)) { using (SQLiteCommand cmd = new SQLiteCommand(sql, con)) { con.Open(); if (param != null) { cmd.Parameters.AddRange(param); } string sql2 = cmd.CommandText; //con.Close(); return cmd.ExecuteNonQuery(); } } } catch (SQLiteException se) { return 0; } } /// <summary> /// 增删改 /// 20180723 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">sql参数</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string sql) { try { using (SQLiteConnection con = new SQLiteConnection(str)) { using (SQLiteCommand cmd = new SQLiteCommand(sql, con)) { con.Open(); string sql2 = cmd.CommandText; con.Close(); return cmd.ExecuteNonQuery(); } } } catch (SQLiteException se) { return 0; } } /// <summary> /// 查询 /// 20180723 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">sql参数</param> /// <returns>首行首列</returns> public static object ExecuteScalar(string sql) { try { using (SQLiteConnection con = new SQLiteConnection(str)) { using (SQLiteCommand cmd = new SQLiteCommand(con)) { con.Open(); cmd.CommandText = sql; return cmd.ExecuteScalar(); } } } catch(Exception ex){ MessageBox.Show(ex.Message ); return null; } } /// <summary> /// 查询 /// 20180723 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">sql参数</param> /// <returns>首行首列</returns> public static object ExecuteScalar(string sql, params SQLiteParameter[] param) { using (SQLiteConnection con = new SQLiteConnection(str)) { using (SQLiteCommand cmd = new SQLiteCommand(sql, con)) { con.Open(); if (param != null) { cmd.Parameters.AddRange(param); } return cmd.ExecuteScalar(); } } } /// <summary> /// 多行查询 /// 20180723 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">sql参数</param> /// <returns>SQLiteDateReader</returns> public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] param) { using (SQLiteConnection con = new SQLiteConnection(str)) { using (SQLiteCommand cmd = new SQLiteCommand(sql, con)) { if (param != null) { cmd.Parameters.AddRange(param); } try { con.Open(); return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } catch (Exception ex) { con.Close(); con.Dispose(); throw ex; } } } } /// <summary> /// 查询多行数据 /// 20180723 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">sql参数</param> /// <returns>一个表</returns> public static DataTable ExecuteTable(string sql, params SQLiteParameter[] param) { DataTable dt = new DataTable(); using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, str)) { if (param != null) { sda.SelectCommand.Parameters.AddRange(param); } sda.Fill(dt); } return dt; } /// <summary> /// 数据插入 /// 20180725 /// </summary> /// <param name="tbName">表名</param> /// <param name="insertData">需要插入的数据字典</param> /// <returns>受影响行数</returns> public static int ExecuteInsert(string tbName, Dictionary<String, String> insertData) { string point = "";//分隔符号(,) string keyStr = "";//字段名拼接字符串 string valueStr = "";//值的拼接字符串 List<SQLiteParameter> param = new List<SQLiteParameter>(); foreach (string key in insertData.Keys) { keyStr += string.Format("{0} `{1}`", point, key); valueStr += string.Format("{0} @{1}", point, key); param.Add(new SQLiteParameter("@" + key, insertData[key])); point = ","; } string sql = string.Format("INSERT INTO `{0}`({1}) VALUES({2})", tbName, keyStr, valueStr); //return sql; return ExecuteNonQuery(sql, param.ToArray()); } /// <summary> /// 执行Update语句 /// 20180725 /// </summary> /// <param name="tbName">表名</param> /// <param name="where">更新条件：id=1</param> /// <param name="insertData">需要更新的数据</param> /// <returns>受影响行数</returns> public static int ExecuteUpdate(string tbName, string where, Dictionary<String, String> insertData) { string point = "";//分隔符号(,) string kvStr = "";//键值对拼接字符串(Id=@Id) List<SQLiteParameter> param = new List<SQLiteParameter>(); foreach (string key in insertData.Keys) { kvStr += string.Format("{0} {1}=@{2}", point, key, key); param.Add(new SQLiteParameter("@" + key, insertData[key])); point = ","; } string sql = string.Format("UPDATE `{0}` SET {1} WHERE {2}", tbName, kvStr, where); return ExecuteNonQuery(sql, param.ToArray()); } /// <summary> /// 查询 /// 20180723 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">sql参数</param> /// <returns>首行首列</returns> public static DataTable GetDataTable(string sql) { try { using (SQLiteConnection con = new SQLiteConnection(str)) { using (SQLiteCommand cmd = new SQLiteCommand(sql , con)) { con.Open(); SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd); DataTable tb = new DataTable(); ad.Fill(tb); con.Close(); return tb; } } } catch (Exception ex) { MessageBox.Show(ex.Message); return null; } } } } 　　 Test: //see one value where ......... MessageBox.Show("" + SqliteHelper.ExecuteScalar("select uid from members where uid<>''")); //see sql create syntax DataTable tb = SqliteHelper.GetDataTable("select * from sqlite_master where name = 'members'"); MessageBox.Show("" + tb.Rows[0]["sql"]); //or: //string syntax = "" + SqliteHelper.ExecuteScalar("select sql from sqlite_master where name = 'members'"); //MessageBox.Show(syntax); //see if column exists in table 'members' bool hasCol = int.Parse("" + SqliteHelper.ExecuteScalar("select count(name) from sqlite_master where name = 'members' and sql like '%sendAccount%'")) > 0; if (!hasCol) { //add column: ALTER TABLE table-name ADD COLUMN column-name column-type SqliteHelper.ExecuteScalar("alter table members add COLUMN sendAccount varchar(20)"); //alter table student drop column name // 该行在SQlite中不能用，SQlite不支持drop //SqliteHelper.ExecuteScalar("alter table members drop sendAccount33"); } //drop table if exists A string s = "" + SqliteHelper.ExecuteScalar("drop table if exists members2"); MessageBox.Show(s);