[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

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

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
valentinemhlanga
Asked:
valentinemhlanga
1 Solution

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now