在实际开发项目中,通常采用存储过程查询数据,这样一来,不仅可以过滤SQL语句中非法字符,且存储过程在创建时即在服务器上进行编译。一旦创建存储过程,很多需要执行该过程的应用程序都可以调用存储过程,减少程序员可能出现的错误。应用存储过程可对数据进行添加、修改和删除等基本操作。在存储过程中还可以使用数据存取语句、流程控制语句、错误处理语句等。其主要特点是执行效率高,可重复使用。在创建存储过程时,SQL Server会将存储过程编译成一个执行计划并保存起来,在执行存储过程时,不需要重新编译,因此执行速度快。
另外,为了防止存储过程被恶意修改,可以对其进行加密操作。存储过程如果被修改,将会给应用程序带来灾难性的后果,从而使应用程序无法正常运行。同时应用存储过程还可以防止SQL注入式攻击。
SQL注入式攻击是指利用设计上的漏洞,在目标服务器上运行Sql命令以及进行其他方式的攻击,动态生成Sql命令时没有对用户输入的数据进行验证,此为Sql注入攻击得逞的主要原因。
例如:如果用户的查询语句是select * from 员工信息表 where 员工姓名='江南' ,如果用户名为:江南' or '1'='1,则查询语句将会变成:
SELECT*FROM员工信息表WHERE(员工姓名='江南')OR('1'='1')
经系统核对无误,将检索出所有数据。所以防范的时候需要对用户的输入进行检查。特别是一些特殊字符,比如单引号、双引号、分号、逗号、冒号、连接号等进行转换或者过滤。出现上述情况是完全可以应用查询存储过程解决。
使用SQL Server中的存储过程而不使用存储在客户计算机本地的T-SQL程序的优点如下。
(1)允许模块化程序设计
只需创建一次并将其存储在数据库中,以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建,并可独立于程序源代码而单独修改。
(2)允许更快执行
(3)减少网络流量
一个需要数百行T-SQL代码的操作由一条执行过程代码的单独语句就可以实现,而不需要在网络中发送数百行代码。
(4)可作为安全机制使用
即使对于没有直接执行存储过程中语句的权限的用户,也可以授予他们执行该存储过程的权限。
SQL Server存储过程可以用T-SQL语句的CREATE PROCEDURE创建,并可用ALTER PROCEDUREY语句进行修改,也可以用可视化管理工具——企业管理器来创建存储过程。
使用T-SQL语句CREATE PROCEDRE创建存储过程前,应考虑下列事项。
不能将CREATE PROCEDRE语句与其他SQL语句组合到单个批处理中。
创建存储过程的权限默认属于数据库中所有者,该所有者可将此权限授予其他用户。
存储过程是数据库对象,其名称必须遵守标识符规则。
只能在当前数据库中创建存储过程。
创建存储过程时,还需要指定:
所有输入参数和向调用过程或批处理返回的输出参数。
执行数据库操作(包括调用其他过程)的编程语句。
2.实施过程
方案实施一 应用存储过程对数据进行添加和删除操作
本实例根据RadioButtonList控件中的选择条件,来判断对图书信息的操作——数据的添加或更新操作。这两种操作都是通过使用使用企业管理器来创建存储过程。
具体实施过程。
(1)新建一个网站,将其命名为8,默认主页为Default.aspx。
(2)在Default.aspx页面中涉及的主要控件如表3.6所示。
表 Default.aspx页面用到的控件
控件类型 | 控件ID名称 | 用途 |
HTML/Button | btnBackup | 执行图书信息添加操作 |
btnRestore | 执行图书信息更新操作 | |
标准/RadioButtonList | RadioButtonList1 | 单项选择条件 |
标准/DropDownList | DropDownList3 | 选择图书ID号 |
DropDownList1 | 选择是否推荐选项 | |
标准/Button | Button1 | 输入图书名称信息 |
Button2 | 输入图书介绍信息 | |
Button3 | 输入图书建议价格信息 |
使用存储过程添加数据之前,首先需要创建存储过程probookinfo,代码如下。
/*创建存储过程并给予命名*/
CREATE PROCEDUREprobookinfo
(@BookName[VarChar](50),
@BookIntroduce [VarChar](50),
@BookPrice [Money],
@BookIsNew [VarChar](50))
/*指明该存储过程中将要执行的动作*/
AS
/*包含在存储过程中的SQL语句*/
INSERTINTO[mrdb].[dbo].[tb_BookInfo](
[BookName],
[BookIntroduce],
[BookPrice],
[BookIsNew])
VALUES(
@BookName,
@BookIntroduce,
@BookPrice,
@BookIsNew)
GO
使用存储过程修改数据之前,首先需要创建存储过程probookinfom,代码如下。
/*创建存储过程并给予命名*/
CREATE PROCEDUREproUpdatebookinfo
(
/*定义存储过程的参数和类型*/
@BookID[int],
@BookName[VarChar](50),
@BookIntroduce[VarChar](50),
@BookPrice [Money],
@BookIsNew [Char](10)
)
AS/*指明该存储过程要执行的动作*/
/*包含在存储过程中的SQL语句*/
UPDATE[mrdb].[dbo].[tb_Book]/*欲更新的数据库的表*/
SET
[BookID]=@BookID,
[BookName]=@BookName,
[BookIntroduce]=@BookIntroduce,
[BookPrice]=@BookPrice,
[BookIsNew]=@BookIsNew
WHERE
([BookID]=@BookID)
GO
(4)主要程序代码
在命名空间区域引用using System.Data.SqlClient和using System.IO命名空间,代码如下。
/***系统自动生成的命名空间省略***/
usingSystem.Data.SqlClient; //引用命名空间
usingSystem.IO; //引用命名空间
当RadioButtonList控件选择“图书信息添加”选项时,图书信息添加界面显示。在此界面中双击【图书信息添加】按钮触发该按钮的Click事件,在该事件中通过应用存储过程执行对数据库的添加操作,代码如下。
protectedvoidbtnBackup_Click(objectsender,EventArgse)
{
SqlConnectionmyconn=newSqlConnection("server=(local);Database=mrdb;Uid=sa;Pwd=");
//打开链接
myconn.Open();
stringstr="selectcount(*)fromtb_BookInfowhereBookName='"+TextBox1.Text.ToString()+"'";
//创建SqlCommand对象
SqlCommandcom=newSqlCommand(str,myconn);
intintcont=Convert.ToInt32(com.ExecuteScalar());
if(intcont>0)
{
Response.Write("<scriptlanguage=javascript>alert('对不起!
不允许填写相同记录!')</script>");
}
else
{
try
{
SqlCommandmycom=newSqlCommand("probookinfo",myconn);
//调用存储过程
mycom.CommandType=CommandType.StoredProcedure;
//添加参数
SqlParameter[]prams={
newSqlParameter("@BookName",SqlDbType.VarChar,50),
newSqlParameter("@BookIntroduce",SqlDbType.VarChar,50),
newSqlParameter("@BookPrice",SqlDbType.Money,8),
newSqlParameter("@BookIsNew",SqlDbType.Char,10)
};
//给参数赋值
prams[0].Value=TextBox1.Text;
prams[1].Value=TextBox2.Text;
prams[2].Value=Convert.ToDecimal(TextBox3.Text);
prams[3].Value=DropDownList1.SelectedValue.ToString();
foreach(SqlParameterparameterinprams)
{
mycom.Parameters.Add(parameter);
}
//执行sql语句
mycom.ExecuteNonQuery();
myconn.Close();
//BindData();
Response.Write("<scriptlanguage=javascript>alert('
添加成功!')</script>");
}
catch(Exceptionex)
{
Response.Write(ex.Message.ToString());
}
}
}
注意:在编写上述代码前,必须引用using System.Data.SqlClient命名空间。
当RadioButtonList控件选择“图书信息修改”选项时,图书信息修改界面显示。在此界面中双击【图书信息修改】按钮触发该按钮的Click事件,在该事件中通过应用存储过程执行对数据库的更新操作,代码如下。 protectedvoidbtnRestore_Click(objectsender,EventArgse)
{
SqlConnectionmyconn=newSqlConnection("server=(local);Database=mrdb;Uid=sa;Pwd=");
//打开链接
myconn.Open();
//创建SqlCommand对象
SqlCommandmycom=newSqlCommand("proUpdatebookinfo",myconn);
//调用存储过程
mycom.CommandType=CommandType.StoredProcedure;
//添加参数
SqlParameter[]prams={
newSqlParameter("@BookName",SqlDbType.VarChar,50),
newSqlParameter("@BookIntroduce",SqlDbType.VarChar,50),
newSqlParameter("@BookPrice",SqlDbType.Money,8),
newSqlParameter("@BookIsNew",SqlDbType.Char,10),
newSqlParameter("@BookID",SqlDbType.Int,4)
};
//给参数赋值
prams[0].Value=TextBox4.Text;
prams[1].Value=TextBox6.Text;
prams[2].Value=Convert.ToDecimal(TextBox7.Text);
prams[3].Value=DropDownList2.SelectedValue.ToString();
//prams[4].Value=TextBox5.Text.Trim();
prams[4].Value=Convert.ToInt32(DropDownList3.SelectedValue.ToString());
//依次把参数传入命令文本
foreach(SqlParameterparameterinprams)
{
mycom.Parameters.Add(parameter);
}
try
{
//执行更新语句
mycom.ExecuteNonQuery();
Response.Write("<scriptlanguage=javascript>alert('修改成功!')</script>");
}
catch(Exceptionex)
{
Response.Write(ex.Message.ToString());
Response.Write("<scriptlanguage=javascript>alert('修改失败!')</script>");
}
}
注意:在编写上述代码前,需要在命名空间区域添加“using System.Data.SqlClient“命名空间。
当RadioButtonList控件选择“图书信息修改”选项时,图书信息修改界面显示,在此界面中当从下拉框中选择图书ID号时,数据库中相应的全部数据将显示。用户可根据实际需要更新所列出的数据。代码如下。
protectedvoidDropDownList3_SelectedIndexChanged(objectsender,EventArgse)
{
if(DropDownList3.SelectedValue.ToString()!=null)
{
stringcmdtxt1="Server=(local);DataBase=mrdb;Uid=sa;Pwd=";
stringcmdtxt2="select*fromtb_Book
wherebookid='"+Convert.ToInt32(DropDownList3.SelectedValue)+"'";
SqlConnectionmycon=newSqlConnection(cmdtxt1);
mycon.Open();
SqlCommandmycom=newSqlCommand(cmdtxt2,mycon);
SqlDataReaderdr=mycom.ExecuteReader();
while(dr.Read())
{
TextBox4.Text=dr[1].ToString();
TextBox6.Text=dr[2].ToString();
TextBox7.Text=dr[3].ToString();
}
dr.Close();
mycon.Close();
}
}
}
注意:在编写上述代码前,必须引用using System.Data.SqlClient命名空间