MVC2 and mySQL database

Question

I'm following the example from the ASP.NET MVC 2 Framework book (Steven Sanderson), except instead of using SQLServer, I'm using mySQL.

public class ProductsController : Controller
{
    private IProductsRepository productsRepository;
    public ProductsController()
    {
        // Temporary hard-coded connection string until we set up dependency injection
        string connString = "server=xx.xx.xxx.xxx;Database=db_SportsStore;User Id=dbUser;Pwd=xxxxxxxxx;Persist Security Info=True;";
        productsRepository = new SportsStore.Domain.Concrete.SqlProductsRepository(connString);

    }
    public ViewResult List()
    {
        return View(productsRepository.Products.ToList());
    }
}

When I run the project I get the following error:

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)

I have downloaded the MySQL .Net connector and in the Server Explorer I was able to view the tables on that DB. I also have made sure that the DBs on the server allows for remote connections.

I am creating and running the code locally via VS2010 and the MySQL DB is on hosted server.

What do I have to do to get this MVC2 example to function for MySQL?

Update:

I was playing around with the code while waiting for an answer and I updated the ProductsController to the following:

using MySql.Data.MySqlClient;
            string connString = "server=xx.xx.xxx.xxx;Database=db_SportsStore;User Id=dbUser;Pwd=xxxxxx;";
            var connection = new MySqlConnection(connString);

            productsRepository = new SportsStore.Domain.Concrete.SqlProductsRepository(connection);

Then in the repository:

public SqlProductsRepository(MySql.Data.MySqlClient.MySqlConnection connection)
{
    connection.Open();
    productsTable = (new DataContext(connection)).GetTable<Product>();
    connection.Close();
}

Now I get a totally different error:

{"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[t0].[ProductID], [t0].[Name], [t0].[Description], [t0].[Price], [t0].[Category]' at line 1"}

I should also mention... This example is using LINQ to SQL. I'm not sure if that matters or not. Please advise.

Kevin LaBranche · Accepted Answer · 2011-04-30 17:38:09Z

2

A couple of thoughts come to mind.

The connection string you are using is the connection string if you are connecting to a SQL Server DB instead of a MySQL .Net Connector connection string. You're going to have to change the connection string example from the book to what MySQL needs. For example, MySQL doesn't have a Trusted Connection option. (http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html)
In your SQLProductsRepository you'll need to use the MySQLConnection Object and so forth instead of a SQLConnection object if used. I think the book used Linq To SQL or Linq to Entity Framework. Either way you'll have to modify the code to use the MySQL... objects or modify the model to hit your MySQL if it's Entity Framework. If you already did this then the connection string in it will give a good idea of what you need in #1.

http://dev.mysql.com/doc/refman/5.1/en/connector-net-tutorials-intro.html

EDIT - With your update...

MySQL doesn't have the same syntax as SQL Server sometimes. I am assuming that is what this error is pointing to.

On the MySQL Connector connection string, it has an option to use SQL Server Syntax called SQL Server Mode. Set this to true and see if this works....

Something like:

Server=serverIP;Database=dbName;Uid=Username;Pwd=Password;SQL Server Mode=true;

edited Apr 30, 2011 at 17:38

answered Apr 30, 2011 at 17:22

Kevin LaBranche

21.1k5 gold badges55 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

webdad3 Over a year ago

While I was waiting I changed it up a little (see my updated question). Now I get a different error.

webdad3 Over a year ago

That was it... After I changed it to a mySQL connector and then added the SQL Server Mode = true then it worked. Thank you very much.

Collectives™ on Stack Overflow

MVC2 and mySQL database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related