初学者使用 C# 访问 SQL Server 的指南

引言

在本文中，我将演示如何从 SQL Server 或 MSDE 数据库插入和读取数据。此代码应在 SQL Server（我使用的是 2000）和 MSDE 上运行。我使用的是 Visual Studio 2002，但此代码应适用于 Visual Studio 2003、Web Matrix 和命令行 SDK。此代码应适用于 C# 应用程序以及 C# Web 应用程序和 Web 服务。此代码不能在 FreeBSD 上使用 Rotor [^] 编译。

背景

我当前项目的一部分要求我从数据库存储和检索信息。我决定使用 C# 作为我的目标语言，因为我目前正在阅读 《C# 内部第二版》 [^] (作者：Tom Archer [^])，顺便说一句，这是一本必读的书。但是我找不到任何清晰且通用的 C# 访问 SQL Server 的示例。

使用代码

我没有包含示例应用程序，因为本文中提供的代码可以直接放入并应能正常工作。此外，在整篇文章中，我将引用 SQL Server，MSDE 是 SQL Server 的免费版本，它没有一些 GUI 工具，并且有一些其他限制，例如数据库大小。此代码在这两者上都能正常工作。

建立连接

假设 SQL Server 已正确设置（本文中我不会深入探讨这一点），创建与 SQL Server 的连接并没有真正的神秘之处，事实上 .NET 使得处理 SQL 变得相当容易。第一步是添加 SQL 客户端命名空间。

using System.Data.SqlClient;

然后我们创建一个 SqlConnection 并指定连接字符串。

SqlConnection myConnection = new SqlConnection("user id=username;" + 
                                       "password=password;server=serverurl;" + 
                                       "Trusted_Connection=yes;" + 
                                       "database=database; " + 
                                       "connection timeout=30");

注意：连接字符串中的换行符仅用于格式化目的

SqlConnection.ConnectionString

连接字符串只是选项和值的集合，用于指定如何连接以及连接到什么。通过查阅 Visual Studio .NET 帮助文件，我发现有几个字段有多个名称，但作用相同，例如 Password 和 Pwd 可以互换使用。目前，我还没有包含 SqlConnection.ConnectionString 的所有选项。当我有机会测试和使用这些其他选项时，我会在文章中包含它们。

用户 ID

当您使用 SQL 身份验证时，会使用 User ID。根据我的经验，当使用 Trusted_Connection 或 Windows 身份验证时，它会被忽略。如果用户名与密码关联，则会使用 Password 或 Pwd。

"user id=userid;"

密码或 Pwd

密码字段应与用户 ID 一起使用，只使用密码而没有用户名登录是没有意义的。Password 和 Pwd 完全可以互换使用。

"Password=validpassword;"-或-
"Pwd=validpassword;"

数据源或服务器或地址或Addr或网络地址

在查阅 MSDN 文档后，我发现有几种方法可以指定网络地址。文档没有提到它们之间的任何区别，它们似乎可以互换使用。该地址是一个有效的网络地址，为简洁起见，我在示例中仅使用 localhost 地址。

"Data Source=localhost;"
-或-
"Server=localhost;"
-或-
"Address=localhost;"-或-"Addr=localhost;"
-或-"Network Address=localhost;"

集成安全或 Trusted_Connection

Integrated Security 和 Trusted_Connection 用于指定连接是否安全，例如 Windows 身份验证或 SSPI。可识别的值为 true、false 和 sspi。根据 MSDN 文档，sspi 等同于 true。注意：我不知道 SSPI 如何工作，或者如何影响连接。

连接超时或Connection Timeout

这些指定服务器在生成错误之前等待响应的时间（以秒为单位）。默认值为 15（秒）。

"Connect Timeout=10;"-或-
"Connection Timeout=10;"

初始目录或数据库

Initial Catalog 和 Database 只是选择与连接关联的数据库的两种方式。

"Inital Catalog=main;"
-或-
"Database=main;"

网络库或Net

如果您通过 TCP/IP 以外的协议与服务器通信，则网络库选项至关重要。Network Library 的默认值为 dbmssocn，即 TCP/IP。以下选项可用：dbnmpntw （命名管道）、dbmsrpcn （多协议）、dbmsadsn （Apple Talk）、dbmsgnet （VIA）、dbmsipcn （共享内存）和 dbmsspxn （IPX/SPX）以及 dbmssocn （TCP/IP）。与以前一样，Network Library 和 Net 可以互换使用。注意：必须在您连接的系统上安装相应的网络协议。

