DataConnector
データのクエリ
ADO.NET provider for CSV > データのクエリ

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#
コードのコピー 
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#
コードのコピー 
static void ReadDataReader() 
{
    static string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
    
    using (var con = new C1CSVConnection(csvConnectionString))
    {
        con.Open();

        // ''Read'' コマンドを作成します。
        var cmd = con.CreateCommand();
        cmd.CommandText = "Select * From sampleCSV";

        // Readコマンドを実行し、取得したデータを表示します。
        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 Update command.

C#
コードのコピー 
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 Delete command.

C#
コードのコピー 
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();
    }
}

Querying With C1CSVDataAdapter

The C1CSVDataAdapter class can be used to retrieve a single result set containing all the data that matches a given query. The C1CSVDataAdapter uses its Fill method to fetch data from the data source. An empty DataTable instance is passed as an argument to the Fill method. Once the method returns, the DataTable instance is populated with the queried data. Since the Fill method must retrieve all the data from the data source before returning, the C1CSVDataAdapter is slower compared to the DbDataReader.

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

Create Data

The example adds new records by executing the Insert command.

C#
コードのコピー 
static void InsertDataAdapter() 
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
    
    using (var conn = new C1CSVConnection(csvConnectionString))
    {
        //DataTable を設定します。
        C1CSVDataAdapter adapter = new C1CSVDataAdapter(conn, "Select * from sampleCSV");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        
        //Insertコマンドを作成します。
        adapter.InsertCommand = new C1CSVCommand(conn);
        adapter.InsertCommand.CommandText = "Insert Into sampleCSV([year], [industry_code], [industry_name]) values (@Year,@Industry_code, @Industry_name)";
        adapter.InsertCommand.Parameters.Add("@Year", "year");
        adapter.InsertCommand.Parameters.Add("@Industry_code", "industry_code");
        adapter.InsertCommand.Parameters.Add("@Industry_name", "industry_name");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
        
        //新しい行を挿入します。
        DataRow sampleCSVRow = dataTable.NewRow();
        sampleCSVRow["year"] = 2022;
        sampleCSVRow["industry_code"] = "C";
        sampleCSVRow["industry_name"] = "Telecommunications";
        dataTable.Rows.Add(sampleCSVRow);
        
        //データベースを更新します。
        var i = adapter.Update(dataTable);
        if (i != -1)
            {
                Console.WriteLine("Insert operation successful !!! \n \n");
            }
        
        //結果を表示して確認するには、コメントを解除します。
        ShowDataTable(dataTable);
    }
}

Read Data

This example retrieves data by executing the Select command.

C#
コードのコピー 
static void ReadDataAdapter() 
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");

    using (var conn = new C1CSVConnection(csvConnectionString))
    {
        //DataTable を設定します。
        C1CSVDataAdapter adapter = new C1CSVDataAdapter();
        
        //Select コマンドを作成します。
        adapter.SelectCommand = new C1CSVCommand(conn);
        adapter.SelectCommand.CommandText = "Select * from sampleCSV";
        adapter.SelectCommand.UpdatedRowSource = UpdateRowSource.None;
        
        //DataAdapter を使用して結果を Datatable に設定します。
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        Console.WriteLine("Select operation successful !!! \n \n");
        
        //結果を表示します。
        ShowDataTable(dataTable);
    }
}

static void ShowDataTable(DataTable table, int length = 25)
{
    foreach (DataColumn col in table.Columns)
    {
        Console.Write("{0,-" + length + "}", col.ColumnName);
    }
    Console.WriteLine();

    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn col in table.Columns)
        {
            if (col.DataType.Equals(typeof(DateTime)))
                Console.Write("{0,-" + length + ":d}", row[col]);
            else if (col.DataType.Equals(typeof(decimal)))
                Console.Write("{0,-" + length + ":C}", row[col]);
            else
                Console.Write("{0,-" + length + "}", row[col]);
        }
        Console.WriteLine();
    }
}

Update Data

This example modifies data by executing Update command.

C#
コードのコピー 
static void UpdateDataAdapter() 
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
    
    using (var conn = new C1CSVConnection(csvConnectionString))
    {
        //DataTable を設定します。
        C1CSVDataAdapter adapter = new C1CSVDataAdapter(conn, "Select * from sampleCSV");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        
        //Update コマンドを作成します。
        adapter.UpdateCommand = new C1CSVCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE sampleCSV SET year=@Year where id=@Id";
        adapter.UpdateCommand.Parameters.Add("@Year", "year");
        adapter.UpdateCommand.Parameters.Add("@Id", "id");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
        
        //既存の行を更新します。
        DataRow sampleCSVRow = dataTable.Rows[0];
        sampleCSVRow["id"] = 90;
        sampleCSVRow["year"] = 2022;
        
        //データベースを更新します。
        var i = adapter.Update(dataTable);
        if (i != -1)
        {
            Console.WriteLine("Update operation successful !!! \n \n");
        }
        
        //結果を表示して確認するには、コメントを解除します。
        ShowDataTable(dataTable);
    }
}

Delete Data

This example removes data by executing Delete command.

C#
コードのコピー 
static void DeleteDataAdapter() 
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
    
    using (var conn = new C1CSVConnection(csvConnectionString))
    {
        //DataTable を設定します。
        C1CSVDataAdapter adapter = new C1CSVDataAdapter(conn, "Select * from sampleCSV");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        
        //Delete コマンドを作成します。
        adapter.DeleteCommand = new C1CSVCommand(conn);
        adapter.DeleteCommand.CommandText = "Delete from sampleCSV where id = @Id";
        adapter.DeleteCommand.Parameters.Add("@Id", "id");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
        
        //行を削除します。
        DataRow sampleCSVRow = dataTable.Rows[dataTable.Rows.Count - 1];
        sampleCSVRow.Delete();
        
        //データベースを更新します。
        var i = adapter.Update(dataTable);
        if (i != -1) 
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
        
        //結果を表示して確認するには、コメントを解除します。
        ShowDataTable(dataTable);
    }
}

 

 

Copyright © MESCIUS, Inc. All rights reserved.