Connection string ASP.NET LocalDB

Question

I currently have a problem where I can't connect a database on my server but it works fine when running locally. I am using this connection string in the controller:

@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='|DataDirectory|\Test.mdf';";

Controller code:

namespace PathTest.Controllers
{
    public class HomeController : Controller
    {
        string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='|DataDirectory|\Test.mdf';";

        public ActionResult Index()
        {
            DataTable daTbl = new DataTable();

            using(SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                sqlCon.Open();

                SqlDataAdapter sqlDa = new SqlDataAdapter("SELECT * FROM Numbers", sqlCon);
                sqlDa.Fill(daTbl);

            }

            return View(daTbl);
        }
    }
}

Error when running on server:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Specified LocalDB instance name is invalid.)

Any help would be appreciated

A LocalDB doesn't exist until you install the libraries and even after that you need to give to the IIS worker process the permissions to use that database file. — Steve
Why don't you load the database to the server and connect to it instead of always attaching it like this? I think you should find some better examples of using databases in mvc applications. — Sean Lange

Daniel Cruz Daniel Cruz · Accepted Answer · 2018-01-17T21:19:24

First of all, I believe that you don't want to run the localdb on production server. You should deploy, if not did yet, a SQL Server instance on your server, so the connection string should be:

"Server=[Server IP/Name];Database=MSSQLLocalDB;User Id=[Some User Created];Password=[Password of User Created];"

If I'm wrong, and you want the localdb on server, you need to check if the SQL Server Express Service is running.

If so, check if the database file exists, if not, you can create an local database for express instance with the tool sqllocaldb.

And for the last, you can keep the two connection strings, using the Configuration Manager by the .config files (you can use the local db on Debug profile and the server db on Release profile).

I hope it helps you!

Connection string ASP.NET LocalDB

1 Answers