Introduction

Stored procedures are pivotal components of a database design. They can be defined to work with different types of parameters. They can be written to perform varied SQL and work with multiple database objects simultaneously. While these attributes make stored procedures efficient, they can pose a challenge to application writers to access them. The IBM Data Server Provider for .NET addresses this challenge with functionality support to provide simple access to the stored procedures through a .NET application.

This article illustrates how to access simple stored procedures using IBM Data Server Provider for .NET with ease, and then the article explains how to access complex stored procedures with the same level of ease. Though the stored procedures listed are written to a DB2® for Linux®, UNIX®, and Windows® server, you could modify them to run against Informix® or any of the other IBM data servers that support the feature exploited by the stored procedure. See Resources for more information about the list of servers and levels the IBM Data Server Provider for .NET supports.

Understanding parameter directions

Parameters in a stored procedure can be declared as IN, OUT or INOUT parameters. The default direction is IN. Within the .NET code the Direction property in the DB2Parameter object should be used to indicate the direction of the parameter. The default value for Direction is Input.

The stored procedure AddProducts defined in Listing 1 accepts the product name and its price as input parameters and inserts the information into the products table. It also inserts the initial data into the inventory table. It retrieves the generated value for pid and returns it as an output parameter.

CREATE PROCEDURE AddProduct 
    (v_name VARCHAR(128), v_price DECIMAL(30,2), OUT v_PID INTEGER ) 
BEGIN 
  SELECT pid INTO v_PID FROM NEW TABLE 
    (INSERT INTO Products (name, price) VALUES (v_name, v_price)); 
  INSERT INTO inventory(pid, quantity, replenish) VALUES (v_PID, 0, 'y'); 
END

By default the output values are returned as a .NET system type. If the output value has a high precision such that it does not fit in a .NET system type, the ExecuteNonQuery method throws a DB2TruncateException exception. To read such high precision values, you must set the DB2TypeOutput property to true in the output parameter object. By doing so, the value is retrieved as a type in the IBM.Data.DB2Types namespace. In Listing 2, if DB2TypeOutput is set, the cmd.Parameters["@pid"].Value object will be of type DB2Int32. The DB2TypeOutput property can also be set on the command object to avoid setting it individually on each parameter object.

