- Analyzing Design Requirements
- Choosing Our Properties
- The Constructors
- Create, Update, and Delete Methods
- Caching the Data for Better Performance
- Getting More than One Record at a Time
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Create, Update, and Delete Methods
In the example we gave at the beginning of the chapter, we created a Customer object using the default constructor, assigned some properties, and then called a Create() method. This Create() method takes all of the current property values and inserts them into their corresponding columns in a new row in the Customers table. Again, the code in Listing 5.6 should be very familiar to you.
Example 5.6. The Create() method
C#
public int Create()
{
SqlConnection connection = new SqlConnection(_ConnectionString);
connection.Open();
SqlCommand command = new SqlCommand("INSERT INTO Customers "
+ "(LastName, FirstName, Address, City, State, Zip, Phone, "
+ "SignUpDate) VALUES (@LastName, @FirstName, @Address, "
+ "@City, @State, @Zip, @Phone, @SignUpDate)",
connection);
command.Parameters.AddWithValue("@LastName", _LastName);
command.Parameters.AddWithValue("@FirstName", _FirstName);
command.Parameters.AddWithValue("@Address", _Address);
command.Parameters.AddWithValue("@City", _City);
command.Parameters.AddWithValue("@State", _State);
command.Parameters.AddWithValue("@Zip", _Zip);
command.Parameters.AddWithValue("@Phone", _Phone);
command.Parameters.AddWithValue("@SignUpDate", _SignUpDate);
command.ExecuteNonQuery();
command.Parameters.Clear();
command.CommandText = "SELECT @@IDENTITY";
int newCustomerID = Convert.ToInt32(command.ExecuteScalar());
connection.Close();
_CustomerID = newCustomerID;
return newCustomerID;
}
VB.NET
Public Function Create() As Integer
Dim connection As New SqlConnection(_ConnectionString)
connection.Open()
Dim command As New SqlCommand("INSERT INTO Customers " _
& "(LastName, FirstName, Address, City, State, Zip, Phone, "_
& "SignUpDate) VALUES (@LastName, @FirstName, @Address, @City, "_
& "@State, @Zip, @Phone, @SignUpDate)", connection)
command.Parameters.AddWithValue("@LastName", _LastName)
command.Parameters.AddWithValue("@FirstName", _FirstName)
command.Parameters.AddWithValue("@Address", _Address)
command.Parameters.AddWithValue("@City", _City)
command.Parameters.AddWithValue("@State", _State)
command.Parameters.AddWithValue("@Zip", _Zip)
command.Parameters.AddWithValue("@Phone", _Phone)
command.Parameters.AddWithValue("@SignUpDate", _SignUpDate)
command.ExecuteNonQuery()
command.Parameters.Clear()
command.CommandText = "SELECT @@IDENTITY"
Dim newCustomerID As Integer = _
Convert.ToInt32(command.ExecuteScalar())
connection.Close()
_CustomerID = newCustomerID
Return newCustomerID
End Function
Generally when we create a record in the database, we're done with it, and we move on to other things. However, just in case, we've added an extra step to our Create() method. We're going back to the database to see what the value is in the CustomerID column of the new record we've created, using the SQL statement "SELECT @@IDENTITY." We're assigning that value to the CustomerID property of our class and sending it back as the return value of our method. Given the design parameter decision we made earlier, changing the CustomerID value to anything other than 0 means that it corresponds to an actual record in the database.
Going back again to the first code sample, we'll use a method called Update() to change the data in a specific row of our database table. That method is shown in Listing 5.7.
Example 5.7. The Update() method
C#
public bool Update()
{
if (_CustomerID == 0) throw new Exception("Record does not exist in Customers table.");
SqlConnection connection = new SqlConnection(_ConnectionString);
connection.Open();
SqlCommand command = new SqlCommand("UPDATE Customers SET "
+ "LastName = @LastName, FirstName = @FirstName, "
+ "Address = @Address, City = @City, State = @State, "
+ "Zip = @Zip, Phone = @Phone, SignUpDate = @SignUpDate "
+ "WHERE CustomerID = @CustomerID", connection);
command.Parameters.AddWithValue("@LastName", _LastName);
command.Parameters.AddWithValue("@FirstName", _FirstName);
command.Parameters.AddWithValue("@Address", _Address);
command.Parameters.AddWithValue("@City", _City);
command.Parameters.AddWithValue("@State", _State);
command.Parameters.AddWithValue("@Zip", _Zip);
command.Parameters.AddWithValue("@Phone", _Phone);
command.Parameters.AddWithValue("@SignUpDate", _SignUpDate);
command.Parameters.AddWithValue("@CustomerID", _CustomerID);
bool result = false;
if (command.ExecuteNonQuery() > 0) result = true;
connection.Close();
return result;
}
VB.NET
Public Function Update() As Boolean
If _CustomerID = 0 Then Throw New Exception("Record does not exist in Customers table.")
Dim connection As New SqlConnection(_ConnectionString)
connection.Open()
Dim command As New SqlCommand("UPDATE Customers SET "_
& "LastName = @LastName, FirstName = @FirstName, "_
& "Address = @Address, City = @City, State = @State, "_
& "Zip = @Zip, Phone = @Phone, SignUpDate = @SignUpDate "_
& "WHERE CustomerID = @CustomerID", connection)
command.Parameters.AddWithValue("@LastName", _LastName)
command.Parameters.AddWithValue("@FirstName", _FirstName)
command.Parameters.AddWithValue("@Address", _Address)
command.Parameters.AddWithValue("@City", _City)
command.Parameters.AddWithValue("@State", _State)
command.Parameters.AddWithValue("@Zip", _Zip)
command.Parameters.AddWithValue("@Phone", _Phone)
command.Parameters.AddWithValue("@SignUpDate", _SignUpDate)
command.Parameters.AddWithValue("@CustomerID", _CustomerID)
Dim result As Boolean = False
If command.ExecuteNonQuery() > 0 Then result = True
connection.Close()
Return result
End Function
We start our Update() method with a check of the CustomerID value. If it's 0, we know that the object does not correspond to an existing record in the database, so we throw an exception. If the code is allowed to continue, the rest includes the familiar connection and command objects, as well as parameters that take the current values of our properties and use them to update our database record.
The last few lines are used to check for a successful update of the database. The ExecuteNonQuery() method of the command object returns an integer indicating the number of rows affected by our command. Because our WHERE clause is matching the CustomerID column, a column that we know must have a unique value, the only thing we're interested in knowing is that at least one row was affected. If a value greater than 0 is returned from ExecuteNonQuery(), then we return a Boolean true value back to the calling code. This enables us to confirm that the data was indeed updated.
Where we can create and update data, we can also delete it. Enter our Delete() method in Listing 5.8, the simplest of the lot.
Example 5.8. The Delete() method
C#
public void Delete()
{
SqlConnection connection = new SqlConnection(_ConnectionString);
connection.Open();
SqlCommand command = new SqlCommand("DELETE FROM Customers "
+ "WHERE CustomerID = @CustomerID", connection);
command.Parameters.AddWithValue("@CustomerID", _CustomerID);
command.ExecuteNonQuery();
connection.Close();
_CustomerID = 0;
}
VB.NET
Public Sub Delete()
Dim connection As New SqlConnection(_ConnectionString)
connection.Open()
Dim command As New SqlCommand("DELETE FROM Customers "_
& "WHERE CustomerID = @CustomerID", connection)
command.Parameters.AddWithValue("@CustomerID", _CustomerID)
command.ExecuteNonQuery()
connection.Close()
_CustomerID = 0
End Sub
There isn't anything complex about this code. We create our connection object and command objects, use the current value of the CustomerID property to make our match in our SQL statement, and execute the command. Just in case the calling code decides it wants to do something with the data, such as call the object's Update() method against a record that no longer exists, we set the CustomerID property back to 0.