ComponentOne DataServices Edition
ADO.NET provider for CSV / Querying Data
In This Topic
Querying Data
In This Topic

The ADO.NET Provider for CSV implements two classes you can use to perform CRUD (Create, Read, Update, and Delete) operations: DbDataReader and C1CSVDataAdapter classes. The tabs below describe the interfaces and code implementations.

The provider supports CRUD operations on both local CSV files and Web API CSV streams. For local CSV files, no additional configuration files are needed. However, when working with Web API CSV streams, you must utilize configuration files to specify the necessary settings for accessing and manipulating the data through the Web API. For more information on creating a configuration file, see Configuration.

Querying With DbDataReader

The DbDataReader class can be used to fetch data in subset increments as required. It can retrieve data quicker than the C1CSVDataAdapter as it retrieves data in pages. When you read data from the DbDataReader, it requests the succeeding page from the data source to load the result, which makes the data retrieval faster.

The following code examples demonstrate create, read, update, and delete operations from the data source using the DbDataReader.

Create Data

The example adds new records by executing the Insert command.

C#
Copy Code 
static void InsertDataReader() 
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
        
    using (var con = new C1CSVConnection(csvConnectionString))
    {
        con.Open();
        var sqlInsert = "Insert Into sampleCSV([year], [industry_code], [industry_name]) values (2022,'C', 'Telecommunications')";
        var cmdInsert = con.CreateCommand();
        cmdInsert.CommandText = sqlInsert;
        var result1 = cmdInsert.ExecuteNonQuery();
    } 
}

Read Data

The example retrieves data from the CSV file named sampleCSV.csv by executing the Select command.

C#
Copy Code 
static void ReadDataReader() 
{
    static string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
    
    using (var con = new C1CSVConnection(csvConnectionString))
    {
        con.Open();

        // Create Read command
        var cmd = con.CreateCommand();
        cmd.CommandText = "Select * From sampleCSV";

        // Execute Read command and display fetched data
        DbDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine(String.Format("\t{0} --> \t\t{1}", rdr["name"], rdr["description"]));
        }
        Console.WriteLine("Read operation successful! \n\n");
    }
}

Update Data

This example modifies data by executing the Update command.

C#
Copy Code 
static void UpdateDataReader() 
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
                
    using (var con = new C1CSVConnection(csvConnectionString))
    {
        con.Open();
        var sqlUpdate = "Update sampleCSV set [year] = 2022 where [id] = 10";
        var cmdUpdate = con.CreateCommand();
        cmdUpdate.CommandText = sqlUpdate;
        var result2 = cmdUpdate.ExecuteNonQuery();
    }
}

Delete Data

This example removes data by executing the Delete command.

C#
Copy Code 
static void DeleteDataReader() 
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");

    using (var con = new C1CSVConnection(csvConnectionString))
    {
        con.Open();
        var sqlDelete = "Delete from sampleCSV where [id] = 90";
        var cmdDelete = con.CreateCommand();
        cmdDelete.CommandText = sqlDelete;
        var result3 = cmdDelete.ExecuteNonQuery();
    }
}