cmd.CommandText = "CALL AddProduct(@name, @price, @pid)";
cmd.Parameters.Add(new DB2Parameter("@name", "wireless router"));
cmd.Parameters.Add(new DB2Parameter("@price", 49.99));
cmd.Parameters.Add(new DB2Parameter("@pid", DB2Type.Integer));
cmd.Parameters["@pid"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int newPID = (int)cmd.Parameters["@pid"].Value;
Console.WriteLine("newPID= newPID.ToString());

If the stored procedure execution results in an exception, the value of the output parameter is undefined. The input and input output parameters remain unchanged.

Back to top

Accessing a stored procedure that returns a result set

Accessing the result set that a stored procedure returns is similar to accessing the result set that a select query returns. Using the default command type of CommandText instead of StoredProcedure is recommended. Doing so makes the code more readable and gives users the flexibility to use named parameter markers and to add the parameters to the parameter collection in any order. Using the command type of StoredProcedure is not recommended, because it can increase the execution time of the CALL statement if the procedure is overloaded.

The stored procedure GetNewProductPrice accesses the orders table to get a count of the number of orders placed for each product. Based on the range in which the count falls, the stored procedure reduces the price of the product by a preset ratio and returns the product details as a result set. The application then iterates through the result set by using a data reader. Listing 3 calls the ExecuteReader method to access the result set. You can also call ExecuteResultSet or ExecutePageReader methods to access the result set. The results from stored procedure can also be filled into a data set by using a DB2DataAdapter.

CREATE PROCEDURE GetNewProductPrice() 
LANGUAGE SQL RESULT SETS 1 
BEGIN 
  DECLARE v_pid INT DEFAULT -1; DECLARE v_count INT DEFAULT -1; 
  DECLARE SQLCODE INT DEFAULT 0;
  DECLARE v_numProductsUpdated INT DEFAULT 0; 
  DECLARE c0 CURSOR FOR 
       SELECT pid, COUNT(*) FROM orders group by pid; 
  DECLARE c1 CURSOR WITH RETURN FOR 
       SELECT * FROM products; 
  OPEN c0;
  FETCH c0 INTO v_pid, v_count; 
  WHILE SQLCODE <> 100 
  DO 
     IF v_count = 0 THEN 
       UPDATE products SET price = price * 0.25 WHERE pid = v_pid; 
       SET v_numProductsUpdated = v_numProductsUpdated + 1; 
     ELSEIF v_count <= 50 THEN 
       UPDATE products SET price = price * 0.5 WHERE pid = v_pid; 
       SET v_numProductsUpdated = v_numProductsUpdated + 1; 
     ELSEIF v_count <= 100 THEN 
       UPDATE products SET price = price * 0.75 WHERE pid = v_pid; 
       SET v_numProductsUpdated = v_numProductsUpdated + 1; 
     ELSEIF v_count <= 500 THEN 
       UPDATE products SET price = price * 0.9 WHERE pid = v_pid; 
       SET v_numProductsUpdated = v_numProductsUpdated + 1; 
     END IF; 
     FETCH c0 INTO v_pid, v_count; 
  END WHILE; 
  CLOSE c0;
  OPEN c1; 
  RETURN numProductsUpdated;
END;

Listing 4 shows the corresponding .NET code.

cmd.CommandText = "CALL GetNewProductPrice()";
DB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
   //Perform logic
}

Back to top

Using array type input parameters

Consider a modification to the UpdateProductPrice stored procedure in Listing 3 in which the ratio at which the new price is determined and the list of products whose price should not be affected are passed in as input parameters. The natural way to do this would be to accept an array of values as input parameters. The DB2 for Linux, UNIX, and Windows (DB2 LUW) server Version 9.5 and later supports the capability of defining a stored procedure with array type parameters (see Resources for more information). The IBM Data Server Provider for .NET, Version 9.7 and later, exploits this feature for .NET applications.

Create an array type using the CREATE TYPE command (see Resources for more information). The type should then be used when defining the stored procedure. Note that 1-based indexing should be used when accessing the elements of the array within the stored procedure. Within the .NET application, the ArrayLength property on the DB2Parameter object should be set to indicate the number of values bound in for the parameter. As is evident from the stored procedure definition in Listing 5, it is not necessary that each of the array parameters have the same length.

CREATE TYPE intArr3 AS int ARRAY[3]
CREATE TYPE decArr4 AS decimal(3,2) ARRAY[4]
CREATE PROCEDURE ApplyNewProductPriceWithExcludes(
     excludePIDs intArr3, newPriceRatio decArr4) 
LANGUAGE SQL RESULT SETS 1 
BEGIN 
  DECLARE v_pid INT DEFAULT -1; DECLARE v_count INT DEFAULT -1; 
  DECLARE SQLCODE INT DEFAULT 0;
  DECLARE v_numProductsUpdated INT DEFAULT 0; 
  DECLARE c0 CURSOR FOR 
       SELECT pid, SUM(quantity) FROM orders group by pid; 
  DECLARE c1 CURSOR WITH RETURN FOR 
       SELECT * FROM products; 
  OPEN c0;
  FETCH c0 INTO v_pid, v_count; 
  WHILE SQLCODE <> 100 
  DO 
    IF (v_pid <> excludePIDs[1] AND v_pid <> excludePIDs[2] AND v_pid <> excludePIDs[3]) 
    THEN 
     IF v_count = 0 THEN 
       UPDATE products SET price = price * newPriceRatio[1] WHERE pid = v_pid; 
       SET v_numProductsUpdated = v_numProductsUpdated + 1; 
     ELSEIF v_count <= 50 THEN 
       UPDATE products SET price = price * newPriceRatio[2] WHERE pid = v_pid; 
       SET v_numProductsUpdated = v_numProductsUpdated + 1; 
     ELSEIF v_count <= 100 THEN 
       UPDATE products SET price = price * newPriceRatio[3] WHERE pid = v_pid; 
       SET v_numProductsUpdated = v_numProductsUpdated + 1; 
     ELSEIF v_count <= 500 THEN 
       UPDATE products SET price = price * newPriceRatio[4] WHERE pid = v_pid; 
       SET v_numProductsUpdated = v_numProductsUpdated + 1; 
     END IF; 
    END IF; 
     FETCH c0 INTO v_pid, v_count; 
  END WHILE; 
  CLOSE c0;
  OPEN c1; 
  RETURN numProductsUpdated;
END

Listing 6 shows the corresponding .NET code.

cmd.CommandText = "CALL ApplyNewProductPriceWithExcludes(
         @p_excludePIDs, @p_newPriceRatio)";
