VS calls the SQL Server stored procedure

Source: Internet
Author: User





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.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.