Dynamics 365 / Dataverse access SQL Server database from C#

This article talks about the following topic : Dataverse access SQL Server database C# (Dynamics 365/CDS)
This is to follow up on our article on how to access the Dynamics 365 SQL Server database from SQL Server Management Studio.

Prerequisite

In fact, you can start from our previous article in order to configure correctly your environnement then continue here. 

So let's say that you can successfully run this query on your SSMS : 

And now you would like to run the same query by using C#. 

And now with C# ?

This is very simple, you just have to use the famous SqlConnection class of System.Data.SqlClient .Net Library. But please not some important point that I met during my tests (explained at the end of the article). 

The C# code is : 

var connetionString = "Server=<servername>.crm4.dynamics.com,5558;Authentication=Active Directory Password;Database=<dbname>;User Id=<useremail_upn> ;Password=<password>;";
var sql = "select top 10 contactid from contact";
var connection = new SqlConnection(connetionString);
try
{
      connection.Open();
      var command = new SqlCommand(sql, connection);
      var dataReader = command.ExecuteReader();
      while (dataReader.Read())
      {
            Console.Write(dataReader.GetValue(0) + Environment.NewLine);
      }
      dataReader.Close();
      command.Dispose();
      connection.Close();
}

The result will be 

That's it !  As you can see now you can call CDS / Dataverse / Dynamics 365 SQL Data from your C# code ! 

Considerations

This code works thanks to the ADAL libraries for SQL which you can install here. The Lib will support the Authentication=Active Directory Password that is on the connectionstring.

While testing I encountered an error saying: Unable to load adalsql.dll
After some research I noticed that my version of ADAL for SQL was not the right one.
You will simply have to remove the component installed on your computer and install the one from the link below.

The one to remove : 

Conclusion

This new functionality will allow you to further extend your possibilities for integrating your systems. By now being able to rely on the power of the SQL Server engine. That's very good news. Thanks Mircosoft.

Dataverse access SQL Server database C#