Solved

C# Application Local DB Connection String

Posted on 2016-11-16
23
61 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 45

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 45

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
 

Author Comment

by:aj85
ID: 41889785
Hello Vitor,

It returned to the DB not found error.

Regards,
aj85
0
 
LVL 45

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 45

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 62

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 62

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

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 62

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 45

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now