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