DataConnector
ADO.NET provider for Dynamics 365 Sales / CRUD Operations
In This Topic
    CRUD Operations
    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

    DbDataReader class can be used to fetch data in subset increments as required.

    The DbDataReader can fetch data quicker than the C1D365SDataAdapter as it retrieves data in pages. When you read data from 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 DbDataReader.

     

    Create Data

    The example adds new records by executing the Insert command.

    C#
    Copy Code
    using (var con = new C1D365SConnection(connstr))//Creating Connection using connection string as parameter
    {
       con.Open();
       //Create Insert Command Object
       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");
       }
    }
    

    Read Data

    This examples retrieves data by executing the Select command.

    C#
    Copy Code
    using (var con = new C1D365SConnection(connstr))//Creating Connection using connection string as parameter
    {
       con.Open();
       //Creating Read command
       var cmd = con.CreateCommand();
       cmd.CommandText = "Select  accountid, name  FROM Accounts limit 10'";
       //Execute read command and display result
       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 Update command.

    C#
    Copy Code
    using (var con = new C1D365SConnection(connstr))//Creating Connection using connection string as parameter
    {
      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 Delete command.

    C#
    Copy Code
    using (var con = new C1D365SConnection(connstr))//Creating Connection using connection string as parameter
    {
       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("Deletion Operation Successful");
       }
    }
    

    Querying With C1D365SDataAdapter

    C1D365SDataAdapter class can be used to retrieve a single result set of all the data that matches a query.

    DataAdapter uses its Fill method to fetch data from the data source. An empty DataTable instance is passed as an argument to the Fill method. When the method returns, the DataTable instance is populated with the queried data.

    The Fill method needs to retrieve all the data from data source before returning, and hence C1D365SDataAdapter is slower than the DbDataReader.

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

     

    Create Data

    The example adds new records by executing the Insert command.

    C#
    Copy Code
    using (C1D365SConnection conn = new C1D365SConnection(connstr))
    {               
      conn.Open();
      //Populate Datatable
      C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 10");
      DataTable dataTable = new DataTable();
      adapter.Fill(dataTable);//Data Table fill
      //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"));//adding Parameters
      adapter.InsertCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
      adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
      //Perform Insert Operation
      DataRow bookRow1 = dataTable.NewRow();
      bookRow1["accountid"] = Guid.NewGuid();//Datatable Rows Updated for Insertion
      bookRow1["name"] = "Rajesh";
      dataTable.Rows.Add(bookRow1);
      //Update database
      adapter.Update(dataTable);
    }
    

    Read Data

    This examples retrieves data by executing the Select command.

    C#
    Copy Code
    using (C1D365SConnection conn = new C1D365SConnection(connstr))
    {
      conn.Open();
      C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select  accountid, name  FROM Accounts where name ='Updated_Name'");
      DataTable dataTable = new DataTable();
      adapter.Fill(dataTable);
    }
    

    Update Data

    This example modifies data by executing Update command.

    C#
    Copy Code
    using (C1D365SConnection conn = new C1D365SConnection(connstr))
    {               
       conn.Open();
       //Populate Datatable
       C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 100");
       DataTable dataTable = new DataTable();
       adapter.Fill(dataTable);
       //Create Update Command
       adapter.UpdateCommand = new C1D365SCommand(conn);
       adapter.UpdateCommand = new C1D365SCommand(conn, "UPDATE accounts SET name = @upname " + "WHERE name = @name");
       adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
       adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@upname", DbType.String, "name"));//Adding Parameters
       adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
       //Perform Update Operation
       DataRow bookRow1 = dataTable.Rows[0];
       bookRow1["name"] = "Rajesh";
                   
       //Update database
       adapter.Update(dataTable);
    }
    

    Delete Data

    This example removes data by executing Delete command.

    C#
    Copy Code
    using (C1D365SConnection conn = new C1D365SConnection(connstr))
    {               
       conn.Open();
       //Populate Datatable
       C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 10");
       DataTable 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;
       //Perform Delete Operation
       DataRow bookRow1 = dataTable.Rows[3];
       bookRow1["name"] = "Rajesh";
       bookRow1.Delete();
       //Update database
       adapter.Update(dataTable);
    }