c# 数据存储过程 存储函数

一、什么是存储过程？

　　用某百科的话来说就是一堆为了完成某一功能或者某些功能的SQL语句的集合，而数据库则会将这些存储过程的方法存储到数据库中去。

优点：

　　1.可重用并且效率高：存储过程经过一次编译后不需要再次进行编译，这就提高了效率，再过一次编译的存储过程可重复使用，无需在进行第二次编译。

　　2.减少网络流量：以前传输的往往是SQL语句，当使用存储过程后使用的是参数，只需要传递参数即可。

　　3.安全性：在SqlServer中，可以使用Grant进行权限的分配不同的权限，也可以使用Revoke进行收回，还可以进行Deny拒绝。

二、如何使用存储过程？

1.建立

关于建立存储，当然是在已经有的数据库中建立。下面的是我建立的数据库和表。

 打开“可编程性”可以看到“存储过程”，右键创建新的存储过程即可：

--调用数据库
use 数据库名称
go
create proc 存储过程名称
    --字段
as
    --关联
go

 

2.具体代码使用过程如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
// 引入数据库管理控件
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            // SQL语句插入操作
            /*
             * // 多个参数操作
            SqlConnection conn = new SqlConnection();  // 初始化一个SqlConnection()类
            conn.ConnectionString = "server=服务器名称;database=pc_test;uid=账号;pwd=密码";  // 连接字符串,格式(注意间隔要使用分号（";"）来做分割)："server=数据库名称(据说可以使用"."来表示本地数据库，没试过);database=数据库;uid=账号;pwd=密码"。注：如果使用的是"."进行登陆的话，可以将uid和pwd换成"integrated security=SSPI"即："server=.;database=数据库名;integrated security=SSPI";
            conn.Open();  // 打开数据库
            string sql = "insert into student(name,age,sex) values(@tname,@tage,@tsex);";  // SQL命令
            SqlCommand comm = new SqlCommand(sql, conn);  // 初始化SqlCommand()类
            SqlParameter[] pars =
            {
                new SqlParameter("@tname",SqlDbType.NChar,10),
                new SqlParameter("@tage",SqlDbType.Int,10),
                new SqlParameter("@tsex",SqlDbType.TinyInt,2)
            };
            pars[0].Value = "namejr1";
            pars[1].Value = 12;
            pars[2].Value = 1;
            comm.CommandType = CommandType.Text;  // 这个表明是SQL命令
            comm.Parameters.AddRange(pars);
            SqlDataAdapter sda = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            sda.Fill(ds, "student");  // 对这个表进行显示，具体显示方法下一个会给出来
            conn.Close();*/
            //
            /*
             * 单条SQL操作
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "server=NAMEJR-PC;database=pc_test;uid=sa;pwd=123456";  // 连接字符串,格式(注意间隔要使用分号（";"）来做分割)："server=数据库名称(据说可以使用"."来表示本地数据库，没试过);database=数据库;uid=账号;pwd=密码"。注：如果使用的是"."进行登陆的话，可以将uid和pwd换成"integrated security=SSPI"即："server=.;database=数据库名;integrated security=SSPI";
            conn.Open();  // 打开数据库
            string sql = "select name,age,sex from student where id=@Id;";
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlParameter par = comm.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int, 10));
            par.Value = 1;
            comm.CommandType = CommandType.Text;  // 这个表明是SQL命令
            SqlDataAdapter sda = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            sda.Fill(ds, "student");
            foreach(DataTable dt in ds.Tables)
            {
                foreach(DataRow dr in dt.Rows)
                {
                    foreach(DataColumn dc in dt.Columns)
                    {
                        // dt.TableName:就是使用Fill()指定的表名。dc.ColumnName字段名，dr[dc]字段值
                        Console.WriteLine("{0}=>{1}=>{2}", dt.TableName, dc.ColumnName, dr[dc]);
                    }
                }
            }
            conn.Close();
            */
            //
            /*
             * 快捷显示
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "server=NAMEJR-PC;database=pc_test;uid=sa;pwd=123456";
            conn.Open();
            string sql = "execute TSelect 1";
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataAdapter sda = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            sda.Fill(ds, "student");
            // 显示同上
            */
        }
    }
}

带输入输出函数的存储过程：

