C# 如何从数据库中获取连接字符串

How to get the connection String from a database

提问by Pomster

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?

我已经使用 SQL Server Management Studio 创建了一个数据库，现在我想在我的 C# 应用程序中使用它。我需要连接字符串？

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?

如何发布我的数据库以便 Visual Studio 可以获取它？那我可以直接拉那里的连接串吗？

采纳答案by JotaBe

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.

获取连接字符串的最简单方法是使用 Visual Studio 中的“服务器资源管理器”窗口（菜单View、Server Explorer）并从该窗口连接到服务器。

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).

然后就可以在所连接服务器的属性中看到连接字符串（选择连接并按F4或Alt+Enter或在右键菜单中选择属性）。

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.

高级连接字符串设置：创建连接时，您可以通过单击“高级...”按钮来修改任何高级连接字符串选项，如 MARS、弹性、timeot、池配置等。添加连接”对话框。您可以稍后通过右键单击数据连接并选择“修改连接...”来访问此对话框。可用的高级选项因服务器类型而异。

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.

如果您使用 SQL Server Management Studio 创建数据库，该数据库将在服务器实例中创建，因此，要部署您的应用程序，您必须备份数据库并将其部署在部署 SQL Server 中。或者，您可以使用 SQL Server Express（SQL Server 2012 中的 localDB）使用数据文件，该文件将随您的应用轻松分发。

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.

即，如果它是一个 ASP.NET 应用程序，则有一个 App_Datafolder。如果右键单击它，您可以添加一个新元素，它可以是 SQL Server 数据库。该文件将位于该文件夹中，可与 SQL Express 一起使用，并且易于部署。您需要在您的机器上安装 SQL Express / localDB 才能使其工作。

回答by Sai Kalyan Kumar Akshinthala

The sql server database will be storedby default in the following path

在SQL Server数据库将被存储在默认情况下在以下路径

<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.

，其中<drive>是安装驱动器，X 是实例编号（MSSQL.1 表示数据库引擎的第一个实例）。为了提供连接字符串，您应该知道 sql 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

一般服务器名会是数据库所在机器的ip地址，默认实例是SqlExpress

A connection stringcontains Data Sourcename i.e., server name, Initial catalogi.e., database name, user idi.e., login user id of the database, passwordi.e., login password of the database.

一个连接字符串包含数据源名称即服务器名称，初始目录即数据库名称，用户ID即数据库的登录用户ID，密码即数据库的登录密码。

回答by MarioDS

On connectionstrings.comyou can find the connection string for every DB provider. A connection string is built up with certain attributes/properties and their values. For SQL server 2008, it looks like this (standard, which is what you'll need here):

在connectionstrings.com 上，您可以找到每个数据库提供程序的连接字符串。连接字符串由某些属性/属性及其值构成。对于 SQL Server 2008，它看起来像这样（标准，这是您在这里需要的）：

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

on myServerAddress, write the name of your installed instance (by default it's .\SQLEXPRESSfor SQL Server Express edition). Initial catalog = your database name, you'll see it in SSMS on the left after connecting. The rest speaks for itself.

在 上myServerAddress，写下您安装的实例的名称（默认情况下，它.\SQLEXPRESS用于 SQL Server Express 版本）。初始目录 = 您的数据库名称，连接后您将在左侧的 SSMS 中看到它。其余的不言自明。

edit

编辑

You will need to omit username and password for windows authentication and add Integrated Security=SSPI.

您将需要省略 Windows 身份验证的用户名和密码并添加Integrated Security=SSPI.

回答by Filburt

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

检索连接字符串的一种非常简单的方法是创建一个文本文件，将扩展名从.txt更改为.udl。

Double-clicking the .udlfile will open the Data Link Propertieswizard.

双击.udl文件将打开数据链接属性向导。

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.

关闭向导并使用您选择的文本编辑器打开 .udl 文件，只需复制连接字符串（不带Provider=<driver>部分）即可在您的 C# 应用程序中使用它。

sample udl file content

样本 udl 文件内容

[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/

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

回答by Vishal Parekh

put below tag in web.config file in configuration node

将下面的标签放在配置节点的 web.config 文件中

 <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();

回答by Abhishek

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.

如果我错了，请纠正我。

回答by karthi

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

回答by David Elgstuen

My solution was to use excel(2010).

我的解决方案是使用excel(2010)。

In a new worksheet, select a cell, then:

在新工作表中，选择一个单元格，然后：

Data -> From Other Sources -> From SQL Server 

put in the server name, select table, etc,

输入服务器名称，选择表等，

When you get to the "Import Data" dialog,
click on Propertiesin the "Connection Properties" dialog,
select the "Definition" tab.

当您进入“导入数据”对话框时，
单击Properties“连接属性”对话框中的 ，
选择“定义”选项卡。

And there Excel nicely displays the Connection String for copying
(or even Export Connection File...)

并且 Excel 很好地显示了用于复制的连接字符串
（甚至导出连接文件...）

回答by ΩmegaMan

If one uses the tool Linqpad, after one connects to a target database from the connections one can get a connection string to use.

如果使用 Linqpad 工具，则在从连接连接到目标数据库后，可以获得要使用的连接字符串。

1. Right click on the database connection.
2. Select Properties
3. Select Advanced
4. Select Copy Full Connection String to Clipboard

1. 右键单击数据库连接。
2. 选择 Properties
3. 选择 Advanced
4. 选择 Copy Full Connection String to Clipboard

Result: Data Source=.\jabberwocky;Integrated Security=SSPI;Initial Catalog=Rasa;app=LINQPad

结果： Data Source=.\jabberwocky;Integrated Security=SSPI;Initial Catalog=Rasa;app=LINQPad

---

---

Remove the app=LinqPaddepending on the drivers and other items such as Serverinstead of source, you may need to adjust the driver to suit the target operation; but it gives one a launching pad.

删除app=LinqPad依赖驱动程序和其他项目，例如Server代替源，您可能需要调整驱动程序以适应目标操作；但它给了一个发射台。

回答by Juned Khan Momin

If you have installed and setup MS SQL Server and Management Studio, go to Visual Studio (Visual Studio not SQL Server Management Studio).

如果您已经安装并设置了 MS SQL Server 和 Management Studio，请转到 Visual Studio（Visual Studio 不是 SQL Server Management Studio）。

1]In Visual Studio go to Tools -> Connect to Database.

2]Under Server Name Select your Database Server Name (Let the list Populate if its taking time).

3]Under Connect to a Database, Select Select or enter a database name.

4]Select your Database from Dropdown.

5]After selecting Database try Test Connection.

6]If Test Connection Succeeds, Click Ok.

7]In Visual Studio go to View -> Server Explorer.

8]In Server Explorer window, Under Data Connections Select your Database. Right Click your Database -> Click Properties.

9]In Propertieswindow you will see your Connection String.

1]在 Visual Studio 中，转到Tools -> Connect to Database。

2]在服务器名称下选择您的数据库服务器名称（如果需要时间，让列表填充）。

3]在连接到数据库下，选择选择或输入数据库名称。

4]从下拉列表中选择您的数据库。

5]选择数据库后尝试测试连接。

6]如果测试连接成功，请单击“确定”。

7]在 Visual Studio 中，转到View -> Server Explorer。

8]在服务器资源管理器窗口中，在数据连接下选择您的数据库。右键单击您的数据库 -> 单击 Properties。

9]在属性窗口中，您将看到您的连接字符串。