Dapper Cannot find open Connection MVC3

asp.net-mvc-3 asp.net-mvc-4 dapper mysql

Question

I've recently created a new project in MVC3 and connected a MySQL db to it without a problem. I'm able to create users and roles (using Universal Providers / SecurityGuard from NuGet). Now I'm trying to run a query using Dapper for my Edit page and get a SQL Exception:

Debug: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

YSOD:

The system cannot find the file specified.
         var connection = new SqlConnection(connString);

         connection.Open();
         return connection;
     }

Here's what I'm trying to do:

web.config:

  <connectionStrings>
     <add name="DefaultConnection" connectionString="Database=[database];Data Source=localhost;Uid=[username];Pwd=[password];" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

<membership defaultProvider="MySqlMembershipProvider">
  <providers>
    <clear />
    <add name="MySqlMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.6.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" autogenerateschema="true" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/" />
  </providers>
</membership>
<profile defaultProvider="MySQLProfileProvider">
  <providers>
    <clear />
    <add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.6.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </providers>
</profile>
<roleManager enabled="true" defaultProvider="MySqlRoleProvider">
  <providers>
    <clear />
    <add connectionStringName="DefaultConnection" applicationName="/" autogenerateschema="True" name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.6.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </providers>
</roleManager>

Db.cs:

public class Db
{
    public static IDbConnection GetOpenConnection()
    {
        var connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        var connection = new SqlConnection(connString);

        //---- Hangs Here ----//
        connection.Open();
        return connection;
    }
}

MembershipController:

var connection = Db.GetOpenConnection();
    {
            const string conn = @"SELECT id
                               FROM my_aspnet_users
                               WHERE name = @UserName";

            var result = connection.Query<UserViewModel>(conn, new { userName }).Single();

            viewModel.Id = result;
        }

I never make it to the controller, as it hangs when trying to open the connection. I'm new to working with MySQL so it may very well be something I've overlooked, or forgotten to add. Thanks!

Accepted Answer

Figured out my issue. I was trying to use

var connection = new SqlConnection(connString);

which is not supported with MySQL. Instead I found Connecting an ASP.NET MVC application to MySQL which used the proper connector.

using (var connection = new MySqlConnection(ConnectionString))
using (var cmd = connection.CreateCommand())
{
connection.Open();
cmd.CommandText = "SELECT name FROM foo;";
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        string name = reader.GetString(reader.GetOrdinal("name"));
        // TODO: do something with the name ...
    }
}

}


Popular Answer

On the face on it the code looks fine, I suspect this is a problem with the connection string. Based on a recent MySql project I worked on the connection string is slightly different. Can you try the following (Notice Data Source changed to server)

<connectionStrings>
     <add name="DefaultConnection" connectionString="Server=localhost;Database=[database];Uid=[username];Pwd=[password];" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Some more MySql ConnectionStrings here : http://www.connectionstrings.com/mysql




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why