Solved

C# Application Local DB Connection String

Posted on 2016-11-16
23
950 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 5
  • +1
23 Comments
 
LVL 51

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 51

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:aj85
ID: 41889785
Hello Vitor,

It returned to the DB not found error.

Regards,
aj85
0
 
LVL 51

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 42

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 51

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 51

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 42

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 42

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 42

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 42

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 51

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

626 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