int[] excludePIDs = new int[] { 1, 2, 5 };
cmd.Parameters.Add(new DB2Parameter("@p_excludePIDs", excludePIDs));
cmd.Parameters["@p_excludePIDs"].ArrayLength = 3;
decimal[] newPriceRatio = new decimal[] { 0.3M, 0.5M, 0.6M, 0.8M };
cmd.Parameters.Add(new DB2Parameter("@p_newPriceRatio", newPriceRatio));
cmd.Parameters["@p_newPriceRatio"].Precision = 3; 
cmd.Parameters["@p_newPriceRatio"].Scale = 2;
cmd.Parameters["@p_newPriceRatio"].ArrayLength = 4;
DB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    //Perform logic
}

Although the DB2 LUW server supports array type parameters for input and output parameters, the IBM Data Server Provider for .NET supports the array type for input parameters only.

Accessing the return value

The stored procedures GetNewProductPrice and ApplyNewProductPriceWithExcludes defined above have the RETURN statement to return the number of products for which the new price was computed. You can access this return value from .NET code by binding a parameter whose direction is set to ReturnValue. Note that the command text needs to be placed within braces {} to execute it correctly to get back the return value, as shown in Listing 7.

cmd.CommandText = "{@numProductsAffected = CALL GetNewProductPrice()}";
cmd.Parameters.Add(new DB2Parameter("@numProductsAffected", DB2Type.Integer));
cmd.Parameters["@numProductsAffected"].Direction = ParameterDirection.ReturnValue;
DB2DataReader dr = cmd.ExecuteReader();
int numProductsAffected = (int)cmd.Parameters["@numProductsAffected"].Value;
Console.WriteLine("The price for " + numProductsAffected + " product(s) was affected");
while (dr.Read())
{
    //Perform logic
}

When connected to Informix servers, the return values from a stored procedure are returned as a result set. If such a result set contains only one value and you want to access the value through a return value parameter, set the ResultSetAsReturnValue attribute.

Back to top

Accessing multiple result sets that stored procedure returns

Stored procedures can be defined to return more than one result set. Use the NextResult method on the DB2DataReader object to access the subsequent result sets. The method closes the current cursor and opens the next cursor. It is not necessary to know the total number of result sets that the stored procedure is returning. If NextResult returns false, it indicates that there are no more result sets available.

The stored procedure ProcessDiscountsAndInventory shown in Listing 8 looks up all the orders placed for a given period of time. It applies discounts to customer, and it identifies products that need to be replenished. It returns two results sets: one for all customers who received a discount and one for all products that need to be replenished.

Listing 8 shows the stored procedure code.

CREATE PROCEDURE ProcessDiscountsAndInventory 
      (p_startDate TIMESTAMP, p_endDate TIMESTAMP) 
LANGUAGE SQL RESULT SETS 2 
BEGIN 
  DECLARE v_pid INT DEFAULT -1; 
  DECLARE v_custID INT DEFAULT -1; 
  DECLARE v_quantity INT DEFAULT -1; 
  DECLARE SQLCODE INT DEFAULT 0; 
  DECLARE c0 CURSOR FOR 
       SELECT pid, custID, quantity FROM orders WHERE date >startDate AND date <endDate ; 
  DECLARE c1 CURSOR WITH RETURN FOR 
       SELECT * FROM customers WHERE discount_date = current date ; 
  DECLARE c2 CURSOR WITH RETURN FOR 
       SELECT * FROM inventory WHERE replenish = 'y'; 
  OPEN c0; 
  FETCH c0 INTO v_pid, v_custID, v_quantity; 
  WHILE SQLCODE <> 100 DO 
     UPDATE customers SET discount = 10, discount_date = current date 
         WHERE custID = v_custID; 
     UPDATE inventory SET replenish = 'y', quantity = v_quantity * 2 
         WHERE pid = v_pid AND quantity < v_quantity OR quantity < minQuantity; 
     FETCH c0 INTO v_pid, v_custID, v_quantity; 
  END WHILE; 
  CLOSE c0; 
  OPEN c1; 
  OPEN c2; 
