Stored procedure in Connected model in VB.NET

This article explain about stored procedure is precompiled set of statement and how easy to execute.
  • 2781
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).

add-connection-in-vb.net.gif

Figure 1

Step 2:
Enter server name, database name in Add Connection dialog box (See figure 2 ).

connection-process-in-vb.net.gif

Figure 2

Step 3
: Expand master database. Right click on Stored Procedures then click Add New Stored Procedure (See figure 3).

add-new-store-procedure-in-vb.net.gif

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).

run-sql-command.gif

Figure 4

step 5: You can view stored procedure by expanding stored procedure (see figure 5)

expand-store-procedure-in-vb.net.gif

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


show-table-data-in-vb.net.gif

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.