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

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

The provider supports CRUD (Create, Read, Update, Delete) actions for both local JSON files and Web APIs. This functionality is explained in the following sections. It is important to note that when working with Web API JSON streams, configuration files are required for performing CRUD operations. You can either use an existing configuration file or create a new one. For detailed instructions on creating a configuration file, please refer to the Configuration section.

Querying With DbDataReader

The DbDataReader class can be used to fetch data in subset increments as required. It can retrieve data quicker than the C1JsonDataAdapter 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 read, create, 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 connectionString = string.Format(@"Data Model={0};Uri='{1}';Json Path='{2}'; api config file = 'api_config.xml'; 
                   Use Pool=true; username='****'; password='*****'", "Relational", "http://45.125.239.138:8088/api/Album", "$.Album");
            
    using (var con = new C1JsonConnection(connectionString))
    {
        con.Open();
        var sqlInsert = "Insert Into Album([AlbumId], [Title], [ArtistId]) values (9999667,'test', 1)";
        var cmdInsert = con.CreateCommand();
        cmdInsert.CommandText = sqlInsert;
        var result1 = cmdInsert.ExecuteNonQuery();
    }
}

Read Data

The example retrieves data from JSON file named json_bookstore.json by executing the Select command.

C#
コードのコピー 
static void ReadDataReader()
{
    static string documentConnectionString = $"Data Model=Document;Uri='json_bookstore.json';Json Path='$.bookstore.books'";
    
    using (var con = new C1JsonConnection(documentConnectionString))
    {
        con.Open();
        // Read コマンドを作成します。
        var cmd = con.CreateCommand();
        cmd.CommandText = "Select * From books";
        
        // Readコマンドを実行し、取得したデータを表示します。
        DbDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine(String.Format("\t{0} --> \t\t{1}", rdr["title"], rdr["ISBN"]));
        }
        Console.WriteLine("Read operation successful! \n\n");
    } 
}

Update Data

This example modifies data by executing Update command.

