Solved

C# Application Local DB Connection String

Posted on 2016-11-16
23
180 Views
Last Modified: 2016-11-17
I have a C# application that I want to have connect to a local DB that is going to be located in a specific path on the users system.  The application runs fine if I have it pointed to the full SQL Server environment where the DB is located.  However if I change the configuration file, and try pointing the application to a local path I get an error that the DB is not found.  Below is my connection string from the connection properties:


----------------

<connectionStrings>
        <add name="BAppLocal.Properties.Settings.MyConnectionString"
            connectionString="Data Source=C:\BAppLocal\M3\DB\TestDatabase.mdf;Initial Catalog=TestDatabase;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" />
    </connectionStrings>

--------------------------

Please advise, as I am not sure where my issue is happening.

Regards,
aj85
0
Comment
Question by:aj85
  • 9
  • 6
  • 5
  • +1
23 Comments
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41889689
Did you check MSDN for this? In their article they show how to do it. So based on that you should have something like:
<connectionStrings>
         <add name="BAppLocal.Properties.Settings.MyConnectionString"
             providerName="System.Data.SqlClient"
                 connectionString="Data Source=(LocalDB)\v11.0;AttachDbFileName=|DataDirectory|C:\BAppLocal\M3\DB\TestDatabase.mdf;Initial Catalog=TestDatabase;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" />
     </connectionStrings>

Open in new window

0
 

Author Comment

by:aj85
ID: 41889709
Hello Victor,

Thanks for the response.  I tried your suggestion but now get the attached error when the application is executed.

Regards,
aj85
Error.PNG
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41889722
It says the given path format is not supported. Try like this:
<connectionStrings>
         <add name="BAppLocal.Properties.Settings.MyConnectionString"
             providerName="System.Data.SqlClient"
                 connectionString="Data Source=(LocalDB)\v11.0;AttachDbFileName=|DataDirectory|\TestDatabase.mdf;Initial Catalog=TestDatabase;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" />
     </connectionStrings>

Open in new window

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:aj85
ID: 41889785
Hello Vitor,

It returned to the DB not found error.

Regards,
aj85
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41889791
I don't have access to your system so you need to troubleshoot this by your own.
Confirm the LocalDB instance name, database name and location and the user permissions.
0
 
LVL 41

Accepted Solution

by:
pcelba earned 250 total points
ID: 41889809
You cannot point to a disk file when using SQL Server.  You have to install SQL Server backend engine and point to its name and (optionally) database name in connection string, e.g.:
<connectionStrings>
	<add name="ConnName" connectionString="Data Source=.\SQL2012;Initial Catalog=YourDBname;Integrated Security=True;App=WhatEverYouNeedHere;"/>
</connectionStrings>

Open in new window

The  ".\" in Data Source points to your local computer and "SQL2012" means the SQL Server engine name assigned during the installation. (You can install more SQL Server engines on one computer.) To use encrypted connection on local computer is not necessary. The App name appears in the connection description in SQL Server.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41889827
You cannot point to a disk file when using SQL Server.
This is LocalDB and by the MSDN article is how it connects to the database.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41889888
On the users system do you have the Microsoft SQL Server 2016 Express? Without this you will not have localDb and therefore will not be able to connect.
0
 

Author Comment

by:aj85
ID: 41889902
Hello Fernando,

Thanks for your response.  The users system has MS SQL 2014 installed.

Regards,
aj85
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41889948
To your statement, "The users system has MS SQL 2014 installed.", OK but to use localDb you need the Express version if I am not mistaken. From the link I previously posted
LocalDB is a lightweight version of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. Use this if you need a simple way to create and work with databases from code. It can be bundled with Application and Database Development tools like Visual Studio and or embedded with an application that needs local databases.
0
 

Author Comment

by:aj85
ID: 41889967
Okay Fernando, so you are saying to install the localized version of SQL Server and use that instead of the full SQL Express?  I just want to make sure I am clear before making this change.  Based upon yours and others feedback, I take it that the connection string would point to: "Data Source=(localdb)\" if I am to use the LocalDB version?

