ADO.NET call Stored Procedure In VB.NET
Here we will see how to create a stored procedure in database and calling stored procedure from database to asp.net using vb language.
Stored procedure
A stored procedure is a batch of Transact-SQL statements compiled into a single execution and then reutilizes the execution plan. when we write SQL statements, like select, inserts, updates to access your data from database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure.Every time you write a query it is parsed in database. If you have written a stored procedure for it, it will be compiled once and can be executed number of times.Stored procedures can also improve performance.
Step-1
Creating a table in database
create table logintab
(
username varchar(50),
password varchar(40)
)
Step-2
Creating Stored procedure
create procedure sploginproc
(
@username varchar(40),
@password varchar(20)
)
as
insert into logintab values(@username,@password )
Step-3
Calling stored procedure in VB.NET
Taking two TextBox, one Button and Label control on the form.
Now double click on the Save button control and add the following code.
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Dim str As String = "Data Source=.;uid=sa;pwd=123;database=master"
Dim con As New SqlConnection(str)
Dim com As SqlCommand = New SqlCommand("sploginproc", con)
com.Parameters.Add("@username", SqlDbType.VarChar).Value = TextBox1.Text
com.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text
com.CommandType = CommandType.StoredProcedure
con.Open()
com.ExecuteNonQuery()
Label1.Text = "record has been saved"
con.Close()
End Sub
End Class
Now run the application and enter the username and password.
Now click on the save Button.
Now open the database and test it.