Net-informations.com
SiteMap  | About    

ASP.NET Procedure with Parameter

The ADO.NET data providers used to connect to a database, execute commands, and retrieve results. The Command Object in ADO.NET provides a number of Execute methods that can be used to perform the SQL queries in a variety of fashions.

The main advantage of Stored Procedure is that the Sql Server compiles each stored procedure once and then we can reuse the execution plan again and again. In many cases stored procedures accept input parameters and return multiple values . Parameter values can be supplied if a stored procedure is written to accept them. A sample stored procedure with accepting input parameter is given below :

  

		CREATE PROCEDURE SPCITY
		@CITY VARCHAR(20)
		AS
		SELECT AU_LNAME FROM AUTHORS WHERE CITY = @CITY
		GO

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

vb.net
  command.CommandType = CommandType.StoredProcedure
  param = New SqlParameter("@CITY", "Berkeley")
  param.Direction = ParameterDirection.Input
  param.DbType = DbType.String

command.Parameters.Add(param)

C#
  command.CommandType = CommandType.StoredProcedure;
  param = new SqlParameter("@CITY", "Berkeley");
  param.Direction = ParameterDirection.Input;
  param.DbType = DbType.String;

command.Parameters.Add(param);

The following ASP.NET program call a procedure and display all authors from Berkeley city.

Default.aspx

  

<!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 id="Head1" runat="server">
	<title>Untitled Page</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
	<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
	<br />
		<asp:ListBox ID="ListBox1" runat="server"></asp:ListBox>
	<br />
	<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
	</div>
	</form>
</body>
</html>


Click the following links to see full source code

C# Source Code
VB.NET Source Code
default.aspx.cs
default.aspx.vb






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.