SqlConnection.Open()

这是连接的最后一步，只需通过以下方式执行（请记住首先确保您的连接具有连接字符串）

try
{
    myConnection.Open();
}
catch(Exception e)
{
    Console.WriteLine(e.ToString());
}

SqlConnection.Open() 是一个 void 函数，不返回错误，而是抛出异常，因此请记住将其放入 try/catch 块中。这样可以避免程序在用户面前崩溃。

命令你

SQL 命令可能是使用 SQL 数据库最困难的部分，但是 .NET 框架已经很好地封装了一切，并消除了大部分猜测工作。

SqlCommand

SqlCommand 是用来做什么的？如果你猜是 SQL 命令，那么你就对了。一个 SqlCommand 至少需要两样东西才能运行。一个命令字符串和一个连接。首先我们来看看连接要求。有两种方法可以指定连接，如下所示：

SqlCommand  myCommand = new SqlCommand("Command String", myConnection);

// - or -

myCommand.Connection = myConnection;

连接字符串也可以通过 SqlCommand.CommandText 属性以两种方式指定。现在让我们看看我们的第一个 SqlCommand。为了简单起见，它将是一个简单的 INSERT 命令。

SqlCommand myCommand= new SqlCommand("INSERT INTO table (Column1, Column2) " +
                                     "Values ('string', 1)", myConnection);

// - or - 

myCommand.CommandText = "INSERT INTO table (Column1, Column2) " + 
                        "Values ('string', 1)";

现在我们来看看这些值。 table 只是数据库中的表。Column1 和 Column2 只是列名。在 values 部分，我演示了如何插入 string 类型和 int 类型的值。字符串值用单引号括起来，如你所见，整数直接传递。最后一步是使用以下命令执行：

myCommand.ExecuteNonQuery();

SqlDataReader

插入数据很好，但取出数据也同样重要。这时 SqlDataReader 就派上用场了。您不仅需要数据读取器，还需要 SqlCommand。以下代码演示了如何设置和执行一个简单的读取器

try
{
    SqlDataReader myReader = null;
    SqlCommand    myCommand = new SqlCommand("select * from table", 
                                             myConnection);
    myReader = myCommand.ExecuteReader();
    while(myReader.Read())
    {
        Console.WriteLine(myReader["Column1"].ToString());
        Console.WriteLine(myReader["Column2"].ToString());
    }
}
catch (Exception e)
{
    Console.WriteLine(e.ToString());
}

如您所见，SqlDataReader 不直接访问数据库，它只保存数据并提供一个易于使用数据的接口。SqlCommand 相当简单，table 是您要从中读取的表。Column1 和 Column2 只是表中的列。由于您很可能会读取多行，因此需要一个 while 循环来检索所有记录。并且一如既往，您希望 try 和 catch 它，以免它崩溃。

SqlParameter

我演示的 SqlCommand 用法存在一个小问题，它留下了一个很大的安全漏洞。例如，如果按照之前演示的方式，您从用户那里获取输入，您的命令字符串将像这样构建：

 SqlCommand myCommand = new SqlCommand(
  "SELECT * FROM table WHERE Column = " + input.Text, myConnection);
            

如果用户输入的语法正确，一切都很好。但是，如果用户输入 value1, DROP table 会发生什么？最好的情况是它会导致异常（我没有检查这个例子会做什么，但它说明了一个问题），最坏的情况是你就可以和你的表说再见了。你可以解析所有用户输入并去除任何可能导致问题的字符，或者你可以使用 SqlParameter。现在 SqlParameter 类很大，但我将只向你展示基本的参数用法。基本上，创建参数需要三样东西：名称、数据类型和大小。（注意：对于某些数据类型，你可能需要省略大小，例如 Text）。

SqlParameter myParam = new SqlParameter("@Param1", SqlDbType.VarChar, 11);
myParam.Value = "Garden Hose";
        
SqlParameter myParam2 = new SqlParameter("@Param2", SqlDbType.Int, 4);
myParam2.Value = 42;

SqlParameter myParam3 = new SqlParameter("@Param3", SqlDbType.Text);
myParam.Value = "Note that I am not specifying size. " + 
  "If I did that it would trunicate the text.";

按照命名约定，所有参数名称都必须以 @ 符号开头，我不确定这是否是强制性的。那么如何使用参数呢？就像下面的代码所示，这很容易。

