How To Validate The Entity Framework Generated Database Connection String Before Starting An Application

Introduction 

Here we going to discuss, how to validate the entity framework generated database connection string in c# before starting an application. Now we will see these in following steps 

Step 1

Once you integrate an entity framework with your application, it will add the connection string to app.config or web.config. you can see that as below

<connectionStrings>
    <add name="CSharpCornerEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=SMKG;initial catalog=CSharpCorner;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>

now am going to make a mistake in above valid connection as below, you know that data source represents computer name or server name data source=SMKG to data source=notexist

<connectionStrings>
    <add name="CSharpCornerEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=notexist;initial catalog=CSharpCorner;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>

Now we made an invalid connect string.

Step 2: Database.Exists Method

This Database.Exists is used to Checks whether or not the database exists on the server or local system. 

Syntax

public bool Exists() 

Example

using(CSharpCornerEntities ce = new CSharpCornerEntities()) {
    try {
        bool check = ce.Database.Exists();
        Console.WriteLine(check);
    } catch (Exception ex) {
        Console.WriteLine(ex);
    }
}

Remarks

True if the database exists; false otherwise. 

Step 3 ADO.Net - SqlConnection

Another way we will use the Sqlconnection to Checks whether or not the database exists on the server or local system. To use ConfigurationManager, we need to add a reference System.Configuration.dll in our application

Example 

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CSharpCornerEntities"].ConnectionString);
try {
    con.Open();
} catch (Exception ex) {
    Console.WriteLine(ex);
}

Now run your application



You should get above error because The connection string for ADO.NET (in this case SqlConnection) doesn't take that format. You're using the one specific for Entity Framework. The ADO.NET one should be something like:

"data source=SMKG-PC\SQLSERVER;initial catalog=CSharpCorner;integrated security=True"

So, to sum it up, you need two separate connection strings, one for EF and one for ADO.NET. but without keeping separate connection string we can validate the connection string using EntityConnectionStringBuilder.

Step 4 EntityConnectionStringBuilder - ProviderConnectionString

EntityConnectionStringBuilder is a class and simple way to create and manage the contents of connection strings used by the EntityClient. We need to use the System.Data.Entity.Core.EntityClient namespace to access EntityConnectionStringBuilder. here we will see only how to get the provider connection string using EntityConnectionStringBuilder.

Example 

EntityConnectionStringBuilder entityConnection = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["CSharpCornerEntities"].ConnectionString);
SqlConnection connectionString = new SqlConnection(entityConnection.ProviderConnectionString);
try {
    connectionString.Open();
} catch (Exception ex) {
    Console.WriteLine(ex);
}

Now run your application, you can see that it provides ado.net connection string

 

 

Step 3 and 4 are will throw the exception if the connection is not valid.

Complete Code 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Entity.Core.EntityClient;
namespace CheckDBConnectionString {
    class Program {
        static void Main(string[] args) {
            using(CSharpCornerEntities ce = new CSharpCornerEntities()) {
                try {
                    bool check = ce.Database.Exists();
                    Console.WriteLine(check);
                } catch (Exception ex) {
                    Console.WriteLine(ex);
                }
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CSharpCornerEntities"].ConnectionString);
                try {
                    con.Open();
                } catch (Exception ex) {
                    Console.WriteLine(ex);
                }
                EntityConnectionStringBuilder entityConnection = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["CSharpCornerEntities"].ConnectionString);
                SqlConnection connectionString = new SqlConnection(entityConnection.ProviderConnectionString);
                try {
                    connectionString.Open();
                } catch (Exception ex) {
                    Console.WriteLine(ex);
                }
            }
        }
    }
}

I hope It's helpful