Component querying a CSV file

This example shows how to create a component table that returns data from a CSV file as if it were a database table with multiple rows: a component table mimics the behaviour, or part of the behaviour of a database table but is not itself in the database.

The component table uses an underlying .NET component that uses the query protocol to interpret file contents as a multi-row data set.

The example component expects a .CSV input file with the following structure: ID, DESTINATION, TOUR_TYPE, START_DATE, END_DATE, PRICE.

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 a CSV file

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. IMPORTANT: This name will also be the name of your component table.

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 CSV(string filename){
         csvFileName = filename;
 }

 public long QueryExe(Nullable<Int32> id, string destination, string tourType, Nullable<DateTime> startDate, Nullable<DateTime> returnDate, Nullable<Int32> price)
 {
         if(csvFileName == null)
                 throw new Exception("Filename is not set.");

         reader = new StreamReader(csvFileName);
         return 1;
 }

 public long QueryFetch(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;

        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 Constructor tab, set :

• Constructor SQL = select 'C:\Temp\schedtour.csv'

7. Press the Check button. See that Correct = Y.

8. Set:

• Active = Y

9. 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    null
,         null
,         null
,         null
,         null
,         null

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

Step 4: Component table

1. Choose Define, RDMI, dotNet Components from the USoft Definer menu. The DotNetComponents window opens. Query the record with Name = CSV.

2. On the State tab, set :

• Stateful Component = Y
• Participate in Transaction = N
• Support tablecomponent = Y
• Update = N
• Insert = N

• Delete = N

3. Save work.

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

5. Fill out the name of your new component table. This must be the name that you provided for the supporting component in a previous step, in this case: CSV.

6. Provide values in the Abbreviation, Object Name and Object Name Plural fields.

7. Define the following columns:

8. Save work.

Step 5: Testing the component table

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. See that it presents data from the CSV file.

4. Query the info window with the search condition:

Destination = AUSTRALIA

See that only records with AUSTRALIA as destination are now displayed.