[C#开发] SQLite with Entity Framework Code First

Entity Framework Code First默认使用SQL Server，这里提供使用SQLite的方法。

Prerequisites

支持NuGet和Entity Framework Code First的Visual Studio。

Steps

1. 新建工程，在Solution Explorer里右键工程，选Manage NuGet Packages。
2. 找到System.Data.SQLite并安装。
3. 参考Entity Framework Code First，新建一个Context（如UserContext），然后在.config（如Web.config）中加入连接字符串连接数据库mydb.db：

  <connectionStrings>
    <add name="UserContext" connectionString="Data Source=|DataDirectory|mydb.db" providerName="System.Data.SQLite.EF6" />
  </connectionStrings>

同时修改（不加入System.Data.SQLite会出错，参考http://www.codeproject.com/Questions/731487/No-Entity-Framework-provider-found-for-the-ADO-NET）

    <DbProviderFactories>
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite.EF6" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>

这样理论上就能通过DbContext以及DbSet等Entity Framework Code First的操作了。

Option

• 新建SQLiteDbHelper在程序开始运行时自动新建数据库并建立相关的table。如以下示例：

using System.Data.SQLite;

namespace Test
{
    public class SQLiteDbHelper
    {
        public const string TABLE_USERS = "Users";

        private SQLiteConnection connection;

        /// <summary>
        /// Init SQLite DB connectionString.
        /// </summary>
        /// <param name="dbFilePath">SQLite Database file path</param>
        public SQLiteDbHelper(string dbFilePath)
        {
            string connectionString = "Data Source=" + dbFilePath;
            connection = new SQLiteConnection(connectionString);
        }

        /// <summary>
        /// Create DB and tables.
        /// </summary>
        public void Create()
        {
            connection.Open();
            SQLiteCommand command = connection.CreateCommand();
            command.CommandText = string.Format("CREATE TABLE {0}({1}, {2}, {3}, {4})",
                TABLE_USERS, "_id INTEGER PRIMARY KEY AUTOINCREMENT",
                "Name VARCHAR", "Email VARCHAR", "Password VARCHAR");
            command.ExecuteNonQuery();
            connection.Close();
        }
    }

        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns the number
        /// of rows affected.
        /// </summary>
        /// <param name="sql">sql command</param>
        /// <param name="parameters">The parameters of sql command.</param>
        /// <returns>The number of rows affected.</returns>
        public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            connection.Open();
            using (DbTransaction transaction = connection.BeginTransaction())
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    affectedRows = command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            connection.Close();
            return affectedRows;
        }

        /// <summary>
        /// Sends the command to the connection and builds a SQLiteDataReader.
        /// </summary>
        /// <param name="sql">sql command</param>
        /// <param name="parameters">The parameters of sql command.</param>
        /// <returns>A SQLiteDataReader object.</returns>
        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result
        /// set returned by the query. Additional columns or rows are ignored.
        /// </summary>
        /// <param name="sql">sql command</param>
        /// <param name="parameters">The parameters of sql command.</param>
        /// <returns>The first column of the first row in the result set, or a null reference
        /// if the result set is empty. Returns a maximum of 2033 characters.</returns>
        public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteCommand command = new SQLiteCommand(sql, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                return dataTable;
            }
        }
    }
}

• 初始化DB

// Init DB.
string dbFilePath = Path.Combine(AppDomain.CurrentDomain.GetData("DataDirectory").ToString(), "mydb.db");
SQLiteDbHelper sqliteDbHelper = new SQLiteDbHelper(dbFilePath);
if (!File.Exists(dbFilePath))
{
    sqliteDbHelper.Create();
}