Calling a Stored Procedure that Returns Nothing

The simplest example of calling a stored procedure is one that returns nothing to the caller. There are two such procedures defined below, one for updating a pre-existing Region record, and the other for deleting a given Region record.
Record Update
Updating a Region record is fairly trivial, as there is only one column that can be modified (assuming primary keys cannot be updated).

CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,              @RegionDescription NCHAR(50)) AS

                 SET NOCOUNT OFF

                 UPDATE Region SET RegionDescription = @RegionDescription WHERE RegionID = @RegionID

                 GO

An update command on a more real-world table might need to re-select and return the updated record in its entirety. This stored procedure takes two input parameters (@RegionID and @RegionDescription), and issues an UPDATE statement against the database.
To run this stored procedure from within .NET code, you need to define a SQL command and execute it:

SqlCommand aCommand = new SqlCommand("RegionUpdate", conn);

aCommand.CommandType = CommandType.StoredProcedure;

aCommand.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID"));

This code creates a new SqlCommand object named aCommand, and defines it as a stored procedure. We then add each parameter in turn, and finally set the expected output from the stored procedure to one of the values in the UpdateRowSource enumeration, which is discussed later in this chapter.
The stored procedure takes two parameters: the unique primary key of the Region record being updated, and the new description to be given to this record.
Once the command has been created, it can be executed by issuing the following commands:

aCommand.Parameters[0].Value = 999;

aCommand.Parameters[1].Value = "South Western England";

aCommand.ExecuteNonQuery();

Here we are setting the value of the parameters, then executing the stored procedure. As the procedure returns nothing, ExecuteNonQuery() will suffice.
Command parameters may be set by ordinal as shown above, or set by name.
Record Deletion
The next stored procedure required is one that can be used to delete a Region record from the database:

CREATE PROCEDURE RegionDelete (@RegionID INTEGER) AS

       SET NOCOUNT OFF

       DELETE FROM Region WHERE RegionID = @RegionID

GO

This procedure only requires the primary key value of the record. The code uses a SqlCommand object to call this stored procedure as follows:

SqlCommand aCommand = new SqlCommand("RegionDelete" , conn);

aCommand.CommandType = CommandType.StoredProcedure;

aCommand.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int,0, "RegionID"));

This command only accepts a single parameter as shown in the following code, which will execute the RegionDelete stored procedure; here we see an example of setting the parameter by name:

aCommand.Parameters["@RegionID"].Value= 999; aCommand.ExecuteNonQuery();

Calling a Stored Procedure that Returns Output Parameters
Both of the previous examples execute stored procedures that return nothing. If a stored procedure includes output parameters, then these need to be defined within the .NET client so that they can be filled when the procedure returns.
The following example shows how to insert a record into the database, and return the primary key of that record to the caller.
Record Insertion
The Region table only consists of a primary key (RegionID) and description field (RegionDescription). To insert a record, this numeric primary key needs to be generated, then a new row inserted into the database. I have chosen to simplify the primary key generation in this example by creating one within the stored procedure. The method used is exceedingly crude, which is why I have devoted a section to key generation later in the chapter. For now this primitive example will suffice:

CREATE PROCEDURE RegionInsert(@RegionDescription NCHAR(50),

                @RegionID INTEGER OUTPUT) AS

       SET NOCOUNT OFF

       SELECT @RegionID = MAX(RegionID)+ 1 FROM Region

       INSERT INTO Region(RegionID, RegionDescription) VALUES(@RegionID, @RegionDescription)

GO

The insert procedure creates a new Region record. As the primary key value is generated by the database itself, this value is returned as an output parameter from the procedure (@RegionID). This is sufficient for this simple example, but for a more complex table (especially one with default values), it is more common not to utilize output parameters, and instead select the entire inserted row and return this to the caller. The .NET classes can cope with either scenario.
Calling this stored procedure is similar to the previous examples, except in this instance we need to read the output parameter after executing the procedure:

aCommand.Parameters["@RegionDescription"].Value = "South West";

aCommand.ExecuteNonQuery(); int newRegionID =

       (int)aCommand.Parameters["@RegionID"].Value;

After executing the command, we read the value of the @RegionID parameter and cast this to an integer.
You may be wondering what to do if the stored procedure you call returns output parameters and a set of rows. In this instance, define the parameters as appropriate, and rather than calling ExecuteNonQuery(), call one of the other methods (such as ExecuteReader()) that will permit you to traverse any record(s) returned.

Quick Data Access: The Data Reader

public class DataReaderExample {

       public static void Main(string[] args) {

                string select = "SELECT ContactName,CompanyName FROM Customers";

                OleDbConnection conn = new OleDbConnection(source);

                conn.Open();

                OleDbCommand cmd = new OleDbCommand(select , conn);

                OleDbDataReader aReader = cmd.ExecuteReader();

                while(aReader.Read())

                         Console.WriteLine("'{0}' from {1}",

                                 aReader.GetString(0) , aReader.GetString(1));

                aReader.Close();

                conn.Close();

       }

}

Insert Code

                       

                        SqlParameter[] arParams = new SqlParameter[4];

                        try

                        {

                              #region save data for Department

                              // "EntityID" as Input parameter

                              arParams[0] = new SqlParameter("@EntityID",SqlDbType.Int);

                              // Checks for the null Value for "EntityID" parameter

                              if(EntityID != 0)

                              {

                                    arParams[0].Value =  EntityID ;

                              }

                              else

                              {

                                    arParams[0].Value = System.DBNull.Value ;

                              }

                              // "ContactName" as Input parameter

arParams[1] = new SqlParameter("@ContactName",SqlDbType.VarChar);

                              // Checks for the null Value for "ContactName" parameter

                              if(ContactName != null)

                              {

                                    arParams[1].Value =  ContactName ;

                              }

                              else

                              {

                                    arParams[1].Value = System.DBNull.Value ;

                              }

                              // "DepartmentID" as output parameter

arParams[2] = new SqlParameter("@DepartmentID",SqlDbType.Int);

                              arParams[2].Direction = ParameterDirection.Output;

                              // "Department" as Input parameter

arParams[3] = new SqlParameter("@Department",SqlDbType.NVarChar);

                              // Checks for the null Value for "Department" parameter

                              if(Department != null)

                              {

                                    arParams[3].Value =  Department ;

                              }

                              else

                              {

                                    arParams[3].Value = System.DBNull.Value ;

                              }

                              #endregion

                                                     

                              ///Execute stored procedure and get the values

SqlCommand objSqlCommand = new SqlCommand("Proc_StudentSave", objSqlConnection);

objSqlCommand.CommandType = CommandType.StoredProcedure;

if (arParams != null)

{

objSqlCommand.Parameters.AddRange(arParams);

}

if (objSqlCommand.ExecuteNonQuery() > 0)

{

Response.Write("Record is insterd. New DepartmentId Is "  + arParams[2].Value);

}                   

                   

}

Sample Stored Procedure

CREATE PROCEDURE [dbo].[Proc_DepartmentSave]       

@DepartmentID int out,       

@Department varchar(50),       

@ContactName varchar(50),        

@EntityID int       

       

 AS       

     

If Exists (Select * From Department Where Department = @Department)   

Begin   

 set @DepartmentID = -1  

 return -1   

End   

        

INSERT INTO Department( Department, ContactName, EntityID)       

VALUES (@Department, @ContactName,@EntityID)     

     

select @DepartmentID=SCOPE_IDENTITY()