数据库系统在WinRT中的使用（二）：ESENT/JET API的使用

Question

什么是ESE？

ESE（Extensible Storage Engine）或者又被称为Jet Blue，是一种ISAM数据库引擎。ESE并不是执行SQL语句查询的数据库。ESE运行库（ESENT.dll）自Windows 2000开始之后的每一个Windows版本都会附带在系统中可以让我们直接使用。

如果你的应用需要一个可靠的，高性能，低开销用来存取结构或者半结构的数据那么ESE是一个好的选择。ESE提供了一种崩溃恢复机制，高并发性的事物处理使得ESE适合服务器应用。ESE最多能够支持16TB的数据。

不过请注意，ESE并不能够允许多个进程同时的操作，ESE比较适合需求简单，预定义查询的程序。

更详细的信息可以参考MSDN文档：

http://msdn.microsoft.com/en-us/library/windows/desktop/gg269259(v=exchg.10).aspx

怎么在商店应用中使用ESE？

ESE的API是支持商店应用的，由于是本地的API，因此我们如果需要在托管语言中（C#）使用的话需要使用P/invoke来调用API。在这里我们推荐ManagedEsent开源第三方项目：

http://managedesent.codeplex.com/

这个项目也支持商店应用，请到Source code下直接下载源代码：

请注意：

直接使用编译好的dll到商店应用会出现错误，原因是WinRT只支持一部分.Net类并不支持所有的类。

之后在解决方案中添加源代码中的EsentInteropMetro.csproj：

这时候我们可以在添加引用中找到刚才添加的工程：

我们使用代码来测试一下ESE：

（代码来自Gerald Haslhofer的博客，原文请见：http://gerhas.netii.net/wordpress/?p=93）

代码演示生成数据库，写入读取数据。如果与原数据有改动将会输出CHANGED：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Windows.Foundation;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Data;
using System.Text;
using Microsoft.Isam.Esent.Interop;
using Microsoft.Isam.Esent.Interop.Vista;

namespace DatabaseTest
{
    partial class MainPage
    {
        public MainPage()
        {
            InitializeComponent();
        }

        public static void TestJet()
        {

            JET_INSTANCE instance;
            JET_SESID sesid;
            JET_DBID dbid;
            JET_TABLEID tableid;

            JET_COLUMNDEF columndef = new JET_COLUMNDEF();
            JET_COLUMNID columnid;

            // Initialize ESENT. Setting JET_param.CircularLog to 1 means ESENT will automatically
            // delete unneeded logfiles. JetInit will inspect the logfiles to see if the last
            // shutdown was clean. If it wasn't (e.g. the application crashed) recovery will be
            // run automatically bringing the database to a consistent state.
            Api.JetCreateInstance(out instance, "instance");
            Api.JetSetSystemParameter(instance, JET_SESID.Nil, JET_param.CircularLog, 1, null);

            // CHANGED
            // NOTE YOU NEED THE TRAILING SLASH or you'll get access denied
            string jetDir = Windows.Storage.ApplicationData.Current.LocalFolder.Path + "\\";

            // CHANGED Set paths to AppData
            Api.JetSetSystemParameter(instance, JET_SESID.Nil, JET_param.LogFilePath, 0, jetDir);
            Api.JetSetSystemParameter(instance, JET_SESID.Nil, JET_param.SystemPath, 0, jetDir);
            Api.JetSetSystemParameter(instance, JET_SESID.Nil, JET_param.TempPath, 0, jetDir);

            // CHANGED 
            JET_RSTINFO recinfo;

            // CHANGED 
            VistaApi.JetInit3(ref instance, null, InitGrbit.None);
            //Api.JetInit(ref instance);
            Api.JetBeginSession(instance, out sesid, null, null);

            // Create the database. To open an existing database use the JetAttachDatabase and 
            // JetOpenDatabase APIs.

            //CHANGED make sure it's the AppDataPath
            string path = Windows.Storage.ApplicationData.Current.LocalFolder.Path + "\\edbtest.db";


            Api.JetCreateDatabase(sesid, path, null, out dbid, CreateDatabaseGrbit.OverwriteExisting);

            // Create the table. Meta-data operations are transacted and can be performed concurrently.
            // For example, one session can add a column to a table while another session is reading
            // or updating records in the same table.
            // This table has no indexes defined, so it will use the default sequential index. Indexes
            // can be defined with the JetCreateIndex API.
            Api.JetBeginTransaction(sesid);
            Api.JetCreateTable(sesid, dbid, "table", 0, 100, out tableid);
            columndef.coltyp = JET_coltyp.LongText;
            columndef.cp = JET_CP.ASCII;
            Api.JetAddColumn(sesid, tableid, "column1", columndef, null, 0, out columnid);
            Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush);

            // Insert a record. This table only has one column but a table can have slightly over 64,000
            // columns defined. Unless a column is declared as fixed or variable it won't take any space
            // in the record unless set. An individual record can have several hundred columns set at one
            // time, the exact number depends on the database page size and the contents of the columns.
            Api.JetBeginTransaction(sesid);
            Api.JetPrepareUpdate(sesid, tableid, JET_prep.Insert);
            byte[] payload = new byte[10];
            payload[0] = 30;

            //CHANGED: original sample had Encoding.ASCII which is not supported any more
            Api.SetColumn(sesid, tableid, columnid, payload);
            Api.JetUpdate(sesid, tableid);
            Api.JetCommitTransaction(sesid, CommitTransactionGrbit.None);    // Use JetRollback() to abort the transaction

            // Retrieve a column from the record. Here we move to the first record with JetMove. By using
            // JetMoveNext it is possible to iterate through all records in a table. Use JetMakeKey and
            // JetSeek to move to a particular record.
            Api.JetMove(sesid, tableid, JET_Move.First, MoveGrbit.None);

            //CHANGED: original sample had Encoding.ASCII which is not supported any more
            byte[] res = Api.RetrieveColumn(sesid, tableid, columnid);
            System.Diagnostics.Debug.WriteLine(res[0].ToString());

            // Terminate ESENT. This performs a clean shutdown.
            Api.JetCloseTable(sesid, tableid);
            Api.JetEndSession(sesid, EndSessionGrbit.None);
            Api.JetTerm(instance);
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            TestJet();
        }
    }
}

---

Aaron
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.