/// <summary>
/// Infer the data type for each value in the specified <paramref name="column" /> containing <see cref="String" />s.
/// </summary>
/// <param name="column">The column containing <see cref="String" />s for which to infer the datatype.</param>
/// <param name="toMixed">If false, each value converted from string must be of the same type. If true, the column DataType becomes object.</param>
/// <exception cref="ArgumentException">The DataType of the specified <paramref name="column" /> is not <see cref="String" />.</exception>
/// <exception cref="ArgumentNullException">The specified <paramref name="column" /> is <c>null</c>.</exception>
/// <exception cref="InvalidOperationException"><paramref name="toMixed" /> is false and the specified <paramref name="column" /> contains multiple types when converting from <see cref="String" />.</exception>
public static void ConvertFromString(this DataColumn column, bool toMixed)
{
if (!column.DataType.Equals(typeof(string)))
{
throw new ArgumentException("Column DataType is not String", nameof(column));
}
Convert(column, value => value is DBNull ? DBNull.Value : ToOrFromString.ConvertValueFromString((string)value), toMixed);
}
/// <summary>
/// Convert <see cref="Object" /> <paramref name="value" /> to a string that can be safely output in a CSV file.
/// </summary>
/// <param name="value">The object to convert into it's equivalent/safe representation in CSV.</param>
/// <param name="separator">The field separator that will be used in the CSV, so that <paramref name="value" /> can be appropriately escaped.</param>
/// <returns><paramref name="value" /> represented in CSV.</returns>
public static string GetValueForCSV(object value, string separator, bool replaceLineBreaks = false)
{
const string quote = "\"";
return(ToOrFromString.GetValueBase(value, string.Empty, string.Empty, s => {
if (replaceLineBreaks)
{
s = s.Replace(Environment.NewLine, " ").Replace("\r", " ").Replace("\n", " "); // remove line breaks, replace with spaces
}
// the following text qualification rules and quote doubling are based on recommendations in RFC 4180
var qualify = s.Contains(quote) || s.EndsWith(" ") || s.EndsWith("\t") || s.StartsWith(" ") || s.StartsWith("\t") || (s.IndexOf("\n", StringComparison.InvariantCultureIgnoreCase) > -1) || (s.IndexOf(separator, StringComparison.InvariantCultureIgnoreCase) > -1); // qualify the text in quotes if it contains a quote, starts or ends in whitespace, or contains the separator or a newline
return qualify ? (quote + s.Replace(quote, quote + quote) + quote) : s; // to escape a quote, we double it up
}));
}
/// <summary>
/// Create a SQL "insert" statement for the data in the specified <paramref name="dt" />.
/// </summary>
/// <param name="dt">The <see cref="DataTable" />s whose data will be written as a SQL "insert" statement.</param>
/// <param name="tableName">The name of the table to use in the insert statement.</param>
/// <param name="createTable">Whether or not a "CREATE TABLE"/"DECLARE @"... "TABLE" statement should also be written.</param>
/// <param name="writer">The <see cref="TextWriter" /> to output the insert statement(s) to.</param>
public static void TableToSQLInsert(this DataTable dt, string tableName, bool createTable, TextWriter writer)
{
Func <string, string> escapeIfNecessary = name => {
// TODO: split by dot and then check each substring for square brackets, to allow for cross-database create table
if (!name.StartsWith(@"[")) // TODO: could also check if contains invalid characters, rather than always escaping...
{
return(@"[" + name + @"]");
}
else
{
return(name);
}
};
const string tableVariableChar = @"@";
if (createTable)
{
Func <DataColumn, string> ColumnDataTypeToSQL = col => {
var underlyingType = Nullable.GetUnderlyingType(col.DataType);
var rows = col.Table.Rows.OfType <DataRow>();
var nullable = (underlyingType != null) || col.AllowDBNull || rows.Any(row => row[col] == null);
Func <Func <object, int>, string> getMaxLength = getLength => {
var max = 1;
if (rows.Any())
{
max = Math.Max(max, rows.Where(dr => dr[col] != null && !(dr[col] is DBNull)).Max(dr => getLength(dr[col])));
}
return(@"(" + max.ToString() + @")");
};
string sql;
sql = ((underlyingType ?? col.DataType).Name.ToLowerInvariant());
switch (sql)
{
case "byte[]":
sql = @"varbinary" + getMaxLength(obj => ((byte[])obj).Length);
break;
case "guid":
sql = @"uniqueidentifier";
break;
case "int32":
sql = @"int";
break;
case "int64":
sql = @"bigint";
break;
case "boolean":
sql = @"bit";
break;
case "string":
sql = @"nvarchar" + getMaxLength(obj => ((string)obj).Length);
break;
}
sql += ((nullable) ? string.Empty : @" not") + @" null";
return(sql);
};
writer.WriteLine((tableName.StartsWith(tableVariableChar) ? @"DECLARE " + tableName : @"CREATE TABLE " + escapeIfNecessary(tableName)) + (tableName.StartsWith(tableVariableChar) ? @" TABLE " : string.Empty) + @" (" +
string.Join(
@", ",
dt.Columns.OfType <DataColumn>().Select(col => escapeIfNecessary(col.ColumnName) + @" " + ColumnDataTypeToSQL(col))
) + @")"
);
}
writer.WriteLine(@"insert into " + (tableName.StartsWith(tableVariableChar) ? tableName : escapeIfNecessary(tableName)) + @" (" +
string.Join(@", ", dt.Columns.OfType <DataColumn>().Select(
col => escapeIfNecessary(col.ColumnName)
)) + @") values");
var lines = dt.Rows.OfType <DataRow>().Select(
row => @"(" + string.Join(@", ", dt.Columns.OfType <DataColumn>().Select(
col => ToOrFromString.GetValueForSQL(row[col])
)) + @")");
bool first = true;
foreach (var line in lines)
{
if (first)
{
first = false;
writer.Write(@" ");
}
else
{
writer.Write(@",");
}
writer.WriteLine(line);
}
writer.Flush();
}
