VS calls the SQL Server stored procedure



stored procedures are compiled and persisted in a set of data SQL statements, which can improve the reusability and extensibility of programs by creating and using stored procedures, provide modular functions, but also facilitate the maintenance and management of the program. Let's talk a little bit about how vb.net calls stored procedures 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 (one)   --Parameter name Asselect * from Ondutyinfo where Teacherid <a target=_blank href= "Mailto:[email protected]" >[email protected]</a> </span>

(The stored procedure executes the query teacher duty record operation)

The function to be implemented is to query the user's watch record in VS the implementation 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 Connection object        cmd. Connection = cn        cmd. CommandText = "pro_ondutyinfo"  stored procedure name        cmd. CommandType = CommandType.StoredProcedure  ' indicates that the type of connection is a stored procedure        pt = New SqlParameter ("@teacherID", "11090241032")  ' get parameter        cmd. Parameters.Add (PT)    ' This is the Add method, and the method can only add a parameter        CN. Open ()        rdr = cmd. ExecuteReader  ' read operation        If (RDR. Read) then   ' reads the data            MessageBox.Show (RDR (0) in the form of a data stream. ToString)        end If    End sub</span>

The above operation is a simple operation of the stored procedure call, of course, you may have a problem, if there are more than one parameter in the stored procedure, how to do it? such as the following stored procedures

We see that there will be a lot of incoming parameters, in fact, very simple, do not worry, just change the method added.

<span style= "FONT-SIZE:18PX;" >alter procedure [dbo]. [Pro_addoffinfo] @teacherId char (one),   --Employee number @offtime time (0),  --@offdate date---      -Asupdate Ondutyinfo Set [email protected],[email protected] where offtime is null and Teacherid [email protected]--perform update teacher down machine operation </ Span>

The following changes are done:

<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 Parameters        Cmd. Parameters.addrange (paras)    ' Note here a change of method </span>

The above is a small demo that implements VS calls SQL Server, and also analyzes the difference between add and Addranger.