Component querying multiple CSV files

This is a variation on Example 1. Instead of querying a CSV file identified by a hard-coded filepath, a column is added to the component table. This allows the user, at query time, to identify the CSV file to be queried.

Example

Here is an input file called C:\Temp\schedtour.csv that demonstrates the data format expected by the example component:

146,AUSTRALIA,ISLAND SUNTANNER,21-3-2020 11:59:01,1-4-2020 11:59:01,2500
145,AUSTRALIA,ISLAND SUNTANNER,20-11-2019 11:59:01,1-12-2019 11:59:01,2500
144,BRAZIL,RAINFOREST RIDE,16-10-2021 11:59:01,29-10-2021 11:59:01,3000
143,BRAZIL,RAINFOREST RIDE,15-9-2021 11:59:01,28-9-2021 11:59:01,3000
142,BRAZIL,RAINFOREST RIDE,15-8-2021 11:59:01,28-8-2021 11:59:01,5500

Step 1: .NET component for retrieving data from multiple CSV files

1. Choose Define, RDMI, dotNet Components from the USoft Definer menu. The DotNetComponents window opens.

2. Provide a Name for your new component, in this case: CSV.

3. In the Program Source field, provide the source code of the component:

using System;
using System.Text;
using System.IO;
class CSV
{
 string csvFileName = null;
 StreamReader reader = null;

 public long QueryExe(string fileName, Nullable<Int32> id, string destination, string tourType, Nullable<DateTime> startDate, Nullable<DateTime> returnDate, Nullable<Int32> price)
 {
     csvFileName = fileName;
     if(String.IsNullOrEmpty(csvFileName))
         throw new Exception("Filename is not set.");
     reader = new StreamReader(csvFileName);
     return 1;
}

public long QueryFetch(out string fileName, out Nullable<Int32> id, out string destination, out string tourType, out Nullable<DateTime> startDate, out Nullable<DateTime> returnDate, out Nullable<Int32> price)
{
     id = null;
     destination = null;
     tourType = null;
     startDate = null;
     returnDate = null;
     price = null;
     fileName = csvFileName;
     if(reader.EndOfStream){
          reader.Close();
          return 0;
     }

     try{
          var line = reader.ReadLine();
          var values = line.Split(',');
          int n = 0;
          if(values[0] != null && !Int32.TryParse(values[0], out n))
               throw new Exception("Cannot convert value " + values[0] + " to a number");

          id = n;
          destination = values[1];
          tourType = values[2];
          if(values[3] != null)
               startDate = Convert.ToDateTime(values[3]);

          if(values[4] != null)
               returnDate = Convert.ToDateTime(values[4]);

          if(values[5] != null && !Int32.TryParse(values[5], out n))
               throw new Exception("Cannot convert value " + values[5] + " to a number");

          price = n;
     }catch(Exception e){
             reader.Close();
             throw e;
     }

     return 1;
 }
}

4. Save work.

5. Press the Check button. The RDMI interface is generated by USoft.

6. On the State tab, set:

• Stateful Component = Y

• Participant in Transaction = N

• Support tablecomponent = Y

• Update = N

• Insert = N

• Delete = N

7. Save work.

Step 2: Query protocol association

1. In the DotNet Components window, click the Query Protocol Associations tab page.

2. Fill out the following fields:

• Protocol = QUERY
• Execute Method = QUERYEXE
• Fetch Method = QUERYFETCH

3. Save work.

Step 3: Testing the query protocol

At this point, you can test the query protocol with null values.

1. Open the User Application from USoft Binder.

2. In Sql Command, execute:

INVOKE csv.query WITH
SELECT 'C:\Temp\schedtour.csv', null, null, null, null, null, null

Step 4: Component table

1. Choose Tables, Component Tables from the USoft Definer menu.

2. Provide the name CSV for your new component table.

3. Provide values for the Abbreviation, Object Name and Object Name Plural fields.

4. Define the following columns:

Name	Data Type for domain	Prompt	Key	Position
FILE_NAME	NVARCHAR2(255)	CSV File	No	1
ID	NUMBER	Id	1	2
DESTINATION	NVARCHAR2(20)	Destination	No	3
TOUR_TYPE	NVARCHAR2(30)	Tour Type	No	4
START_DATE	DATE	Start Date	No	5
END_DATE	DATE	End Date	No	6
PRICE	NUMBER	Price	No	7

Step 5: Query Mandatory

This component will not work unless the user specifies a filepath to the CSV file.

1. Open the USoft GUI tool (Windows Designer, Web Designer), find the table class for the CSV component table, and for the column control based on the FILE_NAME column, set Query Mandatory = Yes.

Step 6: Testing the solution

1. Open the client application (User Application item from USoft Binder).

2. Choose View, Objects from the menu. Open the object that correspond to the component table.

3. Query the info window using the search condition:

• CSV File = C:\Temp\schedtour.csv

See that the data from the specified CSV file are retrieved.

4. Open a Sql Command window and execute:

SELECT    *
FROM      csv
WHERE     file_name = 'C:\Temp\schedtour.csv'

See that the SQL Command Report area displays data from the specified CSV file.