/// <summary>Gets string array of the months from a datatable</summary> /// <param name="table">The datatable to seach</param> /// <param name="firstDate">The start of the date range to search</param> /// <param name="lastDate">The end of the date range to search</param> /// <returns>A String array containing the distinct month names (abbreviated), in order, ie, "Jan, Feb, Mar"</returns> static public string[] GetDistinctMonthsasStrings(DataTable table, DateTime firstDate, DateTime lastDate) { //where row.Field<DateTime>(colName) >= firstDate var result = (from row in table.AsEnumerable() where (DataTableUtilities.GetDateFromRow(row) >= firstDate && DataTableUtilities.GetDateFromRow(row) <= lastDate) orderby DataTableUtilities.GetDateFromRow(row) select new { Month = DataTableUtilities.GetDateFromRow(row).Month }).Distinct(); List <string> rValues = new List <string>(); foreach (var row in result) { rValues.Add(CultureInfo.CurrentCulture.DateTimeFormat.GetAbbreviatedMonthName(row.Month)); } return(rValues.ToArray()); }
/// <summary>Convert a database to a string.</summary> public static string TableToString(IDbConnection connection, string tableName, IEnumerable <string> fieldNames = null) { string sql = "SELECT "; if (fieldNames == null) { sql += "*"; } else { bool first = true; foreach (string fieldName in fieldNames) { if (first) { first = false; } else { sql += ","; } sql += fieldName; } } sql += " FROM " + tableName; DataTable data = null; using (var command = connection.CreateCommand()) { command.CommandText = sql; var reader = command.ExecuteReader(); data = new DataTable(); data.Load(reader); } System.IO.StringWriter writer = new System.IO.StringWriter(); DataTableUtilities.DataTableToText(data, 0, ",", true, writer); return(writer.ToString()); }
/// <summary> /// Move all data from the specified table in destination to source. /// </summary> /// <param name="source">The source database.</param> /// <param name="destination">The destination database.</param> /// <param name="tableName">The name of the table to merge.</param> /// <param name="oldIDNewIDMapping">A mapping from source IDs to destination IDs.</param> private static void MergeTable(IDatabaseConnection source, IDatabaseConnection destination, string tableName, Dictionary <int, int> oldIDNewIDMapping) { var sourceData = source.ExecuteQuery("SELECT * FROM " + tableName); DataTable destinationData; if (destination.GetTableNames().Contains(tableName)) { destinationData = destination.ExecuteQuery("SELECT * FROM " + tableName); } else { // Need to create the table. var colNames = sourceData.Columns.Cast <DataColumn>().Select(col => col.ColumnName).ToList(); var colTypes = sourceData.Columns.Cast <DataColumn>().Select(col => source.GetDBDataTypeName(col.DataType)).ToList(); destination.CreateTable(tableName, colNames, colTypes); } var columnNames = DataTableUtilities.GetColumnNames(sourceData).ToList(); foreach (DataRow simulationRow in sourceData.Rows) { if (columnNames.Contains("SimulationID")) { var oldID = Convert.ToInt32(simulationRow["SimulationID"]); if (oldIDNewIDMapping.TryGetValue(oldID, out int newID)) { // Change the ID to new ID simulationRow["SimulationID"] = newID; } } destination.InsertRows(tableName, columnNames, new List <object[]>() { simulationRow.ItemArray }); } }