Esempio n. 1
0
        public static SqlTable CsvToSqlTable(TextReader textReader, ISqlWriter sqlWriter, bool hasHeader = true, string delimiter = ",", int count = -1)
        {
            var config = new CsvHelper.Configuration.CsvConfiguration();
            config.Delimiter = delimiter;
            config.HasHeaderRecord = hasHeader;

            var reader = new CsvHelper.CsvReader(textReader, config);
            var rows = reader.GetRecords<object>().ToList();

            textReader.Close();
            SqlTable sqlTable = null;
            if (rows.Count > 0)
            {
                sqlTable = new SqlTable();
                sqlTable.Headers = new List<SqlColumnTypeDefinition>();
                sqlTable.Rows = new List<SqlRow>();
                var i = 0;
                foreach (var row in rows)
                {
                    if (i == count)
                        break;

                    i++;

                    var columns = (System.Dynamic.ExpandoObject)row;
                    var sqlRow = new SqlRow();
                    sqlTable.Rows.Add(sqlRow);
                    var countUnkown = 1;
                    foreach (var col in columns)
                    {
                        // set rows
                        var sqlColumn = new SqlColumn();
                        sqlColumn.Type = sqlWriter.SqlTypeToCSharpType(col.Value);
                        sqlColumn.Value = col.Value;
                        sqlRow.Columns.Add(sqlColumn);

                        // set headers
                        var key = col.Key;
                        if (string.IsNullOrWhiteSpace(key))
                        {
                            key = "Unkown" + countUnkown;
                            countUnkown++;
                        }

                        SqlColumnTypeDefinition sqlColumnTypeDefinition = sqlTable.Headers.FirstOrDefault(f => f.Name == key);
                        if (sqlColumnTypeDefinition == null)
                        {
                            sqlColumnTypeDefinition = new SqlColumnTypeDefinition();
                            sqlColumnTypeDefinition.Name = col.Key;
                            sqlColumnTypeDefinition.Type = sqlColumn.Type;
                            sqlTable.Headers.Add(sqlColumnTypeDefinition);
                        }

                        sqlColumn.ColumnTypeDefinition = sqlColumnTypeDefinition;

                        // override type of header to STRING because exists a string in the column
                        if (sqlColumnTypeDefinition.Type != typeof(string) && sqlColumn.Type == typeof(string))
                            sqlColumnTypeDefinition.Type = typeof(string);
                    }
                }

                // Fix type to STRING if all values is 'NULL'
                foreach (var sqlHeader in sqlTable.Headers)
                {
                    if (sqlHeader.Type == null)
                        sqlHeader.Type = typeof(string);

                    sqlHeader.TypeFormatted = sqlWriter.CSharpTypeToSqlType(sqlHeader.Type);
                }
            }

            return sqlTable;
        }
Esempio n. 2
0
        public static SqlTable CsvToSqlTable(TextReader textReader, ISqlWriter sqlWriter, bool hasHeader = true, string delimiter = ",", int count = -1)
        {
            var config = new CsvHelper.Configuration.CsvConfiguration();

            config.Delimiter       = delimiter;
            config.HasHeaderRecord = hasHeader;

            var reader = new CsvHelper.CsvReader(textReader, config);
            var rows   = reader.GetRecords <object>().ToList();

            textReader.Close();
            SqlTable sqlTable = null;

            if (rows.Count > 0)
            {
                sqlTable         = new SqlTable();
                sqlTable.Headers = new List <SqlColumnTypeDefinition>();
                sqlTable.Rows    = new List <SqlRow>();
                var i = 0;
                foreach (var row in rows)
                {
                    if (i == count)
                    {
                        break;
                    }

                    i++;

                    var columns = (System.Dynamic.ExpandoObject)row;
                    var sqlRow  = new SqlRow();
                    sqlTable.Rows.Add(sqlRow);
                    var countUnkown = 1;
                    foreach (var col in columns)
                    {
                        // set rows
                        var sqlColumn = new SqlColumn();
                        sqlColumn.Type  = sqlWriter.SqlTypeToCSharpType(col.Value);
                        sqlColumn.Value = col.Value;
                        sqlRow.Columns.Add(sqlColumn);

                        // set headers
                        var key = col.Key;
                        if (string.IsNullOrWhiteSpace(key))
                        {
                            key = "Unkown" + countUnkown;
                            countUnkown++;
                        }

                        SqlColumnTypeDefinition sqlColumnTypeDefinition = sqlTable.Headers.FirstOrDefault(f => f.Name == key);
                        if (sqlColumnTypeDefinition == null)
                        {
                            sqlColumnTypeDefinition      = new SqlColumnTypeDefinition();
                            sqlColumnTypeDefinition.Name = col.Key;
                            sqlColumnTypeDefinition.Type = sqlColumn.Type;
                            sqlTable.Headers.Add(sqlColumnTypeDefinition);
                        }

                        sqlColumn.ColumnTypeDefinition = sqlColumnTypeDefinition;

                        // override type of header to STRING because exists a string in the column
                        if (sqlColumnTypeDefinition.Type != typeof(string) && sqlColumn.Type == typeof(string))
                        {
                            sqlColumnTypeDefinition.Type = typeof(string);
                        }
                    }
                }

                // Fix type to STRING if all values is 'NULL'
                foreach (var sqlHeader in sqlTable.Headers)
                {
                    if (sqlHeader.Type == null)
                    {
                        sqlHeader.Type = typeof(string);
                    }

                    sqlHeader.TypeFormatted = sqlWriter.CSharpTypeToSqlType(sqlHeader.Type);
                }
            }

            return(sqlTable);
        }