ComponentOne DataServices Edition
ADO.NET provider for Dynamics 365 Sales / Querying Data
In This Topic
Querying Data
In This Topic

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

Querying With DbDataReader

The DbDataReader class can be used to fetch data in subset increments as required. It can retrieve data quicker than the C1D365SDataAdapter 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()
{
    using (var con = new C1D365SConnection(connectionString))
    {
        con.Open();
        // Create Insert command
        var cmd = con.CreateCommand();
        cmd.CommandText = $"
            INSERT INTO accounts (accountid, name)
            VALUES ('{Guid.NewGuid().ToString()}','Demen_Ins')";

        // Execute Insert command
        int i = cmd.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Insert Operation Successful \n \n");
        }
    }
}

Create Data and return created values

SQLite RETURNING clause is used at the end of the INSERT statement to return the inserted values. More on the SQLite’s RETURNING clause can be found here. When providing the RETURNING clause, call the ExecuteReader method to obtain the inserted values. This method will return a reader which can be used to read the result table containing the newly inserted values.

C#
Copy Code 
static void ReturningClause()
{
    using (var con = new C1D365SConnection(connstr))
    {
        con.Open();
        // Create Insert command
        var cmd = con.CreateCommand();
        cmd.CommandText = $"INSERT INTO accounts (accountid, name)"
                + $"VALUES ('{Guid.NewGuid().ToString()}','Demen_Ins') RETURNING accountid";

        // Execute Insert command and use the reader object with the values returned
        var reader = cmd.ExecuteReader();
        while(reader.Read()){
            Console.WriteLine($"accountid value --> {reader["accountid"]}");
        }
    }
}

Read Data

This example retrieves data by executing the Select command.

C#
Copy Code 
static void ReadDataReader()
{
    using (var con = new C1D365SConnection(connectionString))
    {
        con.Open();
        // Create Read command
        var cmd = con.CreateCommand();
        cmd.CommandText = "SELECT accountid, name FROM Accounts LIMIT 10";

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

Update Data

This example modifies data by executing the Update command.

C#
Copy Code 
static void UpdateDataReader()
{
    using (var con = new C1D365SConnection(connectionString))
    {
        con.Open();
        // Create Update command
        var cmd = con.CreateCommand();
        cmd.CommandText = "UPDATE Accounts SET name = 'UPDNAME' WHERE name = 'Demen_Ins'";

        // Execute Update command
        int i = cmd.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Update operation successful! \n\n");
        }
    }
}

Delete Data

This example removes data by executing the Delete command.

C#
Copy Code 
static void DeleteDataReader()
{
    using (var con = new C1D365SConnection(connectionString))
    {
        con.Open();
        // Create Delete command
        var cmd = con.CreateCommand();
        cmd.CommandText = "DELETE FROM accounts WHERE name = 'Demen_Ins'";

        // Execute Delete command
        int i = cmd.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful! \n\n");
        }
    }
}