.NET 4.0中使用sqlite

Getting SQLite to run (the ADO.NET version) under C# 4.0 is a bit tricky. You can follow the steps below to get it to work.

First, download SQLite (the ADO.NET version) from here. This is the sourceforge page for the official project. Install it.

Second, download SQLiteAdmin, a free, awesome GUI tool for SQLite. You can get it here.

Next, create a database using SQLiteAdmin. Add a single table with a single row, so that you can test that things work.

Then, launch up Visual Studio 2010 and create a new project. Add a reference to the System.Data.SQLite.dll file (located in SQLite.NETbin). I recommend copying the DLL into an “extlib” folder in your project and referencing that.

Finally, run a query and see how things go! Here’s a quick method I hacked together:        public static DataTable ExecuteQuery(string sql)        {            // Validate SQL            if (string.IsNullOrWhiteSpace(sql))            {                return null;            }            else            {                if (!sql.EndsWith(“;”))                {                    sql += “;”;                }                SQLiteConnection connection = new SQLiteConnection(“Data Source=blah.db”);                connection.Open();                SQLiteCommand cmd = new SQLiteCommand(connection);                cmd.CommandText = sql;                DataTable dt = new DataTable();                SQLiteDataReader reader = cmd.ExecuteReader();                dt.Load(reader);                reader.Close();                connection.Close();                return dt;            }        }

The only wierd thing is that the function returns a DataTable, which essentially makes it easy for you to iterate.

Then, call this function, and trace out the results:DataTable table = ExecuteQuery(“SELECT * FROM someTable”);foreach (DataRow row in table.Rows) {  Debug.Trace(“Record: id=” + row[“id”] + ” name=” + row[“name”]);}

But wait! You get this annoying and un-googlable error:

Mixed mode assembly is built against version ‘v2.0.50727’ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

Ouch! It seems like you’re running a .NET 2.0 library, which is ok, but it has unmanaged code in it, which may or may not be ok; we need to tell our project explicitly that we can run it.

Right-click on your project, and add a new file; select “Application Config File” (depicted below):

Paste the following XML inside the configuration tag:<startup useLegacyV2RuntimeActivationPolicy=”true”>    <supportedRuntime version=”v4.0″/>  </startup>

And viola! The exception should disappear. You should see something like (assuming you inserted two names):Record: id=1 Name=CSharpCityRecord: id=2 Name=Google

That wasn’t so hard, now was it?

===

using System.Configuration; using System.Data; using System.Data.SQLite; namespace SQLliteTest {     public class SqliteHelper     {         /// <summary>         /// 获得连接对象         /// </summary>         /// <returns></returns>         public static SQLiteConnection GetSQLiteConnection()         {             return                 new SQLiteConnection(ConfigurationManager.ConnectionStrings[“db”].ConnectionString);         }         private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params SQLiteParameter[] commandParameters)         {             if (conn.State != ConnectionState.Open)             {                 conn.Open();             }             cmd.Parameters.Clear();             cmd.Connection = conn;             cmd.CommandText = cmdText;             cmd.CommandType = CommandType.Text;             cmd.CommandTimeout = 30;             if (commandParameters != null)             {                 cmd.Parameters.AddRange(commandParameters);             }         }         public static DataSet ExecuteDataset(string cmdText, params SQLiteParameter[] commandParameters)         {             var ds = new DataSet();             var command = new SQLiteCommand();             using (SQLiteConnection connection = GetSQLiteConnection())             {                 PrepareCommand(command, connection, cmdText, commandParameters);                 var da = new SQLiteDataAdapter(command);                 da.Fill(ds);             }             return ds;         }         public static DataRow ExecuteDataRow(string cmdText, params SQLiteParameter[] commandParameters)         {             DataSet ds = ExecuteDataset(cmdText, commandParameters);             if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)                 return ds.Tables[0].Rows[0];             return null;         }         /// <summary>         /// 返回受影响的行数         /// </summary>         /// <param name=”cmdText”>a</param>         /// <param name=”commandParameters”></param>         /// <returns></returns>         public static int ExecuteNonQuery(string cmdText, params SQLiteParameter[] commandParameters)         {             var command = new SQLiteCommand();             using (SQLiteConnection connection = GetSQLiteConnection())             {                 PrepareCommand(command, connection, cmdText, commandParameters);                 return command.ExecuteNonQuery();             }         }         /// <summary>         /// 返回SqlDataReader对象         /// </summary>         /// <param name=”cmdText”></param>         /// <param name=”commandParameters”>传入的参数</param>         /// <returns></returns>         public static SQLiteDataReader ExecuteReader(string cmdText, params SQLiteParameter[] commandParameters)         {             var command = new SQLiteCommand();             SQLiteConnection connection = GetSQLiteConnection();             try             {                 PrepareCommand(command, connection, cmdText, commandParameters);                 SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);                 return reader;             }             catch             {                 connection.Close();                 throw;             }         }         /// <summary>         /// 返回结果集中的第一行第一列，忽略其他行或列         /// </summary>         /// <param name=”cmdText”></param>         /// <param name=”commandParameters”>传入的参数</param>         /// <returns></returns>         public static object ExecuteScalar(string cmdText, params SQLiteParameter[] commandParameters)         {             var cmd = new SQLiteCommand();             using (SQLiteConnection connection = GetSQLiteConnection())             {                 PrepareCommand(cmd, connection, cmdText, commandParameters);                 return cmd.ExecuteScalar();             }         }         /// <summary>         /// 分页         /// </summary>         /// <param name=”recordCount”></param>         /// <param name=”pageIndex”></param>         /// <param name=”pageSize”></param>         /// <param name=”cmdText”></param>         /// <param name=”countText”></param>         /// <param name=”commandParameters”></param>         /// <returns></returns>         public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText,                                            string countText, params SQLiteParameter[] commandParameters)         {             if (recordCount < 0)                 recordCount = int.Parse(ExecuteScalar(countText, commandParameters).ToString());             var ds = new DataSet();             var command = new SQLiteCommand();             using (SQLiteConnection connection = GetSQLiteConnection())             {                 PrepareCommand(command, connection, cmdText, commandParameters);                 var da = new SQLiteDataAdapter(command);                 da.Fill(ds, (pageIndex – 1) * pageSize, pageSize, “result”);             }             return ds;         }     } }