1、C#操作SQLiteDBC#操作SQLite DatabaseC#下SQLite操作驱动dll下载：System.Data.SQLiteC#使用SQLite步骤：（1）新建一个project（2）添加SQLite操作驱动dll引用（3）使用API操作SQLite DataBaseusing System;using System.Data.SQLite;namespace SQLiteSamples class Program /数据库连接 SQLiteConnection m_dbConnection; static void Main(string args) Program p = ne
2、w Program(); public Program() createNewDatabase(); connectToDatabase(); createTable(); fillTable(); printHighscores(); /创建一个空的数据库 void createNewDatabase() SQLiteConnection.CreateFile(MyDatabase.sqlite); /创建一个连接到指定数据库 void connectToDatabase() m_dbConnection = new SQLiteConnection(Data Source=MyDataba
3、se.sqlite;Version=3;); m_dbConnection.Open(); /在指定数据库中创建一个table void createTable() string sql = create table highscores (name varchar(20), score int); SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection); command.ExecuteNonQuery(); /插入一些数据 void fillTable() string sql = insert into highscor
4、es (name, score) values (Me, 3000); SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection); command.ExecuteNonQuery(); sql = insert into highscores (name, score) values (Myself, 6000); command = new SQLiteCommand(sql, m_dbConnection); command.ExecuteNonQuery(); sql = insert into highscores (
5、name, score) values (And I, 9001); command = new SQLiteCommand(sql, m_dbConnection); command.ExecuteNonQuery(); /使用sql查询语句，并显示结果 void printHighscores() string sql = select * from highscores order by score desc; SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection); SQLiteDataReader reader =
6、 command.ExecuteReader(); while (reader.Read() Console.WriteLine(Name: + readername + tScore: + readerscore); Console.ReadLine(); 操作SQLite Database的C#帮助类SQLite Helper将一些常用的功能封装一下，封装成SQLite Helper类using System;using System.Data;using System.Text.RegularExpressions;using System.Xml;using System.IO;usi
7、ng System.Collections;using System.Data.SQLite;namespace DBUtility.SQLite / / SQLiteHelper is a utility class similar to SQLHelper in MS / Data Access Application Block and follows similar pattern. / public class SQLiteHelper / / Creates a new instance. The ctor is marked private since all members a
8、re static. / private SQLiteHelper() / / Creates the command. / / Connection. / Command text. / Command parameters. / SQLite Command public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter commandParameters) SQLiteCommand cmd = new SQLiteComma
9、nd(commandText, connection); if (commandParameters.Length 0) foreach (SQLiteParameter parm in commandParameters) cmd.Parameters.Add(parm); return cmd; / / Creates the command. / / Connection string. / Command text. / Command parameters. / SQLite Command public static SQLiteCommand CreateCommand(stri
10、ng connectionString, string commandText, params SQLiteParameter commandParameters) SQLiteConnection cn = new SQLiteConnection(connectionString); SQLiteCommand cmd = new SQLiteCommand(commandText, cn); if (commandParameters.Length 0) foreach (SQLiteParameter parm in commandParameters) cmd.Parameters.
11、Add(parm); return cmd; / / Creates the parameter. / / Name of the parameter. / Parameter type. / Parameter value. / SQLiteParameter public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue) SQLiteParameter parameter = new SQLiteParam
12、eter(); parameter.DbType = parameterType; parameter.ParameterName = parameterName; parameter.Value = parameterValue; return parameter; / / Shortcut method to execute dataset from SQL Statement and object arrray of parameter values / / SQLite Connection string / SQL Statement with embedded param styl
13、e parameter names / object array of parameter values / public static DataSet ExecuteDataSet(string connectionString, string commandText, object paramList) SQLiteConnection cn = new SQLiteConnection(connectionString); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; if (paramLis
14、t != null) AttachParameters(cmd,commandText, paramList); DataSet ds = new DataSet(); if (cn.State = ConnectionState.Closed) cn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Dispose(); cn.Close(); return ds; / / Shortcut method to execute dataset from SQL S
15、tatement and object arrray of parameter values / / Connection. / Command text. / Param list. / public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object paramList) SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; if (paramList != null) AttachParameter
16、s(cmd,commandText, paramList); DataSet ds = new DataSet(); if (cn.State = ConnectionState.Closed) cn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Dispose(); cn.Close(); return ds; / / Executes the dataset from a populated Command object. / / Fully populat
17、ed SQLiteCommand / DataSet public static DataSet ExecuteDataset(SQLiteCommand cmd) if (cmd.Connection.State = ConnectionState.Closed) cmd.Connection.Open(); DataSet ds = new DataSet(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Connection.Close(); cmd.Dispose()
18、; return ds; / / Executes the dataset in a SQLite Transaction / / SQLiteTransaction. Transaction consists of Connection, Transaction, / and Command, all of which must be created prior to making this method call. / Command text. / Sqlite Command parameters. / DataSet / user must examine Transaction O
19、bject and handle transaction.connection .Close, etc. public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, params SQLiteParameter commandParameters) if (transaction = null) throw new ArgumentNullException(transaction); if (transaction != null & transaction.Connectio
20、n = null) throw new ArgumentException(The transaction was rolled back or committed, please provide an open transaction., transaction); IDbCommand cmd = transaction.Connection.CreateCommand(); cmd.CommandText = commandText; foreach (SQLiteParameter parm in commandParameters) cmd.Parameters.Add(parm);
21、 if (transaction.Connection.State = ConnectionState.Closed) transaction.Connection.Open(); DataSet ds = ExecuteDataset(SQLiteCommand)cmd); return ds; / / Executes the dataset with Transaction and object array of parameter values. / / SQLiteTransaction. Transaction consists of Connection, Transaction
22、, / and Command, all of which must be created prior to making this method call. / Command text. / object array of parameter values. / DataSet / user must examine Transaction Object and handle transaction.connection .Close, etc. public static DataSet ExecuteDataset(SQLiteTransaction transaction, stri
23、ng commandText, object commandParameters) if (transaction = null) throw new ArgumentNullException(transaction); if (transaction != null & transaction.Connection = null) throw new ArgumentException(The transaction was rolled back or committed, please provide an open transaction., transaction); IDbCom
24、mand cmd = transaction.Connection.CreateCommand(); cmd.CommandText = commandText; AttachParameters(SQLiteCommand)cmd,cmd.CommandText, commandParameters); if (transaction.Connection.State = ConnectionState.Closed) transaction.Connection.Open(); DataSet ds = ExecuteDataset(SQLiteCommand)cmd); return d
25、s; #region UpdateDataset / / Executes the respective command for each inserted, updated, or deleted row in the DataSet. / / / e.g.: / UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, Order); / / A valid SQL statement to insert new records into the data source / A valid SQL statement to delete records from the data source / A valid SQL statement used to update records in the data source / The DataSet used to update the data source / The DataTable used to update the data source. p
