CODEWITHSUNDEEP
c#asp.net-mvcconnection-stringdapper
Kelum
Kelum

Reputation: 1775

ConnectionString with DAPPER ORM not working

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

Upvotes: 2

Views: 16120

Answers (2)

Steve
Steve

Reputation: 216288

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.

Upvotes: 3

M.K
M.K

Reputation: 218

Below is a sample code block from one of my project. Its working fine for me. Please refer

using (IDbConnection connection = new SqlConnection(ConnectionString))
        {
            // Query
            string query = "Query";

            DynamicParameters dp = new DynamicParameters();
            dp.Add("@userName", userName, DbType.AnsiString);

            return connection.Query<Model>(query, commandType: CommandType.StoredProcedure, param: dp);
        }

Upvotes: -1

Related Questions