直接上代码:
class="csharpcode"> class DBHelperSqlite { ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); private string dbName = "cmcc2.db"; private string connectionString = "Data Source=cmcc2.db;Pooling=true;FailIfMissing=false"; /// <summary> /// if the db is not exists,create it and create the table /// </summary> public DBHelperSqlite() { string SQLCreateStr = "CREATE TABLE [cmcc_businesses_info] (businessName nvarchar(50),businessDate nvarchar(10),indb_datetime nvarchar(20))"; #region CASE2 FileInfo fi = new FileInfo(dbName); if (fi.Exists == false) { logger.InfoFormat("db不存在"); //Console.WriteLine("db不存在"); SQLiteConnection.CreateFile(dbName); logger.InfoFormat("db创建完成"); using (SQLiteConnection conn = new SQLiteConnection(connectionString)) { conn.Open(); using (SQLiteCommand cmd = new SQLiteCommand(SQLCreateStr, conn)) { if (cmd.ExecuteNonQuery() > 0) { logger.InfoFormat("表创建成功"); //Console.WriteLine("表创建成功"); } } } } #endregion }//ctor /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public void ExecuteSqlTran(IList<string> SQLStringList) { using (SQLiteConnection conn = new SQLiteConnection(connectionString)) { conn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = conn; SQLiteTransaction tran = conn.BeginTransaction(); cmd.Transaction = tran; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tran.Commit(); } catch (System.Data.SQLite.SQLiteException ex) { logger.Error(ex); tran.Rollback(); throw new Exception(ex.Message); } } }//trans /// <summary> /// execute sql.get the return /// </summary> /// <returns></returns> public IDictionary<string, string> ExecuteReader() { IDictionary<string, string> dic = new Dictionary<string, string>(); using (SQLiteConnection conn = new SQLiteConnection(connectionString)) { conn.Open(); string SQLStr = "select businessname,max(businessDate) from cmcc_businesses_info group by businessname"; using (SQLiteCommand cmd = new SQLiteCommand(SQLStr, conn)) { SQLiteDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string key = reader.GetString(0); string value = reader.GetString(1); if (dic.ContainsKey(key)) { Console.WriteLine("已存在:" + key + "\t" + value); } else { dic.Add(key, value); } } } } return dic; }