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.

Data Reader
Data Adapter

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"); } } }

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"]}"); } } }

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"); } }

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"); } } }

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"); } } }

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");
        }
    }
}

Querying With C1D365SDataAdapter

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

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

Create Data

The example adds new records by executing the Insert command.

C#	Copy Code
static void InsertDataAdapter() { using (var conn = new C1D365SConnection(connectionString)) { conn.Open(); // Populate Datatable var adapter = new C1D365SDataAdapter(conn, "SELECT * FROM accounts LIMIT 10"); var dataTable = new DataTable(); adapter.Fill(dataTable); // Create Insert command adapter.InsertCommand = new C1D365SCommand(conn); adapter.InsertCommand.CommandText = "INSERT INTO accounts (accountid, name) VALUES (@accountid,@name)"; adapter.InsertCommand.Parameters.Add(new C1DbParameter("@accountid", DbType.Guid, "accountid")); adapter.InsertCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name")); adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None; // Insert new row DataRow AccountRow = dataTable.NewRow(); AccountRow["accountid"] = Guid.NewGuid(); AccountRow["name"] = "Rajesh"; dataTable.Rows.Add(AccountRow); // Update database adapter.Update(dataTable); } }

Copy Code

static void InsertDataAdapter()
{
    using (var conn = new C1D365SConnection(connectionString))
    {
        conn.Open();
        // Populate Datatable
        var adapter = new C1D365SDataAdapter(conn, "SELECT * FROM accounts LIMIT 10");
        var dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Create Insert command
        adapter.InsertCommand = new C1D365SCommand(conn);
        adapter.InsertCommand.CommandText = "INSERT INTO accounts (accountid, name) VALUES (@accountid,@name)";
        adapter.InsertCommand.Parameters.Add(new C1DbParameter("@accountid", DbType.Guid, "accountid"));
        adapter.InsertCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        // Insert new row
        DataRow AccountRow = dataTable.NewRow();
        AccountRow["accountid"] = Guid.NewGuid();
        AccountRow["name"] = "Rajesh";
        dataTable.Rows.Add(AccountRow);

        // Update database
        adapter.Update(dataTable);
    }
}

Read Data

This examples retrieves data by executing the Select command.

C#	Copy Code
static void ReadDataAdapter() { using (var conn = new C1D365SConnection(connectionString)) { conn.Open(); var adapter = new C1D365SDataAdapter(conn, "SELECT accountid, name FROM Accounts WHERE name ='Updated_Name'"); var dataTable = new DataTable(); adapter.Fill(dataTable); } }

Copy Code

static void ReadDataAdapter()
{
    using (var conn = new C1D365SConnection(connectionString))
    {
        conn.Open();
        var adapter = new C1D365SDataAdapter(conn,
            "SELECT accountid, name FROM Accounts WHERE name ='Updated_Name'");

        var dataTable = new DataTable();
        adapter.Fill(dataTable);
    }
}

Update Data

This example modifies data by executing the Update command.

C#	Copy Code
static void UpdateDataAdapter() { using (var conn = new C1D365SConnection(connectionString)) { conn.Open(); // Populate Datatable var adapter = new C1D365SDataAdapter(conn, "SELECT * FROM accounts"); var dataTable = new DataTable(); adapter.Fill(dataTable); // Create Update command adapter.UpdateCommand = new C1D365SCommand(conn); adapter.UpdateCommand.CommandText = "UPDATE accounts SET emailaddress1=@EmailAddress1 WHERE name=@Name"; adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name")); adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@EmailAddress1", DbType.String, "emailaddress1")); adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; // Update existing row DataRow AccountRow = dataTable.Rows[0]; AccountRow["name"] = "Rajesh"; AccountRow["emailaddress1"] = "update@email.com"; //Update database adapter.Update(dataTable); } }

Copy Code

static void UpdateDataAdapter()
{
    using (var conn = new C1D365SConnection(connectionString))
    {
        conn.Open();
        // Populate Datatable
        var adapter = new C1D365SDataAdapter(conn, "SELECT * FROM accounts");
        var dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Create Update command
        adapter.UpdateCommand = new C1D365SCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE accounts SET emailaddress1=@EmailAddress1 WHERE name=@Name";
        adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
        adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@EmailAddress1", DbType.String, "emailaddress1"));
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        // Update existing row
        DataRow AccountRow = dataTable.Rows[0];
        AccountRow["name"] = "Rajesh";
        AccountRow["emailaddress1"] = "update@email.com";

        //Update database
        adapter.Update(dataTable);
    }
}

Delete Data

This example removes data by executing the Delete command.

C#	Copy Code
static void DeleteDataAdapter() { using (var conn = new C1D365SConnection(connectionString)) { conn.Open(); // Populate Datatable var adapter = new C1D365SDataAdapter(conn, "SELECT * FROM accounts LIMIT 10"); var dataTable = new DataTable(); adapter.Fill(dataTable); // Create Delete command adapter.DeleteCommand = new C1D365SCommand(conn); adapter.DeleteCommand.CommandText = "DELETE FROM accounts WHERE name = @name"; adapter.DeleteCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name")); adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None; // Delete a row DataRow AccountRow = dataTable.Rows[3]; AccountRow["name"] = "Rajesh"; AccountRow.Delete(); // Update database adapter.Update(dataTable); } }

Copy Code

static void DeleteDataAdapter()
{
    using (var conn = new C1D365SConnection(connectionString))
    {
        conn.Open();
        // Populate Datatable
        var adapter = new C1D365SDataAdapter(conn, "SELECT * FROM accounts LIMIT 10");
        var dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Create Delete command
        adapter.DeleteCommand = new C1D365SCommand(conn);
        adapter.DeleteCommand.CommandText = "DELETE FROM accounts WHERE name = @name";
        adapter.DeleteCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

        // Delete a row
        DataRow AccountRow = dataTable.Rows[3];
        AccountRow["name"] = "Rajesh";
        AccountRow.Delete();

        // Update database
        adapter.Update(dataTable);
    }
}