How can create dynamic connection string?

Question

3.00/5 (2 votes)

See more:

, +

Hi I Create C# windows form project and setup file for it. but I get the error after install my app on my friends system. the error 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 search and find this error come from connection string and different server name, username and password on each system. so how can create dynamic connection string to solve my problem?

What I have tried:

currently I save my connection string in App.config file.
this is my code for now:

XML

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

	</connectionStrings>

Posted 3-Jun-23 19:59pm

Member 11400059

Updated 6-Jun-23 7:11am

PIEBALDconsult

v2

Add a Solution

Comments

Member 15627495 4-Jun-23 2:47am

Hello,

the error throw could be relevant and indicating where to fix.
source ??? an IP/ a host name

https://www.connectionstrings.com/sql-server/

or maybe one parameter is wrong, or missing.

PIEBALDconsult 4-Jun-23 11:40am

Don't store connection strings.
Allow the user to specify the server and database names and possibly store those in a config file, but not the whole connection string.
I found recently that .net has a built-in facility which allows you to provide the values and it will dynamically create the connection string at run-time.
I will have to refresh my memory and get back to you.

Kelly Herald 6-Jun-23 15:16pm

Are you thinking of the SqlConnectionStringBuilder class? That is what I use in my code.

PIEBALDconsult 6-Jun-23 16:13pm

In part, yes. As in the solution I posted. But I discovered the SqlConnectionStringBuilder class only a few months ago.
Ever since I began learning ADO.net in 2002 (?) I have cobbled my own connection strings for whichever database I was using -- SQL Server (including CE), Oracle, MySQL, Teradata, Cache, Ingres, Excel via JET or ACE, etc. I don't know that all providers include a connection string builder.

Shahin Khorshidnia 5-Jun-23 19:19pm

Have you tried these:
Adding a reference to the System.Configuration assembly in your project.
In your code, replace the hard-coded connection string with a call to ConfigurationManager.ConnectionStrings["Con"].ConnectionString. This will retrieve the connection string stored in the configuration file.
Storing the connection string in the configuration file, as you are already doing.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Answer 1 · 2023-06-06T07:11:00

I recommend something along these lines:

C#

string server   = null ; // Fetch from configuration
string database = null ; // Fetch from configuration

System.Data.SqlClient.SqlConnectionStringBuilder constr =
  new System.Data.SqlClient.SqlConnectionStringBuilder()
  { { "Server"              , server   }
  , { "Database"            , database }
  , { "Integrated Security" , true     }
  } ;

System.Data.SqlClient.SqlConnection con =
  new System.Data.SqlClient.SqlConnection ( constr.ConnectionString ) ;

OriginalGriff · Answer 2 · 2023-06-03T21:00:00

There is no single string that will work for all server instances, unless it always directs to one specific server - in which case you need two things:
1) The IP address or host name of the server: mySQLServber.mydomain.com for example.
2) The server instance will need to be configured to accept remote connections - and not all are for security reasons.
In addition, you will almost certainly need actual login information: it's very unusual for remote connections to accept integrated security.

If you are trying to work to his local instance, you need to find it as part of your installation program - you cannot assume that it shares any name or IP with yours. You should also test the connection and install the DB at that point as well.

Your existing connection data assumes that there is a DB server on the current machine - if that isn't the case it will fail, but we can't tell you what to use instead! We can't even tell if he has SQL Server installed!
If you are only using the DB as a single user each time (rather than sharing data) it's probably a better idea to use a single user DB such as Access or SqLite instead of SQL Server - that way you only need to add the DB itself to your installation and the DB engine will be included in your app assemblies.

You may find this helps if you are sharing a number of DB related apps with your friend: Instance Storage - A Simple Way to Share Configuration Data among Applications[^]

How can create dynamic connection string?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0