0

I am programming a Windows application that has a local database. I run the program with C# and SQL Server. Everything in the program works correctly, but when I create setup file and transfer it to other systems, I get a connection string error, which is normal (I think) because the server name may be different on each system. In the data source section for the connection string (I found all this solution in internet):

  1. I used . but it didn't work.
  2. I used sqlexpress/. but it didn't work either.
  3. I used the IP and gave it 127.0.0.1 but it didn't work. Then I realized that if you want to use IP, you must allow the use of tcp/ip in the SQL settings and allow SQL to listen to the port you want.

When I created the installation file with Visual Studio, I included .NET Framework 4.5 and SQL Server 2019 Express as prerequisites. Well then, the issue is that most of my users here do not have any expertise in the field of software and working with computers, so you want to tell them to go and make the SQL Server or connection string settings, I want it to be done automatically during installation without user intervention and involving them The complexity of the program to be installed. How should I do this?

My error on another system is:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I tried these 3 connection strings in app.config file right now:

<connectionStrings>
    <add name="CS" 
         connectionString="data source=.;initial catalog=mydbname; integrated security=true" 
         providerName="system.data.sqlclient"/>
</connectionStrings>

<connectionStrings>
    <add name="CS" 
         connectionString="data source=sqlexpress/.;initial catalog=mydbname; integrated security=true" 
         providerName="system.data.sqlclient"/>
</connectionStrings>

<connectionStrings>
    <add name="CS"  
         connectionString="data source=127.0.0.1,20123;initial catalog=mydbname; integrated security=true" 
         providerName="system.data.sqlclient"/>
</connectionStrings>

How can I fix my problem and create an application and setup file that work in all of system and Without involving the user with the complexities behind the background?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sadegh
  • 103
  • 5
  • 1
    First of all - if you install SQL Server **Express** with all the defaults, your instance name will be `.\SQLEXPRESS` (dot first, not last). Also - not sure but the `providerName` value in the connection string seems off - should respect the proper capitalization: `providerName="System.Data.SqlClient"`.... – marc_s Jun 11 '23 at 04:57
  • Does this answer your question? [Sharing SQL Server Between Multiple System](https://stackoverflow.com/questions/7518847/sharing-sql-server-between-multiple-system) – Son of Man Jun 11 '23 at 05:02
  • Also, characters matter. The convention is to use a backslash character to seperate the server name or address from the instance name, so use `.\SQLEXPRESS` as opposed to `./SQLEXPRESS`. – AlwaysLearning Jun 11 '23 at 05:21
  • 1
    "has a local database" do you mean LocalDB or do you mean SQL Server Express or something else? Is the database installed on the same system as the application? – Charlieface Jun 11 '23 at 06:31
  • 1
    If that's going to be a local database, only and exclusive to your app then SQL Server is a rather poor choice. And you may want to consider something like SQLite. You may also want to consider licensing issues with your current choice as soon as you actually distribute your app. – Fildor Jun 11 '23 at 07:17
  • A database can be attached to a server of a localDb. It can't be both. You connection string contains "data source" which indicates the database is attached to a server and not a localDb. In both cases the database is a MDF file (log file LDF). A PC can also have multiple instances of the SQL Server and the Data Source will be different for each instance. When the Data Source start with a period it means it is on the local PC. The Data Source and start with the PC name or can be an IP address. – jdweng Jun 11 '23 at 12:11
  • charlieface => I use sql server 2018 to create my project and in setup file i use sql express 2019 as prerequires. and the database is completely local and in the same system as the program installed. fildor => the setup project of VS 2019 has express 2019 for prerequires so i do not think this is problem. (i am not sure). jdweng => so how to solve my problem? i still try the different way to find solution. – sadegh Jun 12 '23 at 05:03
  • @jdweng thanks for all of your suggestion. I search it more and find solution. if we have local database on same system of program, we can use "Microsoft® SQL Server® Compact 4.0 SP1". this is free and copy the instance of database in project or installed path folder. also you can use this for portable app. but now I have some new problem i asked in separated post. please help me on that too. the link is: https://stackoverflow.com/questions/76487329/how-to-choose-guid-as-the-primary-key-in-sql-compact – sadegh Jun 16 '23 at 06:06
  • It is better to use a database attached to a SQL Server than to use a file on SQL Compact. SQL Server is much more efficient. SQL Server the express version is also free and supports multi-user, while compact is just a file. – jdweng Jun 16 '23 at 09:12
  • @jdweng thanks, But my end users don't have any knowledge in the field of software and I don't want to involve them in the complexities of installing and configuring Skras, but Compact does not have this problem and is easy to install and does not involve the user. Am I right or is there another way to solve this challenge? – sadegh Jun 16 '23 at 11:57
  • The issue is performance. If the database get to be large or multiple users need access at the same time then you should use SQL Server. – jdweng Jun 16 '23 at 12:16

0 Answers0