Sqlite通用类库
using System; using System.Collections; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Data; using System.Data.SQLite; using System.Windows.Forms; namespace CommonLib { public static class DB_Sqlite { public static void CreateDB(string datasource,ArrayList sqlList) { if ((datasource == null) || (datasource == "") || (datasource.Length <= 0)) { MessageBox.Show("数据库名不能为空"); return; } if ((sqlList == null) || (sqlList.Count <= 0)) { MessageBox.Show("创建数据库表的语句不能为空"); return; } try { if (!File.Exists(@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; //connstr.Password = "";//设置密码,SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open(); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); foreach(Object OO in sqlList) { cmd.CommandText = OO.ToString(); cmd.Connection = conn; cmd.ExecuteNonQuery(); } conn.Close(); } } catch (Exception ex) { MessageBox.Show("SQLite数据库:" + ex.ToString()); } } public static Int32 Fun_ExeUpdate(string strSql, string datasource) { Int32 nExcnt = 0; try { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(); System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); conn = new System.Data.SQLite.SQLiteConnection(); connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; //connstr.Password = "";//设置密码,SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open(); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn); cmd.CommandText = strSql; nExcnt = cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); #region 已经注释的代码 //using (SQLiteConnection connection = new SQLiteConnection(connstr.ToString ())) //{ // connection.Open(); // using (SQLiteCommand command=new SQLiteCommand (strSql ,connection )) // { // command.ExecuteNonQuery(); // command.Dispose(); // } // connection.Close(); //} #endregion } catch (Exception ex) { MessageBox.Show("SQLite数据库:" + ex.ToString()); } return nExcnt; } public static Object Fun_ExeScaler(string strSql, string datasource) { Object nExcnt = 0; try { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(); System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); conn = new System.Data.SQLite.SQLiteConnection(); connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; //connstr.Password = "";//设置密码,SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open(); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn); cmd.CommandText = strSql; nExcnt = cmd.ExecuteScalar(); cmd.Dispose(); conn.Close(); } catch (Exception ex) { MessageBox.Show("SQLite数据库:" + ex.ToString()); } return nExcnt; } public static Int32 Fun_ExeUpdate_TR(ref ArrayList strColl, string datasource) { Int32 nExcnt = 0; SQLiteTransaction tran = null; try { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(); System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; //connstr.Password = "";//设置密码,SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open(); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn); tran = conn.BeginTransaction(); cmd.Transaction = tran; foreach (object obj in strColl) { cmd.CommandText = obj.ToString(); cmd.ExecuteNonQuery(); } tran.Commit(); } catch { tran.Rollback(); } return nExcnt; } public static DataTable Fun_Quary(string strsql, string datasource) { DataTable data = new DataTable(); try { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(); System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); conn = new System.Data.SQLite.SQLiteConnection(); connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; //connstr.Password = "";//设置密码,SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open(); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn); cmd.CommandText = strsql; SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); adapter.Fill(data); cmd.Dispose(); conn.Close(); } catch (Exception ex) { MessageBox.Show("SQLite数据库:" + ex.ToString()); } return data; } } }