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

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#
Copy Code 
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 a JSON file named json_bookstore.json by executing the Select command.

C#
Copy Code 
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();
        // Create Read command
        var cmd = con.CreateCommand();
        cmd.CommandText = "Select * From books";
        
        // Execute Read command and display fetched data
        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 the Update command.

C#
Copy Code 
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 the Delete command.

C#
Copy Code 
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();
    }
}