6.5 常用的数据库操作

6.5　常用的数据库操作

6.5　常用的数据库操作

本节将以Microsoft SQL Server为例，讨论如何使用Command和DataReader来操作数据库的函数、触发器和存储过程。

为了测试的需要，需要在Microsoft SQL Server的ASPNET4数据库里添加一张Employee表。表结构如图6-14所示。

图　6-14　数据库ASPNET4里的Employee表结构

6.5.1　使用数据库函数

既然要调用数据库函数，首先就需要来创建一个函数ConvertString，该函数用来处理一个字符串，它把字符串中的每个单词的第一个字母转换为大写字母。函数代码如下所示：

CREATE function[dbo].[ConvertString]

（

——定义输入参数

@inputString varchar（2000）

）

——定义函数返回的类型

returns varchar（2000）

begin

——转换为小写字母

set@inputString=lower（@inputString)

——设置第一个字母大写

set@inputString=stuff（@inputString，1，1，upper(substring（@inputString，1，1）））

——定义临时变量i，用做循环

declare@i int

set@i=1

——循环处理输入字符串

while@i＜len（@inputString)

begin

——检查是否为单词的开始

if substring（@inputString，@i，1）=''

begin

——设置第一个字母大写

set@inputString=stuff（@inputString，@i+1，1，upper(substring（@inputString，@i+1，1）））

end

——循环变量增1

set@i=@i+1

end

——返回修改后的字符串

return@inputString

end

定义好函数ConvertString之后，就可以直接在SQL语句中应用该函数了，其方法与使用系统函数一样。不过需要注意的是，在Microsoft SQL Server 2005中，需要在自定义函数前加一个dbo，即dbo.ConvertString。示例代码如下所示：

public partial class WebForm1：System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e）

{

string sql="select employeename, department, address，

email from Employee"；

BindGridView(sql)；

}

protected void Button1_Click(object sender, EventArgs e）

{

//调用ConvertString函数

string sql="select employeename, department, address，

dbo.ConvertString(email)email from Employee"；

BindGridView(sql)；

}

private void BindGridView(string sql)

{

string connectionString=

WebConfigurationManager.ConnectionStrings

["ConnectionString"].ConnectionString；

SqlConnection con=new SqlConnection(connectionString)；

SqlCommand cmd=new SqlCommand(sql, con)；

using(con)

{

con.Open（）；

SqlDataReader dr=cmd.ExecuteReader（）；

GridView1.DataSource=dr；

GridView1.DataBind（）；

dr.Close（）；

}}

}

示例的初始运行结果如图6-15所示。

在图6-1 5中，当单击页面上的“调用ConvertString函数”按钮时，将触发protected void Button1_Click(object sender, EventArgs e）事件。在该事件里，通过语句“string sql="select employeename, department, address, dbo.Convert String(email)email from Employee"；”调用了自定义的ConvertString函数，将Employee表的“email”字段里的内容的首个字母全部转换为大写字母。其结果如图6-16所示。

图　6-15　初始运行结果

6.5.2　使用触发器

触发器是一种特殊的存储过程，类似于其他编程语言中的事件函数，SQL Server允许为insert、update、delete创建触发器，当在表（或者视图）中插入、更新、删除记录时，触发一个或一系列T-SQL语句。

图　6-16　单击“调用ConvertString函数”按钮后的运行结果

下面的示例定义了Employee表的触发器EmployeeTrigger。其中，EmployeeTrigger的触发器在Employee表的字段employeename、department、address和email被修改时触发。代码如下所示：

create trigger EmployeeTrigger

on dbo.Employee

for update

if update(employeename)

begin

select'employeename字段被修改'

end

if update(department)

begin

select'department字段被修改'

end

if update(address)

begin

select'address字段被修改'

end

if update(email)

begin

select'email字段被修改'

end

这样，当对Employee表的employeename、department、address和email字段执行update操作时，将会触发EmployeeTrigger触发器，返回修改信息，如图6-17所示。

图　6-17　触发器的返回值

6.5.3　使用存储过程

其实，在一些大型数据库系统中，存储过程(（Sored Procedure)和触发器具有很重要的作用。存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程在运算时生成执行方式，所以，以后对其再运行时其执行速度很快。因此，无论存储过程还是触发器，都是SQL语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程。

在SQL Server中有两类存储过程：系统存储过程和用户自定义存储过程。

系统存储过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。通过系统存储过程，SQL Server中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。尽管这些系统存储过程被放在master数据库中，但是仍可以在其他数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。

用户自定义存储过程也就是常在程序中使用的存储过程，它是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。

相比之下，存储过程存在着许多优点：

1）存储过程允许标准组件式编程。存储过程被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。

2）存储过程能够实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。

3）存储过程能够减少网络流量。对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL语句，从而大大增加了网络流量，降低了网络负载。

4）存储过程可被作为一种安全机制来充分利用。系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。

存储过程的创建方法很简单，例如下面的AddEmployee存储过程实现了Employee表信息的添加功能。它有4个参数(（eployeename、department、address、email)和1个输出参数(（gtsucceed)。其中，employeename、department、address和email参数是向表Employee中需要添加的信息；getsucceed参数输出信息添加的结果，如果信息添加成功则返回employeeid的值，添加失败则返回0。如下面的代码所示：

USE[ASPNET4]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE procedure[dbo].[AddEmployee]

（

@employeename varchar（100），

@department varchar（100），

@address varchar（200），

@email varchar（200），

——输出参数：如果添加成功，则返回employeeid的值；否则返回0

@getsucceed int output

）

——如果存在相同的employeename，则将@getsucceed参数返回0，结束添加操作

if Exists(Select employeename from Employee

where employeename=@employeename)

begin

——给@getsucceed赋值0

Set@getsucceed=0

end

——不存在相同的employeename，继续执行添加操作

else

begin

——临时变量@id用于保存max(employeeid)+1的值

declare@id as int

Set@id=(（Slect max(employeeid)+1 from Employee)

——执行添加操作

Insert into Employee

(（eployeeid, employeename, department, address, email)

values（@id，@employeename，@department，@address，@email)

——将@id的值赋给@GetSucceed

Set@getsucceed=@id

end

设计好存储过程AddEmployee之后，为了便于测试这个存储过程，还需要继续来设计一个简单的Web页面。页面代码如下所示：

＜form id="form1"runat="server"＞

＜div＞

＜asp:GridView ID="GridView1"runat="server"＞

＜/asp:GridView＞

＜br/＞

＜asp:Label ID="lb_msg"runat="server"＞＜/asp:Label＞

＜br/＞

employeename：＜asp:TextBox ID="txt_employeename"

runat="server"Width="213px"＞＜/asp:TextBox＞

＜br/＞

department：＜asp:TextBox ID="txt_department"

runat="server"Width="235px"＞＜/asp:TextBox＞

＜br/＞

address：＜asp:TextBox ID="txt_address"runat="server"

Width="256px"＞＜/asp:TextBox＞

＜br/＞

email：＜asp:TextBox ID="txt_email"runat="server"

Width="271px"＞＜/asp:TextBox＞

＜br/＞

＜asp:Button ID="Add_Employee"runat="server"

Text="添加"Width="87px"

OnClick="Add_Employee_Click"/＞

/div＞

＜/form＞

在后台代码里调用存储过程与调用SQL语句的方式相似。首先，需要创建一个SqlCommand对象来调用存储过程的名称，并将该对象的CommandType设置为CommandType.StoredProcedure。如下面的代码所示：

SqlCommand cmd=new SqlCommand（"AddEmployee"，con)；

