Page 224 - Open Soource Technologies 304.indd

Unit 13: Stored Procedure

The rest of the code is the same as in the previous example without parameters. As illustrated Notes
in the previous examples, ADO.NET takes a lot of pain out of database programming. Calling a
stored procedure uses virtually the same code as using standard SQL and specifying parameters
is a painless process.

13.2.10 Data Retrieval
Data Retrieval with stored procedures is the same (surprise!) as if using standard SQL. You can
wrap a DataAdapter around the Command object or you can use a DataReader to fetch the data
one row at a time. The previous examples have already illustrated how to use a DataAdapter
and fill a DataSet. The following example shows usage of the DataReader:

SqlConnection conn = new SqlConnection(“Data

Source=localhost;Database=Northwind;Integrated Security=SSPI”);
SqlCommand command = new SqlCommand(“GetProducts”, conn);

command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(“@CategoryID”, SqlDbType.Int).Value = 1;

conn.Open();SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{

Console.WriteLine(reader[“ProductName”]);
}

conn.Close();
Again, using either a DataAdapter or a DataReader against a query from a stored procedure is
the same as specifying the SQL from within the code.

13.2.11 Inserting Data Using Parameters
Using other SQL statements such as INSERT, UPDATE or DELETE follow the same procedure.
First, create a stored procedure that may or may not accept parameters, and then call the stored
procedure from within the code supply the necessary values if parameters are needed. The
following example illustrates how to insert a new user in a users table that has a username and
password field.

CREATE PROCEDURE [dbo].[InsertUser] (

@Username varchar(50),

@Password varchar(50)
) AS

INSERT INTO Users VALUES(@Username, @Password)

string username = ... // get username from user
string password = ... // get password from user

LOVELY PROFESSIONAL UNIVERSITY 219