c# 操作sqlite

首先要添加引用文件：System.Data.SQLite.dll

• 执行操作语句

        public void ExeSqliteSql()
        {

            SQLiteConnection conn = null;
            string strZDBTemp = "";//要执行操作的数据库路径
            string dbPath = "Data Source =" + strZDBTemp;
            conn = new SQLiteConnection(dbPath);//创建数据库实例，指定文件位置
            conn.Open();//打开数据库，若文件不存在会自动创建

            SQLiteCommand cmdSQLite = new SQLiteCommand(conn);
            cmdSQLite.CommandText = "DROP VIEW IF EXISTS LANDTYPEAREARATIO;";//具体sql语句
            cmdSQLite.ExecuteNonQuery();
            cmdSQLite.CommandText ="";//具体sql语句
            cmdSQLite.ExecuteNonQuery();

            DataTable dt = new DataTable();
            SQLiteDataAdapter sqliteAdapter = new SQLiteDataAdapter("select * from test", cnn);
            sqliteAdapter.Fill(dt);//结果填充到DataTable中


            conn.Close();
        }

• 保存、获取二进制数据

        static void SaveByte()
        {
            if (File.Exists("test.db3"))
            {
                File.Delete("test.db3");
            }
            using (var connection = new SQLiteConnection("Data Source=test.db3;Version=3")) using (var command = new SQLiteCommand("CREATE TABLE PHOTOS(ID INTEGER PRIMARY KEY AUTOINCREMENT, PHOTO BLOB)", connection))
            {
                connection.Open();
                command.ExecuteNonQuery();

                byte[] photo = new byte[] { 1, 2, 3, 4, 5 };

                command.CommandText = "INSERT INTO PHOTOS (PHOTO) VALUES (@photo)";
                command.Parameters.Add("@photo", DbType.Binary, 20).Value = photo;
                command.ExecuteNonQuery();

                command.CommandText = "SELECT PHOTO FROM PHOTOS WHERE ID = 1"; using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        byte[] buffer = GetBytes(reader);
                    }
                }
            }
        }
        static byte[] GetBytes(SQLiteDataReader reader)
        {
            const int CHUNK_SIZE = 2 * 1024;
            byte[] buffer = new byte[CHUNK_SIZE];
            long bytesRead;
            long fieldOffset = 0; using (MemoryStream stream = new MemoryStream())
            {
                while ((bytesRead = reader.GetBytes(0, fieldOffset, buffer, 0, buffer.Length)) > 0)
                {
                    stream.Write(buffer, 0, (int)bytesRead);
                    fieldOffset += bytesRead;
                } return stream.ToArray();
            }
        }

可能会出现的问题：

混合模式程序集是针对“v2.0.50727”版的运行时生成的，在没有配置其他信息的情况下，无法在 4.0 运行时中加载该程序集。

解决：

在app.config中添加一个配置节：startup

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku = ".NETFramework,Version=v4.0"/>
    <supportedRuntime version="v2.0.50727"/>
  </startup>
</configuration>