public void ExportTable(string tableName, string destinationPath)
{
var tables = GetTablesNames(dbid);
if (!tables.Contains(tableName))
throw new ArgumentException(string.Format("The given table name {0} is does not exsist in the database.\n",tableName) +
"available tables are:\n"+ string.Join(", ",tables));
JET_TABLEID tableId;
Api.JetOpenTable(sesid,dbid,tableName,null ,0 ,OpenTableGrbit.ReadOnly, out tableId);
bool success = Api.TryMoveFirst(sesid, tableId);
var columnInfo = Api.GetTableColumns(sesid, tableId);
using(var writer = new CsvFileWriter(destinationPath))
{
writer.WriteHeaders(columnInfo.Select(x => x.Name));
if (!success) throw new Exception("Failed to move to the first record.");
while (success)
{
ExportSingleRecord(writer,columnInfo, tableId);
success = Api.TryMoveNext(sesid, tableId);
}
}
}
private void ExportSingleRecord(CsvFileWriter writer, IEnumerable<ColumnInfo> columnInfo, JET_TABLEID tableId)
{
foreach (var column in columnInfo)
{
var c = Api.RetrieveColumn(sesid, tableId, column.Columnid);
switch (column.Coltyp)
{
case JET_coltyp.Nil:
case JET_coltyp.Bit:
case JET_coltyp.UnsignedByte:
case JET_coltyp.Short:
case JET_coltyp.Currency:
case JET_coltyp.IEEESingle:
case JET_coltyp.IEEEDouble:
writer.WriteCsvColumnValue("unknown field");
break;
case JET_coltyp.Long:
writer.WriteCsvColumnValue(Api.RetrieveColumnAsInt32(sesid, tableId, column.Columnid).ToString());
break;
case JET_coltyp.DateTime:
writer.WriteCsvColumnValue(DateTime.FromBinary(Api.RetrieveColumnAsInt64(sesid, tableId, column.Columnid).Value).ToString());
break;
case JET_coltyp.Binary:
if (column.MaxLength == 8)
{
writer.WriteCsvColumnValue(DateTime.FromBinary(Api.RetrieveColumnAsInt64(sesid, tableId, column.Columnid).Value).ToString());
}
else if (column.MaxLength == 16)
{
var data = Api.RetrieveColumn(sesid, tableId, column.Columnid);
//etag is null
if (data == null)
{
writer.WriteCsvColumnValue("null");
}
else
{
writer.WriteCsvColumnValue(Etag.Parse(data));
}
}
else if (column.MaxLength == 20)
{
var sha1Hash = BitConverter.ToString(Api.RetrieveColumn(sesid, tableId, column.Columnid));
writer.WriteCsvColumnValue(sha1Hash);
}
else
{
writer.WriteCsvColumnValue("unknown binary field");
}
break;
case JET_coltyp.Text:
writer.WriteCsvColumnValue(Api.RetrieveColumnAsString(sesid, tableId, column.Columnid));
break;
case JET_coltyp.LongBinary:
writer.WriteCsvColumnValue(Api.RetrieveColumn(sesid, tableId, column.Columnid).ToJObject().ToString(),escape:true);
break;
case JET_coltyp.LongText:
writer.WriteCsvColumnValue(Api.RetrieveColumnAsString(sesid, tableId, column.Columnid));
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
private void ExportSingleRecord(CsvFileWriter writer, IEnumerable <ColumnInfo> columnInfo, JET_TABLEID tableId)
{
foreach (var column in columnInfo)
{
var c = Api.RetrieveColumn(sesid, tableId, column.Columnid);
switch (column.Coltyp)
{
case JET_coltyp.Nil:
case JET_coltyp.Bit:
case JET_coltyp.UnsignedByte:
case JET_coltyp.Short:
case JET_coltyp.Currency:
case JET_coltyp.IEEESingle:
case JET_coltyp.IEEEDouble:
writer.WriteCsvColumnValue("unknown field");
break;
case JET_coltyp.Long:
writer.WriteCsvColumnValue(Api.RetrieveColumnAsInt32(sesid, tableId, column.Columnid).ToString());
break;
case JET_coltyp.DateTime:
writer.WriteCsvColumnValue(DateTime.FromBinary(Api.RetrieveColumnAsInt64(sesid, tableId, column.Columnid).Value).ToString());
break;
case JET_coltyp.Binary:
if (column.MaxLength == 8)
{
writer.WriteCsvColumnValue(DateTime.FromBinary(Api.RetrieveColumnAsInt64(sesid, tableId, column.Columnid).Value).ToString());
}
else if (column.MaxLength == 16)
{
var data = Api.RetrieveColumn(sesid, tableId, column.Columnid);
//etag is null
if (data == null)
{
writer.WriteCsvColumnValue("null");
}
else
{
writer.WriteCsvColumnValue(Etag.Parse(data));
}
}
else if (column.MaxLength == 20)
{
var sha1Hash = BitConverter.ToString(Api.RetrieveColumn(sesid, tableId, column.Columnid));
writer.WriteCsvColumnValue(sha1Hash);
}
else
{
writer.WriteCsvColumnValue("unknown binary field");
}
break;
case JET_coltyp.Text:
writer.WriteCsvColumnValue(Api.RetrieveColumnAsString(sesid, tableId, column.Columnid));
break;
case JET_coltyp.LongBinary:
writer.WriteCsvColumnValue(Api.RetrieveColumn(sesid, tableId, column.Columnid).ToJObject().ToString(), escape: true);
break;
case JET_coltyp.LongText:
writer.WriteCsvColumnValue(Api.RetrieveColumnAsString(sesid, tableId, column.Columnid));
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