C#
コードのコピー 
static void UpdateDataReader()
{
    string connectionString = string.Format(@"Data Model={0};Uri='{1}';Json Path='{2}';api config file = 'api_config.xml';
                   Use Pool=true; username='****'; password='*****'", "Relational", "http://45.125.239.138:8088/api/Album", "$.Album");
            
    using (var con = new C1JsonConnection(connectionString))
    {
        con.Open();
        var sqlUpdate = "Update Album set [Title] = 'abcde' where [AlbumId] = 9999667";
        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 connectionString = string.Format(@"Data Model={0};Uri='{1}';Json Path='{2}';api config file = 'api_config.xml';
                   Use Pool=true; username='****'; password='*****'", "Relational", "http://45.125.239.138:8088/api/Album", "$.Invoice");
            
    using (var con = new C1JsonConnection(connectionString))
    {
        con.Open();
        var sqlDelete = "Delete from Album where [AlbumId] = 9999667";
        var cmdDelete = con.CreateCommand();
        cmdDelete.CommandText = sqlDelete;
        var result3 = cmdDelete.ExecuteNonQuery();
    }
}

Querying With C1JsonDataAdapter

The C1JsonDataAdapter class can be used to retrieve a single result set containing all the data that matches a given query. The C1JsonDataAdapter 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 C1JsonDataAdapter is slower compared to the DbDataReader.

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

Create Data

The example adds new records by executing the Insert command.

C#
コードのコピー 
static void InsertDataAdapter() 
{
    string connectionString = string.Format(@ "Data Model={0};Uri='{1}';Json Path='{2}'; api config file = 'api_config.xml'; 
                   Use Pool=true; username='******'; password='*******'", "Relational", "http://45.125.239.138:8088/api/Album", "$.Album");
    
        using(C1JsonConnection conn = new C1JsonConnection(connectionString)) 
        {
        //DataTable を設定します。
        C1JsonDataAdapter adapter = new C1JsonDataAdapter(conn, "Select * from Album");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Insert コマンドを作成します。
        adapter.InsertCommand = new C1JsonCommand(conn);
        adapter.InsertCommand.CommandText = "Insert into Album (AlbumId, Title, ArtistId) values (@AlbumId, @Title, @ArtistId)";
        adapter.InsertCommand.Parameters.Add("@AlbumId", "AlbumId");
        adapter.InsertCommand.Parameters.Add("@Title", "Title");
        adapter.InsertCommand.Parameters.Add("@ArtistId", "ArtistId");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
        
        //新しい行を挿入します。
        DataRow albumRow = dataTable.NewRow();
        albumRow["AlbumId"] = 9999668;
        albumRow["Title"] = "test";
        albumRow["ArtistId"] = 1;
        dataTable.Rows.Add(albumRow);

        //データベースを更新します。
        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#
コードのコピー 
public void ReadDataAdapter()
{
    string connectionString = string.Format(@"Data Model={0};Uri='{1}';Json Path='{2}'; api config file = 'api_config.xml'; 
                   Use Pool=true; username='*****'; password='******'", "Relational", "http://45.125.239.138:8088/api/Album", "$.Album");

    using (C1JsonConnection conn = new C1JsonConnection(connectionString))
    {
        //DataTable を設定します。
        C1JsonDataAdapter adapter = new C1JsonDataAdapter();

        //Select コマンドを作成します。
        adapter.SelectCommand = new C1JsonCommand(conn);
        adapter.SelectCommand.CommandText = "Select * from Album";
        adapter.SelectCommand.UpdatedRowSource = UpdateRowSource.None;

        //DataAdapter を使用して結果を DataTable に設定します。
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        Console.WriteLine("Select operation successful !!! \n \n");

        //結果を表示します。
        ShowDataTable(dataTable);
    }
}

Update Data

This example modifies data by executing Update command.

C#
コードのコピー 
static void UpdateDataAdapter() 
{
    string connectionString = string.Format(@ "Data Model={0};Uri='{1}';Json Path='{2}'; api config file = 'api_config.xml'; 
                   Use Pool=true; username='*******'; password='********'", "Relational", "http://45.125.239.138:8088/api/Album", "$.Album");
    
        using(C1JsonConnection conn = new C1JsonConnection(connectionString)) 
        {
        //DataTable を設定します。
        C1JsonDataAdapter adapter = new C1JsonDataAdapter(conn, "Select * from Album");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Update コマンドを作成します。
        adapter.UpdateCommand = new C1JsonCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE Album SET ArtistId=@ArtistId where AlbumId=@AlbumId";
        adapter.UpdateCommand.Parameters.Add("@ArtistId", "ArtistId");
        adapter.UpdateCommand.Parameters.Add("@AlbumId", "AlbumId");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        //既存の行を更新します。
        DataRow albumRow = dataTable.Rows[0];
        albumRow["ArtistId"] = 63;

        // データベースを更新します。
        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 connectionString = string.Format(@ "Data Model={0};Uri='{1}';Json Path='{2}'; api config file = 'api_config.xml'; 
                   Use Pool=true; username='*********'; password='*******'", "Relational", "http://45.125.239.138:8088/api/Album", "$.Album");
    
        using(C1JsonConnection conn = new C1JsonConnection(connectionString)) 
        {
        //DataTable を設定します。
        C1JsonDataAdapter adapter = new C1JsonDataAdapter(conn, "Select * from Album");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Delete コマンドを作成します。
        adapter.DeleteCommand = new C1JsonCommand(conn);
        adapter.DeleteCommand.CommandText = "Delete from Album where AlbumId = @AlbumId";
        adapter.DeleteCommand.Parameters.Add("@AlbumId", "AlbumId");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

        //行を削除します。
        DataRow albumRow = dataTable.Rows[dataTable.Rows.Count - 1];
        albumRow.Delete();

        // データベースを更新します。
        var i = adapter.Update(dataTable);

        if (i != -1) 
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }

        //結果を表示して確認するには、コメントを解除します。
        ShowDataTable(dataTable);
    }
}

 

 

Copyright © MESCIUS, Inc. All rights reserved.