设置带可选参数的存储过程，并使用强类型TableAdapter执行所述存储过程
收集有关存储过程可选参数以及将 null 值传递给强类型数据适配器 Fill 方法的信息。
引言
我确切地知道本文的零散部分分散在本网站以及其他编程网站上。我将尝试将它们全部整合到一个“从头到尾”的解决方案中。
背景
即使是构建一个简单的程序内数据搜索引擎也可能很复杂；构建一个针对相同程序内数据的高级搜索引擎，其中任何或所有参数都可以使用，这可能是一场噩梦。
我想分享我对此问题的解决方案；但是，请记住，我确定不止一种方法可以做到这一点。它只是我发现的最有效的方法（平衡速度和快速实现）。
预规划阶段
我们有一个用于纹身店管理器的数据库，其中包含以下表
- Clients - 包含姓名、地址、城市、州、出生日期、电子邮件、电话号码、性别、引导他们找到公司的广告类型。
- Jobs - 包含工作标题、类型、分配给工作的客户 ID、分配给工作的艺术家 ID、工作日期、工作时长、工作状态等。
商店经理程序的一部分是客户管理器，它允许用户根据各种标准搜索（或过滤是更准确的术语）数据库中的客户。
上图中的所有内容都是可选的；它可以根据所有或任何标准进行搜索。
攻击计划
一种实现方法是，一旦用户单击“搜索客户”按钮，就遍历上面的所有控件并检查每个控件的值，然后根据存在的值构建一个内联 SQL Select 语句。
在我看来，这种方法相当直接，而且您永远不必离开您的代码。但有一些重大的弊端。
- 我当前的 WinForm 有 30 多个用于搜索引擎的输入控件；遍历、转换和检查每个控件都需要一些时间，更不用说可能需要几百行代码来实现。
- 搜索涉及多个表；让您的代码创建正确的 JOIN 可能会很糟糕。
- 添加或删除条件可能会导致 SQL
Select语句生成代码的大量重写。
另一种实现方法是编写存储过程，以涵盖所有可想象的组合或搜索条件。
当然，这完全不切实际，而且比上一种选择更有意义，但我认为应该提到可以这样做。
不使用此选项的一些简单原因
- 思考并编写每个存储过程所需的时间，您会老几岁。
- 实现搜索引擎的开发人员需要一个绑定器来保存存储过程列表以供参考。
现在是第三种实现方法。对我来说，这介于前两种选择之间。使用一个存储过程，我们提供所有可能的搜索参数，但我们将每个参数设为可选，以便存储过程可以使用部分、全部或不使用参数来执行。使用强类型 TableAdapter，我们使用以前的存储过程来填充我们的搜索结果数据集。
实现 - 步骤 1：编写存储过程
参数部分
CREATE PROCEDURE dbo.SearchClients
(
@ClientFirstName varchar(20) = null,
@ClientLastName varchar(20) = null,
@ClientHasTattoo bit = null,
@JobFinished bit = null,
@JobTypeID smallint = null,
@ArtistID Smallint = null,
@JobTotalHoursMinimum float = null,
@JobTotalHoursMaximum float = null,
@AdvertisingID smallint = null,
@ClientDOBMinimum datetime = null,
@ClientDOBMaximum datetime = null,
@ClientStreetAddress varchar(75) = null,
@ClientCity varchar(20) = null,
@ClientState varchar(2) = null,
@ClientZip varchar(10) = null,
@ClientHomePhone varchar(14) = null,
@ClientWorkPhone varchar(14) = null,
@ClientEmail varchar(50) = null,
@ClientSex bit = null,
@JobDateMinimum datetime = null,
@JobDateMaximum datetime = null,
@JobTitle varchar(50) = null
)
这里有几点需要注意
- 每个参数都设置为
null；这给了它一个默认值，当我们在不提供自己的值时存储过程可以使用。 - 您会注意到每个文本字段都是
varchar。我稍后会解释原因；现在，我只是想引起您的注意。
Select 部分
AS
IF @JobFinished IS NULL AND
@JobTypeID IS NULL AND
@ArtistID IS NULL AND
@JobTotalHoursMaximum IS NULL AND
@JobTotalHoursMinimum IS NULL AND
@JobDateMinimum IS NULL AND
@JobDateMaximum IS NULL AND
@JobTitle IS NULL
BEGIN
SELECT DISTINCT
ClientID, ClientFirstName, ClientLastName, ClientStreetAddress,
ClientCity, ClientState, ClientZip, ClientHomePhone,
ClientWorkPhone, ClientEmail, ClientDOB, ClientSex, ClientHasTattoo, AdvertisingID
FROM Clients
WHERE
(ClientFirstName LIKE ISNULL(@ClientFirstName, ClientFirstName) + '%') AND
(ClientLastName LIKE ISNULL(@ClientLastName, ClientLastName) + '%') AND
(ClientHasTattoo = ISNULL(@ClientHasTattoo, ClientHasTattoo)) AND
(AdvertisingID = ISNULL(@AdvertisingID, AdvertisingID)) AND
(ClientDOB >= ISNULL(@ClientDOBMinimum, ClientDOB)) AND
(ClientDOB <= ISNULL(@ClientDOBMaximum, ClientDOB)) AND
(ClientStreetAddress LIKE ISNULL(@ClientStreetAddress, ClientStreetAddress) + '%') AND
(ClientCity LIKE ISNULL(@ClientCity, ClientCity) + '%') AND
(ClientState LIKE ISNULL(@ClientState, ClientState) + '%') AND
(ClientZip LIKE ISNULL(@ClientZip, ClientZip) + '%') AND
(ClientHomePhone LIKE ISNULL(@ClientHomePhone, ClientHomePhone) + '%') AND
(ClientWorkPhone LIKE ISNULL(@ClientWorkPhone, ClientWorkPhone) + '%') AND
(ClientEmail LIKE ISNULL(@ClientEmail, ClientEmail) + '%') AND
(ClientSex = ISNULL(@ClientSex, ClientSex))
ORDER BY ClientID, ClientLastName, ClientFirstName
END
ELSE
BEGIN
SELECT DISTINCT
Clients.ClientID, Clients.ClientFirstName, Clients.ClientLastName,
Clients.ClientStreetAddress, Clients.ClientCity,
Clients.ClientState, Clients.ClientZip, Clients.ClientHomePhone,
Clients.ClientWorkPhone, Clients.ClientEmail,
Clients.ClientDOB, Clients.ClientSex,
Clients.ClientHasTattoo, Clients.AdvertisingID
FROM Job RIGHT OUTER JOIN
Clients ON Job.ClientID = Clients.ClientID
WHERE
(Clients.ClientFirstName LIKE ISNULL(@ClientFirstName, Clients.ClientFirstName) + '%') AND
(Clients.ClientLastName LIKE ISNULL(@ClientLastName, Clients.ClientLastName) + '%') AND
(Clients.ClientHasTattoo = ISNULL(@ClientHasTattoo, Clients.ClientHasTattoo)) AND
(Job.JobFinished = ISNULL(@JobFinished, Job.JobFinished)) AND
(Job.ArtistID = ISNULL(@ArtistID, Job.ArtistID)) AND
(Job.JobTotalHours >= ISNULL(@JobTotalHoursMinimum, Job.JobTotalHours)) AND
(Job.JobTotalHours <= ISNULL(@JobTotalHoursMaximum, Job.JobTotalHours)) AND
(Job.JobTypeID = ISNULL(@JobTypeID, Job.JobTypeID)) AND
(Clients.AdvertisingID = ISNULL(@AdvertisingID, Clients.AdvertisingID)) AND
(Clients.ClientDOB >= ISNULL(@ClientDOBMinimum, Clients.ClientDOB)) AND
(Clients.ClientDOB <= ISNULL(@ClientDOBMaximum, Clients.ClientDOB)) AND
(Clients.ClientStreetAddress LIKE ISNULL(@ClientStreetAddress,
Clients.ClientStreetAddress) + '%') AND
(Clients.ClientCity LIKE ISNULL(@ClientCity, Clients.ClientCity) + '%') AND
(Clients.ClientState LIKE ISNULL(@ClientState, Clients.ClientState) + '%') AND
(Clients.ClientZip LIKE ISNULL(@ClientZip, Clients.ClientZip) + '%') AND
(Clients.ClientHomePhone LIKE ISNULL(@ClientHomePhone, Clients.ClientHomePhone) + '%') AND
(Clients.ClientWorkPhone LIKE ISNULL(@ClientWorkPhone, Clients.ClientWorkPhone) + '%') AND
(Clients.ClientEmail LIKE ISNULL(@ClientEmail, Clients.ClientEmail) + '%') AND
(Clients.ClientSex = ISNULL(@ClientSex, Clients.ClientSex)) AND
(Job.JobDate >= ISNULL(@JobDateMinimum, Job.JobDate)) AND
(Job.JobDate <= ISNULL(@JobDateMaximum, Job.JobDate)) AND
(Job.JobTitle LIKE ISNULL(@JobTitle, Job.JobTitle) + '%')
ORDER BY Clients.ClientID, Clients.ClientLastName, Clients.ClientFirstName
END
这里有几点需要注意
- 您会注意到一个相当长的
IF语句。 - 您会注意到上面的 Select 语句中有许多
ISNULL。除了在参数部分将值设置为null之外，它们是此过程有效工作最重要的部分。 - 还记得参数部分中的那些
varchar吗？嗯，这就是我们使用它们的原因。 - 最后要注意的一点是 Select 语句前的
DISTINCT关键字，这可以确保您不会得到一长串重复的客户。
IF @JobFinished IS NULL AND
@JobTypeID IS NULL AND
@ArtistID IS NULL AND
@JobTotalHoursMaximum IS NULL AND
@JobTotalHoursMinimum IS NULL AND
@JobDateMinimum IS NULL AND
@JobDateMaximum IS NULL AND
@JobTitle IS NULL
这正在检查是否没有提供适用于客户表外部表的参数；然后我们可以使用稍微简化的 Select 语句
SELECT DISTINCT
ClientID, ClientFirstName, ClientLastName, ClientStreetAddress,
ClientCity, ClientState, ClientZip, ClientHomePhone,
ClientWorkPhone, ClientEmail, ClientDOB, ClientSex, ClientHasTattoo, AdvertisingID
FROM Clients
WHERE
(ClientFirstName LIKE ISNULL(@ClientFirstName, ClientFirstName) + '%') AND
(ClientLastName LIKE ISNULL(@ClientLastName, ClientLastName) + '%') AND
(ClientHasTattoo = ISNULL(@ClientHasTattoo, ClientHasTattoo)) AND
(AdvertisingID = ISNULL(@AdvertisingID, AdvertisingID)) AND
(ClientDOB >= ISNULL(@ClientDOBMinimum, ClientDOB)) AND
(ClientDOB <= ISNULL(@ClientDOBMaximum, ClientDOB)) AND
(ClientStreetAddress LIKE ISNULL(@ClientStreetAddress,
ClientStreetAddress) + '%') AND
(ClientCity LIKE ISNULL(@ClientCity, ClientCity) + '%') AND
(ClientState LIKE ISNULL(@ClientState, ClientState) + '%') AND
(ClientZip LIKE ISNULL(@ClientZip, ClientZip) + '%') AND
(ClientHomePhone LIKE ISNULL(@ClientHomePhone, ClientHomePhone) + '%') AND
(ClientWorkPhone LIKE ISNULL(@ClientWorkPhone, ClientWorkPhone) + '%') AND
(ClientEmail LIKE ISNULL(@ClientEmail, ClientEmail) + '%') AND
(ClientSex = ISNULL(@ClientSex, ClientSex))
ORDER BY ClientID, ClientLastName, ClientFirstName
或者，如果提供了一个参数来使用更高级的 Select 语句
SELECT DISTINCT
Clients.ClientID, Clients.ClientFirstName, Clients.ClientLastName,
Clients.ClientStreetAddress, Clients.ClientCity,
Clients.ClientState, Clients.ClientZip,
Clients.ClientHomePhone, Clients.ClientWorkPhone, Clients.ClientEmail,
Clients.ClientDOB, Clients.ClientSex,
Clients.ClientHasTattoo, Clients.AdvertisingID
FROM Job RIGHT OUTER JOIN
Clients ON Job.ClientID = Clients.ClientID
WHERE
(Clients.ClientFirstName LIKE ISNULL(@ClientFirstName,
Clients.ClientFirstName) + '%') AND
(Clients.ClientLastName LIKE ISNULL(@ClientLastName,
Clients.ClientLastName) + '%') AND
(Clients.ClientHasTattoo =
ISNULL(@ClientHasTattoo, Clients.ClientHasTattoo)) AND
(Job.JobFinished = ISNULL(@JobFinished, Job.JobFinished)) AND
(Job.ArtistID = ISNULL(@ArtistID, Job.ArtistID)) AND
(Job.JobTotalHours >= ISNULL(@JobTotalHoursMinimum, Job.JobTotalHours)) AND
(Job.JobTotalHours <= ISNULL(@JobTotalHoursMaximum, Job.JobTotalHours)) AND
(Job.JobTypeID = ISNULL(@JobTypeID, Job.JobTypeID)) AND
(Clients.AdvertisingID = ISNULL(@AdvertisingID, Clients.AdvertisingID)) AND
(Clients.ClientDOB >= ISNULL(@ClientDOBMinimum, Clients.ClientDOB)) AND
(Clients.ClientDOB <= ISNULL(@ClientDOBMaximum, Clients.ClientDOB)) AND
(Clients.ClientStreetAddress LIKE ISNULL(@ClientStreetAddress,
Clients.ClientStreetAddress) + '%') AND
(Clients.ClientCity LIKE ISNULL(@ClientCity, Clients.ClientCity) + '%') AND
(Clients.ClientState LIKE ISNULL(@ClientState,
Clients.ClientState) + '%') AND
(Clients.ClientZip LIKE ISNULL(@ClientZip, Clients.ClientZip) + '%') AND
(Clients.ClientHomePhone LIKE ISNULL(
@ClientHomePhone, Clients.ClientHomePhone) + '%') AND
(Clients.ClientWorkPhone LIKE ISNULL(
@ClientWorkPhone, Clients.ClientWorkPhone) + '%') AND
(Clients.ClientEmail LIKE ISNULL(@ClientEmail,
Clients.ClientEmail) + '%') AND
(Clients.ClientSex = ISNULL(@ClientSex, Clients.ClientSex)) AND
(Job.JobDate >= ISNULL(@JobDateMinimum, Job.JobDate)) AND
(Job.JobDate <= ISNULL(@JobDateMaximum, Job.JobDate)) AND
(Job.JobTitle LIKE ISNULL(@JobTitle, Job.JobTitle) + '%')
ORDER BY Clients.ClientID, Clients.ClientLastName, Clients.ClientFirstName
ISNULL(@JobFinished, Job.JobFinished))
如果 @JobFinished = null（我们使用默认值），则使用当前记录的 Job.JobFinished 的当前值。
(Clients.ClientFirstName LIKE ISNULL(@ClientFirstName, Clients.ClientFirstName) + '%')
使用 varchar 允许我们进行部分单词搜索，因为尾部空格会自动从 varchar 字符串中修剪（nchar 和 char 则不行）。结合 + '%'，您现在可以输入一个单词的开头，它仍然会提取所有以这些字母开头的单词。例如
如果我们传递 'jo' 作为 @ClientFirstName，该过程将选择“Joe”、“John”、“Joseph”等。
实现 - 步骤 2：填充强类型数据集
使用 Visual Studio 的数据集设计器，我们创建了一个带有存储过程的强类型数据集。设计器根据存储过程的参数自动创建 Fill() 和 GetData() 方法。
现在，只需将我们的值输入到 Fill() 方法中即可。
好了，看看它的实际效果！
this.searchClientsTableAdapter.Fill(
this.tattooDataSet.SearchClients,
this.NewClientFirstNameTextBox.Text == "" ? null : this.ClientFirstNameTextBox.Text,
this.NewClientLastNameTextBox.Text == "" ? null : this.NewClientLastNameTextBox.Text,
this.UndecidedRadioButton.Checked ? (bool?)null :
(this.HasTattooRadioButton.Checked ? true : false),
!this.UseJobIsCheckBox.Checked ? (bool?)null :
(this.BothRadioButton.Checked ? (bool?)null :
(this.CompletedRadioButton.Checked ? true : false)),
!this.UseJobTypeCheckBox.Checked ? (short?)null :
(short)this.JobTypeComboBox.SelectedValue,
!this.SearchArtistCheckBox.Checked ? (short?)null :
(short)this.ArtistsComboBox.SelectedValue,
!this.MinimumTimeCheckBox.Checked ? (double?)null :
double.Parse(this.MinimumHourNumericUpDown.Value.ToString()),
!this.MaximumTimeCheckBox.Checked ? (double?)null :
double.Parse(this.MaximumHourNumericUpDown.Value.ToString()),
!this.UseAdvertisementsCheckBox.Checked ? (short?)null :
(short)this.AdvertismentsComboBox.SelectedValue,
!this.BornAfterCheckBox.Checked ? (DateTime?)null :
this.MinimumDOBDateTimePicker.Value,
!this.BornBeforeCheckBox.Checked ? (DateTime?)null :
this.MaximumDOBDateTimePicker.Value,
this.NewClientAddressTextBox.Text == "" ? null :
this.NewClientAddressTextBox.Text,
this.NewClientCityTextBox.Text == "" ? null :
this.NewClientCityTextBox.Text,
this.NewClientProvenceTextBox.Text == "" ? null :
this.NewClientProvenceTextBox.Text,
this.NewClientPostalCodeTextBox.Text == "" ? null :
this.NewClientPostalCodeTextBox.Text,
this.NewClientHomePhoneTextBox1.Text == "" ? null :
this.NewClientHomePhoneTextBox1.Text +
(((this.NewClientHomePhoneTextBox1.TextLength ==
this.NewClientHomePhoneTextBox1.MaxLength) &&
(this.NewClientHomePhoneTextBox2.TextLength > 0)) ?
("-" + this.NewClientHomePhoneTextBox2.Text) : ("")) +
(((this.NewClientHomePhoneTextBox2.TextLength ==
this.NewClientHomePhoneTextBox2.MaxLength) &&
(this.NewClientHomePhoneTextBox3.TextLength > 0)) ?
("-" + this.NewClientHomePhoneTextBox3.Text) : ("")),
this.NewClientWorkPhoneTextBox1.Text == "" ? null :
this.NewClientWorkPhoneTextBox1.Text +
(((this.NewClientWorkPhoneTextBox1.TextLength ==
this.NewClientWorkPhoneTextBox1.MaxLength) &&
(this.NewClientWorkPhoneTextBox2.TextLength > 0)) ?
("-" + this.NewClientWorkPhoneTextBox2.Text) : ("")) +
(((this.NewClientWorkPhoneTextBox2.TextLength ==
this.NewClientWorkPhoneTextBox2.MaxLength) &&
(this.NewClientWorkPhoneTextBox3.TextLength > 0)) ?
("-" + this.NewClientWorkPhoneTextBox3.Text) : ("")),
this.NewClientEmailTextBox.Text == "" ? null :
this.NewClientEmailTextBox.Text,
this.BothSexRadioButton.Checked ? (bool?)null :
(this.MaleRadioButton.Checked ? true : false),
!this.ScheduledAfterCheckBox.Checked ? (DateTime?)null :
this.JobMinimumDateTimePicker.Value,
!this.ScheduledBeforeCheckBox.Checked ? (DateTime?)null :
this.JobMaximumDateTimePicker.Value,
!this.UseJobTitleCheckBox.Checked ? null : this.JobTitleSearchTextBox.Text);
为了提高可读性，我将每个参数都放在单独的行上，但这仍然只是一个函数调用。添加或删除搜索选项就像添加或删除参数一样简单。
关于 Fill() 方法，我特别想指出两件非常巧妙的事情，它们都涉及一个单一字符：?。
- ? 是条件运算符，它以一种或另一种形式存在了很多年（我记得在我还在编写纯 C 代码时就用过它）。
- 它们可以去别人去不了的地方（尝试将
if语句作为方法调用的参数）。 - 它们提供了一种更清洁、更高效、更简洁的解决方案。
- 首先，
(this.CompletedRadioButton.Checked ? true : false)评估为true或false。 - 其次，
this.BothRadioButton.Checked ? (bool?)null :评估为null或上一个条件的结果。 - 最后，
!this.UseJobIsCheckBox.Checked ? (bool?)null :评估为null或上一个条件的结果。 - 如果 Completed 单选按钮被选中，则为 true；否则，我们假设 Incompleted 单选按钮被选中，将
false传递给下一步。 - 现在，如果任何一个单选按钮被选中，那么我们就知道 Incompleted 单选按钮没有被选中，所以我们将 null 传递给下一步；如果不是，那么我们就知道 Incompleted 单选按钮肯定被选中了，所以我们将
false传递给下一步。 - 最后，如果 Job Is 复选框未选中，那么搜索的该部分将被禁用，并且无论前面的值是
true、false还是null，它都自动为null；否则，它将评估为前一个语句的值。 - ? 在 .NET 2.0 中，除了之前提到的作为条件运算符的职责外，它也是新的可空运算符。这使得任何与数据库交互的人都非常高兴！
如果您将 if...else 和 select...case 语句视为编程武器库中的步兵，它们可以完成工作，但有时在战术上会显得残酷而混乱，而 ? 则是您的特种部队。
this.NewClientCityTextBox.Text == "" ? null : this.NewClientCityTextBox.Text,
第一部分 this.NewClientCityTextBox.Text == "" 是条件部分；如果它评估为 true，则使用 ? 之后的值，在本例中为 null；否则，使用 : 之后的值，在本例中为 this.NewClientCityTextBox.Text。
条件语句可以嵌套，并且按从右到左的顺序进行评估。
!this.UseJobIsCheckBox.Checked ? (bool?)null :
(this.BothRadioButton.Checked ? (bool?)null :
(this.CompletedRadioButton.Checked ? true : false)),
如果您回看上面的图片，您会注意到 Job Is 复选框已选中，在其右侧有三个单选按钮，它们实际上包含在自己的面板中以进行相应分组。
现在，用通俗的语言来看上面的嵌套条件语句，我们说的是
C#、VB.NET、C、Java 等的某些数据类型，以及 .NET 框架类型（如 bool），例如，没有可以赋值为 null 的 null 状态。
bool IsThisNull = null;
无法编译。在数据库中，布尔值可以是 true、false 或 null。过去的问题在于弥合源代码 null 和数据库 null 之间的 null 差距。可空运算符可以很好地做到这一点。
bool? IsThisNull = null;
这可以编译并正常工作。IsThisNull 的行为仍然像普通的 bool 一样，额外的好处是它可以为 null，用于将值传递给数据库存储过程或内联 SQL 语句。
正如 .NET 2.0 框架提供给我们像 ++、--、=+ 这样的重载运算符一样，它也提供了一个重载的可空运算符 ??。
bool? IsThisNull = null;
bool RegularBool = IsThisNull ?? false;
RegularBool 被赋值为 IsThisNull 持有的任何 true 或 false 值，除非 IsThisNull 为 null - 那么 RegularBool 为 false。
我们还没玩够，各位。显式转换（或装箱）
!this.UseAdvertisementsCheckBox.Checked ? (short?)null :
(short)this.AdvertismentsComboBox.SelectedValue,
注意 (short?)？这个方便的小 ? 将我们普通的 null 值转换为一个带有 null 值的可空短类型，因此我们的存储过程可以得到它想要的 null 值，而我们的类型安全 C# 方法可以得到它期望的 short 类型参数。
每个人都得到他们想要的，并秉承 MPATQFTHG 的精神：“普天同庆”。
回顾
步骤 1 - 编写我们的存储过程，记住将每个参数的默认值设置为 null；然后使用 ISNULL 语句来确保在 Select 语句中传递正确的值。
步骤 2 - 在 Visual Studio 中创建带有强类型 TableAdapter 的强类型数据集。
步骤 3 - 调用生成的 Fill() 方法，使用 ? 条件运算符以及 ? 可空运算符来过滤并传递正确的值给参数以及相应地转换正确的类型。
作者专区
我可能不会提供代码示例供下载，其简单原因是因为本文旨在为您提供一个创建自己的工作解决方案的良好分步计划，而不是一个可用的解决方案。此外，您需要知道的所有内容都在文章的 <pre></pre> 块中。
将来，我会整理一个演示程序供您玩耍；目前，它被用于一个正在开发中的庞大的软件套件中，这里没有人愿意花大价钱来了解拖拉机是如何运行的。
我始终欢迎评论、问题，甚至投诉，所以请不要害羞。
历史
- 2006 年 3 月 24 日 - 初版。
- 2006 年 3 月 29 日 - 做了一些小的改动；有人指出我称其为 DataAdapter 而不是 TableAdapter；我太习惯于在 1.1 中使用 DataAdapters 了，以至于完全忽略了这一点。