Load data from Stored Procedure

Loading data from stored procedures is essential for data bound applications. Executing stored procedures and retrieving data is not that difficult. With the use of SQL Command and SQL Data Adapter we can fill either a dataset or a strongly typed dataset.

Setting up Sample Code

I use Northwind sample database provided by Microsoft for sample source code in this website. So to run this code you need to download it from MSDN. After downloading, there will not be any stored procedure which is explained in this sample. So I have added SQL server stored procedure script with this sample code. So run this script to get the stored procedure inside the Sample DB before running the sample code.

Load Data using a SQL Adapter

As I explained earlier we need a SQL Command and parameters to form the Command. Then we have to use the SQL Adapter to execute the prepared command. First we need to add the necessary parameter ...

Use Parameters in Stored procedures

Using stored procedures in asp.net application helps in various ways to run couple of SQL statements, and makes the applications to get the data by calling those procedures. When using stored procedures, the parameters have to be sent to while executing the stored procedures.

SqlParameter to pass the value

Since most of the samples I am working on are related to SQL Server, I need to use the SqlParameter. There are various ways to pass the values from asp.net (or win forms, WPF applications, win service, etc...) applications.  I prefer just create a new parameter by specifying its name and type while initializing and then pass the value using the value parameter.

Stored procedure

Stored procedures which are accepting the parameters have to specify its datatype and the length of the data. If you are expecting that there will be a possibility that the parameter may not be passed at all i.e. the opt ...

Output Parameter values from Stored Procedure

Executing stored procedures are as easy as calling other SQL Queries.  But there are few places we need to concentrate to get the output value from a stored procedure in .Net. The changes will be in stored procedures and SQL Commands.

Setting up Sample Code

I use Northwind sample database provided by Microsoft for sample source code in this website. So to run this code you need to download it from MSDN. Further to downloading, since there is no stored procedure with output parameter. So I have added SQL server stored procedure script with this sample code. So run this script to get the stored procedure inside the Sample DB.

Points to note in Stored Procedures

The basic thing to note is we need an output parameter first. To mark a parameter as output parameter we need to add the word output next to the data type declaration. For e.g. @CategoryID int output”.  After the processing we need to s ...


Spam Bot Trap



   



Select Theme
White
Blue
Brown
Gray