END

Listing 9 shows the corresponding .NET code.

cmd.CommandText = "CALL ProcessDiscountsAndInventory(@startDate, @endDate)";
cmd.Parameters.Add("@startDate", DateTime.Parse("1/1/2010"));
cmd.Parameters.Add("@endDate", DateTime.Parse("12/31/2010"));
DB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    //Perform logic on customer who received discount today
}
dr.NextResult();
while (dr.Read())
{
    //Perform logic on list of products for which the inventory needs to be replenished
}
dr.Close();

Creating data sets and stored procedures

Stored procedures are heavily used to generate data for reports. The complex logic to pull together the data for a report can be coded efficiently within a stored procedure and returned using result sets to the application. When accessing such stored procedures, you should use data sets instead of using a data reader. Once the data has been retrieved into a data set, the connection to the database can be freed, and the dataset can be passed around to modules within the application to create reports.

The DB2DataAdapter object should be used to read the result sets into a data set. For each result set, a data table is created in the data set. Because ProcessDiscountsAndInventory returns two result sets, two tables are created in the data set, as shown in Listing 10.

cmd.CommandText = "CALL ProcessDiscountsAndInventory(@p_startDate, @p_endDate)";
cmd.Parameters.Add("@p_startDate", DateTime.Parse("1/1/2010"));
cmd.Parameters.Add("@p_endDate", DateTime.Parse("12/31/2010"));
DB2DataAdapter da = new DB2DataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    //Perform logic on customer who received discount today
}
for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
{
    //Perform logic on list of products for which the inventory needs to be replenished
}

Back to top

Accessing result sets returned by a stored procedure simultaneously

Business logic is not always as simple as applying a flat 10% discount to each customer as illustrated in the previous section. The logic could require that the discount be computed based on how the product has been selling, what the current inventory is, and how many discounts a particular customer has received.

The OrderDetails stored procedure shown in Listing 11 declares three cursors, each giving relevant information about the product and its sales. The caller needs to access the cursors simultaneously so that it can gather the relevant information for a particular product from each of the cursors and calculate the discount. To provide simultaneous access to the cursors, the stored procedure should be defined to return the cursors as output parameters. The .NET code should set the DB2Type to DB2Type.Cursor when binding the parameter object.

CREATE OR REPLACE TYPE cur AS CURSOR
CREATE PROCEDURE OrderDetails (p_startDate TIMESTAMP, p_endDate TIMESTAMP, 
  OUT prodDetails cur, OUT prodOrderDetails cur, OUT custOrderDetails cur) 
LANGUAGE SQL 
BEGIN 
  SET prodDetails = CURSOR WITH HOLD FOR 
    SELECT p.pid, price, quantity FROM products p, inventory i 
      WHERE p.pid = i.pid AND p.pid IN (SELECT DISTINCT pid FROM orders) ORDER BY pid; 
  SET prodOrderDetails = CURSOR WITH HOLD FOR 
    SELECT pid, COUNT(*), SUM (quantity) FROM orders  
      WHERE date >= p_startDate AND date <= p_endDate GROUP BY pid ORDER BY pid; 
  SET custOrderDetails = CURSOR WITH HOLD FOR
    SELECT pid, custID, COUNT(*), SUM(quantity) FROM orders 
      WHERE date >= p_startDate AND date <= p_endDate 
      GROUP BY pid, custID ORDER by pid, custID;
  OPEN prodDetails; 
  OPEN prodOrderDetails; 
  OPEN custOrderDetails; 
END;

Listing 12 shows the corresponding .NET code.

cmd.CommandText = "CALL OrderDetails(
      @p_startDate, @p_endDate, @prodDetails, @prodOrderDetails, @custOrderDetails)";
