Developing Methods for the StudentDB Class for ASP.NET 2.0 - Explaining the code for the methods
(Page 3 of 5 )
I think that by now you have a good idea of how we retrieve the data, from the previous article's code. Today we execute INSERT, UPDATE and DELETE statements using SqlCommand instances. As you can see, we have put the data access code inside a try/catch block to handle exceptions. The InsertStudent() method takes a Student object as a parameter, inserts a record using this object into the Students table, and returns an integer value that represents the newly inserted StudentID. The UpdateStudent() method also accepts a Student object as a parameter but doesn't return a value. The DeleteStudent() method accepts an integer value that represents the StudentID which will be deleted and returns the number of rows that have been affected by the command. The value will be 1 if it executes without problems, because we are deleting one record from the Students table.
Those three methods use another two versions of the SqlCommand.Parameters.Add() method that are different from the one we used in the previous article. The first overload accepts the parameter name and its data type; you specify the data type of SqlParameter objects through the use of the enumeration SqlDbType values. It then adds the parameter to the Parameters collection of the SqlCommand object. The return value of calling this Add() overload is the SqlParameter object that has been newly added to the SqlCommand.Parameters collection, and that's why we used the following syntax, which creates the parameter, adds it to the collection and assigns its value all in one line of code:
command.Parameters.Add("@AdmissionDate",
SqlDbType.DateTime).Value = student.AdmissionDate;
The Value property of the SqlParameter object in the above line is called on the return object of calling the Add() method, which is a SqlParameter object as I just said. We simply assign the student.AdmissionDate to the SqlParameter.Value. Note that this code is very concise and elegant too, but it can get even better.
The second overload of the Add() method that we have used looks like this:
command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = student.FirstName;
It's the same as the first overload that we just talked about, but it also accepts the size of the parameter's data type in the third parameter.
There is one more thing we need to talk about regarding these methods. We used the SqlCommand.ExecuteNonQuery() to execute the INSERT, UPDATE and DELETE statements. This method returns an integer value that represents the number of rows that have been affected by executing the command. In our example we didn't use the return value of calling the SqlCommand.ExecuteNonQuery() method in the UpdateStudent() and InsertStudent() method, we simply called it to execute the command. But we have used the returned value with the DeleteStudent() method to find out whether or not the record has been deleted. Note that if you are inserting a record with a matching StudentID in the database, an exception will be thrown. You can try that yourself by creating a student object in the Page_Load() event handler method with an existing StudentID record, and then passing it to the InsertStudent() method and observing what happens.
The InsertStudent() method calls the InsertStudent stored procedure which has an OUTPUT parameter. It returns the newly created StudentID using that parameter, and we show that we have an OUTPUT parameter to get its value using the following syntax:
command.Parameters.Add("@StudentID", SqlDbType.Int);
command.Parameters["@StudentID"].Direction =
ParameterDirection.Output;
connection.Open();
command.ExecuteNonQuery();
return (int) command.Parameters["@StudentID"].Value;
We created and added the @StudentID SqlParameter object in the same manner, without assigning it a value of course, then used the indexer of the Parameters collection to get access to that parameter. We simply told the SqlCommand that this is an output parameter by assigning the enumeration value ParameterDirection.Output to that SqlParameter's Direction property. After calling the SqlCommand.ExecuteNonQuery() method we can get the value of the OUTPUT parameter; we just return this value to the caller.
Now let's write some code to test those methods in the Page_Load() event handler method.
Next: Testing the Data Access Methods >>
More ASP.NET Articles
More By Michael Youssef
