Use of SQL Server Stored Procedure in VB.NET for Client/Server application.

After booming of internet, people have divert concentration from client/server application but client/server windows application is not died so far and Microsoft have taken the steps to remove syntax and logic difference between web as well as windows application. They have not achieved it 100% even this step is really appreciable. Today I would like to show you demonstration about how you can get record set from SQL Stored procedure and use its value in VB.NET windows application to write employee name in windows form on dynamic label.

We will have two steps for this demo.

1.) SQL Server coding

--create table

USE [adventureworks]

GO

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

--insert records

INSERT INTO emps

SELECT 'rITESH','MIS','ECHEM' UNION ALL

SELECT 'Rajan','MIS','mar'

--creating stored procedure which return emp name

CREATE PROC [dbo].[getEmpList]

AS

SELECT Name FROM emps

--checking SP

EXEC getemplist

2.) VB.NET code for windows application to utilize SP of SQL Server 2005.

'draw one button name 'Button1' in your windows form and use below event for it

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection

conn.ConnectionString = "Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa"

conn.Open()

Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand

cmd.CommandType = System.Data.CommandType.StoredProcedure

cmd.Connection = conn

cmd.CommandText = "[getEmpList]"

Dim reader As System.Data.SqlClient.SqlDataReader

reader = cmd.ExecuteReader

Dim XPos As Integer = 0

Dim YPos As Integer = 0

Do While reader.Read()

XPos = 9

YPos = YPos + 35

Dim lblfieldname As System.Windows.Forms.Label = New System.Windows.Forms.Label

lblfieldname.Text = reader("Name").ToString()

lblfieldname.Location = New System.Drawing.Point(XPos, YPos)

lblfieldname.Size = New System.Drawing.Size(120, 23)

Me.Controls.Add(lblfieldname)

XPos = XPos + 20

YPos = YPos + 35

Loop

End Sub

Happy Programming!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com