sql-server创建存储过程

use EFDB
go

--添加学生存储过程
--usp_AddStudents 自定义的存储过程name
if exists(select * from sysobjects where name='usp_AddStudents')
drop procedure usp_AddStudents
go

--@StudentName varchar(20), 对应表中的字段
--@Gender char(2), 对应表中的字段
--@Birthday smalldatetime, 对应...
--@StudentIdNo numeric(18, 0), 对应...
--@Age int, 对应...
--@PhoneNumber varchar(50), 对应...
--@StudentAddress varchar(500), 对应...
--@ClassId int 对应...

create procedure usp_AddStudents
@StudentName varchar(20),
@Gender char(2),
@Birthday smalldatetime,
@StudentIdNo numeric(18, 0),
@Age int,
@PhoneNumber varchar(50),
@StudentAddress varchar(500),
@ClassId int

-- as - go 中间写sql语句
as
insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId) 
values(@StudentName,@Gender,@Birthday,@StudentIdNo,@Age,@PhoneNumber,@StudentAddress,@ClassId)
go

 使用 usp_AddStudents 存储过程 
1.封装参数: 

SqlParameter parameter = new SqlParameter()
{
    new SqlParameter("@StudentName", StudentName),
    new SqlParameter("@Gender",Gender),
    new SqlParameter("@Birthday",Birthday),
    new SqlParameter("@StudentIdNo",StudentIdNo),
    new SqlParameter("@Age", Age),
    new SqlParameter("@PhoneNumber", PhoneNumber),
    new SqlParameter("@StudentAddress", StudentAddress),
    new SqlParameter("@ClassId", ClassId)
};

封装SqlHelper

public static int Update(string uspName, params SqlParameter[] parse)
{
    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand(uspName, conn);
    try
    {
        conn.Open();
        cmd.CommandType = CommandType.StordProcedure; //设置类型为存储过程
        cmd.Parameters.Clear();
        cmd.Parameters.AddRange(parse);
        return cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
        conn.Close();
    }
}