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

     

     

    Copyright © GrapeCity, Inc. All rights reserved.

    Product Support Forum | Print this page