private static void _BulkInsert(Timeline timeline, string path, DataTable timelinesTbl, DataTable exhibitsTbl)
{
// insert child timelines
for (var i = 0; i < timeline.ChildTimelines.Count; i++)
{
var child = timeline.ChildTimelines[i];
var tlPath = path + (i + 1) + "/";
var row = timelinesTbl.NewRow();
row["path"] = SqlHierarchyId.Parse(tlPath);
row["title"] = child.Title;
row["left"] = child.left;
row["right"] = child.right;
row["top"] = child.realY;
row["bottom"] = child.realHeight;
timelinesTbl.Rows.Add(row);
// insert child timeline exhibits
foreach (var exhibit in child.Exhibits)
{
row = exhibitsTbl.NewRow();
row["path"] = SqlHierarchyId.Parse(tlPath);
row["title"] = exhibit.Title;
row["x"] = exhibit.x;
row["y"] = exhibit.y;
row["radius"] = exhibit.size;
row["uri"] = "http://chronozoomproject.org";
exhibitsTbl.Rows.Add(row);
}
}
for (var i = 0; i < timeline.ChildTimelines.Count; i++)
_BulkInsert(timeline.ChildTimelines[i], path + (i + 1) + "/", timelinesTbl, exhibitsTbl);
}
private static void _Insert(Timeline timeline, string path, SqlConnection connection)
{
// insert child timelines
for (var i = 0; i < timeline.ChildTimelines.Count; i++)
{
var child = timeline.ChildTimelines[i];
var tlPath = path + (i + 1) + "/";
var cmd1 = new SqlCommand("INSERT INTO Timelines ([path], [title], [left], [right], [top], [bottom]) VALUES (@path, @title, @left, @right, @top, @bottom)", connection);
cmd1.CommandType = CommandType.Text;
cmd1.Parameters.AddWithValue("@path", tlPath);
cmd1.Parameters.AddWithValue("@title", child.Title);
cmd1.Parameters.AddWithValue("@left", child.left);
cmd1.Parameters.AddWithValue("@right", child.right);
cmd1.Parameters.AddWithValue("@top", child.realY);
cmd1.Parameters.AddWithValue("@bottom", child.realHeight); // todo: verifiy that correct top and bottom are correct
cmd1.ExecuteNonQuery();
// insert child timeline exhibits
foreach (var exhibit in child.Exhibits)
{
var cmd2 = new SqlCommand("INSERT INTO Exhibits ([path], [title], [x], [y], [radius], [uri]) VALUES (@path, @title, @x, @y, @radius, @uri)", connection);
cmd2.CommandType = CommandType.Text;
cmd2.Parameters.AddWithValue("@path", tlPath);
cmd2.Parameters.AddWithValue("@title", exhibit.Title);
cmd2.Parameters.AddWithValue("@x", exhibit.x);
cmd2.Parameters.AddWithValue("@y", exhibit.y); // todo: verifiy that y is correct
cmd2.Parameters.AddWithValue("@radius", exhibit.size);
cmd2.Parameters.AddWithValue("@uri", "http://chronozoomproject.org");
cmd2.ExecuteNonQuery();
}
}
for (var i = 0; i < timeline.ChildTimelines.Count; i++)
_Insert(timeline.ChildTimelines[i], path + (i + 1) + "/", connection);
}
private static void BulkInsert(Timeline timeline, SqlConnection connection)
{
// create timelines table
var timelinesTbl = new DataTable("Timelines");
// create columns
var pathColumnTimelines = new DataColumn();
pathColumnTimelines.DataType = typeof(SqlHierarchyId);
pathColumnTimelines.ColumnName = "path";
timelinesTbl.Columns.Add(pathColumnTimelines);
var titleColumnTimelines = new DataColumn();
titleColumnTimelines.DataType = typeof(string);
titleColumnTimelines.ColumnName = "title";
timelinesTbl.Columns.Add(titleColumnTimelines);
var leftColumnTimelines = new DataColumn();
leftColumnTimelines.DataType = typeof(double);
leftColumnTimelines.ColumnName = "left";
timelinesTbl.Columns.Add(leftColumnTimelines);
var rightColumnTimelines = new DataColumn();
rightColumnTimelines.DataType = typeof(double);
rightColumnTimelines.ColumnName = "right";
timelinesTbl.Columns.Add(rightColumnTimelines);
var topColumnTimelines = new DataColumn();
topColumnTimelines.DataType = typeof(double);
topColumnTimelines.ColumnName = "top";
timelinesTbl.Columns.Add(topColumnTimelines);
var bottomColumnTimelines = new DataColumn();
bottomColumnTimelines.DataType = typeof(double);
bottomColumnTimelines.ColumnName = "bottom";
timelinesTbl.Columns.Add(bottomColumnTimelines);
// create constraints
var keys = new DataColumn[1];
keys[0] = pathColumnTimelines;
timelinesTbl.PrimaryKey = keys;
// add rows
var row = timelinesTbl.NewRow();
row["path"] = SqlHierarchyId.Parse("/");
row["title"] = timeline.Title;
row["left"] = timeline.left;
row["right"] = timeline.right;
row["top"] = timeline.realY;
row["bottom"] = timeline.realHeight;
timelinesTbl.Rows.Add(row);
// create exhibits table
var exhibitsTbl = new DataTable("Exhibits");
// create columns
var pathColumnExhibits = new DataColumn();
pathColumnExhibits.DataType = typeof(SqlHierarchyId);
pathColumnExhibits.ColumnName = "path";
exhibitsTbl.Columns.Add(pathColumnExhibits);
var titleColumnExhibits = new DataColumn();
titleColumnExhibits.DataType = typeof(string);
titleColumnExhibits.ColumnName = "title";
exhibitsTbl.Columns.Add(titleColumnExhibits);
var xColumnExhibits = new DataColumn();
xColumnExhibits.DataType = typeof(double);
xColumnExhibits.ColumnName = "x";
exhibitsTbl.Columns.Add(xColumnExhibits);
var yColumnExhibits = new DataColumn();
yColumnExhibits.DataType = typeof(double);
yColumnExhibits.ColumnName = "y";
exhibitsTbl.Columns.Add(yColumnExhibits);
var radiusColumnExhibits = new DataColumn();
radiusColumnExhibits.DataType = typeof(double);
radiusColumnExhibits.ColumnName = "radius";
exhibitsTbl.Columns.Add(radiusColumnExhibits);
var uriColumnExhibits = new DataColumn();
uriColumnExhibits.DataType = typeof(string);
uriColumnExhibits.ColumnName = "uri";
exhibitsTbl.Columns.Add(uriColumnExhibits);
// create constraints
keys = new DataColumn[2];
keys[0] = xColumnExhibits;
keys[1] = yColumnExhibits;
exhibitsTbl.PrimaryKey = keys;
// add rows
foreach (var exhibit in timeline.Exhibits)
{
row = exhibitsTbl.NewRow();
row["path"] = SqlHierarchyId.Parse("/");
row["title"] = exhibit.Title;
row["x"] = exhibit.x;
row["y"] = exhibit.y;
row["radius"] = exhibit.size;
row["uri"] = "http://chronozoomproject.org";
exhibitsTbl.Rows.Add(row);
}
_BulkInsert(timeline, "/", timelinesTbl, exhibitsTbl);
timelinesTbl.AcceptChanges();
exhibitsTbl.AcceptChanges();
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "dbo.Timelines";
try { bulkCopy.WriteToServer(timelinesTbl); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
bulkCopy.DestinationTableName = "dbo.Exhibits";
try { bulkCopy.WriteToServer(exhibitsTbl); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
}
}
private static void Insert(Timeline timeline, SqlConnection connection)
{
var cmd1 = new SqlCommand("INSERT INTO Timelines ([path], [title], [left], [right], [top], [bottom]) VALUES (@path, @title, @left, @right, @top, @bottom)", connection);
cmd1.CommandType = CommandType.Text;
cmd1.Parameters.AddWithValue("@path", "/");
cmd1.Parameters.AddWithValue("@title", timeline.Title);
cmd1.Parameters.AddWithValue("@left", timeline.left);
cmd1.Parameters.AddWithValue("@right", timeline.right);
cmd1.Parameters.AddWithValue("@top", timeline.realY);
cmd1.Parameters.AddWithValue("@bottom", timeline.realHeight); // todo: verifiy that correct top and bottom are correct
cmd1.ExecuteNonQuery();
foreach (var exhibit in timeline.Exhibits)
{
var cmd2 = new SqlCommand("INSERT INTO Exhibits ([path], [title], [x], [y], [radius], [uri]) VALUES (@path, @title, @x, @y, @radius, @uri)", connection);
cmd2.CommandType = CommandType.Text;
cmd2.Parameters.AddWithValue("@path", "/");
cmd2.Parameters.AddWithValue("@title", exhibit.Title);
cmd2.Parameters.AddWithValue("@x", exhibit.x);
cmd2.Parameters.AddWithValue("@y", exhibit.y); // todo: verifiy that y is correct
cmd2.Parameters.AddWithValue("@radius", exhibit.size);
cmd2.Parameters.AddWithValue("@uri", "http://chronozoomproject.org");
cmd2.ExecuteNonQuery();
}
_Insert(timeline, "/", connection);
}
private static void _GenerateTestingData(Timeline timeline, int level, int depth, int maxNumTimelines, int numChildTimelines, int numChildExhibits, ref int cntTimelines, ref int cntExhibits)
{
for (var i = 0; i < numChildExhibits; i++)
{
var eb = new Exhibit();
eb.Title = "Exhibit - " + cntExhibits;
eb.TimeUnit = "Ga";
eb.Day = 0;
eb.Month = 0;
eb.Year = timeline.FromYear + (timeline.ToYear - timeline.FromYear) / 2;
cntExhibits++;
timeline.Exhibits.Add(eb);
}
if (level <= depth - 1)
{
for (var i = 0; i < numChildTimelines; i++)
{
if (cntTimelines < maxNumTimelines)
{
var child = new Timeline();
child.Title = "Timeline - " + cntTimelines;
child.FromTimeUnit = "Ga";
child.FromDay = 0;
child.FromMonth = 0;
child.FromYear = 13 - (level + 1);
child.ToTimeUnit = "Ga";
child.ToDay = 0;
child.ToMonth = 0;
child.ToYear = 0;
child.Height = 10;
cntTimelines++;
timeline.ChildTimelines.Add(child);
_GenerateTestingData(child, level + 1, depth, maxNumTimelines, numChildTimelines, numChildExhibits, ref cntTimelines, ref cntExhibits);
}
}
}
}
public static void GenerateProperty(Timeline timeline, string direction)
{
if (direction.ToLower() == "from")
{
var fromTimeUnit = timeline.FromTimeUnit.ToLower();
if (fromTimeUnit == "ga")
{
timeline.left = -timeline.FromYear.Value * 1000000000;
}
else if (fromTimeUnit == "ma")
{
timeline.left = -timeline.FromYear.Value * 1000000;
}
else if (fromTimeUnit == "ka")
{
timeline.left = -timeline.FromYear.Value * 1000;
}
else if (fromTimeUnit == "bce")
{
timeline.left = getCoordinateFromDMY(
-timeline.FromYear.Value,
!timeline.FromMonth.HasValue ? 0 : Math.Min(11, Math.Max(0, timeline.FromMonth.Value - 1)),
!timeline.FromDay.HasValue ? 1 : timeline.FromDay.Value
);
}
else if (fromTimeUnit == "ce")
{
timeline.left = getCoordinateFromDMY(
timeline.FromYear.Value,
!timeline.FromMonth.HasValue ? 0 : Math.Min(11, Math.Max(0, timeline.FromMonth.Value - 1)),
!timeline.FromDay.HasValue ? 1 : timeline.FromDay.Value
);
}
}
else if (direction.ToLower() == "to")
{
var toTimeUnit = timeline.ToTimeUnit.ToLower();
if (toTimeUnit == "ga")
{
timeline.right = -timeline.ToYear.Value * 1000000000;
}
else if (toTimeUnit == "ma")
{
timeline.right = -timeline.ToYear.Value * 1000000;
}
else if (toTimeUnit == "ka")
{
timeline.right = -timeline.ToYear.Value * 1000;
}
else if (toTimeUnit == "bce")
{
timeline.right = getCoordinateFromDMY(
-timeline.ToYear.Value,
!timeline.ToMonth.HasValue ? 0 : Math.Min(11, Math.Max(0, timeline.ToMonth.Value - 1)),
!timeline.ToDay.HasValue ? 1 : timeline.ToDay.Value
);
}
else if (toTimeUnit == "ce")
{
timeline.right = getCoordinateFromDMY(
timeline.ToYear.Value,
!timeline.ToMonth.HasValue ? 0 : Math.Min(11, Math.Max(0, timeline.ToMonth.Value - 1)),
!timeline.ToDay.HasValue ? 1 : timeline.ToDay.Value
);
}
}
}
public static Timeline GenerateTestingData(int maxNumTimelines, int numChildTimelines, int numChildExhibits)
{
if (maxNumTimelines > 0)
{
var root = new Timeline();
root.Title = "Cosmos";
root.FromTimeUnit = "Ga";
root.FromDay = 0;
root.FromMonth = 0;
root.FromYear = 13;
root.ToTimeUnit = "Ga";
root.ToDay = 0;
root.ToMonth = 0;
root.ToYear = 0;
root.Height = 80;
int cntTimelines = 1;
int cntExhibits = 0;
int depth = (int)Math.Ceiling(Math.Log(maxNumTimelines, numChildTimelines));
_GenerateTestingData(root, 0, depth, maxNumTimelines, numChildTimelines, numChildExhibits, ref cntTimelines, ref cntExhibits);
Debug.WriteLine("cntTimelines = " + cntTimelines);
Debug.WriteLine("cntExhibits = " + cntExhibits);
return root;
}
return null;
}