SqlCommand myCommand = new SqlCommand(
  "SELECT * FROM table WHERE Column = @Param2", myConnection);
myCommand.Parameters.Add(myParam2);

现在，这可以防止恶意用户劫持您的命令字符串。这不是参数的全部内容，如果您想了解更高级的主题，可以从此处[^]开始。

完成后别忘了关闭！

关闭连接就像打开它一样简单。只需调用 SqlConnection.Close()，但请记住将其放入 try/catch 块中，因为与 SqlConnection.Open() 一样，它不返回错误，而是抛出异常。

try
{
    myConnection.Close();
}
catch(Exception e)
{
    Console.WriteLine(e.ToString());
}

当良好连接出现问题时

信任连接对我来说一直是个谜，我从未弄清楚为什么 IIS 和 SQL Server 似乎总是无法很好地配合。幸运的是，Pete (moredip) 指出了文档中一个有用的部分。为了更简单，我决定将其添加到本文中。我将把它分成两个不同的部分：IIS 6 和其他版本的 IIS。首先，您需要确保 osql.exe 位于您的系统路径中，或者找到它。它应该位于您的 SQL Server 2000 服务器/客户端工具目录中。在我的系统上，它看起来像这样：%Install Directory%\80\Tools\BINN\。为简单起见，我将在示例中使用伪变量，以免造成混淆。例如，伪变量将如下所示：%VARIABLE%。服务器将被称为 %SERVER%\%INSTANCE%。如果您没有使用任何实例名称，则可以是 %SERVER%，如果服务器是本地计算机，则为 (local)。如果您使用实例名称，则可能是 ServerName\ServerInstance 等等。我还会使用 %DATABASE% 来指代数据库名称。

Windows 2003 Server 上的 IIS 6

我知道这将在 Windows 2003 Server 上的 IIS 6 上运行，因为我已经做过，而且目前只有该操作系统带有 IIS 6。在 IIS 6 上，ASP.NET 进程在“NT AUTHORITY\NETWORK SERVICE”帐户下运行。

osql -E -S %SERVER%\%INSTANCE% -Q "sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'"

现在我们的 ASP.NET 应用程序将能够登录到服务器。剩下要做的就是授予对数据库的访问权限。

osql -E -S %SERVER%\%INSTANCE% -d %DATABASE% -Q 
  "sp_grantdbaccess 'NT AUTHORITY\NETWORK SERVICE'"
osql -E -S %SERVER%\%INSTANCE% -d %DATABASE% -Q 
  "sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'"

这两行将为其中一个数据库添加访问权限。因此，如果您想为另一个数据库添加访问权限，只需更改 %DATABASE% 并运行这两行。

IIS 5.1

这应该适用于所有其他 IIS 5.1（可能还有其他版本）组合。IIS 5.1 和 IIS 6 之间唯一的区别是 ASP.NET 进程运行的帐户。IIS 5.1 在 %MACHINENAME%\ASPNET 下运行，其中 %MACHINENAME% 是机器名。

osql -E -S %SERVER%\%INSTANCE% -Q "sp_grantlogin '%MACHINENAME%\ASPNET'"

现在我们的 ASP.NET 应用程序将能够登录到服务器。剩下要做的就是授予对数据库的访问权限。

osql -E -S %SERVER%\%INSTANCE% -d %DATABASE% 
  -Q "sp_grantdbaccess '%MACHINENAME%\ASPNET'"
osql -E -S %SERVER%\%INSTANCE% -d %DATABASE% 
  -Q "sp_addrolemember 'db_owner', '%MACHINENAME%\ASPNET'"

这两行将为其中一个数据库添加访问权限。因此，如果您想为另一个数据库添加访问权限，只需更改 %DATABASE% 并运行这两行。

遗留问题

您现在已经掌握了在 Web 应用程序或桌面应用程序中使用 SQL 数据库所需的基础知识。

本文绝不是最终版本。我计划在时间允许的情况下扩展本文并添加一个示例应用程序。还将包含有关存储过程以及扩展的连接选项部分的信息。如果您有任何建议，请在下面的论坛中提出

历史

• 2004 年 8 月 20 日：添加了关于 SqlParameters 的章节
• 2004 年 2 月 25 日：添加了有关 IIS 权限设置的信息
• 2003 年 7 月 2 日：修订了连接字符串部分
• 2003 年 6 月 28 日：修正了一些印刷错误
• 2003 年 6 月 27 日：首次发布