SQL存储过程的简单应用

在button中的代码

       Dim bh As String
        bh = InputBox("请输入要删除的行数")
        Dim sqlcom As New SqlCommand("DeleteRowProc", sqlcon)
        '将sqlcommand按存储过程传值
        sqlcom.CommandType = CommandType.StoredProcedure
        sqlcom.Parameters.Add(New SqlParameter("@noo", SqlDbType.Int) With {.Value = bh})

        '    Dim aa As New SqlParameter("@noo", SqlDbType.Int)
        '   aa.Value = bh

        Try
            sqlcon.Open()
            sqlcom.ExecuteNonQuery()
            MsgBox("success")
        Catch ex As Exception
            MsgBox("failure")
        End Try

在SQL中的存储过程

 

CREATE PROCEDURE DeleteRowProc

	@noo int
AS
BEGIN

	SET NOCOUNT ON;

	delete 员工表 where 编号=@noo
END

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

员工信息插入模块

 

 

 Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click

        Dim pname As New SqlParameter("@name ", SqlDbType.VarChar, 10)
        Dim page As New SqlParameter("@age ", SqlDbType.Int, 2)
        Dim psex As New SqlParameter("@sex", SqlDbType.VarChar, 2)
        Dim pxl As New SqlParameter("@xl", SqlDbType.VarChar, 10)
        Dim pdep As New SqlParameter("@dep ", SqlDbType.VarChar, 10)
        pname.Value = txtName.Text
        page.Value = txtAge.Text
        psex.Value = txtSex.Text
        pxl.Value = cmbXl.Text
        pdep.Value = cmbDepartment.Text
        Dim icom As New SqlCommand("oooo", sqlcon)
        icom.CommandType = CommandType.StoredProcedure
        icom.Parameters.Add(pname)
        icom.Parameters.Add(page)
        icom.Parameters.Add(psex)
        icom.Parameters.Add(pxl)
        icom.Parameters.Add(pdep)
        Try
            sqlcon.Open()
            icom.ExecuteNonQuery()
            MsgBox("success")
        Catch ex As Exception
            MsgBox("failure")
        End Try
    End Sub

SQL中的存储过程

 

ALTER PROCEDURE [dbo].[oooo] 
@name  varchar(10),
@age int,
@sex varchar(2),
@xl varchar(10),
@dep varchar(10)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

insert into dbo.[员工表](姓名,年龄,性别,学历,所属部门) values (@name,@age,@sex,@xl,@dep)

END