Thanks Sledge. I did all the above before returning to the thread and after doing a whole lot of background work on Postgres Databases etc. Stored procedure definitely helped and then indexing did as well. I am in the process of looking at using a refcurs as well. But the proof will be in the pudding on Sunday when I have to run the billing for the month of May. Just over 2 million records now and it takes around 2, 3 or 4 minutes to complete everything, calculations included and creating PDF output reports.

Excellence is doing ordinary things extraordinarily well.

Thanks Pete and all the other guys. I need all the data to generate a report, summary billing report. SO client-side calculations etc is a must. What I have found helpful was creating a stored procedure on the DB, it is a local DB not a network DB. Concept phase before business would through money at a server. Did a heck of a lot of reading up on PostGres as this is my DB. For now I am sitting at 2 million records and it runs fine. Takes about 2 minutes to collect and do all relevant calculations etc. Indexing the date column helped with performance as well as I use this to create the report for a specific date range. Just for interest sake, 2 million records is only one months worth of data, which is what I am extracting from an Oracle Database which carries way more data than what I am carrying to do the billing. So, in short, it works for now. Will see as the local DB grows how the performance degrades.

Excellence is doing ordinary things extraordinarily well.

Hi Guys. Suppose this question has been asked numerous times. I have a database "postgresql" with about 20 columns and about 1.4million records. I select data from the database between two given date ranges and it takes about 4 minutes to fill the dataset. I would like to speed this up. I am doing no updates, just using the data to compile a report. is there a faster way to do this? Below code is what I have. My results are OK but just filling the dataset is a bit of a problem i that it takes +- 4 to 5 minutes to fill the dataset.

        string sql = @"SELECT \* FROM datbase where bus\_received\_time\_stamp between @startDate AND @endDate";

        //Making use of a Postgresql Connection helper
        NpgsqlConnection conn = new NpgsqlConnection(conns);

        //Here we format the dateTimePicker dates to be used with the database
        string fromDate = dtStartDate.Text;
        string toDate = dtEndDate.Text;

        //Instanciate a SqlCommand and connect to the DB and fill a Dataset
        NpgsqlCommand cmd = new NpgsqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        cmd.Parameters.AddWithValue("@startDate", fromDate + " 00:00:01");
        cmd.Parameters.AddWithValue("@endDate", toDate + " 23:59:59");

        #endregion

        conversionRate = txtConversionRate.Text;
        double conRate = Convert.ToDouble(conversionRate);

        try
        {
            //Open the DB Connection
            conn.Open();
            setText(this, "Connection Established");
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
            setText(this, "Collecting Data for Processing!");
            da.Fill(dts);

//Time between the two "setText statements takes a good 4 to 5 minutes
setText(this, "Define Data To be Used");

From here the processing takes a few minutes as there quite a bit of matching to do etc. However the main thing here is that filling the DataAdapter takes too much time. I would like to cut this down to maybe a few seconds, maybe a minute? I have added an Index on the DB on the Datee column to try and speed things up but now sure if this is correct? Any ideas??

Excellence is doing ordinary things extraordinarily well.

I bound the SelectedSong to a textBox and it works now. Not the cleanest way of doing things but it works. Thanks

Excellence is doing ordinary things extraordinarily well.

Thanks Alan. That resolved my issue. Greatly appreciated.

Excellence is doing ordinary things extraordinarily well.

Hi Mycroft. Apologies for taking this long to reply. Been a little busy. I tried the below and I get the SelectedItem but it does not get passed to "theSong". Am I going crazy or am I just missing the point completely here?

    private void lstFile\_MouseDoubleClick(object sender, MouseButtonEventArgs e)
    {
        try
        {
            var theSong = this.lstFile.SelectedItem as songDetails;

            if (theSong != null)
            {
                string filePath = theSong.FilePath;
            }
        }
        catch (Exception ex)
        {
            System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
        }
        
    }

Excellence is doing ordinary things extraordinarily well.

Hi Mycroft. Thanks for the reply. This may sound stupid but how do I do that? I have tried numerous things and nothing works or I get a very long string of all the items in the object returned. :(

Excellence is doing ordinary things extraordinarily well.

Hi Guys, I have a nice working app in Windows Forms. I am attempting to port it to WPF and I am stuck with selecting a row and getting a value from one of the columns. Apologies for the long code: In Windows Forms App I do the below:

    private void lstFiles\_DoubleClick(object sender, EventArgs e)
    {
        if (lstFiles.SelectedItems.Count != 0)
        {
            string file = lstFiles.SelectedItems\[0\].Text;
            openFormGeneral(file);
        }
    }

In WPF .xaml:

I Populate the ListView as follows:

    public class songDetails
    {
    public string Title { get; set; }
    public string Artist { get; set; }
    public string Track { get; set; }
    public string Set { get; set; }
    public string Album { get; set; }
    public string Genre { get; set; }
    public string Language { get; set; }
    public string FileName { get; set; }
    public string FilePath { get; set; }
    }

    public IList songDetail { get; set; }


    public void AddFile(ID3Info File)

Hi All. I have a slight problem in that my DirectoryInfo does not return the full path. Does anybody know if there is a limitation to the DirectoryInfo? The path to the files is something like this: C:\\Folder1\\Folder2\\Folder3\\Folder4\\Folder5 My code is as follows:

private void getAllFiles(string sExt, string sFolder)
{
DirectoryInfo di = new DirectoryInfo(sFolder);
FileInfo[] fi = di.GetFiles(sExt, SearchOption.AllDirectories);
//List<FileInfo> fi = new List<FileInfo>(di.EnumerateFiles(sExt, SearchOption.AllDirectories));
foreach (FileInfo file in fi)
{
AddNewFile(Path.Combine(di.ToString(), file.ToString()));
counter++;
}
lblTotalFiles.Text = counter.ToString();
//SetText(counter.ToString());
}

FileInfo seems to get the location of the file as it shows me the file it picked up but when the path of DirectoryIfo is passed on it excludes Folder5 thus the object I am passing it to doesn't find the file in question. Is there anyway I can fix this?

Excellence is doing ordinary things extraordinarily well.

Hi Guys and ladies. I am reading a record into a windows form which works fine. The record structure is: Header details of 463 chars and then a repetative set of records of 276 chars each. Problem is that the string can have anything between 1 and a max of 50 repetative records. On clicking the next button i am trying to display the details from the second record etc. I am trying to figure out how to best acieve this as if I click the button the first time it reads the second block of data fine, if I click it again it reads the second block again instead of reading the third bloack of data. How do I read the data to be able to get to the third record? Would it be best to load the blocks into a dataset and then maybe read that? Below is the code I currently have.

    private void exitToolStripMenuItem\_Click(object sender, EventArgs e)
    {
        this.Dispose();
        this.Close();
    }

    private void button2\_Click(object sender, EventArgs e)
    {
        filepath = txtFileName.Text;
        StreamReader sr = new StreamReader(filepath);
        string holdLine = sr.ReadLine();
        int recLength = holdLine.Length;
        int startIndex = 463; 
        int oneRecLength = 276;
        int totalRecs = (recLength - startIndex) / oneRecLength;
        int startread = 739;
        int lastread = 0;
        if ((recLength - 463) > 1015)
        {
            if((totalRecs - 1) != 0)
            {
                string nextrec = holdLine.Substring(startread, 276);
                HOM\_CO\_ID = nextrec.Substring(0, 5); //= 5
                HOM\_PROD\_CODE = nextrec.Substring(5, 3); //= 3
                HOM\_ACCT\_TYPE = nextrec.Substring(8, 1); //= 1
                HOM\_BRANCH = nextrec.Substring(9, 6); //= 6
                HOM\_ACCOUNT\_NO = nextrec.Substring(15, 23); //= 23
                HOM\_BUDGET\_NO = nextrec.Substring(38, 5); //= 5
                HOM\_ACCOUNT\_NAME = nextrec.Substring(43, 30); //= 30
                HOM\_AMOUNT = nextrec.Substring(73, 17); //= 17
                HOM\_REF = nextrec.Substring(90, 20); //= 20
                HOM\_DESCR = nextrec.Substring(110, 47); //= 47
                HOM\_TRAN\_CODE = nextrec.Substring(157, 5); //= 5
                HOM\_REVERSAL\_DESCR = nextrec.Substring(162, 47); //= 47
                NOM\_REF2 = nextrec.Substring(209, 20); //= 20
                NOM\_DESCRIPTION = nextrec.S

I want to creat a .net app reading "messages" or shall I say files defined by a cobol-copy book format/layout. Do this I want to reference the copy-book and pass the message to it for unmarshalling and then writing it out to another message in yet another cobol copy-book format. Like creating a middleware app. If I have to transform the copy book and write it out to as a file format, it could take ages as they are huge layouts and would be easier just to reference the copy-book. Hope this explains!

Excellence is doing ordinary things extraordinarily well.

I tend to agree. I have numerous copy-books and some of them are pretty big. Take this one for instance: 01 X30002-FUNCTION-SEG. 05 X30002-MSG-LL PIC S9(04) COMP. Message length is variable depending on number of items in the set 05 X30002-FUNC-TAG PIC X(08). ZPE0805F 05 X30002-VERSION PIC 9(02). Version Number. Must be set to 00 05 X30002-FUNCTION-INPUT. 10 X30002-HEADER. 15 X30002-BATCH-TYPE PIC X(03). P = Payment batch T = Transfer batch within a customers own profile PS = Purchase of Services batch RTI = Real-time Clearing inward batch RTO = Real-time Clearing outward batch 15 X30002-SET-TYPE PIC X(01). C = Consolidated batch. 1 Debit upto 50 credits I = Itemised batch. Upto 25 Dr/CR items 15 X30002-CUSTOMER-ID PIC X(20). A valid customer identifier from the channel. Not Validated 15 X30002-BATCH-ID PIC X(20). A unique batch identifier. Not Validated 15 X30002-SET-NUMBER PIC 9(05). The number of the set contained within the batch. Start at 1 and increment for each set within the batch 15 X30002-NOMINATED-POST-IND PIC X(01). R = Real-time post = defaults to Real-Time 15 X30002-HOMING-POST-IND PIC X(01). F = Force post R = Real-time post = defaults to Real-Time where applicable 15 X30002-CHECK-AVAIL-BAL-IND PIC X(01). Set to Y if available balance must be checked prior to posting 15 X30002-TRAN-CODE-IND PIC X(01). C = Channel D = Default = Defaults to Channel 15 X30002-NO-OF-TXNS PIC 9(03). The number of transactions contained in the homing table 15 FILLER PIC 9(08). 10 X30002-REVERSAL-DETAILS. 15 X30002-NOM-REV-PROD-CODE PIC X(03). Reversal Product Code eg DDA / TCS 15 X30002-NOM

Hi Guys. I want to dev an app which will read ASCII files but using their COBOL copy-books as format reference and then mapping them into a new COBOL copy-book. Is there a plugin that would let me specify a cobol copy book and then reference the input file against it and read it? I coul probably write the layouts as classes and do it that way bbut this will take ages as I have many copy-books to do. My aim is to read in a file, refer to the copy book for the format and then convert it to a different copy-book by "cross mapping". Anybody done anything like this before? I'm just looking for suggestions or references to articles or some web links. All I can find is some IBM stuff, WEBSPHERE etc but not what I am looking for though. Thanks

Excellence is doing ordinary things extraordinarily well.

Hi Dewald, Not sure if you have found a solution to this as yet but this is what I do when I want to populate a comboBox from a db.

        try
        {
            conn.Open();
            string sql = @"select Titles, TitleID from tblXXXXXXXX";
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
            OleDbCommandBuilder cmdb = new OleDbCommandBuilder(adapter);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            comboBox1.DataSource = dt;
            comboBox1.DisplayMember = "Titles";
            comboBox1.ValueMember = "Titles";
            this.BindingContext\[dt, "Titles"\].Position = 0;
        }

Hope this helps.

Excellence is doing ordinary things extraordinarily well.

Hi guys. I am looking for a simple FTP connection. I have an FTP Server running on my local machine. How do I connect to this FTP server using code? All the samples I find refer to a WEB FTP connection. Can somebody direct me to a simple ftp connection that connects to either a local FTP server or connection to an FTP server on a network? Thanks guys.

Excellence is doing ordinary things extraordinarily well.

After reading this I am canning the adea of getting the software to try out. :^)

Excellence is doing ordinary things extraordinarily well.

Hi Griff. Not sure if that will work for what I have planned. What I am currently doing is writing the data to labels. Sequence B can be made up of multiple records/lines, each containing a set of TAGS. Tags are anything that start and end with ":". From the sample I supplied, one sequence B records starts with TAG :21: and ends with TAG :70:

Excellence is doing ordinary things extraordinarily well.

Hi Guys. I have a file with two sequences, Sequence A and a Sequence B. Sequence A only appears once in the file and Sequence B can appear once or multiple times in the file. See Sample data below: :21:REFERENCE1 :23E:OTHR/DMST :32B:ZAR5005,00 :57A:CODEXXXX :59:/12345600002345678 TEST NAME2 ADDRESS1 ADDRESS2 ADDRESS3 :21:REFERENCE2 :23E:OTHR/DMST :32B:ZAR7005,00 :57A:CODEAAAA :59:/12345657002345678 TEST NAME3 ADDRESS1 ADDRESS2 ADDRESS3 :71A:OUR -} The portion in Bold is Sequence A and the remainder is sequence B data. As you can see by the "TAGS" the fields starting with ':', that there are multiple Sequence B data. I need a way to pick up the data for the multiple Sequence B records. Sequence B start with :21: and ends with :59: followed by the address details. I tried a straight forward StreamReader and then populating the data but my Steamreader always goes from top to bottom in my while loop. Sample below.

        string inFile;
        inFile = textBox1.Text;
        StreamReader sr = new StreamReader(inFile);
        string holdline;
        int lineCount = 0;
        int clientname = 0;
        int creditname = 0;
        while (!sr.EndOfStream)
        {
            holdline = sr.ReadLine();

            if (holdline.Length > 4)
            {
                string tag = holdline.Substring(0, 4);
                string tag2 = holdline.Substring(0, 5);
                if (tag == "{1:F")
                {
                    lblBIC1.Text = holdline.Substring(6, 8).Trim();
                    lblClientBIC.Text = holdline.Substring(46, 8).Trim();
                    lblMsgType.Text = "MT" + holdline.Substring(33, 3).Trim();
                    lblDate.Text = holdline.Substring(36, 10).Trim();
                    lblSWIFTRef.Text = holdline.Substring(58, 10).Trim();
                }

                if (tag == ":20:")
                {
                    int results = holdline.Length - 4;
                    lblA20.Text = holdline.Substring(4, results).Trim();
                }

                else if (tag2 == ":21R:")
                {
                    int r1 = holdline.Length - 5;
                    lblA20.Tex

Hi Guys. How can I check if a file is being used by another process? I have tried the following but it doesn't seem to work though.

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.None)

If I open the .txt file in NotePad and then run the app it opens the file. What I need to do is for my app to throw an exception that tells me that the file being used by another process, or something like that. Anybody have any idea? Thanks

Excellence is doing ordinary things extraordinarily well.

Thanks Griff. How would I write one for multiple columns UPDATE? Been searching and most posts only shows 1 parameter being passed at a time

Excellence is doing ordinary things extraordinarily well.