cmd.Parameters.Add("@p_startDate", DateTime.Parse("1/1/2010"));
cmd.Parameters.Add("@p_endDate", DateTime.Parse("12/31/2010"));
cmd.Parameters.Add("@prodDetails", DB2Type.Cursor); 
cmd.Parameters["@prodDetails"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@prodOrderDetails", DB2Type.Cursor); 
cmd.Parameters["@prodOrderDetails"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@custOrderDetails", DB2Type.Cursor); 
cmd.Parameters["@custOrderDetails"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
DB2DataReader prodDetailsDR = 
    (DB2DataReader)cmd.Parameters["@prodDetails"].Value;
DB2DataReader prodOrderDetailsDR = 
    (DB2DataReader)cmd.Parameters["@prodOrderDetails"].Value;
DB2DataReader custOrderDetailsDR = 
    (DB2DataReader)cmd.Parameters["@custOrderDetails"].Value;

while (prodOrderDetailsDR.Read())
{
    pid = prodOrderDetailsDR.GetInt32(0);
    numOrders = prodOrderDetailsDR.GetInt32(1);
    totalOrderQuantity = prodOrderDetailsDR.GetInt32(2);
    prodDetailsDR.Read();
    price = prodDetailsDR.GetDecimal(1);
    currentInventory = prodDetailsDR.GetInt32(2);
    int totalCustOrders = 0;
    while (custOrderDetailsDR.Read())
    {
        custID = custOrderDetailsDR.GetInt32(1);
        numOrdersByCust = custOrderDetailsDR.GetInt32(2); 
        totalCustOrders += numOrdersByCust;
        totalOrderQuantityByCust = custOrderDetailsDR.GetInt32(3);
        //Calculate discount based on numOrders, numOrdersByCust,
        //  totalOrderQuantity, totalOrderQuantityByCust, price and currentInventory
        if (totalCustOrders == numOrders) //done with this pid
            break;
    }
}
prodDetailsDR.Close();
prodOrderDetailsDR .Close();
custOrderDetailsDR .Close();

Note that the data reader from a cursor type output parameter can be accessed from the Value property only after invoking the ExecuteNonQuery method. If the command is executed using either the ExecuteReader or ExecuteResultSet methods, the result sets are returned in the DB2DataReader or DB2ResultSet object. The subsequent result sets should be accessed sequentially by calling the NextResult method. Though the output parameters have been bound, accessing their Value properties will result in an InvalidOperation exception.

When working with cursors simultaneously, the application might want to close one data reader and continue working on the others. For this, the cursor should be declared as holdable within the stored procedure.

Back to top

Creating default values for parameters

Starting in Version 9.7, the DB2 LUW servers support creating stored procedures with default values for parameters. The caller can specify DEFAULT instead of a parameter marker in the CALL statement.

In the stored procedure in Listing 13, the output value for p_minQuantity is calculated based on the value of p_quantity, as shown in Listing 13. The p_quantity value is defined with a default value of 100.

CREATE PROCEDURE AddProductAndInventory (p_name VARCHAR(128), 
    p_price DECIMAL(30,2), OUT p_PID INTEGER, 
    p_quantity INT DEFAULT 100, OUT p_minQuantity INTEGER  ) 
  BEGIN 
    SELECT pid INTO p_PID FROM NEW TABLE 
      (INSERT INTO Products (name, price) VALUES (p_name, p_price)); 
  IF p_quantity <= 100 THEN 
    SET p_minQuantity = 100; 
  ELSE 
    SET p_minQuantity = p_quantity / 2; 
  END IF; 
  INSERT INTO inventory(pid, quantity, minQuantity, replenish) 
    VALUES (p_PID, p_quantity, p_minQuantity, 'y'); 
END

Listing 14 shows the corresponding .NET code.

cmd.CommandText = 
  "CALL AddProductAndInventory(@p_name, @p_price, @p_pid, DEFAULT, @p_minQuantity)";
cmd.Parameters.Add(new DB2Parameter("@p_name", "wireless router"));
cmd.Parameters.Add(new DB2Parameter("@p_price", 49.99));
cmd.Parameters.Add(new DB2Parameter("@p_pid", DB2Type.Integer));
cmd.Parameters["@p_pid"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new DB2Parameter("@p_minQuantity", DB2Type.Integer));
cmd.Parameters["@p_minQuantity"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
int newPID = (int)cmd.Parameters["@p_pid"].Value;
int minQuantity = (int)cmd.Parameters["@p_minQuantity"].Value;
Console.WriteLine("newPID=" + newPID.ToString() + ", minQuantity=" + minQuantity);

Back to top

Using named arguments

DB2 LUW server, Version 9.7, introduces support for named arguments. When calling a stored procedure, the arguments can be assigned to parameters using a name. Because the arguments are referred to by a name, the order in which the arguments are passed in need not match the order in which the parameters are specified in the stored procedure definition. This is a different programming style than the named parameter marker style that is typically used in .NET programs. Application writers familiar with other data servers might prefer this style.

When calling the AddProductAndInventory stored procedure, you can use named arguments to specify all the input parameters followed by all the output parameters, as shown in Listing 15.

cmd.CommandText = 
  "CALL AddProductAndInventory(p_name => ?, p_price => ?, p_quantity => ?, 
                               p_pid => ?, p_minQuantity => ?)";
cmd.Parameters.Add(new DB2Parameter(null, "wireless router"));
cmd.Parameters.Add(new DB2Parameter(null, 49.99));
cmd.Parameters.Add(new DB2Parameter(null, 500));
cmd.Parameters.Add(new DB2Parameter(null, DB2Type.Integer));
cmd.Parameters[3].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new DB2Parameter(null, DB2Type.Integer));
cmd.Parameters[4].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
int newPID = (int)cmd.Parameters[3].Value;
int minQuantity = (int)cmd.Parameters[4].Value;
Console.WriteLine("newPID=" + newPID.ToString() + ", minQuantity=" + minQuantity);

Listing 15 uses positioned parameter markers (?). You can also use the hostvar parameter markers (:parameterName). For this you must set HostVarParameters to true on the connection string. The named parameter markers style (@parameterName) is not supported in conjunction with named arguments.

Back to top

Using unassigned parameters

When using named arguments, you can omit arguments that have default values from the CALL statement. The code in Listing 14 can be rewritten as shown in Listing 16.

cmd.CommandText = "CALL AddProductAndInventory(p_name => ?, p_price => ?, 
                                         p_pid => ?, p_minQuantity => ?)";
cmd.Parameters.Add(new DB2Parameter(null, "wireless router"));
cmd.Parameters.Add(new DB2Parameter(null, 49.99));
cmd.Parameters.Add(new DB2Parameter(null, DB2Type.Integer));	
cmd.Parameters[2].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new DB2Parameter(null, DB2Type.Integer));
cmd.Parameters[3].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
int newPID = (int)cmd.Parameters[2].Value;
int minQuantity = (int)cmd.Parameters[3].Value;
Console.WriteLine("newPID=" + newPID.ToString() + ", minQuantity=" + minQuantity);