cmd.CommandType=CommandType.StoredProcedure；

接下来，就要将存储过程的参数加入到Command.Parameters集合中。添加参数时，需要精确地指定数据类型和参数的大小，以便于和数据库中的类型进行匹配。如下面的代码所示：

cmd.Parameters.Add(new SqlParameter（

"@employeename"，SqlDbType.VarChar，100））；

cmd.Parameters["@employeename"].Value=employeename；

添加完参数之后，就可以同执行SQL一样采用ExecuteNonQuery（）方法来执行存储过程了。还可以使用cmd.Parameters["@getsucceed"].Value的形式来得到输出参数的值。完整的示例代码如下所示：

public partial class WebForm2：System.Web.UI.Page

{

private readonly string connectionString=

WebConfigurationManager.ConnectionStrings

["ConnectionString"].ConnectionString；

protected void Page_Load(object sender, EventArgs e）

{

if（！Page.IsPostBack)

{

BindGridView（）；

}

protected void Add_Employee_Click(object sender, EventArgs e）

{

int i=AddEmployee(txt_employeename.Text，

txt_department.Text, txt_address.Text, txt_email.Text)；

if(i==0）

{

lb_msg.Text="0：数据库里面存在相同的employeename"；

}

else

{

lb_msg.Text="添加成功，新增加的employeeid为："

+i.ToString（）；

}

BindGridView（）；

}

private int AddEmployee(string employeename，

string department, string address, string email)

{

SqlConnection con=new SqlConnection(connectionString)；

SqlCommand cmd=new SqlCommand（"AddEmployee"，con)；

cmd.CommandType=CommandType.StoredProcedure；

//employeename

cmd.Parameters.Add(new SqlParameter（

"@employeename"，SqlDbType.VarChar，100））；

cmd.Parameters["@employeename"].Value=employeename；

//department

cmd.Parameters.Add(new SqlParameter（

"@department"，SqlDbType.VarChar，100））；

cmd.Parameters["@department"].Value=department；

//address

cmd.Parameters.Add(new SqlParameter（

"@address"，SqlDbType.VarChar，200））；

cmd.Parameters["@address"].Value=address；

//email

cmd.Parameters.Add(new SqlParameter（

"@email"，SqlDbType.VarChar，200））；

cmd.Parameters["@email"].Value=email；

//getsucceed

cmd.Parameters.Add(new SqlParameter（

"@getsucceed"，SqlDbType.Int，4））；

cmd.Parameters["@getsucceed"].Direction=

ParameterDirection.Output；

using(con)

{

con.Open（）；

cmd.ExecuteNonQuery（）；

return(int)cmd.Parameters["@getsucceed"].Value；

}

private void BindGridView（）

{

string sql="select*from Employee"；

SqlConnection con=new SqlConnection(connectionString)；

SqlCommand cmd=new SqlCommand(sql, con)；

using(con)

{

con.Open（）；

SqlDataReader dr=cmd.ExecuteReader（）；

GridView1.DataSource=dr；

GridView1.DataBind（）；

dr.Close（）；

}

运行上面的示例，当输入一个数据库里存在的名称“马伟”时，其结果如图6-18所示。

在图6-18中，因为输入了一个数据库里存在的名称，所以存储过程输出参数输出0，即添加不成功。现在继续来输入一个数据库里不存在的名称“马伟1”，系统添加成功，存储过程输出添加的“employeeid”的值5，如图6-19所示。

图　6-18　输入存在名称的运行结果

图　6-19　输入正常名称的运行结果