VB.NET: Sql Stored Procedure Source Code Example
VB.NET Sql Stored Procedure Source Code Example
Purpose: – Illustrates using Sql Stored Procedure with SqlDataReader.
Prerequistes:
- Install Visual Basic (Express or Standard Edition)
- Install SQL Server Express
- Download Northwind Database
- Attach Northwind Database to Databases in Sql Express
Notes:
- Console Application is used to simplify things, but Windows Forms or Web Forms could also be used
- You can build a library of syntax examples by using same project over and over and just commenting out what you do not want to execute in Module1.vb
Instructions:
- Use VB 2008 (Express or Standard) Edition
- Create new project; select Console Application; name of Project could be VBNET_Syntax.
- Right-click project name in solution explorer; add new folder; name of folder could be DatabaseADONET
- Right-click folder; add class; class name could be clsSqlCallStoredProcedure.vb
- Copy code into clsSqlCallStoredProcedure.vb
- Copy code into Module1.vb
- Click green arrow to start with debugging
Step 1: Use View Plain to Cut-n-paste code into clsSqlCallStoredProcedure.vb
Imports System Imports System.Data Imports System.Data.SqlClient Public Class clsSQLCallStoredProcedure Shared WithEvents con As SqlConnection Sub Main() con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI") Dim cmd As New SqlCommand("Sales By Year", con) cmd.CommandType = CommandType.StoredProcedure ' Add Parameters to Command Parameters collection cmd.Parameters.Add("@Beginning_Date", SqlDbType.DateTime) cmd.Parameters.Add("@Ending_Date", SqlDbType.DateTime) cmd.Parameters("@Beginning_Date").Value = "1/1/1990" cmd.Parameters("@Ending_Date").Value = "1/1/2000" Try con.Open() Dim reader As SqlDataReader = cmd.ExecuteReader() While reader.Read() Console.WriteLine("{0} - {1:c}", _ reader.GetDateTime(0), reader.GetDecimal(2)) End While reader.Close() Finally con.Close() End Try Console.ReadLine() End Sub End Class |
Step 2: Use View Plain to Cut-n-paste code into Module1.vb
Module Module1 Sub Main() '***** DataBaseADONET ************* Dim mySQLCallStoredProcedure As New clsSQLCallStoredProcedure mySQLCallStoredProcedure.Main() End Sub End Module |