Solved

C# write to Text from SQL with padding and Static Fields

Posted on 2015-01-14
1
138 Views
Last Modified: 2015-01-19
Im new to C# and would like to write to a text file and some fields are padded with 0 and some with space. would like a sample script that is doing so . Please help.

I would like to also know how I can pass the columns in the sql table as variables as I will need to concat them with other string and padd them with 0.

Please explain in simple english as I am still learning


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Ru<wbr ></wbr>ntime;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data.Odbc;
using System.Text;

namespace writetotext.csproj
{
    [System.AddIn.AddIn("Scrip<wbr ></wbr>t Main", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Ta<wbr ></wbr>sks.Script<wbr ></wbr>Task.VSTAR<wbr ></wbr>TScriptObj<wbr ></wbr>ectModelBa<wbr ></wbr>se
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Ru<wbr ></wbr>ntime.DTSE<wbr ></wbr>xecResult.<wbr ></wbr>Success,
            Failure = Microsoft.SqlServer.Dts.Ru<wbr ></wbr>ntime.DTSE<wbr ></wbr>xecResult.<wbr ></wbr>Failure
        };
        #endregion
        public void Main()
        {
            string filePath = @"c:\"; //path where file is located 
            string dbFile = filePath + @"\sqlfile.txt";


            writeFileFromDB(dbFile);

        }

        public static void writeFileFromDB(string dbFile)
        {


            string connectionString = null;
            SqlConnection conn;
            SqlCommand command;
            string selectQuery = null;
            string recordtype = "P20";
            string signonrec = "SIGNONRECCMKEEXP";
            DateTime dt = DateTime.Now;
            string date = dt.ToString("yyyyMMdd");
            string time = dt.ToString("hhMMss");
            string fileID = "APPEXT";
            string companyid = "EEREOPXP";

            string cardid = "{0}";
            string reportid = "{1}";
            string crf = "CRF";
            string baf820 = "ACHUIPK";
            string accountnum = "0093942188";
            string paycode = "ACH";// cab be used as payment method 
            DateTime dateadd = dt.AddDays(2);
            string dudate = dateadd.ToString("yyyyMMdd<wbr ></wbr>");
            string currency = "USD";
            string po = "P0";
            string H = "CRF02.1";
            string FILLER1 = "";
            string filler = "";
            string payreffno = (date + "A");
            string compID = "875295334";
            StringBuilder s = new StringBuilder();
            s.Append(recordtype.PadRig<wbr ></wbr>ht(3));
            s.Append(companyid.PadRigh<wbr ></wbr>t(10));
            s.Append(cardid);
            s.Append(reportid);

            string str = s.ToString();

            string strFile = string.Empty;

            for (int i = 0; i <= 5; i++) ;  //  loop from database dataset. 

            string dbcardid = string.Empty;
            string dbreportid = string.Empty;
            strFile += string.Format(str, dbcardid, dbreportid);

            //padding done here 
            companyid.PadRight(10);
            signonrec.PadRight(6);
            date.PadRight(8);
            time.PadRight(6);
            fileID.PadRight(8);
            filler.PadRight(39);
            crf.PadRight(3);
            baf820.PadRight(6);
            accountnum.PadRight(35);
            paycode.PadRight(3);
            dudate.PadRight(8);
            currency.PadRight(3);
            po.PadRight(8);
            FILLER1.PadRight(10);



            //create connection
            connectionString = "SERVER=sql2008;DATABASE=C<wbr ></wbr>MK_HRACCT;<wbr ></wbr>Network=DB<wbr ></wbr>MSSOCN;Tru<wbr ></wbr>sted_Conne<wbr ></wbr>ction = Yes";
            conn = new SqlConnection(connectionSt<wbr ></wbr>ring);

            selectQuery = (@"SELECT 12345382154' as CardNumber,  
                                       ReportID, LEFT(CMK_HRACCT.dbo.[fn_cm<wbr ></wbr>k_no_speci<wbr ></wbr>al_char](R<wbr ></wbr>eportDescr<wbr ></wbr>iption),28<wbr ></wbr>) as ReportName, 
                                          CASE WHEN SUM(ExpenseDistrib) > 0 THEN convert(char(10),'+'+ltrim<wbr ></wbr>(convert(c<wbr ></wbr>har,SUM(Ex<wbr ></wbr>penseDistr<wbr ></wbr>ib))))
                                        WHEN SUM(ExpenseDistrib) < 0 THEN convert(char(10),ltrim(con<wbr ></wbr>vert(char,<wbr ></wbr>SUM(Expens<wbr ></wbr>eDistrib))<wbr ></wbr>))
                               END as Amount, EMPLOYEE
                               FROM CMK_HRACCT..cmk_Apptricity<wbr ></wbr>_Expense_E<wbr ></wbr>xport
                               WHERE PaymentType = 'Corporate Card'
                               AND SENT_TO_BOFA IS NULL
                               and ExpenseDistrib <> 0
                               group by CardNumber, ReportID, LEFT(CMK_HRACCT.dbo.[fn_cm<wbr ></wbr>k_no_speci<wbr ></wbr>al_char](R<wbr ></wbr>eportDescr<wbr ></wbr>iption),28<wbr ></wbr>), EMPLOYEE
                               HAVING SUM(ExpenseDistrib) <> 0");


            conn.Open();
            command = new SqlCommand(selectQuery, conn);
            SqlDataReader reader = command.ExecuteReader();

            // Open the file for write operations.  If exists, it will overwrite due to the "false" parameter
            using (StreamWriter file = new StreamWriter(dbFile, false))
            {
                while (reader.Read())
                {
                    file.WriteLine(reader["Rep<wbr ></wbr>ortID"] + "\t" + reader["ReportName"] + "\t" + reader["Amount"] + "\t" + reader["EMPLOYEE"]);
                }
            }

            reader.Close();
            conn.Close();


            // Open file read and reformart then dump file 

            {

            }
        }

    }
}

Open in new window

0
Comment
Question by:valentinemhlanga
1 Comment
 
LVL 29

Accepted Solution

by:
anarki_jimbel earned 500 total points
ID: 40550427
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question