Product Highlights

The ADO.NET Data Provider for Microsoft Dynamics CRM makes it easy to access CRM data from any .NET application. Normally to accomplish such a task one would have to be fluent in both CRM SDK and FetchXML, but this data provider changes that. Just use the familiar ADO.NET objects that have been utilized for CRM to do all of the heavy lifting for you. Objects such as:

  • XrmConnection - to establish a connection to CRM
  • XrmCommand - to execute commands to CRM
  • XrmDataReader - to read from CRM
  • And more...

The data provider also has our very own SQLtoFetchXML tool built into it, so you can use SQL commands to query CRM. When using the ADO.NET Data Provider for Microsoft Dynamics CRM, developing a .NET application for CRM will be just as easy as developing for SQL.

Connecting To CRM

Connecting to CRM in your .NET application will be just as easy as connecting to a standard database. Just as in other data providers you can establish a connection at both runtime (using the connection object) and design time (using Server Explorer).

Run time:
XrmConnectionStringBuilder connStrBuilder = new XrmConnectionStringBuilder();
connStrBuilder.ServiceUri = "http://(CRMSERVER)/Your-CRM-Organization";
connStrBuilder.Domain = "YourDomain";
connStrBuilder.UserName = "YourUserName";
connStrBuilder.Password = "YourPassword";
//If you know the connection string then you do not need to use the XrmConnectionStringBuilder
XrmConnection conn = new XrmConnection(connStrBuilder.ConnectionString);
Design time:
Microsoft Dynamics CRM Connection Form

Establishing a Connection in design time is just like in SQL. Inside Visual Studio's Server Explorer, right click on "Data Connections" and select "Add Connection...". After you select "Xrm Data Source" as your data source, this form will then appear where you can enter in all of your connection details and then test your connection.

Reading From CRM

After establishing a connection, you may want to read some data. The following examples show how you can easily do this using the XrmDataReader object and XrmDataAdapter object.

Using the XrmDataReader:
XrmConnection conn = new XrmConnection(connectionString);
XrmCommand cmd = new XrmCommand("SELECT * FROM account", conn);
XrmDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine(reader[0] + " " + reader["name"]); //Use index or column name
}
Using the XrmDataAdapter:
XrmConnection conn = new XrmConnection(connectionString);
XrmCommand cmd = new XrmCommand("SELECT * FROM account", conn);
DataSet data = new DataSet();
XrmDataAdapter adapter = new XrmDataAdapter(cmd);
adapter.Fill(data);
//Bind and display data
bindingSource1.DataSource = data.Tables[0];
dataGridView1.DataSource = bindingSource1;

Writing To CRM

Not only can you read from CRM, but there is also full CRUD support. Updating data can be done by either using the XrmCommand object or the XrmDataAdapter object.

Using the XrmCommand:
XrmConnection conn = new XrmConnection(connectionString);

XrmCommand insert = new XrmCommand("INSERT INTO account (name) VALUES (John)", conn);
insert.ExecuteNonQuery();

XrmCommand update = new XrmCommand("UPDATE account SET name=Johnny WHERE name=John", conn);
update.ExecuteNonQuery();

XrmCommand delete = new XrmCommand("DELETE FROM account WHERE name=Johnny", conn);
delete.ExecuteNonQuery();
 
Using the XrmDataAdapter:
XrmConnection conn = new XrmConnection(connectionString);
XrmCommand cmd = new XrmCommand("SELECT * FROM account", conn);
DataSet data = new DataSet();
XrmDataAdapter adapter = new XrmDataAdapter(cmd);
adapter.Fill(data);
//Insert
DataRow newRow = data.Tables[0].NewRow();
newRow["name"] = "John Doe";
data.Tables[0].Rows.Add(newRow);
//Update
data.Tables[0].Rows[0]["firstname"] = "Johnny Doe";
//Delete
data.Tables[0].Rows[0].Delete();
//Save changes to CRM
adapter.Update(data);

Retrieving CRM Metadata

Metadata retrieval will prove to be a useful feature especially when working with CRM. For instance you may want to create a combo box that matches that of an optionset field in CRM. You can get metadata through the XrmConnection object as you would in any data provider, but we have also added a simple way to query CRM metadata using the XrmCommand object.

Using the XrmConnection:
XrmConnection conn = new XrmConnection(connectionString);
//Retrieves metadata on all fields inside of the account entity
DataTable metadata = conn.GetSchema("Fields", new object[] { "account" });
Using the XrmCommand:
XrmConnection conn = new XrmConnection(connectionString);
XrmCommand cmd = new XrmCommand("SELECT * FROM metadata.attribute WHERE entitylogicalname='account'");
XrmDataReader reader = cmd.ExecuteReader();
//Or fill a DataTable by using the XrmDataAdapter

Documentation

For more examples, documentation, and full walkthroughs, download the product help file from the download page.

Contact Us

If you would like to know more specific details about the toolkit, please don't hesitate to contact us.

Fulfill your Microsoft Dynamics CRM Integration needs with our SSIS Integration Toolkit