Call the SQL SERVER Stored Procedure in

Zookeeper

Stored procedures are compiled and permanently stored in a set of SQL statements. By creating and using stored procedures, you can improve the reusability and scalability of programs and provide modular functions for programs, it also facilitates program maintenance and management. Next we will discuss in detail how VB. NET calls the stored procedure in SQL SERVER.

The above is a table in my database-OnDutyInfo

Create a stored procedure

 

<Span style = "font-size: 18px;"> create procedure pro_OnDutyInfo -- stored procedure name @ teacherID char (11) -- parameter name asselect * from OnDutyInfo where teacherId <a target = _ blank href = "mailto: = @ teacherID" >=@ teacherID </a> </span>

(This stored procedure queries the instructor's duty records)

The function is to query the user's duty records and implement the Code in.

<Span style = "font-size: 18px;"> Private Sub btnFind_Click (sender As Object, e As EventArgs) handles Button1.Click Dim strCon As String 'Connection database String Dim cn As SqlConnection Dim cmd As New SqlCommand Dim pt As SqlParameter Dim rdr As SqlDataReader strCon = "initial catalog = ChargeSystem; user id = sa; password = 123456 "cn = New SqlConnection (strCon) 'instantiate the connection object cmd. connection = cn cmd. commandText = "pro_OnDutyInfo" 'stored procedure name cmd. commandType = CommandType. storedProcedure 'indicates that the connection type is stored in the pt = New SqlParameter ("@ teacherID", "11090241032")' to obtain the cmd parameter. parameters. add (pt) 'This is the add method, which can only Add one parameter cn. open () rdr = cmd. executeReader 'read operation If (rdr. read) then' reads the data MessageBox in the form of data streams. show (rdr (0 ). toString) End If End Sub </span>

The above operation is a simple operation called by the stored procedure. Of course, you may have a problem. If there are more than one parameter in the stored procedure, what should we do? Such as the following stored procedures

We can see that there will be a lot of input parameters, which is actually very simple, don't worry, just change the add method.

<Span style = "font-size: 18px;"> ALTER procedure [dbo]. [pro_AddOffInfo] @ teacherId char (11), -- employee ID @ offTime time (0), -- downtime @ offDate date -- date asupdate OnDutyInfo set offTime = @ offtime, offDate = @ offdate where offtime is null and teacherid = @ teacherId -- execute the update instructor offline operation </span>

The changed operation is as follows:

<Span style = "font-size: 18px;"> Dim paras As SqlParameter () = {New SqlParameter ("@ teacherId", En_OnDuty.teacherId ), _ New SqlParameter ("@ offTime", En_OnDuty.offTime.ToString), _ New SqlParameter ("@ offDate", En_OnDuty.offDate.ToString)} 'Get the cmd parameter. parameters. addRange (paras) 'note that a new method is provided here </span>

The above is a small demo for VS to call SQL SERVER. It also analyzes the differences between ADD and ADDRanger.