教你使用微软小型数据库esent



#include <esent.h>
#include <Windows.h>
#include <iostream>
#pragma comment (lib, "esent.lib")
using namespace std;

JET_ERR WriteTest(JET_SESID& sessionID,JET_DBID& dbID)
{
    JET_TABLEID iTableID;
    JET_COLUMNID iColumnId[2];//本例子中表有两列
    JET_ERR err = JetOpenTable(sessionID, dbID, "esenttable", NULL, 0, 0, &iTableID);//获取iTableID,插入时需要指定iTableID
    if (err!=JET_errSuccess)
    {
        return err;
    }

    //以下获取列的ID： iColumnId ***************************************************************
    JET_COLUMNDEF columnInfo = {};
    columnInfo.cbStruct = sizeof(columnInfo);
    err=JetGetColumnInfo(sessionID, dbID, "esenttable", "name", &columnInfo, columnInfo.cbStruct, JET_ColInfo);
    if (err!=JET_errSuccess)
    {
        return err;
    }

    iColumnId[0] = columnInfo.columnid;//获取iColumnId
    ZeroMemory(&columnInfo,sizeof(columnInfo));
    columnInfo.cbStruct = sizeof(columnInfo);
    err=JetGetColumnInfo(sessionID, dbID, "esenttable", "age", &columnInfo, columnInfo.cbStruct, JET_ColInfo);
    if (err!=JET_errSuccess)
    {
        return err;
    }
    iColumnId[1] = columnInfo.columnid;//获取iColumnId
    //获取iColumnId 完毕***************************************************************

    //以下填充要插入的值 表有两列第一列为name 第二列为age
    JET_SETCOLUMN *setColumn = new JET_SETCOLUMN[2];
    ZeroMemory(setColumn, sizeof(JET_SETCOLUMN)*2);
    setColumn[0].columnid = iColumnId[0];//列的id
    setColumn[0].pvData = "zhangpeng_linux";//列的值，类型为char*
    setColumn[0].cbData = strlen("zhangpeng_linux");//值的长度
    setColumn[0].err = JET_errSuccess;
   
    int age=25;
    setColumn[1].columnid = iColumnId[1];//列的id
    setColumn[1].pvData = (char*)&age;//列的值，类型为char*
    setColumn[1].cbData =sizeof(age);//值的长度
    setColumn[1].err = JET_errSuccess;

    err = JetBeginTransaction(sessionID);
    if(JET_errSuccess != err)
    {
        JetCloseTable(sessionID, iTableID);
        delete[] setColumn;
        return err;
    }
    err = JetPrepareUpdate(sessionID, iTableID, JET_prepInsert);
    if (JET_errSuccess != err)
    {
        JetRollback(sessionID, 0);
        JetCloseTable(sessionID, iTableID);
        delete[] setColumn;
        return err;
    }
    //这里第三个参数为JET_SETCOLUMN结构指针
    //第四个参数为第三个参数中JET_SETCOLUMN结构的个数
    err = JetSetColumns(sessionID, iTableID, setColumn, 2);
    if (JET_errSuccess != err)
    {
        JetRollback(sessionID, 0);
        JetCloseTable(sessionID, iTableID);
        delete[] setColumn;
        return err;
    }
    //当函数JetSetColumns成功执行后，不代表每列数据都成功插入，需要对检查 //JET_SETCOLUMN结构中的err字段，确保每一列都成功插入
    for (int i = 0; i < 2; i++)
    {
        if (JET_errSuccess != setColumn[i].err)
        {
            JetRollback(sessionID, 0);
            JetCloseTable(sessionID, iTableID);
            delete[] setColumn;
            return setColumn[i].err;
        }
    }

    delete[] setColumn;
    //后三个字段不需要，填入0即可
    err = JetUpdate(sessionID, iTableID, 0, 0, 0);
    if (JET_errSuccess != err)
    {
        JetRollback(sessionID, 0);
        JetCloseTable(sessionID, iTableID);
        return err;
    }

    err = JetCommitTransaction(sessionID, 0);
    JetCloseTable(sessionID, iTableID);

    return err;
}

JET_ERR ReadTest(JET_SESID& sessionID,JET_DBID& dbID)
{
    JET_TABLEID iTableID;
    JET_COLUMNID iColumnId[2];//本例子中表有两列
    JET_ERR err = JetOpenTable(sessionID, dbID, "esenttable", NULL, 0, 0, &iTableID);//获取iTableID
    if (err!=JET_errSuccess)
    {
        return err;
    }
    //以下获取列的ID： iColumnId
    JET_COLUMNDEF columnInfo = {};
    columnInfo.cbStruct = sizeof(columnInfo);
    err=JetGetColumnInfo(sessionID, dbID, "esenttable", "name", &columnInfo, columnInfo.cbStruct, JET_ColInfo);
    if (err!=JET_errSuccess)
    {
        return err;
    }

    iColumnId[0] = columnInfo.columnid;//获取iColumnId
    ZeroMemory(&columnInfo,sizeof(columnInfo));
    columnInfo.cbStruct = sizeof(columnInfo);
    err=JetGetColumnInfo(sessionID, dbID, "esenttable", "age", &columnInfo, columnInfo.cbStruct, JET_ColInfo);
    if (err!=JET_errSuccess)
    {
        return err;
    }
    iColumnId[1] = columnInfo.columnid;//获取iColumnId

    char buffer[1024] = { 0 };
    int iLen = sizeof(buffer);
    int age=-1;
    unsigned long nReadBytes=0;
    for(err = JetMove(sessionID, iTableID, JET_MoveFirst, 0);JET_errSuccess == err;err = JetMove(sessionID, iTableID, JET_MoveNext, 0))
    {
        err = JetRetrieveColumn(sessionID, iTableID, iColumnId[0], buffer, 1024, &nReadBytes, 0, 0);
        if (nReadBytes >= 1024)
        {
            JetCloseTable(sessionID, iTableID);
        }
        cout<<"name "<<buffer;

        age = -1;
        unsigned long nReadBytes = 0;
        err = JetRetrieveColumn(sessionID, iTableID, iColumnId[1], &age, sizeof(age), &nReadBytes, 0, 0);
        if (JET_errSuccess != err)
        {
            JetCloseTable(sessionID, iTableID);
        }
        cout<<",age "<<age<<endl;
    }

    return 0;

}

