Stored Procedure with Parameter

The ADO.NET classes can be categorized into two main components: Data Providers and DataSet. Data Providers are responsible for establishing connections with databases, executing commands, and retrieving data results. They serve as the bridge between the application and the underlying database. On the other hand, the DataSet is an in-memory representation of data retrieved from a database, providing a disconnected and versatile data manipulation environment.

Within ADO.NET, the Command object plays a significant role in executing SQL queries. It offers a variety of Execute methods, enabling developers to perform SQL queries in different ways. These methods allow for flexibility in executing queries and retrieving results from the database.

Stored Procedure

A Stored Procedure is a collection of programming statements that perform operations within a database. They can encompass various tasks, including calling other procedures. Stored procedures often accept input parameters, which can be supplied when executing the procedure. Additionally, they can return multiple values, offering a convenient means of retrieving data or executing complex operations within the database.A sample stored procedure with accepting input parameter is given below :

CREATE PROCEDURE SPCOUNTRY @COUNTRY VARCHAR(20) AS SELECT PUB_NAME FROM publishers WHERE COUNTRY = @COUNTRY GO

The above stored procedure is accepting a country name (@COUNTRY VARCHAR(20)) as parameter and return all the publishers from the input country. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters.

command.CommandType = CommandType.StoredProcedure param = New SqlParameter("@COUNTRY", "Germany") param.Direction = ParameterDirection.Input param.DbType = DbType.String command.Parameters.Add(param)

The above code passing country parameter to the stored procedure from vb.net.

Full Source VB.NET
Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connetionString As String Dim connection As SqlConnection Dim adapter As SqlDataAdapter Dim command As New SqlCommand Dim param As SqlParameter Dim ds As New DataSet Dim i As Integer connetionString = "Data Source=servername;Initial Catalog=PUBS;User ID=sa;Password=yourpassword" connection = New SqlConnection(connetionString) connection.Open() command.Connection = connection command.CommandType = CommandType.StoredProcedure command.CommandText = "SPCOUNTRY" param = New SqlParameter("@COUNTRY", "Germany") param.Direction = ParameterDirection.Input param.DbType = DbType.String command.Parameters.Add(param) adapter = New SqlDataAdapter(command) adapter.Fill(ds) For i = 0 To ds.Tables(0).Rows.Count - 1 MsgBox(ds.Tables(0).Rows(i).Item(0)) Next connection.Close() End Sub End Class





Related Topics
  1. ADO.NET Connection Object
  2. ADO.NET SQL Server Connection
  3. ADO.NET OLEDB Connection
  4. ADO.NET ODBC Connection
  1. ADO.NET Command
  2. ADO.NET ExecuteNonQuery in SqlCommand Object
  3. ADO.NET ExecuteNonQuery in OleDbCommand Object
  4. ADO.NET ExecuteScalar in SqlCommand Object
  5. ADO.NET ExecuteScalar in OleDbCommand Object
  6. ADO.NET ExecuteReader in SqlCommand Object
  7. ADO.NET ExecuteReader in OleDbCommand Object
  8. How to ADO.NET DataReader
  9. How to ADO.NET SqlDataReader
  10. How to ADO.NET OleDbDataReader
  11. How to Multiple Result Sets in ADO.NET
  12. Getting Schema Informations from SqlDataReader
  13. Getting Schema Informations from OleDbDataReader
  14. What is DataAdapter
  15. What is SqlDataAdapter
  16. What is OleDbDataAdapter
  17. Vb.NET ExecuteReader and ExecuteNonQuery

Related Links
  1. Stored Procedures in VB.NET