Stored procedure in Connected model in VB.NET
This article explain about stored procedure is precompiled set of statement and how easy to execute.
In this article I am going to use stored procedure. Stored procedure is precompiled set of statement and is it easy to execute. It is very useful because different language written by multiple client application. You can follow below step to add store procedure in your visual studio.
Step 1: First right click on Data Connection in Server Explore and Click on Add Connection (See below figure 1).
Figure 1
Step 2: Enter server name, database name in Add Connection dialog box (See figure 2 ).
Figure 2
Step 3: Expand master database. Right click on Stored Procedures then click Add New Stored Procedure (See figure 3).
Figure 3
Step 4: Create Stored Procedure. Select Stored procedure then right click on that area. After that click on Run Selection. (See Figure 4).
Figure 4
step 5: You can view stored procedure by expanding stored procedure (see figure 5)
Figure 5
Default.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1">
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" TypeName="Employee" SelectMethod="All"
runat="server"></asp:ObjectDataSource>
</div>
</form>
</body>
</html>
Web.config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="Employee" connectionString="Data Source=MCNDESKTOP10\SQLEXPRESS;Initial Catalog=master;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true" targetFramework="4.0"/>
</system.web>
</configuration>
App_code/Employee.vb
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports Microsoft.VisualBasic
Public Class Employee
Private Shared ReadOnly Connection_String As String
Private _id As Integer
Private _first As String
Private _last As String
Private _country As String
Public Property ID() As Integer
Get
Return _id
End Get
Set(value As Integer)
_id = value
End Set
End Property
Public Property FirstName() As String
Get
Return _first
End Get
Set(value As String)
_first = value
End Set
End Property
Public Property LastName() As String
Get
Return _last
End Get
Set(value As String)
_last = value
End Set
End Property
Public Property Country() As String
Get
Return _country
End Get
Set(value As String)
_country = value
End Set
End Property
Public Function All() As List(Of Employee)
Dim listEmp As New List(Of Employee)()
Dim con As New SqlConnection(Connection_String)
Dim cmd As New SqlCommand("EmployeeSP", con)
cmd.CommandType = CommandType.StoredProcedure
Using con
con.Open()
Dim sdreader As SqlDataReader = cmd.ExecuteReader()
While sdreader.Read()
Dim emp As New Employee()
emp.ID = CInt(sdreader("id"))
emp.FirstName = DirectCast(sdreader("Emp_fname"), String)
emp.LastName = DirectCast(sdreader("Emp_lname"), String)
emp.Country = DirectCast(sdreader("Emp_country"), String)
listEmp.Add(emp)
End While
End Using
Return listEmp
End Function
Shared Sub New()
Connection_String = WebConfigurationManager.ConnectionStrings("Employee").ConnectionString
End Sub
End Class
Output