Exemple #1
0
        public static void FixedLengthExtractFileTest(MainForm frm)
        {
            PFSQLServer sqlserv          = new PFSQLServer();
            string      connectionString = string.Empty;
            string      sqlQuery         = "select GeographyKey, City, StateProvinceName as StateName, EnglishCountryRegionName as CountryName, PostalCode, SalesTerritoryKey as TerritoryKey from dbo.DimGeography;";
            PFTextFile  extractFile      = new PFTextFile(@"c:\temp\FixedLengthFileTest.txt", PFFileOpenOperation.OpenFileForWrite);
            Stopwatch   watch            = new Stopwatch();

            try
            {
                watch.Start();

                sqlserv.ServerName            = "PROFASTWS1";
                sqlserv.DatabaseName          = "AdventureWorksDW2008R2";
                sqlserv.UseIntegratedSecurity = true;
                sqlserv.ApplicationName       = "TextExtractTest";
                sqlserv.WorkstationId         = Environment.MachineName;

                connectionString = sqlserv.ConnectionString;

                _msg.Length = 0;
                _msg.Append("Connection string is: \r\n");
                _msg.Append(connectionString);
                Program._messageLog.WriteLine(_msg.ToString());


                sqlserv.OpenConnection();


                sqlserv.SQLQuery    = sqlQuery;
                sqlserv.CommandType = CommandType.Text;

                SqlDataReader rdr = (SqlDataReader)sqlserv.RunQueryDataReader();

                DataTable             schemaTable = rdr.GetSchemaTable();
                PFFixedLengthDataLine line        = new PFFixedLengthDataLine(schemaTable.Rows.Count);
                line.UseLineTerminator   = frm.chkFixedLengthCrLf.Checked;
                line.AllowDataTruncation = frm.chkAllowDataTruncation.Checked;

                //foreach (DataRow myField in schemaTable.Rows)
                //{
                //    //For each property of the field...
                //    foreach (DataColumn myProperty in schemaTable.Columns)
                //    {
                //        //Display the field name and value.
                //        Program._messageLog.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString() + " - " );
                //    }
                //}
                //Program._messageLog.WriteLine("--------------------------------");

                //Program._messageLog.WriteLine(schemaTable.Rows[0]["DataType"].ToString());
                //Program._messageLog.WriteLine(schemaTable.Rows[1].ItemArray[12].ToString());
                //Program._messageLog.WriteLine(schemaTable.Rows[2].ItemArray[12].ToString());
                //Program._messageLog.WriteLine(schemaTable.Rows[3].ItemArray[12].ToString());
                //Program._messageLog.WriteLine(schemaTable.Rows[4].ItemArray[12].ToString());
                //Program._messageLog.WriteLine(schemaTable.Rows[5].ItemArray[12].ToString());

                ////DataRow row = schemaTable.Rows[0];
                ////Program._messageLog.WriteLine(row["ColumnName"] + " is " + row["DataType"]);

                //Program._messageLog.WriteLine("--------------------------------");

                //for (int rowInx = 0; rowInx < schemaTable.Rows.Count; rowInx++)
                //{
                //    DataRow row = schemaTable.Rows[rowInx];
                //    Program._messageLog.WriteLine(row["ColumnName"] + " is " + row["DataType"]);
                //}

                //Program._messageLog.WriteLine("--------------------------------");

                //line.SetColumnDefinition(0, "GeographyKey", 12,PFDataAlign.RightJustify);
                //line.SetColumnDefinition(1, "City", 30, PFDataAlign.LeftJustify);
                //line.SetColumnDefinition(2, "StateName", 50, PFDataAlign.LeftJustify);
                //line.SetColumnDefinition(3, "CountryName", 50, PFDataAlign.LeftJustify);
                //line.SetColumnDefinition(4, "PostalCode", 15, PFDataAlign.LeftJustify);
                //line.SetColumnDefinition(5, "TerritoryKey", 12, PFDataAlign.RightJustify);

                //foreach (DataRow myField in schemaTable.Rows)
                //{
                //    //For each property of the field...
                //    foreach (DataColumn myProperty in schemaTable.Columns)
                //    {
                //    //Display the field name and value.
                //        Program._messageLog.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString());
                //    }
                //}


                for (int rowInx = 0; rowInx < schemaTable.Rows.Count; rowInx++)
                {
                    DataRow     row           = schemaTable.Rows[rowInx];
                    string      colName       = row["ColumnName"].ToString();
                    System.Type colType       = (System.Type)row["DataType"];
                    bool        typeIsNumeric = PFFixedLengthDataLine.DataTypeIsNumeric(colType);
                    int         colLen        = PFFixedLengthDataLine.GetNumericTypeMaxExtractLength(colType);
                    if (colLen < 1)
                    {
                        colLen = (int)row["ColumnSize"];
                    }
                    if (colName.Length > colLen)
                    {
                        colLen = colName.Length;
                    }
                    PFDataAlign dataAlignment = typeIsNumeric ? PFDataAlign.RightJustify : PFDataAlign.LeftJustify;

                    line.SetColumnDefinition(rowInx, colName, colLen, dataAlignment);
                    Program._messageLog.WriteLine(colName + ", " + colType.FullName + ", " + colLen.ToString() + ", " + dataAlignment.ToString());
                }
                extractFile.WriteData(line.OutputColumnNames());

                int numRows = 0;
                if (rdr.HasRows)
                {
                    int colInx    = 0;
                    int maxColInx = -1;
                    while (rdr.Read())
                    {
                        numRows++;
                        maxColInx = rdr.FieldCount - 1;
                        for (colInx = 0; colInx <= maxColInx; colInx++)
                        {
                            line.SetColumnData(colInx, rdr[colInx].ToString());
                        }
                        extractFile.WriteData(line.OutputColumnData());
                    }
                }

                _msg.Length = 0;
                _msg.Append("Number of data rows written:   ");
                _msg.Append(numRows.ToString("#,##0"));
                _msg.Append("\r\n");
                _msg.Append("Number of header rows written: 1");
                Program._messageLog.WriteLine(_msg.ToString());
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                sqlserv.CloseConnection();
                sqlserv = null;
                if (extractFile != null)
                {
                    extractFile.CloseFile();
                    extractFile = null;
                }
                watch.Stop();
                _msg.Length = 0;
                _msg.Append("Elapsed time: ");
                _msg.Append(watch.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }//end test
Exemple #2
0
        }//end DelimitedLineTextFileTests

        public static void FixedLengthLineTextFileTests(int numRowsToOutput, bool useCrLfLineTerminator, bool allowDataTruncation)
        {
            PFTextFile            textFile = new PFTextFile(@"c:\temp\FixedLengthText.txt", PFFileOpenOperation.OpenFileForWrite);
            PFFixedLengthDataLine line     = new PFFixedLengthDataLine(5);
            int    num = 0;
            string str = string.Empty;

            char[] tst = { 'a', 'b', 'c', 'd', 'e' };

            try
            {
                line.UseLineTerminator   = useCrLfLineTerminator;
                line.AllowDataTruncation = allowDataTruncation;
                line.SetColumnDefinition(0, "Column01", 10);
                line.SetColumnDefinition(1, "SecondColumnHeadingIsThis", 15);
                line.SetColumnDefinition(2, "Col3HDR", 5);
                line.SetColumnDefinition(3, "Col4", 35);
                line.SetColumnDefinition(4, "FifthColumn", 25, PFDataAlign.RightJustify);

                textFile.WriteData(line.OutputColumnNames());

                for (int rowNum = 0; rowNum < numRowsToOutput; rowNum++)
                {
                    for (int inx = 0; inx < 5; inx++)
                    {
                        num = line.ColumnDefinitions.ColumnDefinition[inx].ColumnLength;
                        if (inx == 4)
                        {
                            num = num - 7;
                        }
                        //str = new string(tst[inx],num);
                        str = PFTextProcessor.RepeatChar(tst[inx], num);
                        line.ColumnData.ColumnDataValue[inx].Data = str;
                        str = null;
                    }
                    textFile.WriteData(line.OutputColumnData());
                }

                textFile.CloseFile();

                line.SaveToXmlFile(@"c:\temp\FixedLengthText.xml");


                //read textfile
                textFile.OpenFile(@"c:\temp\FixedLengthText.txt", PFFileOpenOperation.OpenFileToRead);
                int    lineLen = line.LineLength;
                string input   = textFile.ReadData(lineLen);
                line.VerifyColumnNames(input);
                Program._messageLog.WriteLine(line.ColumnDefinitions.ToString());
                input = textFile.ReadData(lineLen);
                int lineNo = 0;
                while (input != null)
                {
                    lineNo++;
                    line.ParseData(input);
                    Program._messageLog.WriteLine("LINE " + lineNo.ToString() + ": " + line.ColumnData.ToString());
                    input = textFile.ReadData(lineLen);
                }

                textFile.CloseFile();


                PFTextFile            textFile2 = new PFTextFile(@"c:\temp\FixedLengthText2.txt", PFFileOpenOperation.OpenFileForWrite);
                PFFixedLengthDataLine line2     = PFFixedLengthDataLine.LoadFromXmlFile(@"c:\temp\FixedLengthText.xml");

                Program._messageLog.WriteLine(line2.ToXmlString());

                line2.UseLineTerminator = useCrLfLineTerminator;
                line2.SetColumnDefinition(0, "Column01", 10);
                line2.SetColumnDefinition(1, "SecondColumnHeadingIsThis", 15);
                line2.SetColumnDefinition(2, "Col3HDR", 5);
                line2.SetColumnDefinition(3, "Col4", 35);
                line2.SetColumnDefinition(4, "FifthColumn", 25, PFDataAlign.RightJustify);

                textFile2.WriteData(line.OutputColumnNames());

                for (int rowNum = 0; rowNum < numRowsToOutput; rowNum++)
                {
                    for (int inx = 0; inx < 5; inx++)
                    {
                        num = line2.ColumnDefinitions.ColumnDefinition[inx].ColumnLength;
                        if (inx == 4)
                        {
                            num = num - 7;
                        }
                        str = PFTextProcessor.RepeatChar(tst[inx], num);
                        line2.ColumnData.ColumnDataValue[inx].Data = str;
                        str = null;
                    }
                    textFile2.WriteData(line.OutputColumnData());
                }

                Program._messageLog.WriteLine(line2.ToString());


                textFile2.CloseFile();
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (textFile != null)
                {
                    if (textFile.FileIsOpen)
                    {
                        textFile.CloseFile();
                    }
                    textFile = null;
                }
            }
        }//end method