SQL Connection to CRM Database

Dynamics CRM has very efficient web services using which you can retrieve data such as Organization service Or WebApi. However if you still need to retrieve data from database here is a quick code helper – which i am using in a console application.

//Initiate the connection
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServerConnection"].ConnectionString))
{
//create a command variable
using (var command = connection.CreateCommand())
{
//add query to your command
command.CommandText = "your SQLquery goes here";

// open the connection first and then execute the query using Execute Reader
connection.Open();
result = command.ExecuteReader();

here is the app.config which i have used , i am using the windows authentication hence you donot need to mention credentials.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="SQLServerConnection" connectionString="Server=.;Database=events.website;Trusted_Connection=True;"/>
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2"/>
</startup>
</configuration>

By Now you will have your dataset in “result”; here is another piece of code to read the data:

 if (result.HasRows)
{
 while (result.Read())
{
string contactid= result["columnName"].ToString();
string contact = result["columnName"].ToString();
DateTime createdonDate= (DateTime)result["createdon"];
string type = result["columnName"].ToString();
}

}
// close the result
 if (result != null)
result.Close();

I hope this helps!

Cheers!