#region 执行带输入输出的存储过程
        public static void IOSql(string proName, string Courtid, long _costtype, string _time, out string CourtName, out int totalRoomNum, out int NumHousesComple, out int NumHousesNotComple, out int HCTO, out int HCTON)
        {
            /*
             SqlConnection conn = new SqlConnection("server=服务器地址;uid=账号;pwd=密码;database=数据库");
             */
            // 连接数据库
            using (SqlConnection conn = GetConnection())
            {
                conn.Open();  // 打开数据库
                using(SqlCommand commd = conn.CreateCommand()){
                    commd.CommandType = CommandType.StoredProcedure;  // 存储过程方式
                    commd.CommandText = proName;  // 存储过程名称(可以是表名什么的)
                    // 构建数据类型
                    SqlParameter[] para=new SqlParameter[]{
                        new SqlParameter("@CourtId",SqlDbType.Int),
                        new SqlParameter("@CostType",SqlDbType.Int),
                        new SqlParameter("@time",SqlDbType.Date),
                        new SqlParameter("@CourtName",SqlDbType.NVarChar,32),  // 注意，使用nvarchar的时候记得写长度
                        new SqlParameter("@totalRoomNum",SqlDbType.Int),
                        new SqlParameter("@NumHousesComple",SqlDbType.Int),
                        new SqlParameter("@NumHousesNotComple",SqlDbType.Int),
                        new SqlParameter("@HCTO",SqlDbType.Int),
                        new SqlParameter("@HCTON",SqlDbType.Int)
                    };
                    para[0].Value = Courtid;
                    para[1].Value = _costtype;
                    para[2].Value = _time.ToString();
                    para[3].Direction = ParameterDirection.Output;
                    para[4].Direction = ParameterDirection.Output;
                    para[5].Direction = ParameterDirection.Output;
                    para[6].Direction = ParameterDirection.Output;
                    para[7].Direction = ParameterDirection.Output;
                    para[8].Direction = ParameterDirection.Output;
                    commd.Parameters.AddRange(para);
                    commd.ExecuteNonQuery();  // 执行语句
                    // 单个数据查看
                    CourtName = Convert.ToString(commd.Parameters[3].Value);
                    totalRoomNum = Convert.ToInt32(commd.Parameters[4].Value);
                    NumHousesComple = Convert.ToInt32(commd.Parameters[5].Value);
                    NumHousesNotComple = Convert.ToInt32(commd.Parameters[6].Value);
                    HCTO = Convert.ToInt32(commd.Parameters[7].Value);
                    HCTON = Convert.ToInt32(commd.Parameters[8].Value);
                }
                conn.Close();
            }
        }
        #endregion

AddBy 2020-2-24

3.在数据库中使用

Sqlserver中对存储过程的一些操作：

exec sp_databases  -- 查看数据库

exec sp_tables  -- 查看表

exec sp_columns 表名  -- 查看所填表名的列

exec sp_helpIndex 表名-- 查看对应表的索引

exec sp_helpConstraint 表名 -- 查看对应表的约束

exec sp_stored_procedures  -- 查看全部的存储过程

exec sp_helptext 存储进程名  -- 查看对应的存储进程结构

此外还有sp_rename(修改表/列/索引的名称)、sp_renamedb(修改数据库名称)、sp_defaultdb(设置默认登录数据库)、sp_helpdb(获取帮助信息)

sqlserver查询记录命令：
use 数据库名
declare @CourtName nvarchar(32)
declare @totalRoomNum int
declare @NumHousesComple int
declare @NumHousesNotComple int
declare @HCTO int
declare @HCTON int
exec 存储过程名 617,82297,'2019-03-28',@CourtName output,@totalRoomNum output,@NumHousesComple output,@NumHousesNotComple output,@HCTO output,@HCTON output
print @CourtName
print @totalRoomNum
print @NumHousesComple
print @NumHousesNotComple
print @HCTO
print @HCTON

AddBy 2020-10-23

在存储过程中执行构建的SQL语句：

declare @TopicID nvarchar(max);
declare @SqlStr nvarchar(max);
set @SqlStr='select * from info_que_qtntopic';
exec sp_executesql @SqlStr;  -- 无参数
set @TopicID='0197c95c511242c5968622cffa325725';
set @SqlStr='select * from info_que_qtntopic where TopicID=@TopicID';
exec sp_executesql @SqlStr,N'@TopicID nvarchar(max)',@TopicID;  -- 有参数

EndBy 2020-10-23

三、拓展：存储函数的使用：

create function ControlLessHundred(@Num int)
returns int
as begin
  declare @ReNumber int;
 if(@Num>100) begin
   set @ReNumber=100
 end
 else begin
   set @ReNumber=@Num
 end
 return @ReNumber
end

查询方式：

对于无参数的存户函数在sqlserver中查询只需要：
exec ControlLessHundred()

对于有参数的查询
select ControlLessHundred(100)

关于在存储过程中的引用
ControlLessHundred(100)


如果存在'ControlLessHundred' 不是可以识别的 内置函数名称 的报错，那么请在前面加上dbo. 即：
select dbo.ControlLessHundred(50)

EndBy 2020-2-24