ARTICLE
Use stored procedure in Connected model
Stored procedure is precompiled set of statement and is it easy to execute.
Download
Files:
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
