How to get the connection String from a database

Question

I have created a database with SQL Server Management Studio, I would like to now use it in my C# application. I need the connection string?

Where can I find the connection string, and where is my database stored?

Do I have to publish it or something like that, or is it in my documents somewhere?

using (var conn = new SqlConnection("your connection string to the database"))

How do I obtain the connection string? Where can I find the connection string to copy paste into the above section?

How to I publish my database so that Visual Studio can pick it up? Then I can just pull the connection string of there?

Answer 1

put below tag in web.config file in configuration node

 <connectionStrings>
<add name="NameOFConnectionString" connectionString="Data Source=Server;Initial Catalog=DatabaseName;User ID=User;Password=Pwd"
  providerName="System.Data.SqlClient" />

then you can use above connectionstring, e.g.

SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["NameOFConnectionString"].ToString();

Answer 2

A very simple way to retrieve a connection string, is to create a text file, change the extension from .txt to .udl.

Double-clicking the .udl file will open the Data Link Properties wizard.

Configure and test the connection to your database server.

Close the wizard and open the .udl file with the text editor of your choice and simply copy the connection string (without the Provider=<driver>part) to use it in your C# application.

sample udl file content

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=YOURDATABASENAME;Data Source=YOURSERVERNAME;Initial File Name="";Server SPN=""

what you need to copy from it

Integrated Security=SSPI;Initial Catalog=YOURDATABASENAME;Data Source=YOURSERVERNAME;

If you want to specify username and password you can adopt from other answers.

Tutorial: https://teusje.wordpress.com/2012/02/21/how-to-test-an-sql-server-connection/

Answer 3

SqlConnection con = new SqlConnection();
con.ConnectionString="Data Source=DOTNET-PC\\SQLEXPRESS;Initial Catalog=apptivator;Integrated Security=True";

Answer 4

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.

Answer 5

The sql server database will be stored by default in the following path

<drive>:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL\Data\

, where <drive> is the installation drive and X is the instance number (MSSQL.1 for the first instance of the Database Engine). Inorder to provide the connection string you should know what is the server name of the sql server database, where you have stored followed by instance of the database server.

Generally the server name will be like the ip address of the machine where the database is attached and the default instance will be SqlExpress

A connection string contains Data Source name i.e., server name, Initial catalog i.e., database name, user id i.e., login user id of the database, password i.e., login password of the database.

Answer 6

If you created Connection Manager in your project then you can simply pull the connection string from there.

String connection = this.dts.connections["<connection_manager_name>"];

And use this connection in:

using (var conn = new SqlConnection(connection))

Please correct me if I am wrong.