存储过程是预先优化编译好的SQL语句,用户通过存储过程的名字与参数(如果该存储过程带有参数)来执行它。
我们为数据库Forum建一个存储过程,如下图所示:
这个存储过程需要做三件事情:
1,存储过程通过输入参数@ClassName的值查询出这个分类的对应ClassID,把该值赋给输出参数@ClassID。
2,向表tbBoard插入一条记录,板块名为输入参数@BoardName,该板块所属的分类ID为输出参数@ClassID(就是刚查询出来的)
3,最后查询板块的总记录数据@BoardCount,返回给调用存储过程的函数。
创建存储过程,SQL语句如下:
CREATE PROCEDURE [dbo].[CreateBoard]
(-- Add the parameters for the stored procedure here
@ClassName varchar(50),
@BoardName varchar(50),
@ClassID varchar(50) output
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @BoardCount int;
set @ClassID=(select ClassID from tbClass where ClassName=@ClassName);
insert into tbBoard(BoardName,BoardClassID) values(@BoardName,@ClassID);
set @BoardCount = (select count(*) from tbBoard);
return @BoardCount;
END
执行存储过程,SqlCommand如下
为了更灵活指定参数,我们在页面上添加两个文本框,TxtClassName、TxtBoardName,和一个按钮BtnAddBoard,如下
UI界面代码如下
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AddBoard.aspx.cs" Inherits="AddBoard" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
在:
<asp:TextBox ID="Txt_ClassName" runat="server"></asp:TextBox>
分类中添加<asp:TextBox ID="Txt_BoardName" runat="server"></asp:TextBox>
板块
<asp:Button ID="Btn_AddBoard" runat="server" οnclick="BtnAddBoard_Click"
Text="添加" />
<br />
<br />
</div>
</form>
</body>
</html>
CS代码如下:
public partial class AddBoard : System.Web.UI.Page
{
SqlHelper sqlHelper = null;
protected void Page_Load(object sender, EventArgs e)
{
sqlHelper = new SqlHelper();
}
protected void BtnAddBoard_Click(object sender, EventArgs e)
{
//1:从UI界面取得类名与板块名
string className = Txt_ClassName.Text.Trim();
string boardName = Txt_BoardName.Text.Trim();
//2:定义存储过程的需要参数
SqlParameter[] sqlParas = new SqlParameter[]
{
new SqlParameter("@ClassName", SqlDbType.VarChar, 50),
new SqlParameter("@BoardName", SqlDbType.VarChar, 50),
new SqlParameter("@ClassID", SqlDbType.VarChar, 50),
new SqlParameter("@BoardCount", SqlDbType.Int)
};
sqlParas[0].Direction = ParameterDirection.Input;
sqlParas[0].Value = className;
sqlParas[1].Direction = ParameterDirection.Input;
sqlParas[1].Value = boardName;
sqlParas[2].Direction = ParameterDirection.Output;
sqlParas[3].Direction = ParameterDirection.ReturnValue;
//3:执行执行存储过程
sqlHelper.ExecuteScalar(CommandType.StoredProcedure, "CreateBoard", sqlParas);
foreach (SqlParameter parameter in sqlParas)
{
Response.Write(string.Format("参数名:{0},参数方向:{1},参数值:{2}<br>", parameter.ParameterName, parameter.Direction.ToString(), parameter.Value));
}
}
}
后台数据库操作的类:SqlHelper.cs
public class SqlHelper
{
//定义连接字符串
private string sConnectionString = null;
public SqlHelper()
{
//
//TODO: 在此处添加构造函数逻辑
//
string strServer = "MSH03\\SQLEXPRESS"; //服务器,去掉文本框字符串前后的空格
string strDatabase = "forum"; //数据库名称
//string strUserID = null; //帐号
//string strPassword = null; //密码
sConnectionString = string.Format("Server={0};Database={1};Trusted_Connection=True;Connection Timeout=10;", strServer, strDatabase);
}
public int ExecuteNonQueryL(string sSQL)
{
try
{
SqlConnection conn = new SqlConnection(sConnectionString);
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand();
using (cmd)
{
// 绑定有效的数据库连接
cmd.Connection = conn;
//command对象绑定SQL语句
cmd.CommandText = sSQL;
cmd.CommandType = CommandType.Text;
// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数
//对于所有其他类型的语句,返回值为 -1。如果发生回滚,返回值也为 -1;
int numRows = cmd.ExecuteNonQuery();
return numRows;
}
}
}
catch
{
//如果执行SQ语句过程错误则返回值-2
return -2;
}
}
public string ExecuteScalar(string sSQL)
{
try
{
SqlConnection conn = new SqlConnection(sConnectionString);
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand();
using (cmd)
{
// 绑定有效的数据库连接
cmd.Connection = conn;
//绑定操作的SQL语句
cmd.CommandText = sSQL;
cmd.CommandType = CommandType.Text;
// 返回值:结果集中第一行的第一列或空引用(如果结果集为空)
object obj = cmd.ExecuteScalar();
if (obj != null)
{
return obj.ToString();
}
else
{
return null;
}
}
}
}
catch (SqlException e)
{
return "MyError:" + e;
}
}
public string ExecuteScalar(CommandType sqlType ,string sSQL, SqlParameter[] sqlParameters)
{
try
{
SqlConnection conn = new SqlConnection(sConnectionString);
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand();
using (cmd)
{
// 绑定有效的数据库连接
cmd.Connection = conn;
//判断需要执行的SQL类型,再绑定操作的SQL语句
if (sqlType == CommandType.Text)
{
cmd.CommandText = sSQL;
cmd.CommandType = CommandType.Text;
}
if(sqlType == CommandType.StoredProcedure)
{
cmd.CommandText = sSQL;
cmd.CommandType = CommandType.StoredProcedure;
}
//遍历添加到Parameters集合中
foreach (var sqlPara in sqlParameters)
{
cmd.Parameters.Add(sqlPara);
}
// 返回值:结果集中第一行的第一列或空引用(如果结果集为空)
object obj = cmd.ExecuteScalar();
if (obj != null)
{
return obj.ToString();
}
else
{
return null;
}
}
}
}
catch (SqlException e)
{
return "MyError:" + e;
}
}
}