internal static void CreateTable(Session session, JET_DBID dbid)
        {
            JET_TABLEID tableid;
            Api.JetCreateTable(session, dbid, ifcHeaderTableName, 1, 100, out tableid);

            using (var transaction = new Microsoft.Isam.Esent.Interop.Transaction(session))
            {
                JET_COLUMNID columnid;
                
                var columndef = new JET_COLUMNDEF
                {
                    coltyp = JET_coltyp.Long,
                    grbit = ColumndefGrbit.ColumnAutoincrement
                };
                Api.JetAddColumn(session, tableid, _colNameHeaderId, columndef, null, 0, out columnid);
                columndef.coltyp = JET_coltyp.Currency;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(session, tableid, _colNameEntityCount, columndef, null, 0, out columnid);
                
                columndef.coltyp = JET_coltyp.LongBinary;
            
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(session, tableid, _colNameHeaderData, columndef, null, 0, out columnid);
                columndef.coltyp = JET_coltyp.Text;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                columndef.cbMax = 32;

                Api.JetAddColumn(session, tableid, _colNameFileVersion, columndef, null, 0, out columnid);
                transaction.Commit(CommitTransactionGrbit.LazyFlush);
            }
        }

        internal static void CreateTable(JET_SESID sesid, JET_DBID dbid)
        {
            JET_TABLEID tableid;
            Api.JetCreateTable(sesid, dbid, GeometryTableName, 8, 80, out tableid);

            using (var transaction = new Microsoft.Isam.Esent.Interop.Transaction(sesid))
            {
                JET_COLUMNID columnid;

                var columndef = new JET_COLUMNDEF
                {
                    coltyp = JET_coltyp.Long,
                    grbit = ColumndefGrbit.ColumnAutoincrement
                };

                Api.JetAddColumn(sesid, tableid, colNameGeometryLabel, columndef, null, 0, out columnid);

                columndef.grbit = ColumndefGrbit.ColumnNotNULL;

                Api.JetAddColumn(sesid, tableid, colNameProductLabel, columndef, null, 0, out columnid);

                columndef.coltyp = JET_coltyp.UnsignedByte;
                Api.JetAddColumn(sesid, tableid, colNameGeomType, columndef, null, 0, out columnid);
                
                columndef.coltyp = JET_coltyp.Short;
                Api.JetAddColumn(sesid, tableid, colNameProductIfcTypeId, columndef, null, 0, out columnid);
                Api.JetAddColumn(sesid, tableid, colNameSubPart, columndef, null, 0, out columnid);
               

                columndef.coltyp = JET_coltyp.Binary;
                columndef.grbit = ColumndefGrbit.ColumnMaybeNull;
                Api.JetAddColumn(sesid, tableid, colNameTransformMatrix, columndef, null, 0, out columnid);
               
                columndef.coltyp = JET_coltyp.LongBinary;
                //if (EsentVersion.SupportsWindows7Features)
                //    columndef.grbit |= Windows7Grbits.ColumnCompressed;
                Api.JetAddColumn(sesid, tableid, colNameShapeData, columndef, null, 0, out columnid);

                columndef.coltyp = JET_coltyp.Long;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(sesid, tableid, colNameGeometryHash, columndef, null, 0, out columnid);
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(sesid, tableid, colNameStyleLabel, columndef, null, 0, out columnid);
                // The primary index is the type and the entity label.
                string indexDef = string.Format("+{0}\0\0", colNameGeometryLabel);
                Api.JetCreateIndex(sesid, tableid, geometryTablePrimaryIndex, CreateIndexGrbit.IndexPrimary, indexDef, indexDef.Length, 100);
                //create index by geometry hashes    
                indexDef = string.Format("+{0}\0\0", colNameGeometryHash);
                Api.JetCreateIndex(sesid, tableid, geometryTableHashIndex, CreateIndexGrbit.IndexDisallowNull, indexDef, indexDef.Length, 100);
                //Create index by product
                indexDef = string.Format("+{0}\0{1}\0{2}\0{3}\0{4}\0\0", colNameGeomType, colNameProductIfcTypeId, colNameProductLabel, colNameSubPart, colNameStyleLabel);
                Api.JetCreateIndex(sesid, tableid, geometryTableGeomTypeIndex, CreateIndexGrbit.IndexUnique, indexDef, indexDef.Length, 100);
                //create index by style
                indexDef = string.Format("+{0}\0{1}\0{2}\0{3}\0{4}\0\0", colNameGeomType, colNameStyleLabel, colNameProductIfcTypeId, colNameProductLabel, colNameGeometryLabel);
                Api.JetCreateIndex(sesid, tableid, geometryTableStyleIndex, CreateIndexGrbit.None, indexDef, indexDef.Length, 100);
                Api.JetCloseTable(sesid, tableid);
                transaction.Commit(CommitTransactionGrbit.LazyFlush);
            }
           
        }

        public void Setup()
        {
            this.directory = SetupHelper.CreateRandomDirectory();
            this.database = Path.Combine(this.directory, "database.edb");
            this.table = "table";
            this.instance = SetupHelper.CreateNewInstance(this.directory);

            Api.JetSetSystemParameter(this.instance, JET_SESID.Nil, JET_param.Recovery, 0, "off");
            Api.JetInit(ref this.instance);
            Api.JetBeginSession(this.instance, out this.sesid, String.Empty, String.Empty);
            Api.JetCreateDatabase(this.sesid, this.database, String.Empty, out this.dbid, CreateDatabaseGrbit.None);
            Api.JetBeginTransaction(this.sesid);
            Api.JetCreateTable(this.sesid, this.dbid, this.table, 0, 100, out this.tableid);

            JET_COLUMNID ignored;
            var columndef = new JET_COLUMNDEF { coltyp = JET_coltyp.Text, cp = JET_CP.Unicode };

            Api.JetAddColumn(this.sesid, this.tableid, "C1", columndef, null, 0, out ignored);
            Api.JetAddColumn(this.sesid, this.tableid, "C2", columndef, null, 0, out ignored);
            Api.JetAddColumn(this.sesid, this.tableid, "C3", columndef, null, 0, out ignored);

            Api.JetCreateIndex(this.sesid, this.tableid, "Primary", CreateIndexGrbit.IndexPrimary, "+C1\0\0", 5, 100);
            Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);

            JET_INDEXCREATE[] indexcreates = new[]
            {
                new JET_INDEXCREATE { szIndexName = "Index2", cbKey = 5, szKey = "+C2\0\0" },
                new JET_INDEXCREATE { szIndexName = "Index3", cbKey = 5, szKey = "+C3\0\0", cbVarSegMac = 100 },
            };
            Api.JetCreateIndex2(this.sesid, this.tableid, indexcreates, indexcreates.Length);

            Api.JetCloseTable(this.sesid, this.tableid);
            Api.JetOpenTable(this.sesid, this.dbid, this.table, null, 0, OpenTableGrbit.None, out this.tableid);
        }

        private static void CreateDocumentsTable(Session session, JET_TABLEID tableid)
        {
            JET_COLUMNID columnid;

            var guidColumn = new JET_COLUMNDEF
                                 {
                                     cbMax = 255,
                                     coltyp = JET_coltyp.Text,
                                     cp = JET_CP.Unicode,
                                     grbit = ColumndefGrbit.ColumnTagged
                                 };
            Api.JetAddColumn(session, tableid, "id", guidColumn, null, 0, out columnid);

            var collectionColumn = new JET_COLUMNDEF
                                       {
                                           cbMax = 1000,
                                           coltyp = JET_coltyp.Text,
                                           cp = JET_CP.Unicode,
                                           grbit = ColumndefGrbit.ColumnTagged
                                       };
            Api.JetAddColumn(session, tableid, "collection_name", collectionColumn, null, 0, out columnid);

            const string colectionIndexDef = "+collection_name\0\0";
            Api.JetCreateIndex(session, tableid, "by_collection_name", CreateIndexGrbit.None, colectionIndexDef, colectionIndexDef.Length, 100);

            var textColumn = new JET_COLUMNDEF
                                 {
                                     coltyp = JET_coltyp.LongText,
                                     grbit = ColumndefGrbit.ColumnTagged
                                 };
            Api.JetAddColumn(session, tableid, "data", textColumn, null, 0, out columnid);

            const string idIndexDef = "+id\0\0";
            Api.JetCreateIndex(session, tableid, "by_id", CreateIndexGrbit.IndexPrimary, idIndexDef, idIndexDef.Length, 100);
        }

        /// <summary>
        /// Main routine. Called when the program starts.
        /// </summary>
        /// <param name="args">
        /// The arguments to the program.
        /// </param>
        public static void Main(string[] args)
        {
            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);
            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.
            Api.JetCreateDatabase(sesid, "edbtest.db", 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);
            string message = "Hello world";
            Api.SetColumn(sesid, tableid, columnid, message, Encoding.ASCII);
            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);
            string buffer = Api.RetrieveColumnAsString(sesid, tableid, columnid, Encoding.ASCII);
            Console.WriteLine("{0}", buffer);

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

        /// <summary>
        /// Create a JET_COLUMNDEF from a ColumnDefintion.
        /// </summary>
        /// <param name="definition">The column definition to convert.</param>
        /// <returns>A JET_COLUMNDEF representing the ColumnDefintion.</returns>
        public JET_COLUMNDEF CreateColumndefFromColumnDefinition(ColumnDefinition definition)
        {
            ColumndefGrbit grbit = CalculateColumndefGrbit(definition);

            var columndef = new JET_COLUMNDEF
            {
                cbMax = definition.MaxSize,
                coltyp = this.columnTypeToColtypMapping[definition.Type],
                cp = (ColumnType.AsciiText == definition.Type) ? JET_CP.ASCII : JET_CP.Unicode,
                grbit = grbit,
            };

            return columndef;
        }

        public void CreateColumnWithDefaultValue()
        {
            int expected = Any.Int32;

            Api.JetBeginTransaction(this.sesid);
            var columndef = new JET_COLUMNDEF { coltyp = JET_coltyp.Long };
            JET_COLUMNID columnid;
            Api.JetAddColumn(this.sesid, this.tableid, "column_with_default", columndef, BitConverter.GetBytes(expected), 4, out columnid);
            Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);

            Api.JetBeginTransaction(this.sesid);
            Api.JetPrepareUpdate(this.sesid, this.tableid, JET_prep.Insert);
            this.UpdateAndGotoBookmark();
            Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);

            Assert.AreEqual(expected, Api.RetrieveColumnAsInt32(this.sesid, this.tableid, columnid));
        }

        public void ConvertColumndefFromNative()
        {
            var native = new NATIVE_COLUMNDEF()
            {
                cbMax = 1,
                coltyp = (uint)JET_coltyp.LongText,
                columnid = 0x100,
                cp = 1200,
                grbit = (uint)ColumndefGrbit.ColumnMultiValued,
            };

            var columndef = new JET_COLUMNDEF();
            columndef.SetFromNativeColumndef(native);
            Assert.AreEqual(1, columndef.cbMax);
            Assert.AreEqual(JET_coltyp.LongText, columndef.coltyp);
            Assert.AreEqual<uint>(0x100, columndef.columnid.Value);
            Assert.AreEqual(JET_CP.Unicode, columndef.cp);
            Assert.AreEqual(ColumndefGrbit.ColumnMultiValued, columndef.grbit);
        }

        public void ConvertColumndefToNative()
        {
            var columndef = new JET_COLUMNDEF
            {
                cbMax = 0x1,
                coltyp = JET_coltyp.Binary,
                cp = JET_CP.Unicode,
                grbit = ColumndefGrbit.ColumnAutoincrement
            };

            NATIVE_COLUMNDEF native = columndef.GetNativeColumndef();
            Assert.AreEqual<uint>(0, native.columnid);
            Assert.AreEqual<uint>(9, native.coltyp);
            Assert.AreEqual<ushort>(0, native.wCountry);
            Assert.AreEqual<ushort>(0, native.langid);
            Assert.AreEqual<ushort>(1200, native.cp);
            Assert.AreEqual<ushort>(0, native.wCollate);
            Assert.AreEqual<uint>(1, native.cbMax);
            Assert.AreEqual<uint>(0x10, native.grbit);
        }

        public void Setup()
        {
            JET_TABLEID tableid;

            this.directory = SetupHelper.CreateRandomDirectory();
            this.database = Path.Combine(this.directory, "database.edb");
            this.table = "table";
            this.instance = SetupHelper.CreateNewInstance(this.directory);

            // turn off logging so initialization is faster
            Api.JetSetSystemParameter(this.instance, JET_SESID.Nil, JET_param.Recovery, 0, "off");
            Api.JetInit(ref this.instance);
            Api.JetBeginSession(this.instance, out this.sesid, String.Empty, String.Empty);
            Api.JetCreateDatabase(this.sesid, this.database, String.Empty, out this.dbid, CreateDatabaseGrbit.None);
            Api.JetBeginTransaction(this.sesid);
            Api.JetCreateTable(this.sesid, this.dbid, this.table, 0, 100, out tableid);

            var columndef = new JET_COLUMNDEF { coltyp = JET_coltyp.Long };
            Api.JetAddColumn(this.sesid, tableid, "Column1", columndef, null, 0, out this.columnid1);
            Api.JetAddColumn(this.sesid, tableid, "Column2", columndef, null, 0, out this.columnid2);

            var indexDef = "+Column1\0\0";
            Api.JetCreateIndex(this.sesid, tableid, "index1", CreateIndexGrbit.None, indexDef, indexDef.Length, 100);

            indexDef = "+Column2\0\0";
            Api.JetCreateIndex(this.sesid, tableid, "index2", CreateIndexGrbit.None, indexDef, indexDef.Length, 100);

            // Create a cross-product of records. Index intersection can be used to select a subset.
            for (int i = 0; i < 10; ++i)
            {
                for (int j = 0; j < 10; ++j)
                {
                    this.InsertRecord(tableid, i, j);
                }
            }

            Api.JetCloseTable(this.sesid, tableid);
            Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
        }

        public void ConvertColumndefToNative()
        {
            var columndef = new JET_COLUMNDEF
            {
                cbMax = 0x1,
                coltyp = JET_coltyp.Binary,
                cp = JET_CP.Unicode,
                grbit = ColumndefGrbit.ColumnAutoincrement
            };

            NATIVE_COLUMNDEF native = columndef.GetNativeColumndef();
            Assert.AreEqual<uint>(0, native.columnid);
            Assert.AreEqual<uint>(9, native.coltyp);
            #pragma warning disable 618,612 // Disable warning that wCountry/langid/wCollate are obsolete
            Assert.AreEqual<ushort>(0, native.wCountry);
            Assert.AreEqual<ushort>(0, native.langid);
            Assert.AreEqual<ushort>(1200, native.cp);
            Assert.AreEqual<ushort>(0, native.wCollate);
            #pragma warning restore 618,612
            Assert.AreEqual<uint>(1, native.cbMax);
            Assert.AreEqual<uint>(0x10, native.grbit);
        }

        public void AddColumn(JET_TABLEID tableid, ColumnDefinition column)
        {
            JET_COLUMNDEF column_def;
            JET_COLUMNID column_id;

            if (columnDefs.ContainsKey(column.Type)) {
                column_def = columnDefs[column.Type];
            } else {
                column_def = new JET_COLUMNDEF();
                column_def.coltyp = JET_coltyp.LongBinary;
            }

            // TODO validate only one of these
            if (column.IsAutoIncrement) {
                column_def.grbit = column_def.grbit | ColumndefGrbit.ColumnAutoincrement;
            }

            Api.JetAddColumn(connection.session, tableid, column.Name, column_def, null, 0, out column_id);

            if (column.IsPrimaryKey) {
                var indexDef = "+" + column.Name + "\0\0";
                Api.JetCreateIndex(connection.session, tableid, "primary", CreateIndexGrbit.IndexPrimary, indexDef, indexDef.Length, 100);
            }
        }

        public void CreateTwoIndexes()
        {
            JET_TABLEID tableToIndex;

            Api.JetBeginTransaction(this.sesid);
            Api.JetCreateTable(this.sesid, this.dbid, "tabletoindex", 1, 100, out tableToIndex);

            var columndef = new JET_COLUMNDEF()
            {
                cp = JET_CP.Unicode,
                coltyp = JET_coltyp.LongText,
            };
            Api.JetAddColumn(this.sesid, tableToIndex, "column", columndef, null, 0, out this.testColumnid);

            Api.JetCloseTable(this.sesid, tableToIndex);
            Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);

            Api.JetOpenTable(this.sesid, this.dbid, "tabletoindex", null, 0, OpenTableGrbit.DenyRead, out tableToIndex);
            const string Index1Name = "firstIndex";
            const string Index1Description = "-column\0\0";

            const string Index2Name = "secondIndex";
            const string Index2Description = "+column\0\0";

            var indexcreates = new[]
            {
                new JET_INDEXCREATE
                {
                    szIndexName = Index1Name,
                    szKey = Index1Description,
                    cbKey = Index1Description.Length,
                    grbit = CreateIndexGrbit.None,
                    ulDensity = 100,
                },
                new JET_INDEXCREATE
                {
                    szIndexName = Index2Name,
                    szKey = Index2Description,
                    cbKey = Index2Description.Length,
                    grbit = CreateIndexGrbit.None,
                    ulDensity = 100,
                },
            };
            Api.JetCreateIndex2(this.sesid, tableToIndex, indexcreates, indexcreates.Length);

            Api.JetSetCurrentIndex(this.sesid, tableToIndex, Index1Name);
            Api.JetSetCurrentIndex(this.sesid, tableToIndex, Index2Name);
            Api.JetSetCurrentIndex(this.sesid, tableToIndex, null);
            Api.JetCloseTable(this.sesid, tableToIndex);
        }

        public void GetColumnDictionary()
        {
            const string ColumnName = "column4";
            Api.JetBeginTransaction(this.sesid);
            var columndef = new JET_COLUMNDEF()
            {
                cbMax = 10000,
                cp = JET_CP.Unicode,
                coltyp = JET_coltyp.LongText,
                grbit = ColumndefGrbit.None,
            };

            JET_COLUMNID columnid;
            Api.JetAddColumn(this.sesid, this.tableid, ColumnName, columndef, null, 0, out columnid);
            Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);

            IDictionary<string, JET_COLUMNID> dict = Api.GetColumnDictionary(this.sesid, this.tableid);
            Assert.AreEqual(columnid, dict[ColumnName]);
        }

        public void JetGetColumnInfo()
        {
            const string ColumnName = "column3";
            Api.JetBeginTransaction(this.sesid);
            var columndef = new JET_COLUMNDEF()
            {
                cbMax = 200,
                cp = JET_CP.ASCII,
                coltyp = JET_coltyp.LongText,
                grbit = ColumndefGrbit.None,
            };

            JET_COLUMNID columnid;
            Api.JetAddColumn(this.sesid, this.tableid, ColumnName, columndef, null, 0, out columnid);
            Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
            JET_COLUMNDEF retrievedColumndef;
            Api.JetGetColumnInfo(this.sesid, this.dbid, this.table, ColumnName, out retrievedColumndef);

            Assert.AreEqual(columndef.cbMax, retrievedColumndef.cbMax);
            Assert.AreEqual(columndef.cp, retrievedColumndef.cp);
            Assert.AreEqual(columndef.coltyp, retrievedColumndef.coltyp);
            Assert.AreEqual(columnid, retrievedColumndef.columnid);

            // The grbit isn't asserted as esent will add some options by default
        }

        public void CreateOneColumnOfEachType()
        {
            Api.JetBeginTransaction(this.sesid);
            foreach (JET_coltyp coltyp in Enum.GetValues(typeof(JET_coltyp)))
            {
                if (JET_coltyp.Nil != coltyp)
                {
                    var columndef = new JET_COLUMNDEF { coltyp = coltyp };
                    JET_COLUMNID columnid;
                    Api.JetAddColumn(this.sesid, this.tableid, coltyp.ToString(), columndef, null, 0, out columnid);
                    Assert.AreEqual(columnid, columndef.columnid);
                }
            }

            Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
        }

        internal static void CreateTable(JET_SESID sesid, JET_DBID dbid)
        {
            JET_TABLEID tableid;
            Api.JetCreateTable(sesid, dbid, InstanceTableName, 8, 80, out tableid);

            using (var transaction = new Microsoft.Isam.Esent.Interop.Transaction(sesid))
            {
                JET_COLUMNID columnid;

                //Unique instance label
                var columndef = new JET_COLUMNDEF
                {
                    coltyp = JET_coltyp.Long,
                    grbit = ColumndefGrbit.ColumnAutoincrement |ColumndefGrbit.ColumnNotNULL
                };
                Api.JetAddColumn(sesid, tableid, colNameInstanceLabel, columndef, null, 0, out columnid);

                //IFC type ID
                columndef.coltyp = JET_coltyp.Short;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(sesid, tableid, colNameIfcTypeId, columndef, null, 0, out columnid);

                //ifc Product label
                columndef.coltyp = JET_coltyp.Long;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(sesid, tableid, colNameIfcProductLabel, columndef, null, 0, out columnid);

                //style label
                columndef.coltyp = JET_coltyp.Long;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(sesid, tableid, colNameStyleLabel, columndef, null, 0, out columnid);

                //shape label
                columndef.coltyp = JET_coltyp.Long;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(sesid, tableid, colNameShapeLabel, columndef, null, 0, out columnid);

                //Representation Context
                columndef.coltyp = JET_coltyp.Long;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(sesid, tableid, colNameRepresentationContext, columndef, null, 0, out columnid);

                //Representation Context
                columndef.coltyp = JET_coltyp.UnsignedByte;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(sesid, tableid, colNameRepType, columndef, null, 0, out columnid);

                //Transformation data
                columndef.coltyp = JET_coltyp.Binary;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                columndef.cbMax = SizeOfTransformation;
                Api.JetAddColumn(sesid, tableid, colNameTransformation, columndef, null, 0, out columnid);

                //Bounding Box data
                columndef.coltyp = JET_coltyp.Binary;
                columndef.grbit = ColumndefGrbit.ColumnNotNULL;
                Api.JetAddColumn(sesid, tableid, colNameBoundingBox, columndef, null, 0, out columnid);

                string indexDef;
                // The  index on the shape geometry label.
                indexDef = string.Format("+{0}\0\0", colNameShapeLabel);
                Api.JetCreateIndex(sesid, tableid, geometryShapeIndex, CreateIndexGrbit.IndexDisallowNull, indexDef, indexDef.Length, 100);

                //create index by ifc product label..  ..
                indexDef = string.Format("+{0}\0\0", colNameIfcProductLabel);
                Api.JetCreateIndex(sesid, tableid, productIndex, CreateIndexGrbit.IndexDisallowNull, indexDef, indexDef.Length, 100);

                //create index by ifc product type label..  ..
                indexDef = string.Format("+{0}\0\0", colNameIfcTypeId);
                Api.JetCreateIndex(sesid, tableid, productTypeIndex, CreateIndexGrbit.IndexDisallowNull, indexDef, indexDef.Length, 100);

                //create by context,then ifc style...
                indexDef = string.Format("+{0}\0{1}\0{2}\0{3}\0\0", colNameRepresentationContext, colNameStyleLabel, colNameIfcTypeId,  colNameInstanceLabel);
                Api.JetCreateIndex(sesid, tableid, instanceTablePrimaryIndex, CreateIndexGrbit.IndexPrimary, indexDef, indexDef.Length, 100);

                Api.JetCloseTable(sesid, tableid);

                transaction.Commit(CommitTransactionGrbit.LazyFlush);
            }
        }

        public void TestJetSetColumnDefaultValue()
        {
            JET_TABLEID tableid;
            Api.JetCreateTable(this.sesid, this.dbid, "table", 1, 100, out tableid);

            // The column needs to be a tagged column so the default value isn't persisted
            // in the record at insert time.
            var columndef = new JET_COLUMNDEF
                {
                coltyp = JET_coltyp.LongText,
                cp = JET_CP.Unicode,
                };
            byte[] defaultValue = Encoding.ASCII.GetBytes("default");
            JET_COLUMNID columnid;
            Api.JetAddColumn(this.sesid, tableid, "column", columndef, defaultValue, defaultValue.Length, out columnid);
            Api.JetPrepareUpdate(this.sesid, tableid, JET_prep.Insert);
            Api.JetUpdate(this.sesid, tableid);
            Assert.AreEqual("default", this.RetrieveAsciiColumnFromFirstRecord(tableid, columnid));
            Api.JetCloseTable(this.sesid, tableid);

            byte[] newDefaultValue = Encoding.ASCII.GetBytes("newfault");
            Api.JetSetColumnDefaultValue(
                this.sesid, this.dbid, "table", "column", newDefaultValue, newDefaultValue.Length, SetColumnDefaultValueGrbit.None);

            Api.JetOpenTable(this.sesid, this.dbid, "table", null, 0, OpenTableGrbit.None, out tableid);
            Assert.AreEqual("newfault", this.RetrieveAsciiColumnFromFirstRecord(tableid, columnid));
        }

        public void HowDoIMakeAMultiColumnKey()
        {
            JET_SESID sesid = this.testSession;
            JET_DBID dbid = this.testDbid;

            JET_TABLEID tableid;
            JET_COLUMNDEF columndef = new JET_COLUMNDEF();
            JET_COLUMNID boolColumn;
            JET_COLUMNID int32Column;
            JET_COLUMNID stringColumn;
            JET_COLUMNID dataColumn;

            // First create the table. There will be three key columns, a boolean
            // an Int32 and a String. There will be one data column.
            Api.JetCreateTable(sesid, dbid, "table", 0, 100, out tableid);
            columndef.coltyp = JET_coltyp.Bit;
            Api.JetAddColumn(sesid, tableid, "bool", columndef, null, 0, out boolColumn);
            columndef.coltyp = JET_coltyp.Long;
            Api.JetAddColumn(sesid, tableid, "int32", columndef, null, 0, out int32Column);
            columndef.coltyp = JET_coltyp.LongText;
            columndef.cp = JET_CP.Unicode;
            Api.JetAddColumn(sesid, tableid, "string", columndef, null, 0, out stringColumn);
            Api.JetAddColumn(sesid, tableid, "data", columndef, null, 0, out dataColumn);

            const string KeyDescription = "+bool\0+int32\0+string\0\0";
            Api.JetCreateIndex(
                sesid,
                tableid,
                "index",
                CreateIndexGrbit.IndexPrimary,
                KeyDescription,
                KeyDescription.Length,
                100);

            // Insert a record.
            using (var transaction = new Transaction(sesid))
            using (var update = new Update(sesid, tableid, JET_prep.Insert))
            {
                Api.SetColumn(sesid, tableid, boolColumn, true);
                Api.SetColumn(sesid, tableid, int32Column, 8);
                Api.SetColumn(sesid, tableid, stringColumn, "foo", Encoding.Unicode);
                Api.SetColumn(sesid, tableid, dataColumn, "hello world", Encoding.Unicode);
                update.Save();
                transaction.Commit(CommitTransactionGrbit.LazyFlush);
            }

            // Build a key on the index. The index has 3 segments, a Bool
            // (JET_coltyp.Bit), an Int32 (JET_coltyp.Long) and  String
            // (JET_coltyp.LongText). To build a multi-column key we make
            // one call to JetMakeKey for each column. The first call passes
            // in MakeKeyGrbit.NewKey.
            Api.MakeKey(sesid, tableid, true, MakeKeyGrbit.NewKey);
            Api.MakeKey(sesid, tableid, 8, MakeKeyGrbit.None);
            Api.MakeKey(sesid, tableid, "foo", Encoding.Unicode, MakeKeyGrbit.None);
            Api.JetSeek(sesid, tableid, SeekGrbit.SeekEQ);
            Assert.AreEqual("hello world", Api.RetrieveColumnAsString(sesid, tableid, dataColumn));

            // Here we seek on a key prefix. The index has 3 segments but
            // we only build a key on 2 of them. In this case we use SeekGrbit.SeekGE
            // to find the first record that matches the key prefix.
            Api.MakeKey(sesid, tableid, true, MakeKeyGrbit.NewKey);
            Api.MakeKey(sesid, tableid, 8, MakeKeyGrbit.None);
            Api.JetSeek(sesid, tableid, SeekGrbit.SeekGE);
            Assert.AreEqual("hello world", Api.RetrieveColumnAsString(sesid, tableid, dataColumn));
        }

        public void Setup()
        {
            var random = new Random();
            this.numRecords = random.Next(5, 20);

            this.directory = SetupHelper.CreateRandomDirectory();
            this.database = Path.Combine(this.directory, "database.edb");
            this.table = "table";
            this.instance = SetupHelper.CreateNewInstance(this.directory);

            // turn off logging so initialization is faster
            Api.JetSetSystemParameter(this.instance, JET_SESID.Nil, JET_param.Recovery, 0, "off");
            Api.JetSetSystemParameter(this.instance, JET_SESID.Nil, JET_param.MaxTemporaryTables, 0, null);
            Api.JetInit(ref this.instance);
            Api.JetBeginSession(this.instance, out this.sesid, String.Empty, String.Empty);
            Api.JetCreateDatabase(this.sesid, this.database, String.Empty, out this.dbid, CreateDatabaseGrbit.None);
            Api.JetBeginTransaction(this.sesid);
            Api.JetCreateTable(this.sesid, this.dbid, this.table, 0, 100, out this.tableid);

            var columndef = new JET_COLUMNDEF() { coltyp = JET_coltyp.Long };
            Api.JetAddColumn(this.sesid, this.tableid, "Long", columndef, null, 0, out this.columnidLong);

            string indexDef = "+long\0\0";
            Api.JetCreateIndex(this.sesid, this.tableid, "primary", CreateIndexGrbit.IndexPrimary, indexDef, indexDef.Length, 100);

            for (int i = 0; i < this.numRecords; ++i)
            {
                Api.JetPrepareUpdate(this.sesid, this.tableid, JET_prep.Insert);
                Api.JetSetColumn(this.sesid, this.tableid, this.columnidLong, BitConverter.GetBytes(i), 4, SetColumnGrbit.None, null);
                int ignored;
                Api.JetUpdate(this.sesid, this.tableid, null, 0, out ignored);
            }

            Api.JetCloseTable(this.sesid, this.tableid);
            Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
            Api.JetOpenTable(this.sesid, this.dbid, this.table, null, 0, OpenTableGrbit.None, out this.tableid);
        }