public void Parse_SalesOrderHeader(string dbPath, int pageID, int customerID)
{
var db = new RawDataFile(dbPath);
var page = db.GetPage(pageID);
var record = page.Records.First() as RawPrimaryRecord;
var result = RawColumnParser.Parse(record, new IRawType[] {
RawType.Int("SalesOrderID"),
RawType.TinyInt("RevisionNumber"),
RawType.DateTime("OrderDate"),
RawType.DateTime("DueDate"),
RawType.DateTime("ShipDate"),
RawType.TinyInt("Status"),
RawType.Bit("OnlineOrderFlag"),
RawType.NVarchar("PurchaseOrderNumber"),
RawType.NVarchar("AccountNumber"),
RawType.Int("CustomerID"),
RawType.Int("ShipToAddressID"),
RawType.Int("BillToAddressID"),
RawType.NVarchar("ShipMethod"),
RawType.Varchar("CreditCardApprovalCode"),
RawType.Money("SubTotal"),
RawType.Money("TaxAmt"),
RawType.Money("Freight"),
RawType.NVarchar("Comment"),
RawType.UniqueIdentifier("rowguid"),
RawType.DateTime("ModifiedDate")
});
Assert.AreEqual(20, ((Dictionary <string, object>)result).Count);
Assert.AreEqual(71774, result.SalesOrderID);
Assert.AreEqual(1, result.RevisionNumber);
Assert.AreEqual(Convert.ToDateTime("2004-06-01"), result.OrderDate);
Assert.AreEqual(Convert.ToDateTime("2004-06-13"), result.DueDate);
Assert.AreEqual(Convert.ToDateTime("2004-06-08"), result.ShipDate);
Assert.AreEqual(5, result.Status);
Assert.AreEqual(false, result.OnlineOrderFlag);
Assert.AreEqual("PO348186287", result.PurchaseOrderNumber);
Assert.AreEqual("10-4020-000609", result.AccountNumber);
Assert.AreEqual(customerID, result.CustomerID);
Assert.AreEqual(1092, result.ShipToAddressID);
Assert.AreEqual(1092, result.BillToAddressID);
Assert.AreEqual("CARGO TRANSPORT 5", result.ShipMethod);
Assert.AreEqual(null, result.CreditCardApprovalCode);
Assert.AreEqual(880.3484, result.SubTotal);
Assert.AreEqual(70.4279, result.TaxAmt);
Assert.AreEqual(22.0087, result.Freight);
Assert.AreEqual(null, result.Comment);
Assert.AreEqual(new Guid("89e42cdc-8506-48a2-b89b-eb3e64e3554e"), result.rowguid);
Assert.AreEqual(Convert.ToDateTime("2004-06-08"), result.ModifiedDate);
}
public void Parse_BuildVersion(string dbPath, int pageID, string databaseVersion, string versionDate)
{
var db = new RawDataFile(dbPath);
var page = db.GetPage(pageID);
var record = page.Records.First() as RawPrimaryRecord;
var result = RawColumnParser.Parse(record, new IRawType[] {
RawType.TinyInt("SystemInformationID"),
RawType.NVarchar("Database Version"),
RawType.DateTime("VersionDate"),
RawType.DateTime("ModifiedDate")
});
Assert.AreEqual(4, ((Dictionary <string, object>)result).Count);
Assert.AreEqual(1, result.SystemInformationID);
Assert.AreEqual(databaseVersion, ((Dictionary <string, object>)result)["Database Version"]);
Assert.AreEqual(Convert.ToDateTime(versionDate), result.VersionDate);
Assert.AreEqual(Convert.ToDateTime(versionDate), result.ModifiedDate);
}
private void rawaddTablesNode(TreeNode rootNode)
{
var tableRootNode = rootNode.Nodes.Add("Tables");
var tables = rawtables.OrderBy(t => t.Name);
foreach (var t in tables)
{
var tableNode = tableRootNode.Nodes.Add(t.Name);
tableNode.ContextMenu = tableMenu;
logException(null, "Tabla: [" + t.Name + "]");
// Add columns
var tableColumnsNode = tableNode.Nodes.Add("Columns");
var columns = rawcolumns
.Where(c => c.ObjectID == t.id)
.OrderBy(c => c.Name);
rawcolumnType[] rawcolumntypes = new rawcolumnType[30];
rawcolumntypes[0] = new rawcolumnType("image", 34, 16);
rawcolumntypes[1] = new rawcolumnType("text", 35, 16);
rawcolumntypes[2] = new rawcolumnType("uniqueidentifier", 36, 16);
rawcolumntypes[3] = new rawcolumnType("date", 40, 3);
rawcolumntypes[4] = new rawcolumnType("time", 41, 5);
rawcolumntypes[5] = new rawcolumnType("datetime2", 42, 8);
rawcolumntypes[6] = new rawcolumnType("datetimeoffset", 43, 10);
rawcolumntypes[7] = new rawcolumnType("tinyint", 48, 1);
rawcolumntypes[8] = new rawcolumnType("smallint", 52, 2);
rawcolumntypes[9] = new rawcolumnType("int", 56, 4);
rawcolumntypes[10] = new rawcolumnType("smalldatetime", 58, 4);
rawcolumntypes[11] = new rawcolumnType("real", 59, 4);
rawcolumntypes[12] = new rawcolumnType("money", 60, 8);
rawcolumntypes[13] = new rawcolumnType("datetime", 61, 8);
rawcolumntypes[14] = new rawcolumnType("float", 62, 8);
rawcolumntypes[15] = new rawcolumnType("sql_variant", 98, 8016);
rawcolumntypes[16] = new rawcolumnType("ntext", 99, 16);
rawcolumntypes[17] = new rawcolumnType("bit", 104, 1);
rawcolumntypes[18] = new rawcolumnType("decimal", 106, 17);
rawcolumntypes[19] = new rawcolumnType("numeric", 108, 17);
rawcolumntypes[20] = new rawcolumnType("smallmoney", 122, 4);
rawcolumntypes[21] = new rawcolumnType("bigint", 127, 8);
rawcolumntypes[22] = new rawcolumnType("varbinary", 165, 8000);
rawcolumntypes[23] = new rawcolumnType("varchar", 167, 8000);
rawcolumntypes[24] = new rawcolumnType("binary", 173, 8000);
rawcolumntypes[25] = new rawcolumnType("char", 175, 8000);
rawcolumntypes[26] = new rawcolumnType("timestamp", 189, 8);
rawcolumntypes[27] = new rawcolumnType("nvarchar", 231, 8000);
rawcolumntypes[28] = new rawcolumnType("nchar", 239, 8000);
rawcolumntypes[29] = new rawcolumnType("xml", 241, -1);
foreach (var c in columns)
{
try
{
var mainColumn = columns.Where(x => x.ColumnID == c.ColumnID && x.ObjectID == c.ObjectID).First();
var type = rawcolumntypes.Where(x => x.col_System_type_id == mainColumn.TypeID).First();
tableColumnsNode.Nodes.Add(c.Name + " (" + type.columnName + "[" + type.max_Length + "])");
logException(null, "Column: [" + t.Name + "]." + "[" + c.Name + "]");
} catch (Exception ex)
{
logException(ex, "Table: " + t.Name + " Column: " + c.Name + " ColumnID: " + c.ColumnID + " ObjectID: " + c.ObjectID);
}
}
try
{
var unorderedcolumns = rawcolumns
.Where(c => c.ObjectID == t.id);
IRawType[] SchemaRawType = new IRawType[] { };
var unorderedFirstColumn = unorderedcolumns.First();
Boolean firstColumn = true;
var unorderedFirstColumntype = rawcolumntypes.Where(x => x.col_System_type_id == unorderedFirstColumn.TypeID).First();
Array.Resize(ref SchemaRawType, 1);
switch (unorderedFirstColumntype.col_System_type_id)
{
case 34:
SchemaRawType[0] = RawType.Binary(unorderedFirstColumn.Name, unorderedFirstColumn.Length);
break;
case 35:
SchemaRawType[0] = RawType.Binary(unorderedFirstColumn.Name, unorderedFirstColumn.Length);
break;
case 36:
SchemaRawType[0] = RawType.UniqueIdentifier(unorderedFirstColumn.Name);
break;
case 40:
SchemaRawType[0] = RawType.Date(unorderedFirstColumn.Name);
break;
case 41:
SchemaRawType[0] = RawType.DateTime(unorderedFirstColumn.Name);
break;
case 42:
SchemaRawType[0] = RawType.DateTime(unorderedFirstColumn.Name);
break;
case 43:
SchemaRawType[0] = RawType.DateTime(unorderedFirstColumn.Name);
break;
case 48:
SchemaRawType[0] = RawType.TinyInt(unorderedFirstColumn.Name);
break;
case 52:
SchemaRawType[0] = RawType.SmallInt(unorderedFirstColumn.Name);
break;
case 56:
SchemaRawType[0] = RawType.Int(unorderedFirstColumn.Name);
break;
case 58:
SchemaRawType[0] = RawType.DateTime(unorderedFirstColumn.Name);
break;
case 59:
SchemaRawType[0] = RawType.Decimal(unorderedFirstColumn.Name, Convert.ToByte(15), Convert.ToByte(2));
break;
case 60:
SchemaRawType[0] = RawType.Money(unorderedFirstColumn.Name);
break;
case 61:
SchemaRawType[0] = RawType.DateTime(unorderedFirstColumn.Name);
break;
case 62:
if (unorderedFirstColumn.Length <= 24)
{
SchemaRawType[0] = RawType.Decimal(unorderedFirstColumn.Name, Convert.ToByte(7), Convert.ToByte(2));
}
else
{
SchemaRawType[0] = RawType.Decimal(unorderedFirstColumn.Name, Convert.ToByte(15), Convert.ToByte(2));
}
break;
case 98:
SchemaRawType[0] = RawType.Binary(unorderedFirstColumn.Name, unorderedFirstColumn.Length);
break;
case 99:
SchemaRawType[0] = RawType.Binary(unorderedFirstColumn.Name, unorderedFirstColumn.Length);
break;
case 104:
SchemaRawType[0] = RawType.Bit(unorderedFirstColumn.Name);
break;
case 106:
SchemaRawType[0] = RawType.Decimal(unorderedFirstColumn.Name, Convert.ToByte(28), Convert.ToByte(6));
break;
case 108:
SchemaRawType[0] = RawType.Decimal(unorderedFirstColumn.Name, Convert.ToByte(28), Convert.ToByte(6));
break;
case 122:
SchemaRawType[0] = RawType.Money(unorderedFirstColumn.Name);
break;
case 127:
SchemaRawType[0] = RawType.BigInt(unorderedFirstColumn.Name);
break;
case 165:
SchemaRawType[0] = RawType.VarBinary(unorderedFirstColumn.Name);
break;
case 167:
SchemaRawType[0] = RawType.Varchar(unorderedFirstColumn.Name);
break;
case 173:
SchemaRawType[0] = RawType.Binary(unorderedFirstColumn.Name, unorderedFirstColumn.Length);
break;
case 175:
SchemaRawType[0] = RawType.Char(unorderedFirstColumn.Name, unorderedFirstColumn.Length);
break;
case 189:
SchemaRawType[0] = RawType.Binary(unorderedFirstColumn.Name, unorderedFirstColumn.Length);
break;
case 231:
SchemaRawType[0] = RawType.NVarchar(unorderedFirstColumn.Name);
break;
case 239:
SchemaRawType[0] = RawType.NChar(unorderedFirstColumn.Name, unorderedFirstColumn.Length);
break;
case 241:
SchemaRawType[0] = RawType.Xml(unorderedFirstColumn.Name);
break;
default:
SchemaRawType[0] = RawType.VarBinary(unorderedFirstColumn.Name);
break;
}
logException(null, "Column: [" + t.Name + "]." + "[" + unorderedFirstColumn.Name + "]");
int i = 1;
foreach (var c in unorderedcolumns)
{
if (firstColumn != true)
{
try
{
var nextColumn = unorderedcolumns.Where(x => x.ColumnID == c.ColumnID && x.ObjectID == c.ObjectID).First();
var type = rawcolumntypes.Where(x => x.col_System_type_id == c.TypeID).First();
Array.Resize(ref SchemaRawType, i + 1);
SchemaRawType[i] = new RawNVarchar("");
Boolean uniqueColumn;
try
{
uniqueColumn = (SchemaRawType.Where(x => x.Name.StartsWith(nextColumn.Name)).Count() == 0);
} catch (Exception ex)
{
logException(ex, "Column: [" + t.Name + "].[" + c.Name + "]");
uniqueColumn = true;
}
if (uniqueColumn)
{
switch (type.col_System_type_id)
{
case 34:
SchemaRawType[i] = RawType.Binary(nextColumn.Name, nextColumn.Length);
break;
case 35:
SchemaRawType[i] = RawType.Binary(nextColumn.Name, nextColumn.Length);
break;
case 36:
SchemaRawType[i] = RawType.UniqueIdentifier(nextColumn.Name);
break;
case 40:
SchemaRawType[i] = RawType.Date(nextColumn.Name);
break;
case 41:
SchemaRawType[i] = RawType.DateTime(nextColumn.Name);
break;
case 42:
SchemaRawType[i] = RawType.DateTime(nextColumn.Name);
break;
case 43:
SchemaRawType[i] = RawType.DateTime(nextColumn.Name);
break;
case 48:
SchemaRawType[i] = RawType.TinyInt(nextColumn.Name);
break;
case 52:
SchemaRawType[i] = RawType.SmallInt(nextColumn.Name);
break;
case 56:
SchemaRawType[i] = RawType.Int(nextColumn.Name);
break;
case 58:
SchemaRawType[i] = RawType.DateTime(nextColumn.Name);
break;
case 59:
SchemaRawType[i] = RawType.Decimal(nextColumn.Name, Convert.ToByte(15), Convert.ToByte(2));
break;
case 60:
SchemaRawType[i] = RawType.Money(nextColumn.Name);
break;
case 61:
SchemaRawType[i] = RawType.DateTime(nextColumn.Name);
break;
case 62:
if (nextColumn.Length <= 24)
{
SchemaRawType[i] = RawType.Decimal(nextColumn.Name, Convert.ToByte(7), Convert.ToByte(2));
}
else
{
SchemaRawType[i] = RawType.Decimal(nextColumn.Name, Convert.ToByte(15), Convert.ToByte(2));
}
break;
case 98:
SchemaRawType[i] = RawType.Binary(nextColumn.Name, nextColumn.Length);
break;
case 99:
SchemaRawType[i] = RawType.Binary(nextColumn.Name, nextColumn.Length);
break;
case 104:
SchemaRawType[i] = RawType.Bit(nextColumn.Name);
break;
case 106:
SchemaRawType[i] = RawType.Decimal(nextColumn.Name, Convert.ToByte(28), Convert.ToByte(6));
break;
case 108:
SchemaRawType[i] = RawType.Decimal(nextColumn.Name, Convert.ToByte(28), Convert.ToByte(6));
break;
case 122:
SchemaRawType[i] = RawType.Money(nextColumn.Name);
break;
case 127:
SchemaRawType[i] = RawType.BigInt(nextColumn.Name);
break;
case 165:
SchemaRawType[i] = RawType.VarBinary(nextColumn.Name);
break;
case 167:
SchemaRawType[i] = RawType.Varchar(nextColumn.Name);
break;
case 173:
SchemaRawType[i] = RawType.Binary(nextColumn.Name, nextColumn.Length);
break;
case 175:
SchemaRawType[i] = RawType.Char(nextColumn.Name, nextColumn.Length);
break;
case 189:
SchemaRawType[i] = RawType.Binary(nextColumn.Name, nextColumn.Length);
break;
case 231:
SchemaRawType[i] = RawType.NVarchar(nextColumn.Name);
break;
case 239:
SchemaRawType[i] = RawType.NChar(nextColumn.Name, nextColumn.Length);
break;
case 241:
SchemaRawType[i] = RawType.Xml(nextColumn.Name);
break;
default:
SchemaRawType[i] = RawType.VarBinary(unorderedFirstColumn.Name);
break;
}
i = i + 1;
logException(null, "Column: [" + t.Name + "]." + "[" + nextColumn.Name + "]");
}
}
catch (Exception ex)
{
logException(ex, "Column: [" + t.Name + "].[" + c.Name + "]");
}
}
else
{
firstColumn = false;
}
}
RawTablesSchemas.Add(t.Name, SchemaRawType);
} catch (Exception ex)
{
logException(ex, "Table: " + t.name + ". Failed to get schema");
}
// Add indexes
// var tableIndexesNode = tableNode.Nodes.Add("Indexes");
// var indexes = db.Dmvs.Indexes
// .Where(i => i.ObjectID == t.ObjectID && i.IndexID > 0)
// .OrderBy(i => i.Name);
// foreach (var i in indexes)
// {
// var indexNode = tableIndexesNode.Nodes.Add(i.Name);
// Add index columns
// var indexColumns = db.Dmvs.IndexColumns
// .Where(ic => ic.ObjectID == t.ObjectID && ic.IndexID == i.IndexID);
// foreach (var ic in indexColumns)
// {
// var mainColumn = db.Dmvs.Columns.Where(x => x.ColumnID == ic.ColumnID && x.ObjectID == ic.ObjectID).Single();
// var type = db.Dmvs.Types.Where(x => x.SystemTypeID == mainColumn.SystemTypeID).First();
// indexNode.Nodes.Add(columns.Where(c => c.ColumnID == ic.ColumnID).Single().Name + " (" + type.Name + "[" + type.MaxLength + "])");
// }
}
}
private void openToolStripMenuItem1_Click(object sender, EventArgs e)
{
var result = openDatabaseDialog.ShowDialog();
file = "";
if (result == DialogResult.OK)
{
try
{
var files = openDatabaseDialog.FileNames;
file = files[0];
db = new Database(files);
refreshTreeview();
} catch (Exception ex)
{
logException(ex);
try
{
dbf = new RawDataFile(file);
var records = dbf.Pages
.Where(x => x.Header.ObjectID == 34 && x.Header.Type == PageType.Data)
.SelectMany(x => x.Records);
var rows = records.Select(x => RawColumnParser.Parse((RawRecord)x, new IRawType[] {
RawType.Int("id"),
RawType.NVarchar("name"),
RawType.Int("nsid"),
RawType.TinyInt("nsclass"),
RawType.Int("status"),
RawType.Char("type", 2),
RawType.Int("pid"),
RawType.TinyInt("pclass"),
RawType.Int("intprop"),
RawType.DateTime("created"),
RawType.DateTime("modified")
}));
rawtables = rows
.Where(x => x.GetColumnValue("type").ToString().Trim() == "U");
rawrefreshTreeview();
}
catch (Exception rawex)
{
logException(rawex);
try
{
var bestdbf = new RawDataFile(file);
var SysschobjsPages = bestdbf.Pages
.Where(x => x.Header.ObjectID == 34 && x.Header.Type == PageType.Data);
var bestRecords = RawColumnParser.BestEffortParse(SysschobjsPages, new IRawType[] {
RawType.Int("id"),
RawType.NVarchar("name"),
RawType.Int("nsid"),
RawType.TinyInt("nsclass"),
RawType.Int("status"),
RawType.Char("type", 2),
RawType.Int("pid"),
RawType.TinyInt("pclass"),
RawType.Int("intprop"),
RawType.DateTime("created"),
RawType.DateTime("modified")
});
rawtables = bestRecords
.Where(x => x.GetColumnValue("type").ToString().Trim() == "U");
var syscolparspages = bestdbf.Pages.Where(x => x.Header.ObjectID == 41 && x.Header.Type == PageType.Data);
var syscolparsrecords = syscolparspages.SelectMany(x => x.Records).Select(x => (RawPrimaryRecord)x);
var syscolparsrows = RawColumnParser.BestEffortParse(syscolparspages, new IRawType[] {
RawType.Int("id"),
RawType.SmallInt("number"),
RawType.Int("colid"),
RawType.NVarchar("name"),
RawType.TinyInt("xtype"),
RawType.Int("utype"),
RawType.SmallInt("length"),
RawType.TinyInt("prec"),
RawType.TinyInt("scale"),
RawType.Int("collationid"),
RawType.Int("status"),
RawType.SmallInt("maxinrow"),
RawType.Int("xmlns"),
RawType.Int("dflt"),
RawType.Int("chk"),
RawType.VarBinary("idtval")
});
rawcolumns = syscolparsrows.Select(x => new
{
ObjectID = (int?)x.GetColumnValue("id"),
ColumnID = (int?)x.GetColumnValue("colid"),
Number = (short?)x.GetColumnValue("number"),
TypeID = (byte?)x.GetColumnValue("xtype"),
Length = (short?)x.GetColumnValue("length"),
Name = x.GetColumnValue("name")
});
rawrefreshTreeview();
}
catch (Exception bestrawex)
{
logException(bestrawex);
}
}
}
}
}