Example 1: Component table 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

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.

Set:

•Active = Y

9.	Save work.