Beispiel #1
0
        private static void ProcessTable(SqlConnection sqlConnection, Table table, Project project)
        {
            var outputFolderPath = string.IsNullOrEmpty(table.Output) ? project.OutputFolder.Value : table.Output;

            if (!Directory.Exists(outputFolderPath))
            {
                Directory.CreateDirectory(outputFolderPath);
            }

            string fullFileName = Path.Combine(outputFolderPath, table.Csv);
            int records = 0;

            try
            {
                List<Tuple<string, string>> schema = GetTableSchema(sqlConnection, table.Name);
                if (schema.Count == 0)
                {
                    Log.Warning("No columns was found in the '{0}' table to export into a csv file.", table.Name);
                    return;
                }

                if (File.Exists(fullFileName))
                {
                    File.Delete(fullFileName);
                }

                using (var streamWriter = new StreamWriter(new FileStream(fullFileName, FileMode.OpenOrCreate)))
                {
                    var csvWriter = new CsvWriter(streamWriter);
                    // Write Header
                    foreach (var tuple in schema)
                    {
                        csvWriter.WriteField(tuple.Item1);
                    }
                    csvWriter.NextRecord();

                    // Write records
                    var query = ConstructRecordQuery(table.Name, schema, project.MaximumRowsInTable.Value);
                    using (var command = new SqlCommand(query) {Connection = sqlConnection})
                    {
                        using (var dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                for (int i = 0; i < schema.Count; i++)
                                {
                                    object value = dataReader.GetValue(i);

                                    if (value is string)
                                    {
                                        value = ((string) value).Trim(); // Remove all white spaces
                                    }

                                    csvWriter.WriteField(value);
                                }

                                records++;
                                csvWriter.NextRecord();
                            }
                        }
                    }
                }

                Log.Info("{0} records of '{1}' table successfully exported to {2}.", records, table.Name, fullFileName);
            }
            catch (Exception ex)
            {
                Log.Error("{0} export failed because of exception: {1}", table.Name, ex.Message);
            }
        }
Beispiel #2
0
        private static List<Table> GetAvailableTables(SqlConnection sqlConnection, string outputFolder)
        {
            const string CommandText = "SELECT '['+TABLE_SCHEMA+'].['+ TABLE_NAME + ']' FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME";

            var result = new List<Table>();

            using (var schemaCommand = new SqlCommand(CommandText) {Connection = sqlConnection, CommandTimeout = 300})
            {
                using (var schemaReader = schemaCommand.ExecuteReader())
                {
                    while (schemaReader.Read())
                    {
                        var table = new Table();
                        table.Name = schemaReader.GetString(0);
                        table.Csv = ExtractTableName(table.Name) + ".csv";
                        table.Output = outputFolder;
                        result.Add(table);
                    }
                }
            }

            return result;
        }