Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1847
  • Last Modified:

C# Application Local DB Connection String

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
aj85
Asked:
aj85
  • 9
  • 6
  • 5
  • +1
3 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
aj85Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
aj85Author Commented:
Hello Vitor,

It returned to the DB not found error.

Regards,
aj85
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
pcelbaCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Fernando SotoCommented:
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
 
aj85Author Commented:
Hello Fernando,

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

Regards,
aj85
0
 
Fernando SotoCommented:
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
 
aj85Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you telling that you're not sure that you've LocalDB installed?
As Fernando stated, LocalDB is available with MSSQL Express version.
0
 
Fernando SotoCommented:
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
 
pcelbaCommented:
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
 
aj85Author Commented:
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
 
aj85Author Commented:
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
 
pcelbaCommented:
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
 
aj85Author Commented:
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
 
pcelbaCommented:
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
 
aj85Author Commented:
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
 
pcelbaCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
aj85Author Commented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 9
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now