asp.net connection string problem in web.config

Question

I have a little problem with my recent project. I have a connection string in my web.config but i'd like to access it in my sql specific class.

My connection string is looks like this:

"Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-Joblication-20180902120147.mdf;Integrated Security=True"

It's stored in my web.config file.

My problem is that the default asp.net functions can access this database but i'd like to store other data in the database so i tried to access it with SqlConnection class. I set the ConnectionString property of the SqlConnection object:

SqlConncetion connection = new SqlConnection()
connection.ConnectionString = "Data Source=(LocalDb)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\aspnet-Joblication-20180902120147.mdf;Integrated Security=True";

But i get this error everytime:

System.Data.SqlClient.SqlException: 'An attempt to attach an auto- named database for file *.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.'

I replaced the name of the mdf file with a '*' so it is shorter and easily readable.

If i understand this then my .mdf file is already attached to the MSSQLLocalDB so i should connect to the MSSQLLocalDB and i should be able to access the .mdf file somehow, right?

When i'm trying this:

connection = new SqlConnection();
connection.ConnectionString = "Data Source=(LocalDb)\\MSSQLLocalDB;Integrated Security=True";

Then it seems ok cause the connection is working now but my queries don't. My queries are trying to get data from the .mdf file's tables but the .mdf file is not specified in this connection.

So how can i specify it?

Answer 1

First in your web.config update the connection string as exactly as follows:

<add name="DefaultConnection"
    providerName="System.Data.SqlClient"
    connectionString="Data Source=(LocalDb)\\MSSQLLocalDB;AttachDbFileName=|DataDirectory|\aspnet-Joblication-20180902120147.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True" />

Then in the code:

// Don't hard coded the connection string here. Get it from web.config as follows
string connectionString = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (var connection = new SqlConnection(connectionString))
{
   connection.Open();
   // Do your necessary staffs here.
}

Answer 2

Add in your Web.config

<add name="Connectionstring"
providerName="System.Data.SqlClient"
connectionString="Data Source=(localdb)\ProjectsV13;Initial Catalog=TestDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" />

---

 class Program
{
    private readonly string _connectionString;
    public Program()
    {
        _connectionString = ConfigurationManager.ConnectionStrings["Connectionstring"].ConnectionString;
    }
    static void Main(string[] args)
    {

        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            // Do your logic here.
        }
    }
}

Answer 3

In your code portion add database name.I already implemented this way.

"Data Source=(LocalDb);Initial Catalog=databasename;Integrated Security=True;"