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.