JET_ERR OpenDatabase(JET_SESID& sessionID, char* strDBPath,JET_DBID& dbID)
{
    JET_ERR err = JetAttachDatabase(sessionID, strDBPath, 0);
    if (JET_errSuccess != err)
    {
        return err;
    }
    err = JetOpenDatabase(sessionID, strDBPath, 0, &dbID, 0);
    if (JET_errSuccess != err)
    {
        JetDetachDatabase(sessionID, strDBPath);
        return err;
    }
    return err;
}

JET_ERR CloseDatabase(JET_SESID& sessionID, JET_DBID& dbID,char* strDBPath)
{

    JET_ERR err = JetCloseDatabase(sessionID, dbID,0);
    err = JetDetachDatabase(sessionID, strDBPath);
    JetEndSession(sessionID,0);
    return err;
}

JET_ERR CreateDatabaseAndTable(JET_SESID& sessionID, char* strDBPath,JET_DBID& dbID)
{

    JET_ERR err = JetCreateDatabase(sessionID, strDBPath, 0, &dbID, 0);
    if (err==JET_errSuccess)//如果创建数据库成功就创建表
    {
        JET_COLUMNCREATE columnCreate[3] = {};
        columnCreate[0].cbStruct = sizeof(JET_COLUMNCREATE);//结构长度
        columnCreate[0].szColumnName = "index";//列名
        columnCreate[0].coltyp = JET_coltypLong;//列类型
        columnCreate[0].grbit = JET_bitColumnAutoincrement;//列属性，此处为自增
        columnCreate[0].err = JET_errSuccess;

        columnCreate[1].cbStruct = sizeof(JET_COLUMNCREATE);//结构长度
        columnCreate[1].szColumnName = "name";//列名
        columnCreate[1].coltyp =  JET_coltypLongText;//列类型
        columnCreate[1].cbMax = 32;//列长度
        columnCreate[1].grbit = JET_bitColumnTagged;
        columnCreate[1].cp = 1200;//Unicode:1200;English:1252。传0时使用默认的1252。
        columnCreate[1].err = JET_errSuccess;

        columnCreate[2].cbStruct = sizeof(JET_COLUMNCREATE);//结构长度
        columnCreate[2].szColumnName = "age";//列名
        columnCreate[2].coltyp = JET_coltypLong;//列类型
        columnCreate[2].grbit = 0;//列属性，此处为自增
        columnCreate[2].err = JET_errSuccess;

        JET_TABLECREATE tableCreate = {};
        tableCreate.cbStruct = sizeof(tableCreate);//表结构大小
        tableCreate.szTableName ="esenttable";//表名
        tableCreate.rgcolumncreate = columnCreate;//列结构体指针
        tableCreate.cColumns = 3;//列个数
        tableCreate.rgindexcreate = NULL;//索引结构体指针
        tableCreate.cIndexes = 0;//索引个数
        tableCreate.tableid = JET_instanceNil;
        //表结构体初始化后，调用
        err = JetCreateTableColumnIndex(sessionID, dbID, &tableCreate);
    }
    return err;
}
int main()
{
    char *strDBPath="D:\\esent.db";
    JET_DBID dbID;
    JET_INSTANCE instance;
    instance=JET_instanceNil;

    JET_ERR err = JetCreateInstance(&instance, "my_enset1");
    if (JET_errSuccess != err)//Jet_errSuccess为函数成功执行的返回值。
    {
        instance = JET_instanceNil;
        return -1;
    }

    err = JetInit(&instance);
    if (JET_errSuccess != err)
    {
        JetTerm(instance);
        instance = JET_instanceNil;
        return -2;
    }

    JET_SESID sessionID;
    sessionID = JET_sesidNil;
    err = JetBeginSession(instance, &sessionID, 0, 0);
    if (JET_errSuccess != err)
    {
        JetTerm(instance);
        instance = JET_instanceNil;
    }

    err=CreateDatabaseAndTable(sessionID, strDBPath, dbID);
    if (err!=JET_errDatabaseDuplicate && err!=JET_errSuccess)
    {
        return -1;//如果数据库不存在且没有创建成功就退出
    }
    if (err==JET_errDatabaseDuplicate)
    {
        err=OpenDatabase(sessionID, strDBPath, dbID);//如果数据库存在就打开
        if (err!=JET_errSuccess)
        {
            return -1;
        }
    }
    //开始测试写入
    cout<<"begin write...\n";

    for (int i=0;i<100;++i)
    {
        err=WriteTest(sessionID, dbID);
        if (err!=JET_errSuccess)
        {
            cout<<"write error "<<err<<endl;
            cin.get();
            return -1;
        }
       
    }
   
    cout<<"press any key to test read\n";
    cin.get();
    //开始测试读取
    cout<<"begin read...\n";
    ReadTest(sessionID, dbID);
    cout<<"press any key to exit\n";
    cin.get();
    JetTerm(instance);
    CloseDatabase(sessionID,dbID,strDBPath);
    return 0;
}