目录
一、存储过程概念
存储过程就像数据库中运行方法(函数)和C#中的方法一样,由存储过程名、存储过程参数组成,可以有返回值 。
if、else、while、变量、insert、select都可以在存储过程中使用。
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。
优点:
- 执行速度更快,数据库中保存的存储过程都是编译过的;
- 允许模块化程序设计—类似方法的复用 提高系统安全性—防止SQL注入
- 减少网络流通量—只需要传入存储过程名称
缺点:
- 存储过程太多的话,所有的压力都由数据库来承担
种类:
- 系统存储过程 以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。
- 本地存储过程 用户创建的存储过程是由用户创建并完成某一特定功能的存储过程,事实上一般所说的存储过程就是指本地存储过程。
- 临时存储过程一是本地临时存储过程,以井字号(#)作为其名称的第一个字符,则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程,且只有创建它的用户才能执行它;二是全局临时存储过程,以两个井字号(##)号开始,则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程,全局临时存储过程一旦创建,以后连接到服务器的任意用户都可以执行它,而且不需要特定的权限。
- 远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程,通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。
- 扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程,而且扩展存储过程的名称通常以xp_开头。
二、存储过程的操作
1.创建:
CREATE PROCEDURE [拥有者.]存储过程名[;程序编号]
[(参数#1,…参数#1024)]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] [FOR REPLICATION]
AS 程序行 --存储过程要执行的操作
eg:建立一个简单的存储过程order_tot_amt,根据用户输入的订单ID号码(@o_id),由订单明细表 (orderdetails)中计算该订单销售总额[单价(Unitprice)*数量(Quantity)],这一金额通过@p_tot这一参数输出给调用这一存储过程的程序。
CREATE PROCEDURE order_tot_amt
@o_id int, @p_tot int output
AS SELECT @p_tot = sum(Unitprice*Quantity)
FROM orderdetails WHERE orderid=@o_id
2.调用
exec Procedure_Name [参数名] --调用存储过程Procedure_Name。
3.删除
drop procedure sp_name
注意事项:不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
eg:创建存储过程实现验证功能,如果成功则返回,失败,返回
create procedure proc_Validate
@User varchar(20), --参数:用户名 @Pwd varchar(20), --参数:密码 @Flag bit output --参数:返回值
as begin
if exists(select * from UserInfo where UserName=@User and UserPwd=@Pwd) select @Flag= 1
else select @Flag= 0 end
--删除存储过程
drop procedure proc_Validate
--调用存储过程
declare @result bit;
declare @dd bit;
exec proc_Validate '张三','123456',@result output
select @result
4.在程序中调用存储过程
a.数据库语句变成存储过程名称;
b.command命令类型改成存储过程;
c.添加参数(参数名称与存储过程名称严格一致!!!),输出参数的direction置为output
eg:
private void button1_Click(object sender, EventArgs e)
{
//1、创建连接对象
using (SqlConnection con=new SqlConnection(strCon))
{
//6、调用open方法打开
con.Open();
//2、创建SQL语句 sql语句中放的是存储过程的名称
string sql = "proc_UserCheck";
//3、实例化一个Command对象
SqlCommand cmd = new SqlCommand(sql, con);
//4、Command的类型选取成存储过程
cmd.CommandType = CommandType.StoredProcedure;
//5、为Command对象添加参数,切记切记:参数名称要与存储过程的名称严格一致。
//用户名
SqlParameter par1 = new SqlParameter("@User", SqlDbType.VarChar, 30) { Value=textBox1.Text.Trim()};
//密码
SqlParameter par2 = new SqlParameter("@Pwd", SqlDbType.VarChar, 20) { Value = textBox2.Text.Trim() };
//输出参数
SqlParameter par3 = new SqlParameter("@Flag", SqlDbType.Bit);
par3.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par1);
cmd.Parameters.Add(par2);
cmd.Parameters.Add(par3);
try
{
//7、执行Command命令,得到结果
cmd.ExecuteNonQuery();
object b = cmd.Parameters["@Flag"].Value;
bool result = Convert.ToBoolean(b);
if (result==true)
{
label3.Text = "该账号合法,登录成功";
}
else
{
label3.Text = "该账号非法,不准登录!";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
4.Ado.net中调用存储过程与sql语句区别
- v把SQL语句变成存储过程名称
- v设置SQLCommand对象的CommandType为CommandType.StoredProcedure
- v根据存储过程的参数设置SQLCommand的参数
- v如果有输出参数设置输出参数的Direction属性为:Direction=ParameterDirection.Output
cmd.ExecuteNonQuery();
object b=cmd.Parameters["@flag"].Value;
将b转换成对应的类型
bool b=Convert.ToBoolean(b);
注意:如果是通过Command调用ExecuteReader执行存储过程,如果想获取输出参数,必须等到关闭Reader对象以后,才能获取的到。
更新项目:将登录按钮原方法改为新方法
原:
private void btnLogin_Click(object sender, EventArgs e)
{
//编写sql语句,将sql语句赋值给CommandText属性
string sql = string.Format("select COUNT(*) from Users where userName=@userName and Password=@Password");
//准备参数
SqlParameter[] pas = { //实例化一个参数对象 指定名字 类型 长度 参数的值
new SqlParameter("@userName", SqlDbType.VarChar,50){ Value=txtName.Text.Trim().ToString()},
new SqlParameter("@Password", SqlDbType.VarChar,50){ Value=txtPwd.Text.Trim().ToString()},
};
//调用类库中的方法执行
object b = sqlHelper.sqlExecuteScalar(sql, pas);
if ((int)b > 0)
{
Main f1 = new Main();//实例一个Main窗体
f1.StartPosition = FormStartPosition.CenterScreen;//窗体显示于屏幕中间
f1.Show();
this.Visible = false;//隐藏登录窗体
}
else
{
MessageBox.Show("用户名或密码错误!");
}
}
新:
private void btnLogin_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(sqlHelper.strCon))
{
string sql = "proc_UserCheck";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
//准备参数
SqlParameter[] pas = { //实例化一个参数对象 指定名字 类型 长度 参数的值
new SqlParameter("@User", SqlDbType.VarChar,50){ Value=txtName.Text.Trim().ToString()},
new SqlParameter("@Pwd", SqlDbType.VarChar,50){ Value=txtPwd.Text.Trim().ToString()},
new SqlParameter("@flag", SqlDbType.Bit) { Direction=ParameterDirection.Output}
};
cmd.Parameters.AddRange(pas);
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
cmd.ExecuteNonQuery();
object b = cmd.Parameters["@flag"].Value;
if (Convert.ToBoolean(b))
{
MessageBox.Show("登陆成功");
Main f1 = new Main();//实例一个Main窗体
f1.StartPosition = FormStartPosition.CenterScreen;//窗体显示于屏幕中间
f1.Show();
this.Visible = false;//隐藏登录窗体
}
else
{
MessageBox.Show("用户名或密码错误!");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw;
}
}
}
三、 存储过程与类库的结合
在项目中使用类库中的方法,执行存储过程,项目中具体代码见下:
1、将该类库的dll文件拷贝到要使用的项目中
2、右击引用--》添加引用--》浏览--》找到该dll,点击确定
3、右击引用--》添加引用--》程序集--》System.Configuration -->点击确定
4、在App.Config中实现连接字符串,将以下代码拷贝到App.Config中
<!--配置方式一:只能配置数据库连接字符串,不能配置别的-->
<connectionStrings>
<add connectionString="data source=.;database=ttt;user id=sa;pwd=admin123" name="Con1"/>
</connectionStrings>
<!--配置方式二:该软件默认选择汉语,可以在配置文件中指定-->
<appSettings>
<add key="Con2" value="data source=.;database=ttt;user id=sa;pwd=admin123"/>
</appSettings>
5、使用using HuaXingSqlHelper 添加对该dll的引用
6、在程序中调用类库中的方法
类库内容:
/// <summary>
/// 执行SQL语句或存储过程,返回cmd
/// </summary>
/// <param name="sql">要执行的sql语句或存储过程名称</param>
/// <param name="type">Command类型:是Text还是存储过程</param>
/// <param name="pas">参数</param>
/// <returns></returns>
public static SqlCommand HuaXingExecuteNonQueryWithProcedureReturnCmd(string sql, CommandType type, params SqlParameter[] pas)
{
using (SqlConnection con=new SqlConnection(strCon))
{
//创建Command对象
SqlCommand cmd = new SqlCommand(sql, con);
//指定Command类型
cmd.CommandType = type;
//赋参数
if (pas != null)
{
cmd.Parameters.AddRange(pas);
}
//打开数据库
con.Open();
//执行命令
cmd.ExecuteNonQuery();
return cmd;
}
}
/// <summary>
/// 执行SQL语句或存储过程,返回受影响的行数
/// </summary>
/// <param name="sql">要执行的sql语句或存储过程名称</param>
/// <param name="type">Command类型:是Text还是存储过程</param>
/// <param name="pas">参数</param>
/// <returns></returns>
public static int HuaXingExecuteNonQueryWithProcedure(string sql, CommandType type, params SqlParameter[] pas)
{
using (SqlConnection con = new SqlConnection(strCon))
{
//创建Command对象
SqlCommand cmd = new SqlCommand(sql, con);
//指定Command类型
cmd.CommandType = type;
//赋参数
if (pas != null)
{
cmd.Parameters.AddRange(pas);
}
//打开数据库
con.Open();
//执行命令
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行SQL或存储过程,返回第一行第一列的值
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <param name="pars"></param>
/// <returns></returns>
public static object HuaXingExecuteScalarWithProcedure(string sql, CommandType type, params SqlParameter[] pars)
{
using (SqlConnection con=new SqlConnection(strCon))
{
//创建Command对象
SqlCommand cmd = new SqlCommand(sql, con);
//指定Command类型
cmd.CommandType = type;
//指定参数
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
//打开连接
con.Open();
//执行命令
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 执行SQL语句或存储过程,返回Reader对象
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <param name="pars"></param>
/// <returns></returns>
public static SqlDataReader HuaXingExecuteReaderWithProcedure(string sql, CommandType type, params SqlParameter[] pars)
{
//创建连接对象
SqlConnection con = new SqlConnection(strCon);
try
{
//创建Command对象
SqlCommand cmd = new SqlCommand(sql, con);
//指定Command类型
cmd.CommandType = type;
//指定参数
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
//打开连接
con.Open();
//执行命令
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
con.Close();
return null;
}
}
更新项目:登录按钮
原:
private void btnLogin_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(sqlHelper.strCon))
{
//创建SQL语句 sql语句中放的是存储过程的名称
string sql = "proc_UserCheck";
SqlCommand cmd = new SqlCommand(sql, con);
//Command的类型选取成存储过程
cmd.CommandType = CommandType.StoredProcedure;
//准备参数
SqlParameter[] pas = { //实例化一个参数对象 指定名字 类型 长度 参数的值
new SqlParameter("@User", SqlDbType.VarChar,50){ Value=txtName.Text.Trim().ToString()},
new SqlParameter("@Pwd", SqlDbType.VarChar,50){ Value=txtPwd.Text.Trim().ToString()},
new SqlParameter("@flag", SqlDbType.Bit) { Direction=ParameterDirection.Output}
};
cmd.Parameters.AddRange(pas);
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
cmd.ExecuteNonQuery();
object b = cmd.Parameters["@flag"].Value;
if (Convert.ToBoolean(b))
{
MessageBox.Show("登陆成功");
Main f1 = new Main();//实例一个Main窗体
f1.StartPosition = FormStartPosition.CenterScreen;//窗体显示于屏幕中间
f1.Show();
this.Visible = false;//隐藏登录窗体
}
else
{
MessageBox.Show("用户名或密码错误!");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw;
}
}
}
现:
private void btnLogin_Click(object sender, EventArgs e)
{
string sql = "proc_UserCheck";
//准备参数
SqlParameter[] pas = { //实例化一个参数对象 指定名字 类型 长度 参数的值
new SqlParameter("@User", SqlDbType.VarChar,50){ Value=txtName.Text.Trim().ToString()},
new SqlParameter("@Pwd", SqlDbType.VarChar,50){ Value=txtPwd.Text.Trim().ToString()},
new SqlParameter("@flag", SqlDbType.Bit) { Direction=ParameterDirection.Output}
};
SqlCommand cmd = sqlHelper.sqlExecuteNonQueryWithProcedureReturnCmd(sql, CommandType.StoredProcedure, pas);
object b = cmd.Parameters["@flag"].Value;
if (Convert.ToBoolean(b))
{
MessageBox.Show("登陆成功");
Main f1 = new Main();//实例一个Main窗体
f1.StartPosition = FormStartPosition.CenterScreen;//窗体显示于屏幕中间
f1.Show();
this.Visible = false;//隐藏登录窗体
}
else
{
MessageBox.Show("用户名或密码错误!");
}
}