.NET 4.0遭遇采用sqlite

原稿发布时也:2011-12-10 —— 来源于本人的百度文章 [鉴于搬家工具导入]

http://www.csharpcity.com/sqlite-ado-net-c-4-0/

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):

图片 1

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;
        }
    }
}

网站地图xml地图