ConnectionString with DAPPER ORM not working

Question

I created following Repository file

using Dapper;
using System.Configuration;

namespace ProjectName.Repository
{
    public class SMRTRepository : IDashboard
    {
        public SqlConnection con;
        //To Handle connection related activities
        private void connection()
        {
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            con = new SqlConnection(constr);
        }

        .........
    }
}

Then In that project created a App.Config File to define connection string,

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=USER-PC;Initial Catalog=DBNAME;User ID=sa;Password=****;Integrated Security=True;" providerName="System.Data.sqlclient"/>
  </connectionStrings>
</configuration>

but then when I run this , its not connecting to DB , In Server Explorer window in Visual Studio I can see connection string connecting to DB without Red icon

whats wrong in my approach

Solution

You don't specify both Integrated Security = True and an Sql User ID. As explained by MSDN on the Integrated Security option line

If User ID and Password are specified and Integrated Security is set to true, the User ID and Password will be ignored and Integrated Security will be used.

This means that you have to have enabled the Windows Integration when you installed Sql Server and your current windows user is listed between the accepted users of that server.

Another possible problem is caused by some kind of mishandling of the global connection object. As a good practice it is better to avoid global disposable objects like a connection.

I would change your class to be

namespace ProjectName.Repository
{
    public class SMRTRepository : IDashboard
    {
        private SqlConnection OpenConnection()
        {
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            SqlConnection con = new SqlConnection(constr);
            con.Open();
            return con;
        }
        ....

        public IEnumerable<Something>Select(string queryFilter)
        {
            using (SqlConnection cnn = this.OpenConnection())
            {
                return cnn.Query<Something>(queryFilter);
            }
        }
    }
}

Of course you could also separate the two methods in different classes and use the first method as a static method that serves every other repository classes that you need to build. In this way there are no global objects around and the using block ensures a proper cleanup of your disposable objects also in case of exceptions.

Comments

[Steve]

And why this answers the question?

[Kelum]

So whats the connections string should use for your approach <add name="ConnectionString" connectionString="Data Source=USER-PC\SQLEXPRESS;Initial Catalog=DBNAME; User ID = sa; Password = ****;Integrated Security=SSPI" providerName="System.Data.SqlClient" />

[Steve]

I would just remove the User ID and Password part. But if you have installed locally on your PC the Sql Server Express that connection should work as expected. I would try to use the debugger to check if every part of this code returns the expected result. In particular the part where we try to read the connectionstring from the configuration file.