如何使用c#建立数据库（SQL）连接？

How to establish database(SQL) Connection using c#?

提问by user1918566

I want to establish a database connection for receiving the details of the customers using C#. I am using sql-server 2008 along with visual studio 2010.....

我想建立一个数据库连接，用于使用 C# 接收客户的详细信息。我正在使用 sql-server 2008 和 Visual Studio 2010 .....

Here is this code:

这是这段代码：

<asp:Textbox id="TxtBox1" runat="server" />
<asp:Textbox id="TxtBox2" runat="server" />
<asp:Button id="Button1" runat="server" Onclick="Button1_Click" />

and the codebehind:

和代码隐藏：

protected void Button1_Click(Object sender,EventArgs e)
{
string Text1=TextBox1.Text;
string Text2=TextBox2.Text;
string connectionString="Data Source=servername;InitialCatalog=DataBaseNameUserID=sa;  Password=YourPassword;"

SqlConnection sqlConnection=new SqlConnection(connectionString);

string insertStatement="INSERT INTO TableName(column1,column2) VALUES Txtb1+","+Txtb2";

SqlCommand sqlCommand=new SqlCommand(insertStatement,sqlConnection);

try
{
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();

}
finally
{
sqlConnection.Close();
}


}

I have created the data.mdf file in it i have created table containing fields but i am getting Connection has not established error can any one help me to resolve my problem???

我已经在其中创建了 data.mdf 文件，我已经创建了包含字段的表，但我收到连接尚未建立错误，谁能帮我解决我的问题？？？

回答by Steve

This connection string is wrong.

这个连接字符串是错误的。

string connectionString="Data Source=servername;InitialCatalog=DataBaseNameUserID=sa;" + 
                        "Password=YourPassword;"

should be

应该

string connectionString="Data Source=servername;InitialCatalog=DataBaseName; UserID=sa;" + 
                        "Password=YourPassword;"

However, NEVER use the sa user in your applications. Create a new user with Management Studio or better use Integrated Security.

但是，切勿在您的应用程序中使用 sa 用户。使用 Management Studio 创建新用户或更好地使用集成安全性。

Also the remainder of your code should be written in this way

您的代码的其余部分也应该以这种方式编写

using(SqlConnection sqlConnection=new SqlConnection(connectionString))
{
     string insertStatement="INSERT INTO TableName(column1,column2) VALUES (@col1, @col2)";
     SqlCommand sqlCommand=new SqlCommand(insertStatement,sqlConnection);
     sqlCommand.Parameters.AddWithValue("@col1", Text1);
     sqlCommand.Parameters.AddWithValue("@col2", Text2);
     sqlConnection.Open();
     sqlCommand.ExecuteNonQuery();
}

• Use the using statementto correctly close and dispose your SqlConnection
• Use parametrized queryin your sql commands to avoid parsing problems an Sql Injection Attacks

• 使用using 语句正确关闭和处理您的 SqlConnection
• 在 sql 命令中使用参数化查询来避免解析问题和Sql 注入攻击

回答by Slippery Pete

This code does not compile. You need to add a semicolon to this line:

此代码无法编译。您需要在此行中添加一个分号：

string connectionString="Data Source=servername;InitialCatalog=DataBaseNameUserID=sa;  Password=YourPassword;"

Like so:

像这样：

string connectionString="Data Source=servername;InitialCatalog=DataBaseNameUserID=sa;  Password=YourPassword;";

You also need to change this line:

您还需要更改此行：

string insertStatement="INSERT INTO TableName(column1,column2) VALUES Txtb1+","+Txtb2";

To this:

对此：

string insertStatement = "INSERT INTO TableName(column1,column2) VALUES " + Text1 + ", " + Text2;

回答by Ulises

The connection string itself may be the issue, windows has an easy way to create a valid connection string that you can test and then just copy it to your app. I wrote about it a couple of months ago here:

连接字符串本身可能是问题所在，Windows 有一种简单的方法可以创建一个有效的连接字符串，您可以对其进行测试，然后将其复制到您的应用程序中。几个月前我在这里写过它：

Tips for your app:

应用提示：

• As pointed by asawyer, don't use the sa account, create a login with access to the database(s) you need.

• Use the usingstatement

• 正如 asawyer 所指出的，不要使用 sa 帐户，创建一个可以访问您需要的数据库的登录名。

• 使用using语句

example:

例子：

using (SqlConnection connection = new SqlConnection(connectionString))  
    {  
      //here you open and execute the command
    }

• User parameterized queries to avoid sql injection. Here is a good article.

• 用户参数化查询，以避免 sql 注入。这是一篇好文章。

回答by Rashedul.Rubel

use the connection string as follows:

使用连接字符串如下：

connectionString="Data Source=ServerAddress;Initial Catalog=DataBaseName;Integrated Security=SSPI;User ID=Domain\Username;Password=Password;"