Quick Objects Documentation Akal Tech Logo
Running Stored Procedure and Returning a DataSet

Glossary Item Box

Database Library - Tutorial 3:

This tutorial demonstrates how the DB Library can be used to execute a stored procedure on the database and return a DataSet that is filled with the results of the command.

The following code demonstrates how to run a Stored Procedure with a parameter.

using (DatabaseObjectSQL dbObj = new DatabaseObjectSQL())

{

// The following line shows a very useful feature for writing database independent code.

// Since we need to pass a parameter to the stored procedure, normally we would need to know

// the database type, we can create an instance of the appropriate parameter class i.e. OracleParameter or SqlParameter

// With QuickObjects DBLibrary, you don't have to know the Parameter type. The library can be configured to use

// any of the supported databases and hence the GetNewParameterInstance can be used to automatically get an instance of

// a parameter class that is appropriate for the currently configured database type.

IDataParameter param = dbObj.GetNewParameterInstance();

param.ParameterName = "CustomerID";

param.Value = 1;

param.Direction = ParameterDirection.Input;

// RunSPResultDataset method allows you to pass the name of the procedure and all parameters seperated by comma.

// This allows you to pass the exact number of parameters as your stored procedure expects.

DataSet ds = dbObj.RunSPReturnDataset("sp_GetOrdersByCustomerID", param);

if (ds.Tables.Count > 0)

{

this.GridView.DataSource = ds;

this.GridView.DataMember = ds.Tables[0].TableName;

}

}

 

The following is the T-SQL code for the stored procedure.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Ish Singh

-- Description: This procedure returns all Orders that match the passed in CustomerID

-- =============================================

CREATE PROCEDURE sp_GetOrdersByCustomerID

-- Add the parameters for the stored procedure here

@CustomerID int = 0

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT Orders.*, Customers.FirstName, Customers.LastName FROM Orders

INNER JOIN Customers

ON Customers.CustomerID = Orders.CustomerID

WHERE Customers.CustomerID = @CustomerID

END

GO