Ejemplo n.º 1
0
        /// <summary>
        /// Main worker method
        /// </summary>

        static void DoWork()
        {
            Dictionary <string, ColHeader> Cols = null;
            string ServerName   = AppSettingsImpl.Server.Value;
            string DatabaseName = AppSettingsImpl.Db.Value;
            string TableName    = AppSettingsImpl.Tbl.Value;
            string FileName     = AppSettingsImpl.File.Value;
            bool   Fixed        = AppSettingsImpl.Type.Value.ToLower() == "fixed";
            int    MaxWidth     = AppSettingsImpl.MaxWidth.Initialized ? AppSettingsImpl.MaxWidth.Value : 0;

            DateTime Start = DateTime.Now;

            if (AppSettingsImpl.Type.Value.ToLower() == "fixed")
            {
                Log.InformationMessage("Computing column widths");
                bool FitToHeader = ShouldFitDataToHeader();                                                                  // if true, widen data. If false, truncat headers
                Cols = ServerUtils.GetColInfo(ServerName, DatabaseName, TableName, ShouldFitDataToHeader(), true, MaxWidth); // column names will be lower case
                if (!FitToHeader)
                {
                    Log.InformationMessage("Narrowing headers");
                    ColumnUniquer.NarrowHeaders(Cols); // modifies Cols
                }
            }
            else
            {
                Cols = ServerUtils.GetColInfo(ServerName, DatabaseName, TableName, false, false, MaxWidth);
            }
            string Delimiter = AppSettingsImpl.Delimiter.Value.Xlat(new string[] { "pipe", "comma", "tab" }, new string[] { "|", ",", "\t" });

            Log.InformationMessage("Exporting data");

            int  Pad               = AppSettingsImpl.Pad.Value;
            bool Append            = AppSettingsImpl.Append.Value;
            bool ShouldWriteHeader = AppSettingsImpl.Header.Initialized && !Append;
            int  MaxRows           = AppSettingsImpl.MaxRows.Value;
            bool Quote             = AppSettingsImpl.Quote.Value;
            int  RecordsWritten;

            bool FoundDelimiterInData = ServerUtils.DoExport(ServerName, DatabaseName, TableName, FileName, Cols, Fixed,
                                                             Delimiter, Pad, Append, ShouldWriteHeader, MaxRows, Quote, out RecordsWritten);

            if (!Fixed && FoundDelimiterInData && !Quote)
            {
                Log.InformationMessage("Note: The specified delimiter was found in the data, but the -quote option was not specified. " +
                                       "The generated file may not be able to be successfully parsed.");
            }
            if (AppSettingsImpl.Type.Value == "fixed" && AppSettingsImpl.Widths.Value)
            {
                List <ColHeader> Hdrs    = ColHeader.ToList(Cols);
                int[]            Lengths = new int[Hdrs.Count];
                for (int i = 0; i < Hdrs.Count; ++i)
                {
                    Lengths[i] = Hdrs[i].FieldWidth;
                }
                Log.InformationMessage("Exported field widths: {0}", string.Join(",", Lengths));
            }
            Log.InformationMessage("Export completed -- Records written: {0} Elapsed time (HH:MM:SS.Milli): {1}", RecordsWritten, DateTime.Now - Start);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Computes column widths. For varchar max fields, issues a select statement to determine the widths. Can be time-consuming for
        /// a large table. Otherwise uses metadata to compute the widths
        /// </summary>
        /// <param name="ServerName">The server name</param>
        /// <param name="DatabaseName">The database name</param>
        /// <param name="TableName">the table name</param>
        /// <param name="ServerCols">A list of table columns with metadata</param>
        /// <param name="MaxWidth">The max width for output columns. If zero, then don't impose a maximum.</param>
        /// <returns>A dictionary keyed by column name containing corresponding column metadata</returns>

        private static Dictionary <string, ColHeader> ComputeColWidths(string ServerName, string DatabaseName, string TableName,
                                                                       List <Column> ServerCols, int MaxWidth)
        {
            Dictionary <Column, int> VariableCols    = new Dictionary <Column, int> ();
            Dictionary <Column, int> NonVariableCols = new Dictionary <Column, int>();

            foreach (Column Col in ServerCols)
            {
                if (MaxWidth == 0 && Col.DataType.Name.ToLower().In("varchar", "nvarchar") && Col.DataType.MaximumLength == -1)
                {
                    // if there's a max width then we don't do varchar max calculation from the DB
                    VariableCols.Add(Col, 0);
                }
                else if (MaxWidth == 0 && Col.DataType.Name.ToLower().In("varchar", "nvarchar", "char", "nchar") && AppSettingsImpl.Trim)
                {
                    VariableCols.Add(Col, 0);
                }
                else
                {
                    NonVariableCols.Add(Col, 0);
                }
            }
            VariableCols    = ComputeCharColWidths(ServerName, DatabaseName, TableName, VariableCols);
            NonVariableCols = ComputeNonVarcharMaxColWidths(ServerName, DatabaseName, TableName, NonVariableCols, MaxWidth);

            // zip everything back together
            Dictionary <string, ColHeader> Cols = new Dictionary <string, ColHeader>();
            int OrdinalPosition = 0;

            foreach (Column Col in ServerCols) // now zip them back together
            {
                int       ColWidth = VariableCols.ContainsKey(Col) ? VariableCols[Col] : NonVariableCols[Col];
                ColHeader Hdr      = new ColHeader(Col.Name.ToLower(), ColWidth, Col.DataType, OrdinalPosition++);
                Cols.Add(Col.Name.ToLower(), Hdr);
            }
            return(Cols);
        }
Ejemplo n.º 3
0
        /// <summary>
        /// Exports data from a database table to a file
        /// </summary>
        /// <param name="ServerName">The server name</param>
        /// <param name="DatabaseName">The database name</param>
        /// <param name="TableName">the table name</param>
        /// <param name="FileName">The file to export to</param>
        /// <param name="HeadersDict">the Columns to export</param>
        /// <param name="Fixed">true if field-length export</param>
        /// <param name="Delimiter">Delimiter, if not fixed length</param>
        /// <param name="Pad">The number of characters of padding to add to each column if fixed. Zero if no padding</param>
        /// <param name="Append">True to append to the output file. Otherwise re-create the output file</param>
        /// <param name="ShouldWriteHeader">True to write a header</param>
        /// <param name="MaxRows">Max rows (excl. header) to write</param>
        /// <param name="Quote">True to quote fields if fields contain a delimiter character</param>
        /// <returns>true if delimiters were found in the fields, and quoting was not specified</returns>

        public static bool DoExport(string ServerName, string DatabaseName, string TableName, string FileName,
                                    Dictionary <string, ColHeader> HeadersDict, bool Fixed, string Delimiter, int Pad, bool Append, bool ShouldWriteHeader, int MaxRows, bool Quote,
                                    out int RecordsWritten)
        {
            List <ColHeader> ColHeaders             = ColHeader.ToList(HeadersDict);
            string           Sql                    = BuildSelect(TableName, ColHeaders);
            bool             FoundDelimitersInField = false;
            string           Padding                = Pad != 0 ? new string(' ', Pad) : string.Empty;
            int RecordCount = 0;

            using (SqlConnection Cnct = GetSqlConnection(ServerName, DatabaseName))
                using (StreamWriter Writer = new StreamWriter(FileName, Append))
                {
                    SqlCommand command = new SqlCommand(Sql, Cnct)
                    {
                        CommandTimeout = GetSQLTimeout()
                    };
                    command.Connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (RecordCount == 0 && ShouldWriteHeader)
                            {
                                Writer.WriteLine(BuildHeader(ColHeaders, Fixed, Delimiter, Pad));
                            }
                            foreach (ColHeader Hdr in ColHeaders)
                            {
                                if (!Fixed && Hdr.OrdinalPosition > 0)
                                {
                                    Writer.Write(Delimiter);
                                }
                                string Fld = Hdr.ValueFrom(reader, Fixed);
                                if (!Fixed && (Fld.Contains(Delimiter) || Fld.Contains(ONE_DBL_QUOTE)))
                                {
                                    // if the field contains a delimiter, quote it. If it contains any quotes, also
                                    // quote it so the reader can strip the quotes and get the original contents back
                                    if (Quote)
                                    {
                                        Fld = ONE_DBL_QUOTE + Fld.Replace(ONE_DBL_QUOTE, TWO_DBL_QUOTES) + ONE_DBL_QUOTE;
                                    }
                                    FoundDelimitersInField = true;
                                }
                                Writer.Write(Fld);
                                if (Pad != 0)
                                {
                                    Writer.Write(Padding);
                                }
                            }
                            Writer.WriteLine();
                            if (++RecordCount >= MaxRows)
                            {
                                command.Cancel();
                                break;
                            }
                            if (RecordCount % 10000 == 0)
                            {
                                Log.InformationMessage("Records: {0}", RecordCount);
                            }
                        } // while (reader.Read())
                    }     // using SqlDataReader
                }// using SqlConnection & StreamWriter
            RecordsWritten = RecordCount;
            return(FoundDelimitersInField);
        }