C#基础知识 Day28 数据库高阶存储过程

最新推荐文章于 2022-05-26 21:55:49 发布

Aurora_木迦

最新推荐文章于 2022-05-26 21:55:49 发布

阅读量264

点赞数

分类专栏： C# 文章标签：数据库存储过程

本文链接：https://blog.csdn.net/aurora_m/article/details/100877854

版权

C# 专栏收录该内容

31 篇文章 10 订阅

订阅专栏

4.Ado.net中调用存储过程与sql语句区别

更新项目：将登录按钮原方法改为新方法

三、存储过程与类库的结合

更新项目：登录按钮

一、存储过程概念

存储过程就像数据库中运行方法(函数)和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("用户名或密码错误！");
            }

        }

Aurora_木迦

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
C#基础知识 Day28 数据库高阶存储过程

目录一、存储过程概念二、存储过程的操作1.创建：2.调用4.在程序中调用存储过程4.Ado.net中调用存储过程与sql语句区别更新项目：将登录按钮原方法改为新方法三、存储过程与类库的结合更新项目：登录按钮一、存储过程概念存储过程就像数据库中运行方法(函数)和C#中的方法一样，由存储过程名、存储过程参数组成，可以有返回值。if、...
复制链接

扫一扫