Solved

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

Posted on 2015-01-14
1
141 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 30

Accepted Solution

by:
anarki_jimbel earned 500 total points
ID: 40550427
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

730 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