Regards,
aj85
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41889973
Are you telling that you're not sure that you've LocalDB installed?
As Fernando stated, LocalDB is available with MSSQL Express version.
0
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 125 total points
ID: 41889999
Hi aj85;

Yes and as far as this part of the connection string you posted, "Data Source=(localdb)\", it should be "Data Source=(localdb)\vXX.X" where XX.X will be determined by which locals version gets installed.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 41890001
I've posted connection string which you can use in SQL 2014. Everything you need to do is to attach the database file to SQL Server and update the names in connection string. Of course, to install SQL 2016 which supports LocalDB is also an option.
0
 

Author Comment

by:aj85
ID: 41890009
Hello Vitor,

No, I have version 2014 installed on the user machine as indicated.  The issue is with the connection string parameters, as it is not finding the DB.

Regards,
aj85
0
 

Author Comment

by:aj85
ID: 41890026
Hello Pcelba,

When I try the connection string as you have indicated I get the attached error message:

<connectionStrings>
      <add name="BAppLocal.Properties.Settings.MyConnectionString" connectionString="Data Source=.\SQL2014;Initial Catalog=TestDatabase;Integrated Security=True;App=BAppLocal;"/>
</connectionStrings>

Regards,
aj85
ErrorII.PNG
0
 
LVL 41

Expert Comment

by:pcelba
ID: 41890293
How did you name the installed SQL Server instance?
The default instance name is MSSQLSERVER and you don't need to use it and place simply  Data Source=(local)
To list all SQL Server instances use the Installed Instances page of the SQL Server Installation Wizard or look at SQL Server Service name which contains the instance name.
0
 

Author Comment

by:aj85
ID: 41890316
The instance name is the PC name, so are you saying that it should be this:

Data Source=(local);

Instead of:
Data Source=.\SQL2014;

Regards,
aj85
0
 
LVL 41

Expert Comment

by:pcelba
ID: 41890370
To use just (local) as the Data Source requires to install the SQL Server as the default instance.

To have the SQL Server instance name equal to computer name does not sound like a good idea. You have to use different connection string on each computer then...

Did you look at the list of Windows Services on the computer?  SQL Server instance name is in parenthesis right after the SQL Server service name.
0
 

Author Comment

by:aj85
ID: 41890422
Hello,

It appears that on the user system, there are 2 versions of MS SQL Server that are running in services, both 2014.  The first instance is "MSSQLSERVER", the other is "SQLEXPRESS".

Regards,
aj85
0
 
LVL 41

Expert Comment

by:pcelba
ID: 41890553
OK, then you must use the one to which is the database file attached. You may find it when you connect to the SQL Server via SSMS.
0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 41890978
Wondering why you opened this question referring to a LocalDB instance.
Connecting to a SQL Server instance is totally different so we already lost a couple of days trying to give you a solution that you wouldn't be able to use.

Based in your code you can use the following to connect to the default SQL Server instance:
<connectionStrings>
         <add name="BAppLocal.Properties.Settings.MyConnectionString"
             providerName="System.Data.SqlClient"
             connectionString="Data Source=YourServerName;Initial Catalog=TestDatabase;Integrated Security=True;Connect Timeout=30" />
</connectionStrings>

Open in new window

For the EXPRESS edition is mostly the same and you'll only need to add the SQLEXPRESS instance:
<connectionStrings>
         <add name="BAppLocal.Properties.Settings.MyConnectionString"
             providerName="System.Data.SqlClient"
             connectionString="Data Source=YourServerName\SQLEXPRESS;Initial Catalog=TestDatabase;Integrated Security=True;Connect Timeout=30" />
</connectionStrings>

Open in new window

0
 

Author Closing Comment

by:aj85
ID: 41892114
Thank you all for your assistance, I finally solved the issue with a combination of all of your suggestions.  As it turns out you cannot simple point to the directory, so I upgraded to 2016, and it works great with the local copy of the DB.  No need for the full SQL Express installation.

Thanks again everyone.

Regards,
aj85
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question