コード例 #1
0
        /// <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);
        }
コード例 #2
0
        /// <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
            }));
        }
コード例 #3
0
        /// <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();
        }