怎样获取SqLite请参考,怎样在SQLite使用Linq请参考
using System;using System.Collections.Generic;using System.Data;using System.Data.SQLite;//using System.Windows.Forms; class Program{ public static void Main() { SQLiteDatabase sqlite = new SQLiteDatabase(); sqlite.ExecuteNonQuery("create table datas(name text)"); sqlite.ExecuteNonQuery("insert into datas values('hello')"); DataTable dt = sqlite.GetDataTable("select * from datas"); Console.WriteLine(dt.Rows[0][0].ToString()); }} class SQLiteDatabase{ String dbConnection; /// /// Default Constructor for SQLiteDatabase Class. /// public SQLiteDatabase() { dbConnection = "Data Source=recipes.s3db"; } /// /// Single Param Constructor for specifying the DB file. /// /// The File containing the DB public SQLiteDatabase(String inputFile) { dbConnection = String.Format("Data Source={0}", inputFile); } /// /// Single Param Constructor for specifying advanced connection options. /// /// A dictionary containing all desired options and their values public SQLiteDatabase(Dictionary connectionOpts) { String str = ""; foreach (KeyValuePair row in connectionOpts) { str += String.Format("{0}={1}; ", row.Key, row.Value); } str = str.Trim().Substring(0, str.Length - 1); dbConnection = str; } /// /// Allows the programmer to run a query against the Database. /// /// The SQL to run /// A DataTable containing the result set. public DataTable GetDataTable(string sql) { DataTable dt = new DataTable(); try { SQLiteConnection cnn = new SQLiteConnection(dbConnection); cnn.Open(); SQLiteCommand mycommand = new SQLiteCommand(cnn); mycommand.CommandText = sql; SQLiteDataReader reader = mycommand.ExecuteReader(); dt.Load(reader); reader.Close(); cnn.Close(); } catch (Exception e) { throw new Exception(e.Message); } return dt; } /// /// Allows the programmer to interact with the database for purposes other than a query. /// /// The SQL to be run. /// An Integer containing the number of rows updated. public int ExecuteNonQuery(string sql) { SQLiteConnection cnn = new SQLiteConnection(dbConnection); cnn.Open(); SQLiteCommand mycommand = new SQLiteCommand(cnn); mycommand.CommandText = sql; int rowsUpdated = mycommand.ExecuteNonQuery(); cnn.Close(); return rowsUpdated; } /// /// Allows the programmer to retrieve single items from the DB. /// /// The query to run. /// A string. public string ExecuteScalar(string sql) { SQLiteConnection cnn = new SQLiteConnection(dbConnection); cnn.Open(); SQLiteCommand mycommand = new SQLiteCommand(cnn); mycommand.CommandText = sql; object value = mycommand.ExecuteScalar(); cnn.Close(); if (value != null) { return value.ToString(); } return ""; } /// /// Allows the programmer to easily update rows in the DB. /// /// The table to update. /// A dictionary containing Column names and their new values. /// The where clause for the update statement. /// A boolean true or false to signify success or failure. public bool Update(String tableName, Dictionary data, String where) { String vals = ""; Boolean returnCode = true; if (data.Count >= 1) { foreach (KeyValuePair val in data) { vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString()); } vals = vals.Substring(0, vals.Length - 1); } try { this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where)); } catch { returnCode = false; } return returnCode; }/// /// Allows the programmer to easily delete rows from the DB. /// /// The table from which to delete. /// The where clause for the delete. /// A boolean true or false to signify success or failure. public bool Delete(String tableName, String where) { Boolean returnCode = true; try { this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where)); } catch (Exception fail) { Console.WriteLine(fail.Message); returnCode = false; } return returnCode; } /// /// Allows the programmer to easily insert into the DB /// /// The table into which we insert the data. /// A dictionary containing the column names and data for the insert. /// A boolean true or false to signify success or failure. public bool Insert(String tableName, Dictionary data) { String columns = ""; String values = ""; Boolean returnCode = true; foreach (KeyValuePair val in data) { columns += String.Format(" {0},", val.Key.ToString()); values += String.Format(" '{0}',", val.Value); } columns = columns.Substring(0, columns.Length - 1); values = values.Substring(0, values.Length - 1); try { this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values)); } catch (Exception fail) { Console.WriteLine(fail.Message); returnCode = false; } return returnCode; } /// /// Allows the programmer to easily delete all data from the DB. /// /// A boolean true or false to signify success or failure. public bool ClearDB() { DataTable tables; try { tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;"); foreach (DataRow table in tables.Rows) { this.ClearTable(table["NAME"].ToString()); } return true; } catch { return false; } } /// /// Allows the user to easily clear all data from a specific table. /// /// The name of the table to clear. /// A boolean true or false to signify success or failure. public bool ClearTable(String table) { try { this.ExecuteNonQuery(String.Format("delete from {0};", table)); return true; } catch { return false; } }}