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 bitabout howvb.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)   --Participant name Asselect * from Ondutyinfo where Teacherid <a target=_blank href= "Mailto:[email protected]" >[email protected]</a> </span>

(The stored procedure runs the query teacher on 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 the parameter        cmd. Parameters.Add (PT)    ' This is the Add method, which 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, assuming that the stored procedure in more than one, then how to operate it? For example, the following stored procedure

We see that there will be a lot of incoming parameters, in fact, very easy, do not worry, just need to change the way to join.

<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]--run update teacher down machine operation </ Span>

the changed actions are 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 number of        references 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.