How to Set an SQL Server Connection String

Sql Server connection string in client machine

So you host the application and the database server on the same machine? In this case you can just connect to the localhost instance of the SQL Server.

There are a lot of examples which can be found on Connectionstrings.com.

Most common will be Server=localhost;Database=myDataBase;User Id=myUsername;
Password=myPassword; or for integrated security (preferred in this situation) Server=localhost;Database=myDataBase;Trusted_Connection=True;.

If you do this, you might also want to check out the possibility to encrypt/secure your 'secrets' of the configuration file.
Some details on the matter can be found on these sites for example:

• https://web.archive.org/web/20211020203213/https://www.4guysfromrolla.com/articles/021506-1.aspx
• https://msdn.microsoft.com/en-us/library/dx0f3cf2(v=vs.85).aspx

Connection string for SQL Server ( local database )

Part of your problem is you have a trailing '.' in your IP address. Remove that like so:

"Data Source=127.0.0.1;" +
"Initial Catalog=Filter;" +
"Integrated Security=SSPI;";

Also, I would strongly suggest that you wrap your connection object in a using statement like this:

using (SqlConnection conn = new SqlConnection())
{ 
   conn.ConnectionString =
   "Data Source=127.0.0.1.;" +
   "Initial Catalog=Filter;" +
   "Integrated Security=SSPI;";

    conn.Open();
}

Lastly, define your connection in a string and pass it into your SqlConnection object when you instantiate it, like this:

string sqlConnection = "Data Source=127.0.0.1;Initial Catalog=Filter;Integrated Security=SSPI;"

using (SqlConnection conn = new SqlConnection(sqlConnection)
{
   conn.Open();
}

This approach does several things for you:

1. It makes your code much, much easier to read, and clean.
2. It ensures that your connection object will get handled by Dispose even if there is an exception thrown in the using block.
3. It is just a good habit to get into early.

More on the SqlConnection class here, and more on using can be found here.

How can I define SQL server connection string inside a class and use everywhere in project?

Here is on of the (many) possible solutions:

• You can store you connection string inside a configuration file (app.config if you're developing a desktop application or a DLL or web.config if you are developing a web service or a web application). Sample .config file:
  

  <?xml version="1.0" encoding="utf-8" ?>
  <configuration>
      <connectionStrings>
          <add name="TestCfg" connectionString="Data Source=.;Initial Catalog=xxx;IntegratedSecurity=True" providerName="System.Data.SqlClient" />
      </connectionStrings>
  </configuration>
  

• Then you can implement a DAL (Data access layer) that implements all DB-related operations (see here or here for more information)

• Finally your application should always access the DB using methods exposed by the DAL

SQL Server connection string in Appsettings.json + .net Core 3.1

thanks for your help and advice, i might have just found the solution and am able to connection the SQL database successfully now, the problem was as following:

The following package was installed on the solution:

Microsoft.Data.Sqlite.SqliteConnectionStringBuilder

in Startup.cs the following line was used in the ConfigureServices(IServiceCollection services):

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlLite(Configuration.GetConnectionString("DefaultConnection")));

However after research i found out that i should rather use the following line:

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Which was not accessible due the following package missing:

Microsoft.EntityFrameworkCore.SqlServer

After installing this package via package Manager using the following command:

Install-Package Microsoft.EntityFrameworkCore.SqlServer

I was able to access the SQL server using the following connection string:

  "ConnectionStrings": {
    "DefaultConnection": "Server=[servername];Database=[databasename];Persist Security Info=True;User ID=[username];Password=[password];MultipleActiveResultSets=True;"
  },

For safety, as Christian pointed out, I removed also the other connectionstring references.

.Net Web app SQL Server Connection String Linux Issue

This will only work if

1. SQL Server has TCP/IP enabled, and the Browser Service running
2. The Windows firewall allows UDP port 1434 and TCP on whatever port the SQL Server instance is listening on.
3. The client can resolve the hostname

Or you could use the SQL Server Configuration Manager to set the SQL Server instance to listen on a fixed port (1433 is the default), and specify that instead of the instance name in your connection string.

How to get the connection String from a database

The easiest way to get the connection string is using the "Server Explorer" window in Visual Studio (menu View, Server Explorer) and connect to the server from that window.

Then you can see the connection string in the properties of the connected server (choose the connection and press F4 or Alt+Enter or choose Properties on the right click menu).

Advanced connection string settings: when creating the connection, you can modify any of the advanced connection string options, like MARS, resiliency, timeot, pooling configuration, etc. by clicking on the "Advanced..." button on the bottom of the "Add connection" dialog. You can access this dialog later by right clicking the Data Connection, and choosing "Modify connection...". The available advanced options vary by server type.

If you create the database using SQL Server Management Studio, the database will be created in a server instance, so that, to deploy your application you'll have to make a backup of the database and deploy it in the deployment SQL Server. Alternatively, you can use a data file using SQL Server Express (localDB in SQL Server 2012), that will be easily distributed with your app.

I.e. if it's an ASP.NET app, there's an App_Datafolder. If you right click it you can add a new element, which can be a SQL Server Database. This file will be on that folder, will work with SQL Express, and will be easy to deploy. You need SQL Express / localDB installed on your machine for this to work.

---

Related Topics