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; }