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.
Figure 1.gif
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.
Figure 2.gif
Now click on the save Button.
Figure 3.gif
Now open the database and test it.
Figure4.gif