Back to top

Conclusion

This article shows you how easy it is to access simple and complex stored procedures from a .NET application. The links to the server and .NET documentation in the Resources section provide you assistance in extrapolating on the code examples to help you build efficient stored procedures and the equivalent .NET application. The understanding gained from this article should give you a high level of confidence to take on the challenge to move complex business logic into stored procedures and exploit them from your .NET application.

Resources

Learn

• Go to the DB2 for Linux, UNIX, and Windows Information Center for more information about the list of servers and levels supported by the IBM Data Server Provider for .NET.
  
  
• Refer to the following topics in the DB2 for Linux, UNIX, and Windows Information Center for more detailed information:
  • Array type parameters
  • CREATE TYPE command
  • ArrayLength property
  • ResultSetAsReturnValue attribute
  • DB2Type.Cursor
  • Named arguments
  • HostVarParameters
  
  
• See the Information Management and Visual Studio .NET page on developerWorks to get the resources you need to advance your skills in developing IBM database applications using .NET.
  
  
• Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Follow developerWorks on Twitter.
  
  
• Watch developerWorks on-demand demos, ranging from product installation and setup demos for beginners to advanced functionality for experienced developers.
  
  

Get products and technologies

• Download a free trial version of DB2 Enterprise for Linux, UNIX, and Windows.
  
  
• Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Kanchana Padmanabhan has been working on client connectivity tools for IBM Data Servers for the past 11 years. She is the technical lead for the IBM Data Server Providers for .NET team.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Understanding parameter directions
• Accessing a stored procedure that returns a result set
• Using array type input parameters
• Accessing the return value
• Accessing multiple result sets that stored procedure returns
• Creating data sets and stored procedures
• Accessing result sets returned by a stored procedure simultaneously
• Creating default values for parameters
• Using named arguments
• Using unassigned parameters
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers