Solved

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

Posted on 2015-01-14
1
136 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The viewer will learn how to dynamically set the form action using jQuery.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

778 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