C# Interop.Table类代码示例

        public void Update(Session session, JET_DBID dbid, Action<string> output)
        {
            using (var tbl = new Table(session, dbid, "tasks", OpenTableGrbit.None))
            {
                int rows = 0;
                if (Api.TryMoveFirst(session, tbl))
                {
                    var taskTypeColumnId = Api.GetTableColumnid(session, tbl, "task_type");
                    do
                    {
                        using (var update = new Update(session, tbl, JET_prep.Replace))
                        {
                            var taskType = Api.RetrieveColumnAsString(session, tbl, taskTypeColumnId, Encoding.Unicode);
                            Api.SetColumn(session, tbl, taskTypeColumnId, taskType, Encoding.ASCII);
                            update.Save();
                        }

                        if (rows++ % 10000 == 0)
                        {
                            output("Processed " + (rows) + " rows in tasks");
                            Api.JetCommitTransaction(session, CommitTransactionGrbit.LazyFlush);
                            Api.JetBeginTransaction2(session, BeginTransactionGrbit.None);
                        }
                    } while (Api.TryMoveNext(session, tbl));
                }

                SchemaCreator.UpdateVersion(session, dbid, "5.3");
            }
        }

		public static void DumpTable(Session session, Table table, Stream stream)
		{
			using (var writer = new StreamWriter(stream))
			{
				var cols = Api.GetTableColumns(session, table).ToArray();
				foreach (var col in cols)
				{
					writer.Write(col);
					writer.Write(",");
				}
				writer.WriteLine("#");
				Api.MoveBeforeFirst(session, table);
				int count = 0;
				while (Api.TryMoveNext(session, table))
				{
					foreach (var col in cols)
					{
						var val = GetvalueFromTable(session, table, col) ?? "NULL";
						writer.Write(val);
						writer.Write(",");
					}
					writer.WriteLine(++count);
				}
				writer.Flush();
			}
		}

		public void Update(Session session, JET_DBID dbid, Action<string> output)
		{
			using (var tbl = new Table(session, dbid, "lists", OpenTableGrbit.None))
			{
				JET_COLUMNID columnid;
				Api.JetAddColumn(session, tbl, "created_at", new JET_COLUMNDEF
				{
					coltyp = JET_coltyp.DateTime,
					grbit = ColumndefGrbit.ColumnMaybeNull,
				}, null, 0, out columnid);

				if (Api.TryMoveFirst(session, tbl))
				{
					do
					{
						using (var update = new Update(session, tbl, JET_prep.Replace))
						{
							var createdAt = Api.GetTableColumnid(session, tbl, "created_at");
							Api.SetColumn(session, tbl, createdAt, SystemTime.UtcNow);
							update.Save();
						}
					} while (Api.TryMoveNext(session, tbl));
				}

				SchemaCreator.CreateIndexes(session, tbl, new JET_INDEXCREATE
				{
					szIndexName = "by_name_and_created_at",
					szKey = "+name\0+created_at\0\0",
					grbit = CreateIndexGrbit.IndexDisallowNull
				});

				SchemaCreator.UpdateVersion(session, dbid, "5.1");
			}
		}

		public void Update(Session session, JET_DBID dbid)
		{
			using (var tx = new Transaction(session))
			{
				using (var files = new Table(session, dbid, "files", OpenTableGrbit.None))
				{

					const string indexDef = "+etag\0\0";
					Api.JetCreateIndex(session, files, "by_etag", CreateIndexGrbit.IndexDisallowNull, indexDef, indexDef.Length,
									   100);

					using (var details = new Table(session, dbid, "details", OpenTableGrbit.None))
					{
						Api.JetMove(session, details, JET_Move.First, MoveGrbit.None);
						var columnids = Api.GetColumnDictionary(session, details);

						using (var update = new Update(session, details, JET_prep.Replace))
						{
							Api.SetColumn(session, details, columnids["schema_version"], "2.7", Encoding.Unicode);

							update.Save();
						}
					}
				}
				tx.Commit(CommitTransactionGrbit.None);
			}
		}

 private static object GetvalueFromTable(Session session, Table table, ColumnInfo col)
 {
     switch (col.Coltyp)
     {
         case JET_coltyp.Long:
             return Api.RetrieveColumnAsInt32(session, table, col.Columnid);
         case JET_coltyp.DateTime:
             return Api.RetrieveColumnAsDateTime(session, table, col.Columnid);
         case JET_coltyp.Binary:
             var bytes = Api.RetrieveColumn(session, table, col.Columnid);
             if (bytes == null)
                 return null;
             if (bytes.Length == 16)
                 return new Guid(bytes);
             return Convert.ToBase64String(bytes);
         case JET_coltyp.LongText:
         case JET_coltyp.Text:
             var str = Api.RetrieveColumnAsString(session, table, col.Columnid);
             if (str == null)
                 return null;
             if (str.Contains("\""))
                 return "\"" + str.Replace("\"", "\"\"") + "\"";
             return str;
         case JET_coltyp.LongBinary:
             return "long binary val";
         default:
             throw new ArgumentOutOfRangeException("don't know how to handle coltype: " + col.Coltyp);
     }
 }

        public void Update(Session session, JET_DBID dbid)
        {
            using (var tx = new Transaction(session))
            {
                using (var tasks = new Table(session, dbid, "tasks", OpenTableGrbit.None))
                {
                    JET_COLUMNID columnid;
                    Api.JetAddColumn(session, tasks, "added_at",new JET_COLUMNDEF
                    {
                        coltyp = JET_coltyp.DateTime,
                        grbit = ColumndefGrbit.ColumnNotNULL
                    }, null, 0, out columnid);

                    using (var details = new Table(session, dbid, "details", OpenTableGrbit.None))
                    {
                        Api.JetMove(session, details, JET_Move.First, MoveGrbit.None);
                        var columnids = Api.GetColumnDictionary(session, details);

                        using (var update = new Update(session, details, JET_prep.Replace))
                        {
                            Api.SetColumn(session, details, columnids["schema_version"], "2.5", Encoding.Unicode);

                            update.Save();
                        }
                    }
                }
                tx.Commit(CommitTransactionGrbit.None);
            }
        }

        protected override CrawlerQueueEntry PopImpl()
        {
            return m_EsentInstance.Cursor((session, dbid) =>
                {
                    using (Transaction transaction = new Transaction(session))
                    {
                        using (Table table = new Table(session, dbid, EsentTableDefinitions.QueueTableName, OpenTableGrbit.None))
                        {
                            if (Api.TryMoveFirst(session, table))
                            {
                                byte[] data = Api.RetrieveColumn(session, table, dataColumn.columnid);
                                Api.JetDelete(session, table);

                                using (Table table2 = new Table(session, dbid, EsentTableDefinitions.GlobalsTableName, OpenTableGrbit.None))
                                {
                                    Api.EscrowUpdate(session, table2, queueCountColumn.columnid, -1);
                                }

                                transaction.Commit(CommitTransactionGrbit.None);
                                return data.FromBinary<CrawlerQueueEntry>();
                            }
                        }

                        transaction.Rollback();
                        return null;
                    }
                });
        }

 protected void GetColumnIds(
     JET_SESID sessionId, Table table, out JET_COLUMNID projectColumnId, out JET_COLUMNID projectNameColumnId, out JET_COLUMNID documentColumnId)
 {
     projectColumnId = Api.GetTableColumnid(sessionId, table, ProjectColumnName);
     projectNameColumnId = Api.GetTableColumnid(sessionId, table, ProjectNameColumnName);
     documentColumnId = Api.GetTableColumnid(sessionId, table, DocumentColumnName);
 }

        protected AbstractActions(JET_INSTANCE instance, ColumnsInformation columnsInformation, string database, Guid instanceId)
        {
            try
            {
                this.instanceId = instanceId;
                ColumnsInformation = columnsInformation;
                session = new Session(instance);

                transaction = new Transaction(session);
                Api.JetOpenDatabase(session, database, null, out dbid, OpenDatabaseGrbit.None);

                queues = new Table(session, dbid, "queues", OpenTableGrbit.None);
                subqueues = new Table(session, dbid, "subqueues", OpenTableGrbit.None);
                txs = new Table(session, dbid, "transactions", OpenTableGrbit.None);
                recovery = new Table(session, dbid, "recovery", OpenTableGrbit.None);
                outgoing = new Table(session, dbid, "outgoing", OpenTableGrbit.None);
                outgoingHistory = new Table(session, dbid, "outgoing_history", OpenTableGrbit.None);
                recveivedMsgs = new Table(session, dbid, "recveived_msgs", OpenTableGrbit.None);
            }
            catch (Exception)
            {
                Dispose();
                throw;
            }
        }

		public void InitColumDictionaries(JET_INSTANCE instance, string database)
	    {
	        using (var session = new Session(instance))
	        {
	            var dbid = JET_DBID.Nil;
	            try
	            {
	                Api.JetOpenDatabase(session, database, null, out dbid, OpenDatabaseGrbit.None);
	                using (var usage = new Table(session, dbid, "usage", OpenTableGrbit.None))
						UsageColumns = Api.GetColumnDictionary(session, usage);
					using (var details = new Table(session, dbid, "details", OpenTableGrbit.None))
						DetailsColumns = Api.GetColumnDictionary(session, details);
					using (var pages = new Table(session, dbid, "pages", OpenTableGrbit.None))
						PagesColumns = Api.GetColumnDictionary(session, pages);
	                using (var files = new Table(session, dbid, "files", OpenTableGrbit.None))
	                    FilesColumns = Api.GetColumnDictionary(session, files);
					using (var signatures = new Table(session, dbid, "signatures", OpenTableGrbit.None))
						SignaturesColumns = Api.GetColumnDictionary(session, signatures);
					using (var config = new Table(session, dbid, "config", OpenTableGrbit.None))
						ConfigColumns = Api.GetColumnDictionary(session, config);
	            }
	            finally
	            {
	                if (Equals(dbid, JET_DBID.Nil) == false)
	                    Api.JetCloseDatabase(session, dbid, CloseDatabaseGrbit.None);
	            }
	        }
	    }

		public void Update(Session session, JET_DBID dbid, Action<string> output)
        {
            using (var table = new Table(session, dbid, "indexes_stats", OpenTableGrbit.None))
            {
                byte[] defaultValue = BitConverter.GetBytes(1);
                JET_COLUMNID columnid;
                Api.JetAddColumn(session, table, "priority", new JET_COLUMNDEF
                {
                    coltyp = JET_coltyp.Long,
                    grbit = ColumndefGrbit.ColumnFixed | ColumndefGrbit.ColumnNotNULL
                }, defaultValue, defaultValue.Length, out columnid);

				defaultValue = BitConverter.GetBytes(0);
				Api.JetAddColumn(session, table, "created_timestamp", new JET_COLUMNDEF
                {
                    cbMax = 8, //64 bits
                    coltyp = JET_coltyp.Binary,
                    grbit = ColumndefGrbit.ColumnFixed | ColumndefGrbit.ColumnNotNULL
				}, defaultValue, defaultValue.Length, out columnid);

				Api.JetAddColumn(session, table, "last_indexing_time", new JET_COLUMNDEF
				{
					cbMax = 8, //64 bits
					coltyp = JET_coltyp.Binary,
					grbit = ColumndefGrbit.ColumnFixed | ColumndefGrbit.ColumnNotNULL
				}, defaultValue, defaultValue.Length, out columnid);
            }

            SchemaCreator.UpdateVersion(session,   dbid, "4.6");
        }

        public void Update(Session session, JET_DBID dbid, Action<string> output)
        {
            using (var table = new Table(session, dbid, "config", OpenTableGrbit.DenyRead | OpenTableGrbit.PermitDDL))
            {
                JET_COLUMNID newMetadataColumnId;

                Api.JetAddColumn(session, table, "metadata_new", new JET_COLUMNDEF
                {
                    cbMax = 1024*512,
                    coltyp = JET_coltyp.LongText,
                    cp = JET_CP.Unicode,
                    grbit = ColumndefGrbit.ColumnNotNULL
                }, null, 0, out newMetadataColumnId);
            }

            using (var table = new Table(session, dbid, "config", OpenTableGrbit.None))
            {
                Api.MoveBeforeFirst(session, table);

                var rows = 0;

                var columnDictionary = Api.GetColumnDictionary(session, table);

                var metadataColumn = columnDictionary["metadata"];
                var nameColumn = columnDictionary["name"];
                var newMetadataColumn = columnDictionary["metadata_new"];

                while (Api.TryMoveNext(session, table))
                {
                    using (var insert = new Update(session, table, JET_prep.Replace))
                    {
                        var name = Api.RetrieveColumnAsString(session, table, nameColumn, Encoding.Unicode);
                        var metadata = Api.RetrieveColumnAsString(session, table, metadataColumn, Encoding.Unicode);
                        var fixedMetadata = GuidToEtagMigrationInConfigurations(metadata, name);

                        Api.SetColumn(session, table, newMetadataColumn, fixedMetadata, Encoding.Unicode);

                        insert.Save();
                    }

                    if (rows++ % 100 == 0)
                    {
                        output("Processed " + (rows) + " rows from metadata column in config table");
                        Api.JetCommitTransaction(session, CommitTransactionGrbit.LazyFlush);
                        Api.JetBeginTransaction2(session, BeginTransactionGrbit.None);
                    }
                }

                Api.JetCommitTransaction(session, CommitTransactionGrbit.None);
                
                // they cannot be run in transaction scope
                Api.JetDeleteColumn(session, table, "metadata");
                Api.JetRenameColumn(session, table, "metadata_new", "metadata", RenameColumnGrbit.None);
                
                Api.JetBeginTransaction2(session, BeginTransactionGrbit.None);
            }

            SchemaCreator.UpdateVersion(session, dbid, "0.5");
        }

        public void Update(Session session, JET_DBID dbid)
        {
            using(var tx = new Transaction(session))
            {
                using (var mappedResults = new Table(session, dbid, "mapped_results", OpenTableGrbit.None))
                {
                    JET_COLUMNID columnid;
                    Api.JetAddColumn(session, mappedResults, "reduce_key_and_view_hashed", new JET_COLUMNDEF
                    {
                        cbMax = 32,
                        coltyp = JET_coltyp.Binary,
                        grbit = ColumndefGrbit.ColumnNotNULL
                    }, null, 0, out columnid);

                    const string indexDef = "+reduce_key_and_view_hashed\0\0";
                    Api.JetCreateIndex(session, mappedResults, "by_reduce_key_and_view_hashed",
                                       CreateIndexGrbit.IndexDisallowNull, indexDef, indexDef.Length,
                                       100);

                    Api.JetDeleteIndex(session, mappedResults, "by_view_and_reduce_key");


                    var columnDictionary = Api.GetColumnDictionary(session, mappedResults);

                    Api.MoveBeforeFirst(session, mappedResults);
                    while (Api.TryMoveNext(session, mappedResults))
                    {
                        using (var update = new Update(session, mappedResults, JET_prep.Replace))
                        {
                            var computeHash = MapReduceIndex.ComputeHash(
                                Api.RetrieveColumnAsString(session, mappedResults, columnDictionary["view"],
                                                           Encoding.Unicode),
                                Api.RetrieveColumnAsString(session, mappedResults, columnDictionary["reduce_key"],
                                                           Encoding.Unicode));

                            Api.SetColumn(session, mappedResults, columnDictionary["reduce_key_and_view_hashed"],
                                          computeHash);

                            update.Save();
                        }
                    }


                    using (var details = new Table(session, dbid, "details", OpenTableGrbit.None))
                    {
                        Api.JetMove(session, details, JET_Move.First, MoveGrbit.None);
                        var columnids = Api.GetColumnDictionary(session, details);

                        using(var update = new Update(session, details, JET_prep.Replace))
                        {
                            Api.SetColumn(session, details, columnids["schema_version"], "2.2", Encoding.Unicode);

                            update.Save();
                        }
                    }
                }
                tx.Commit(CommitTransactionGrbit.None);
            }
        }

 public override void Initialize(JET_SESID sessionId, JET_DBID databaseId)
 {
     using (var table = new Table(sessionId, databaseId, TableName, OpenTableGrbit.ReadOnly))
     {
         _idColumnId = Api.GetTableColumnid(sessionId, table, IdColumnName);
         _identifierColumnId = Api.GetTableColumnid(sessionId, table, IdentifierColumnName);
     }
 }

		public void Update(Session session, JET_DBID dbid)
		{
			Transaction tx;
			using (tx = new Transaction(session))
			{
				using ( var tbl = new Table(session, dbid, "indexes_stats",
					OpenTableGrbit.PermitDDL | OpenTableGrbit.DenyRead | OpenTableGrbit.DenyWrite))
				{
					var columnDictionary = Api.GetColumnDictionary(session, tbl);

					if (columnDictionary.ContainsKey("reduce_attempts"))
						Api.JetDeleteColumn(session, tbl, "reduce_attempts");
					if (columnDictionary.ContainsKey("reduce_errors"))
						Api.JetDeleteColumn(session, tbl, "reduce_errors");
					if (columnDictionary.ContainsKey("reduce_successes"))
						Api.JetDeleteColumn(session, tbl, "reduce_successes");
				}

				CreateIndexesStatsReduce(session, dbid);

				using (var stats = new Table(session, dbid, "indexes_stats",OpenTableGrbit.None))
				using (var reduce = new Table(session, dbid, "indexes_stats_reduce", OpenTableGrbit.None))
				{
					var tblKeyColumn = Api.GetColumnDictionary(session, stats)["key"];
					var reduceKeyCol = Api.GetColumnDictionary(session, reduce)["key"];

					Api.MoveBeforeFirst(session, stats);
					while (Api.TryMoveNext(session, stats))
					{
						using(var update = new Update(session, reduce, JET_prep.Insert))
						{
							var indexName = Api.RetrieveColumnAsString(session, stats, tblKeyColumn, Encoding.Unicode);
							Api.SetColumn(session, reduce, reduceKeyCol, indexName, Encoding.Unicode);
							update.Save();
						}
					}
				}

				tx.Commit(CommitTransactionGrbit.LazyFlush);
				tx.Dispose();
				tx = new Transaction(session);
			}

			using (var details = new Table(session, dbid, "details", OpenTableGrbit.None))
			{
				Api.JetMove(session, details, JET_Move.First, MoveGrbit.None);
				var columnids = Api.GetColumnDictionary(session, details);

				using (var update = new Update(session, details, JET_prep.Replace))
				{
					Api.SetColumn(session, details, columnids["schema_version"], "3.4", Encoding.Unicode);

					update.Save();
				}
			}
			tx.Commit(CommitTransactionGrbit.None);
			tx.Dispose();
		}