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

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能够接收它?那么我就可以拉这里的连接字符串?

9 个解决方案

#1

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中的“服务器资源管理器”窗口(菜单视图、服务器资源管理器)并从该窗口连接到服务器。

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或在右键单击菜单中选择properties)。

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，有一个App_Datafolder。如果右键单击，可以添加一个新元素，它可以是一个SQL Server数据库。该文件将在该文件夹中，将使用SQL Express，并且将易于部署。您需要在您的机器上安装SQL Express / localDB以使其工作。

#2

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

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

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

双击.udl文件将打开Data Link Properties向导。

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文件，并简单地复制连接字符串(没有提供程序= 部分)，以便在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/

#3

On connectionstrings.com you 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):

在connections.com上，您可以找到每个DB提供程序的连接字符串。连接字符串由某些属性/属性及其值组成。对于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 .\SQLEXPRESS. Initial catalog = your database name, you'll see it in SSMS on the left after connecting. The rest speaks for itself.

在myServerAddress上，写下您安装的实例的名称(默认情况下是:\SQLEXPRESS)。初始目录=您的数据库名，您将在连接后的左侧SSMS中看到它。其余的都不言自明。

edit

编辑

You will need to omit username and password for windows authentication.

您需要省略windows身份验证的用户名和密码。

#4

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 Properties in the "Connection Properties" dialog,
select the "Definition" tab.

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

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

Excel很好地显示了用于复制(甚至导出连接文件…)的连接字符串。

#5

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.

然后你可以使用上面的connectionstring，例如。

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

#6

The sql server database will be stored by 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.

，其中 为安装驱动器，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 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.

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

#7

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.

如果我说错了，请纠正我。

#8

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. 右键单击数据库连接。
3. Select Properties
4. 选择属性
5. Select Advanced
6. 选择先进的
7. Select Copy Full Connection String to Clipboard
8. 选择将完整连接字符串复制到剪贴板

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

结果:数据源=.\jabberwocky;集成安全性=SSPI;初始目录=Rasa;app=LINQPad

---

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

删除app=LinqPad根据驱动程序和服务器等其他项目而不是源程序，您可能需要调整驱动程序以适应目标操作;但它提供了一个发射平台。

#9

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