现在我们有了一个数据库,让我们来看看如何将数据从该数据库获取到我们的应用程序中,或者从我们的应用程序获取到我们的数据库中。如今一个显而易见的选择是对象关系映射器(ORM),它可以从我们的表中自动创建类,并且知道如何用一个简单的命令来读取和存储数据。可能就这么简单。然而,很多你不知道的事情仍在发生。我认为,如果没有 ORM,你就不能真正理解 ORM。事实上,我遇到过除了 ORM 之外从未使用过任何东西的开发人员,当 ORM 不支持他们需要的一些功能时,他们完全不知所措。或者当他们的 ORM 以每秒 100 次的速度喷出巨大的查询时,他们感到惊讶!如果你不真正理解你的 ORM,你将会有一段艰难的时间(和一个缓慢的应用程序)。因此,让我们先在没有 ORM 的情况下工作,看看在(稍微)较低的级别会发生什么。
打开 Visual Studio,用 C#创建一个新的控制台应用程序(如果您愿意,也可以使用 Visual Basic,但我的示例将使用 C#)。那个。NET Framework 有一组用于处理数据源(如数据库或电子表格)的组件,统称为 ADO.NET。早在 20 世纪 80 年代之前,微软就首次引入了 ADO,这是 ActiveX 数据对象的缩写。NET 框架。ADO.NET 的大部分都在System.Data
命名空间中,当你创建一个新项目时,这个命名空间自动可用。
假设我们想从数据库中检索一个人并在我们的软件中使用它。我们首先需要的是与数据库的连接。目前,我们假设我们将始终使用一个 SQL Server 数据库。在这种情况下,我们将广泛使用System.Data.SqlClient
命名空间。
我们首先需要的是一个真正的连接。没有连接,我们将永远无法使用我们的数据库。
为了建立连接,我们需要一个连接字符串:一个文本值,指定建立连接所需的各种选项,例如运行实例的服务器、要连接的数据库、登录凭据、连接名称、超时、语言等等。
您可以使用连接字符串连接到任何类型的数据库,每个数据库供应商都有自己的格式。幸运的是,我们有像connectionstrings.com这样的网站来帮助我们。典型的连接字符串如下所示:
Server=server\instance; Database=database; User Id=username; Password=password
。
或者,如果选择综合安全,可以不输入用户标识和密码,而输入Trusted_Connection=True
;或者Integrated Security=True
;甚至Integrated Security=SSPI
(他们都一样)。
同样,Data Source=server\instance
是Server=server\instance
的常用替代品,Integrated Security=database
是Database=database
的常用替代品。
让我们填空,建立联系。我的连接字符串如下:Server=Laptop23\SQLEXPRESS; Database=SuccinctlyExamples; Integrated Security=SSPI
。
现在让我们用代码创建一个连接并打开它。关于代码的一个简短注释:我正在对字符串使用$”…”
语法,这是 C# 6.0 中的一个新特性。基本上是string.Format
的简写。
代码清单 4:创建一个 SqlConnection
try
{
using (SqlConnection connection = new SqlConnection(
@"Server=LAPTOP23\SQLEXPRESS;
Database=SuccinctlyExamples; Integrated Security=SSPI"))
{
connection.Open();
// The database is
closed upon Dispose() (or Close()).
}
Console.WriteLine("Successfully opened and
closed the database.");
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong while
opening a connection to the database: { ex.Message }");
}
Console.WriteLine("Press any key to
close.");
Console.ReadKey();
第一行代码创建一个新的SqlConnection
实例(在System.Data.SqlClient
中找到),并传入连接字符串。我们将使用的许多对象需要被适当地处理以释放资源(在这种情况下是数据库连接),所以我们将把SqlConnection
适当地包装在一个使用块中。当调用Close()
或Dispose()
方法时(通过使用块),连接自动关闭。connection.Open()
打开连接。
这几行代码可能会出错,因此出现了try
- catch
块。尝试在连接字符串中输入错误(例如,SServer
),使用不存在的数据库(任何随机值),提供无效凭据,或者尝试打开连接两次(中间不关闭)。所有这些都将导致Exception
被抛出。
用 C#硬编码连接字符串在开发过程中可能很方便,但在生产环境中不太实用(或不安全)。每次数据库或环境(开发、测试、生产)发生变化时,您都需要对其进行更改、重建和重新部署。
连接字符串通常存储在配置文件中。对于每个环境,应用程序的配置文件通常是不同的。将各种设置存储在配置文件中的最大优势是,您可以更改应用程序的行为,而无需实际更改和重新部署应用程序,从而允许在多个环境中进行不同的设置。在配置文件中存储配置字符串是非常常见的。NET 配置文件实际上有一个connectionStrings
部分。我已经在 app.config 文件中添加了SuccinctlyDB
连接字符串。
代码清单 5:配置文件中的连接字符串部分
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="SuccinctlyDB" connectionString="Server=LAPTOP23\SQLEXPRESS; Database=SuccinctlyExamples;
Integrated Security=SSPI"/>
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6"
/>
</startup>
</configuration>
我们现在可以轻松地从配置文件中检索连接字符串。首先我们需要给System.Configuration
添加一个项目引用。现在,我们可以轻松地替换第一行代码,使用配置文件中的值。
代码清单 6:使用配置文件中的连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
请注意,当配置文件中不存在连接字符串时,connectionString
将为空,并且在尝试打开连接时将引发Exception
(连接字符串属性未初始化)。如果没有?.
空条件运算符,我们在尝试获取ConnectionString
属性时会得到一个NullReferenceException
。
在某些用例中,您可能想要编辑连接字符串或者从头开始创建它(例如,在编写 SSMS 登录表单时)。对于这个用例,我们可以使用SqlConnectionStringBuilder
。SqlConnectionStringBuilder
顾名思义;它使用各种输入参数创建连接字符串。让我们看一个简单的例子。
代码清单 SqlConnectionStringBuilder
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource
= @"LAPTOP23\SQLEXPRESS";
builder.InitialCatalog
= "SuccinctlyExamples";
builder.IntegratedSecurity
= true;
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
请注意SqlConnectionStringBuilder
实际上使用了术语DataSource
和InitialCatalog
,而不是Server
和Database
。另外,IntegratedSecurity
设置为true
而不是 SSPI(或Trusted_Connection
)。
我们也可以使用SqlConnectionStringBuilder
来编辑连接字符串。构造函数是重载的,因此您可以插入一个连接字符串作为基。
代码清单 8:编辑连接字符串
string baseConnectionString = ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(baseConnectionString);
builder.IntegratedSecurity
= false;
builder.UserID
= "sander";
builder.Password
= "password";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
在这个例子中,我们将UserID
和Password
设置为有利于Integrated Security
。当然,现在尝试打开连接将会失败,因为这些凭据显然无效。
下一步是使用我们的连接来检索一些数据。这需要一个SqlCommand
来保存和执行我们的查询。让我们使用查询SELECT Id, FirstName, LastName, DateOfBirth, GenderId FROM Person
选择我们的Person
表。为此,我们需要创建SqlCommand
,将我们的查询和一个SqlConnection
传递给它,并让它执行查询。
|
当在一个SqlCommand
实例上使用ExecuteReader
函数时,它的选择查询被执行,我们得到一个保存检索到的数据的SqlDataReader
。一SqlDataReader
很遗憾,不是很容易上手。我不知道为什么,但是它需要相当多的打字。SqlDataReader
逐行读取结果集(仅向前),您可以按索引或名称访问列。显然,按索引选择不是很可读,尤其是在大查询中(谁知道列索引 43 是什么?),但按名称获取索引会将值作为对象返回,并要求程序员进行适当的转换。您也可以通过名称获取列的索引,然后使用SqlDataReader
方法获取正确的值。然后,如果没有值存在(有人说值是NULL
,你就要自己转换成默认值或者null
。有趣的事实:数据库中的NULL
与 C#中的null
完全不同。数据库NULL
在 C#中由静态的DBNull.Value
表示。至少可以说是乏味的。我们可能也想将结果存储在一些自定义对象中,所以我们需要创建一个Person
类。让我们看看这是什么样子。
首先,让我们创建一个简单的Person
类。
代码清单 9:个人类
public class Person
{
public int
Id { get; set;
}
public string FirstName { get; set; }
public string LastName { get; set;
}
public DateTime? DateOfBirth { get; set; }
public int?
GenderId { get; set;
}
}
现在让代码从数据库中获取所有人:
代码清单 SqlDataReader 的用法
try
{
List<Person> people = new List<Person>();
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"SELECT Id,
FirstName, LastName, DateOfBirth, GenderId FROM dbo.Person",
connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var p = new
Person();
// Get Id by index...
object idByIndex = reader[0];
// ...And make the
correct conversion.
int idByIndexCast = Convert.ToInt32(idByIndex);
// Get Id by name...
object idByName = reader[nameof(Person.Id)];
// ...And make the
correct conversion.
int idByNameCast = Convert.ToInt32(idByName);
// Or get the Id index
by name...
int idIndex = reader.GetOrdinal(nameof(Person.Id));
// ...And use the
SqlDataReader methods.
p.Id = reader.GetInt32(idIndex);
int firstNameIndex = reader.GetOrdinal(nameof(Person.FirstName));
p.FirstName = reader.GetString(firstNameIndex);
int lastNameIndex = reader.GetOrdinal(nameof(Person.LastName));
if (!reader.IsDBNull(lastNameIndex))
{
p.LastName = reader.GetString(lastNameIndex);
}
int dateOfBirthIndex =
reader.GetOrdinal(nameof(Person.DateOfBirth));
if (!reader.IsDBNull(dateOfBirthIndex))
{
p.DateOfBirth = reader.GetDateTime(dateOfBirthIndex);
}
int genderIdIndex = reader.GetOrdinal(nameof(Person.GenderId));
if (!reader.IsDBNull(genderIdIndex))
{
p.GenderId = reader.GetInt32(genderIdIndex);
}
people.Add(p);
}
}
// The database is
closed upon Dispose() (or Close()).
}
Console.WriteLine("Successfully opened and
closed the database.");
foreach (Person p in people)
{
Console.WriteLine($"{p.FirstName} {p.LastName} was born on {p.DateOfBirth}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong while
opening a connection to the database: { ex.Message }");
}
Console.WriteLine("Press any key to
close.");
Console.ReadKey();
首先,请注意我在顶部声明了我的List<Person>
,这样我就可以在using
块之外使用它。通常最佳做法是打开连接,立即获取数据,然后立即关闭连接。您的数据库连接保持打开,直到它被显式关闭,当使用SqlDataReader
时,您不能关闭它,直到所有内容都被读取。所以,请稍后阅读、关闭并使用您的数据!
创建SqlCommand
非常简单。正如承诺的那样,SqlDataReader
就不那么直白了。SqlDataReader
使用的是一张桌子,一次只能看到一行。当调用SqlDataReader.Read
时,第一行(如果有)将变为可访问。对Read
的任何后续调用将移动到下一行(如果有)。如果没有(下一个)行可用,则Read
将返回false
。要检查是否有可用的行,可以使用SqlDataReader.HasRows
属性。GetOrdinal
函数返回列名的索引(nameof
只在这里起作用,因为我已经将我的属性命名为与我的数据库列相同;如果其中一个发生变化,这段代码将会中断,但是如果两个都发生变化,您的所有代码都将继续工作)。
在这个例子中,我们看到GetInt32
、GetString
和GetDateTime
。还有更多的Get[Type]
方法,像GetInt16
、GetInt64
、GetBoolean
、GetChar
等等。它们将 SQL 类型转换为适当的 CLR 类型。如果缺少一个值,Get[Type]
方法将抛出一个Exception
。所以对于可空字段,我们需要使用IsDBNull
检查NULL
。或者,有一个GetValue
函数将返回一个对象。如果不需要值类型,可以使用它。要检查NULL
,请使用DBNULL.Value
。
代码清单 11: SqlDataReader。获取值(字符串)
object value = reader.GetValue("SomeColumn");
bool isDbNull = value == DBNull.Value;
这是另一个有趣的例子。如果我们想要返回多个结果集呢?假设我们更改选择查询并检索所有性别。SqlDataReader
可以检索多个结果集,并以访问行的相同方式访问它们。阅读器默认在第一个结果集上,但是如果有后续的结果集,可以调用NextResult
,阅读器会移动到下一个结果集。您可以再次调用Read
移动到第一行(如果有)。
代码清单 12:带有多个结果集的 SqlDataReader
List<Person> people = new List<Person>();
List<Gender> genders = new List<Gender>();
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"SELECT Id,
FirstName, LastName, DateOfBirth, GenderId FROM dbo.Person;"
+ "SELECT Id,
Code, Description FROM Gender;",
connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// Process people...
}
reader.NextResult();
while (reader.Read())
{
Gender g = new
Gender();
g.Id = reader.GetInt32(0);
g.Code = reader.GetString(1);
g.Description = reader.GetString(2);
genders.Add(g);
}
}
}
那么从数据库中读取数据有这么难吗?有一些替代方法,我们将在本书的后面讨论。然而,使用SqlDataReader
是从数据库中读取数据的最佳方式(如果操作正确)。
顺便说一下,如果您的查询返回单个值(一行一列的表),您可以使用ExecuteScalar
来代替。这将返回一个可以转换为正确类型的对象。
代码清单 13:execute scalar 的用法
public string GetPersonName(int id)
{
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"SELECT
FirstName FROM dbo.Person WHERE Id = @Id", connection))
{
command.Parameters.Add("Id", SqlDbType.Int).Value = id;
connection.Open();
object result = command.ExecuteScalar();
string firstName = null;
if (result != DBNull.Value)
{
firstName = (string)result;
}
return firstName;
}
}
在我们继续之前,我想谈一个非常非常重要的话题:参数化。使用参数有多重要,我怎么说都不为过。这就是编程的“实践安全性”。不幸的是,就像人们有不安全性行为一样,程序员仍然不总是使用参数。其结果是缓慢的查询,更重要的是,安全漏洞和数据泄露。最糟糕的是,它发生在真正的大型信息技术公司,如索尼、赛门铁克和思爱普。有这个你真的不想进去的好玩的 SQL 注入耻辱堂。真的——使用参数。
既然您知道参数化真的很重要,让我们看看它到底是什么,以及如何实现它。通常,在使用数据库时,您不会选择整个表。大多数时候你会用到一些过滤器,比如WHERE Id = x
,其中x
是一个变量。根据我到目前为止所分享的知识,您可能会想要实现如下的GetPeopleByName
函数。
代码清单 14:非参数化查询
public List<Person> GetPeopleByName(string firstName)
{
List<Person> people = new List<Person>();
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"SELECT Id,
FirstName, LastName, DateOfBirth, GenderId FROM dbo.Person "
+ $"WHERE
FirstName = '{
firstName }'",
connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Person p = new
Person();
// Read the data...
people.Add(p);
}
}
}
return people;
}
当然,它会给你一份名字的名单。它还会生成确切的 SQL 语句,您可以在 SSMS 使用它来获得具有给定名字的人。那有什么问题呢?firstName
变量可能是用户在某个文本字段中输入的值,无论是在网络上还是在桌面应用程序中。如果用户输入一个像“达塔尼昂”这样的名字,这将会破坏你的代码,因为在 SQL Server 中撇号会结束字符串(非常烦人)。不要到处去逃避自己的名字(用“”或类似的词代替);那真是太麻烦了。
更危险的是如果用户(或者更有可能是恶意黑客)输入名字“John’; USE master; DROP DATABASE SuccinctlyExamples; GO --”
?试试看,砰!你的数据库不见了(是的,真的不见了)。这叫做 SQL 注入。用户现在可以直接修改 SQL 语句,这样就可以访问敏感数据并销毁这些数据。其实有一部关于 SQL 注入的传奇 xkcd 漫画我觉得每个开发人员都应该挂在办公室。
这实际上只是数据库的基础知识,然而很多人都搞错了,而且不仅仅是初学者。思爱普、雅虎、领英,甚至联邦调查局和美国国家航空航天局都成了这个简单但如此危险的漏洞的受害者。总而言之,成千上万的应用程序(和程序员)因为人们没有参数化他们的查询而变得不安全。难以置信,对吧?
让我们看看如何在. NET 中参数化。幸运的是,这非常容易(让这么多人不这么做变得更加令人惊讶)。我们可以简单地在 SQL 查询中放置一个参数,并使用SqlCommands (Sql)Parameters
集合添加它的值。
代码清单 15:参数化查询
public List<Person> GetPeopleByName(string firstName)
{
List<Person> people = new List<Person>();
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"SELECT Id,
FirstName, LastName, DateOfBirth, GenderId FROM dbo.Person "
+ "WHERE
FirstName = @FirstName",
connection))
{
command.Parameters.AddWithValue("FirstName", firstName);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Person p = new
Person();
// Read the data...
people.Add(p);
}
}
}
return people;
}
请注意,查询现在有一个占位符@FirstName
(在 SQL Server 中,参数以@)开头,名称应该在这个位置。额外的好处是,我们不必记得在它周围加上引号(因为它是一个字符串),因为 SQL Server 会为我们这样做。使用command.Parameters.AddWithValue(“FirstName”, firstName);
将@FirstName
的实际值推送到SqlCommand
(在此上下文中,参数名称中的@是可选的)。我很少看到一段更能说明问题的代码。如果你现在输入一个像D’artagnan
这样的值,一切都会好的,因为 SQL Server 知道这是一个单一的值,并且会为你转义它。“John’; USE master; DROP DATABASE SuccinctlyExamples; GO --”
也是如此。
然而,这仍不完全正确。它是安全的,但不是最佳的。问题是,我将回到这一点,SQL Server 为每个查询创建一个执行计划。这个计划基本上包含了对 SQL 引擎的指令,以一种可能相当快的方式获取查询的数据。创建执行计划需要一些时间,但幸运的是,SQL Server 缓存了该计划,如果将来出现相同的查询,将会重用它。这是一把双刃剑,但我会回到那一点。目前,我们希望尽可能地重用查询计划。
所以对于查询“SELECT FirstName, LastName FROM dbo.Person”
来说,这很简单。如果出现相同的查询,将使用缓存的计划。但是如果计划包含“SELECT FirstName, LastName FROM dbo.Person WHERE Id = @Id”
等参数呢?下次我们调用该查询时,@Id
的值可能会有所不同。尽管如此,只要@Id
具有相同的类型,但不一定具有相同的值,查询计划将被重用。关键是:我们从来没有为参数指定类型。AddWithValue
方法推断类型,但是是从传递给它的数据来推断的,而不是数据库中的列类型。这里的问题是值“Sander”
被推断为nvarchar(6)
,而值“Bill”
变成了nvarchar(4)
。
另一个问题,也可能是对性能更具破坏性的一个问题,是类型是否被错误地推断出来。例如,FirstName
数据库字段是一个varchar(256)
,但是您的参数有值“Sander”
。这些是不同的类型,为了比较这些类型的值,在比较之前,SQL Server 必须将FirstName
列中的每个值隐式转换为nvarchar(6)
。虽然 SQL Server 可以优化其中的一些差异,但总有一天会让你大吃一惊。
所以为了解决这个问题,我们应该改为调用Parameters.Add
。不幸的是,微软没有给我们留下一个简单的重载,我们可以指定类型、大小、精度和缩放以及值。在varchar(5)
中,varchar 是类型,5 是大小。在decimal(10, 5)
中,十进制是类型,10 是精度,5 是刻度。Parameters.Add
有少量过载;大多数人会为你创建SqlParameter
,一个人只需要一个SqlParameter
作为输入。所有人都返回添加的SqlParameter
。
代码清单 16:一个具有正确类型和大小的参数
command.Parameters.Add("FirstName", SqlDbType.VarChar, 256).Value = "Sander";
所以你看,参数化你的查询不仅安全、健壮、有性能,而且不是很难。
插入、更新和删除数据的方式基本相同。我们用ExecuteNonQuery
代替ExecuteReader
,只返回受影响的行数。
代码清单 17:插入语句
public int
InsertPerson(Person person)
{
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"INSERT INTO
dbo.Person (FirstName, LastName, DateOfBirth, GenderId) "
+ "VALUES
(@FirstName, @LastName, @DateOfBirth, @GenderId)",
connection))
{
command.Parameters.Add("FirstName", SqlDbType.VarChar, 256).Value = person.FirstName;
object dbLastName = person.LastName;
if (dbLastName == null)
{
dbLastName = DBNull.Value;
}
command.Parameters.Add("LastName", SqlDbType.VarChar, 256).Value = dbLastName;
object dbDateOfBirth = person.DateOfBirth;
if (dbDateOfBirth == null)
{
dbDateOfBirth = DBNull.Value;
}
command.Parameters.Add("DateOfBirth", SqlDbType.SmallDateTime).Value = dbDateOfBirth;
object dbGenderId = person.GenderId;
if (dbGenderId == null)
{
dbGenderId = DBNull.Value;
}
command.Parameters.Add("GenderId", SqlDbType.Int).Value = dbGenderId;
connection.Open();
return command.ExecuteNonQuery();
}
}
同样,整个空到DBNull.Value
的转换非常烦人。这里有两个漂亮的(扩展)方法,可以让这类代码变得更短。不幸的是,它们适用于所有类型,无论它们是否与数据库兼容,因此错误的使用将导致Exception
。
代码清单 18:一些参数实用程序
public static class DbUtils
{
public static object ToDbParameter<T>(this T? value)
where T
: struct
{
object dbValue = value;
if (dbValue == null)
{
dbValue = DBNull.Value;
}
return dbValue;
}
public static object ToDbParameter(this object value)
{
object dbValue = value;
if (dbValue == null)
{
dbValue = DBNull.Value;
}
return dbValue;
}
}
现在的用法如下。
代码清单 19:实用程序的使用
command.Parameters.Add("FirstName", SqlDbType.VarChar, 256).Value = person.FirstName;
command.Parameters.Add("LastName", SqlDbType.VarChar, 256).Value = person.LastName.ToDbParameter();
command.Parameters.Add("DateOfBirth", SqlDbType.SmallDateTime).Value = person.DateOfBirth.ToDbParameter();
command.Parameters.Add("GenderId", SqlDbType.Int).Value = person.GenderId.ToDbParameter();
这看起来好多了。
UPDATE
方法看起来差不多;最大的不同是我们现在还需要一个WHERE
条款的Id
参数。
代码清单 20:更新语句
public int
UpdatePerson(Person person)
{
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"UPDATE
dbo.Person "
+ "SET FirstName
= @FirstName,"
+ " LastName =
@LastName,"
+ " DateOfBirth
= @DateOfBirth,"
+ " GenderId =
@GenderId "
+ "WHERE Id =
@Id",
connection))
{
command.Parameters.Add("Id", SqlDbType.Int).Value = person.Id;
command.Parameters.Add("FirstName", SqlDbType.VarChar, 256).Value = person.FirstName;
command.Parameters.Add("LastName", SqlDbType.VarChar, 256).Value = person.LastName.ToDbParameter();
command.Parameters.Add("DateOfBirth", SqlDbType.SmallDateTime).Value = person.DateOfBirth.ToDbParameter();
command.Parameters.Add("GenderId", SqlDbType.Int).Value = person.GenderId.ToDbParameter();
connection.Open();
return command.ExecuteNonQuery();
}
}
最后是DELETE
法。
代码清单 21: Delete 语句
public int
DeletePerson(Person person)
{
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"DELETE FROM
dbo.Person "
+ "WHERE Id =
@Id",
connection))
{
command.Parameters.Add("Id", SqlDbType.Int).Value = person.Id;
connection.Open();
return command.ExecuteNonQuery();
}
}
执行存储过程与前面所有的例子没有什么不同。是否需要调用ExecuteReader
、ExecuteScalar
或ExecuteNonQuery
取决于程序的性质。您可以将程序的名称传递给命令,并将CommandType
更改为StoredProcedure
。假设我们创建了一个存储过程GetFirstName
,它根据身份返回一个人的名字。该调用将如下所示。
代码清单 22:调用存储过程
public string GetPersonName(int id)
{
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("GetFirstName", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("Id", SqlDbType.Int).Value = id;
connection.Open();
object result = command.ExecuteScalar();
string firstName = null;
if (result != DBNull.Value)
{
firstName = (string)result;
}
return firstName;
}
}
存储过程可以利用输出参数,这些参数也很容易实现。假设我们使用一个输出参数作为名字。只需创建一个参数,将其Direction
设置为Output
,执行后读取值。
代码清单 23:一个输出参数
public string GetPersonName(int id)
{
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("GetFirstName", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("Id", SqlDbType.Int).Value = id;
SqlParameter param = command.Parameters.Add("FirstName", SqlDbType.VarChar, 256);
param.Direction = ParameterDirection.Output;
connection.Open();
command.ExecuteNonQuery();
string firstName = null;
if (param.Value != DBNull.Value)
{
firstName = (string)param.Value;
}
return firstName;
}
}
检索数据和手动映射到您自己的 C#类是相当繁琐的工作。将数据库中的数据直接存入内存的一个更简单的方法是使用SqlDataAdapter
。SqlDataAdapter
是相当过时的技术,但我想简要讨论一下它的完整性,因为您可能仍然会找到使用它的代码。这也是对下一章实体框架的一点介绍。
SqlDataAdapter
将数据库中的数据映射到一个DataSet
或DataTable
。一DataTable
就像你在 C#内存中的数据库表。A DataSet
是带有引用、约束等的DataTables
的集合。牛逼的是可以自动生成CREATE
、UPDATE
、DELETE
语句。使用DataTables
,还可以将表参数传递给 SQL Server。
要用数据库中的人填充一个DataTable
,我们只需调用SqlDataAdapter
上的Fill
方法,并给它一个DataTable
。
代码清单 24:填充数据表
public DataTable GetPeople()
{
DataTable people = new DataTable();
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"SELECT Id,
FirstName, LastName, DateOfBirth, GenderId FROM dbo.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(people);
}
return people;
}
SqlDataAdapter
在其构造函数中取一个SqlCommand
。SqlCommand
是用于选择数据的命令。有几个重载,一个采用 SQL SELECT
语句和一个连接字符串。适配器管理连接,因此不需要显式打开和关闭它。
这看起来很棒。代码比我们之前的代码短了很多,但是我们如何从DataTable
读取数据呢?不幸的是,DataTable
是行的集合,而行又是列的集合。因此,为了读取所有内容,我们必须遍历行,然后遍历列。可以通过索引或名称来访问这些列。价值观是所有的对象——所以拳击仍然存在,我们仍然需要施展——甚至DBNull
仍然是DBNull
。所以基本上,映射到定制的 C#对象和使用SqlDataReader
一样困难。
当您在代码中直接使用它们时,DataSets
的强度变得显而易见。这是有意义的,例如,在带有绑定的 WinForms 环境中。您可以直接更新您的DataSet
,DataSet
会记录更改。
代码清单 25:用适配器更新、插入和删除
public void
UpdatePeople()
{
DataTable people = new DataTable();
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"SELECT Id,
FirstName, LastName, DateOfBirth, GenderId FROM dbo.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
{
// Creates columns,
defines primary keys, foreign keys, etc.
adapter.FillSchema(people, SchemaType.Source);
adapter.Fill(people);
// If you followed my
examples, I am the first
// person in the
database, let's change my name.
people.Rows[0]["FirstName"] = "John";
// Delete Bill.
people.Rows[1].Delete();
// And add Satya.
people.Rows.Add(new object[] { null, "Satya", "Nadella", new
DateTime(1967, 8, 19), 1 });
// Sander is updated,
Bill is deleted, Satya is added.
// All in a single line
of code!
adapter.Update(people);
}
}
首先,我添加了SqlCommandBuilder
并通过了SqlDataAdapter
。SqlCommandBuilder
基于SELECT
查询构建CREATE
、UPDATE
和DELETE
SqlCommands
。顺便说一下,它并不能很好地做到这一点,所以您可能需要手动完成(命令是适配器的属性)。之后,我们必须使用DataSet
或DataTable
中的FillSchema
来填充模式。这将从数据库中获取表的模式,并为DataTable
提供关于列、类型、主键等的信息。Fill
方法获取数据。之后我们可以更新、删除和插入行到DataTable
。每个DataRow
都会记录自己的状态和变化。最后但并非最不重要的是,SqlDataAdapter
能够从DataTable
插入、更新和删除数据(按此顺序)。
正如我提到的,您可以使用DataTables
将表值参数传入存储过程。您可以在数据库中创建以下类型和过程来尝试这个。
代码清单 26:创建类型和存储过程
CREATE TYPE
FirstAndLastName AS TABLE
(
FirstName
VARCHAR(256) NOT NULL,
LastName
VARCHAR(256) NULL
)
GO
ALTER PROCEDURE
InsertPeople
@People
FirstAndLastName READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Person
(FirstName, LastName)
SELECT FirstName,
LastName
FROM @People
END
GO
现在,为了从代码中执行这个存储过程,我们可以传入一个DataTable
。
代码清单 27:数据表作为参数
public void
ExecInsertPeople()
{
string connectionString =
ConfigurationManager.ConnectionStrings["SuccinctlyDB"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("InsertPeople", connection))
{
command.CommandType = CommandType.StoredProcedure;
DataTable people = new DataTable();
people.Columns.Add("FirstName", typeof(string));
people.Columns.Add("LastName", typeof(string));
people.Rows.Add(new object[] { "Tim", "Cook" });
people.Rows.Add(new object[] { "Mark", "Zuckerberg" });
command.Parameters.Add("People", SqlDbType.Structured).Value = people;
connection.Open();
command.ExecuteNonQuery();
}
}
你可以用DataTables
和DataSets
做更多的事情,但是我不建议使用这种技术来访问数据库,除非你绝对必须这样做。
如你所见,我在前面的例子中使用了SqlConnections
、SqlCommand
和SqlDataReaders
。它们可以很好地与 SQL Server 配合使用,但是如果您使用的是 Oracle、MySQL 或其他关系数据库呢?
关于 ADO.NET 你应该知道的一件事是有很多抽象概念。例如SqlConnection
继承自DbConnection
,实现IDbConnection
接口。如果你在与甲骨文合作,你可能会使用从DbConnection
继承而来的OracleConnection
。同样,我们有一个OleDbConnection
和一个OdbcConnection
。它们与OracleCommand
、OleDbCommand
、OdbcCommand
协同工作,都是从DbCommand
继承而来。其他供应商已经实现了这些类的自己的版本。
所以你看,如果你知道如何使用一个,你几乎知道如何使用所有的。您可以在整个软件中使用这些基类之一,并使用依赖注入来获得正确的类型。DbProviderFactory
类是构建特定类型的连接、命令、参数等的基础工厂。