Adding Methods to the StudentDataAccess Class for ASP.NET 2.0 - The InsertNewStudent() and UpdateStudent() methods
(Page 2 of 5 )
Let's take a look at the InsertNewStudent() method. Add the following code to the StudentDataAccess class:
private void InsertNewStudent()
{
try
{
if (this.studentId == -1)
{
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand("InsertStudent",
connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = this.firstName;
command.Parameters.Add("@LastName", SqlDbType.NVarChar,
20).Value = this.lastName;
command.Parameters.Add("@DateOfBirth", SqlDbType.DateTime).Value
= this.dateOfBirth;
command.Parameters.Add("@AdmissionDate", SqlDbType.DateTime).Value = this.admissionDate;
command.Parameters.Add("@Major", SqlDbType.NVarChar, 40).Value =
this.major;
command.Parameters.Add("@Active", SqlDbType.Bit).Value =
this.active;
command.Parameters.Add("@StudentID", SqlDbType.Int);
command.Parameters["@StudentID"].Direction =
ParameterDirection.Output;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 1)
{
this.studentId = (int)command.Parameters["@StudentID"].Value;
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException("An error has occurred.");
}
}
Again we check to see whether the studentId is equal to -1. If the expression is evaluated to true we establish a connection to the database and create the command that executes the InsertStudent stored procedure which we have seen in the first part of this series. Note that we have passed the values of the private fields to the appropriate SqlParameter objects; if you haven't seen this syntax before I'm sure you are going to like it.
The syntax that we have used above, to create the SqlParameter objects and assign values to them to be associated and executed with the command, uses one of the available SqlCommand.Parameters.Add() method overloads. This method overload accepts the name of the parameter as a string value and the SQL Server data type of the parameter (through one of the values of the SqlDbType enumeration) and the size of the parameter. This overload creates the SqlParameter object, adds it to the SqlCommand.Parameters collection, and then returns it.
Because this method returns an object of type SqlParameter, you can use its Value property to assign a value for the parameter object, as if you were writing mySqlParameterObject.Value = myValue; which is a concise and elegant syntax. There is another overload that does the same thing but it has no third parameter. For the size of the parameter, we have used both of the overloads in the InsertNewStudent() method as shown next.
command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = this.firstName;
command.Parameters.Add("@LastName", SqlDbType.NVarChar,
20).Value = this.lastName;
command.Parameters.Add("@DateOfBirth", SqlDbType.DateTime).Value
= this.dateOfBirth;
command.Parameters.Add("@AdmissionDate", SqlDbType.DateTime).Value = this.admissionDate;
command.Parameters.Add("@Major", SqlDbType.NVarChar, 40).Value =
this.major;
command.Parameters.Add("@Active", SqlDbType.Bit).Value =
this.active;
command.Parameters.Add("@StudentID", SqlDbType.Int);
command.Parameters["@StudentID"].Direction =
ParameterDirection.Output;
The @StudentID is defined as an OUTPUT parameter in the InsertStudent stored procedure. To tell the command that it's an output parameter you need to set that SqlParameter object's SqlParameter.Direction property to the enumeration value ParameterDirection.Output. After opening the connection and executing the stored procedure you can return the OUTPUT parameter's value through the indexer on the SqlCommand.Parameters, and then assign this value to the studentId field. This means that we have initialized the object from the database and the studentId field has a value such as 1 or 3 from those values of the StudentID column of the Students table.
We check the return value of the SqlCommand.ExecuteNonQuery() method to determine whether or not a record has been inserted into the database; if so we get the value of the OUTPUT parameter.
Let's add the UpdateStudent() method to the StudentDataAccess class.
private void UpdateStudent()
{
try
{
if (this.studentId != -1)
{
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand("UpdateStudent",
connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@StudentID", SqlDbType.Int).Value =
this.studentId;
command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = this.firstName;
command.Parameters.Add("@LastName", SqlDbType.NVarChar,
20).Value = this.lastName;
command.Parameters.Add("@DateOfBirth", SqlDbType.DateTime).Value
= this.dateOfBirth;
command.Parameters.Add("@AdmissionDate", SqlDbType.DateTime).Value = this.admissionDate;
command.Parameters.Add("@Major", SqlDbType.NVarChar, 40).Value =
this.major;
command.Parameters.Add("@Active", SqlDbType.Bit).Value =
this.active;
connection.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new ApplicationException("An error has occurred.");
}
}
As you can see, the UpdateStudent() method is very similar to the InsertNewStudent() method. In this method we check to see if the value of the studentId field is not equal to -1. This makes sense because if we have an initialized StudentDataAccess object we can perform an update operation, but if the studentId value is equal to -1 it means that we don't have a record from the database in this StudentDataAccess object to perform an update on it.
We call the UpdateStudent stored procedure and pass it the values of the fields, through the SqlParameter objects that are added to the SqlCommand object, to perform the update using the UpdateStudent stored procedure. We need to test those methods from the Default.aspx page and see if they work. Before we go to the next section add a default constructor to the StudentDataAccess class as follows:
public StudentDataAccess()
{ }
Next: Testing the methods from a Web page >>
More ASP.NET Articles
More By Michael Youssef
