Solved

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

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

861 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now