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