Updating Custom Object in SQL Database with VB.NET
See more tutorials in Advanced. This post has No Comments.
This tutorial follows on from the Inserting Custom Object and will show you how to build on that class to add the functionality of Updating Objects in VB.NET
Following on from the Updating Custom Object tutorial, we will look into how we can add the Update functionality, allowing us to edit an object and then commit changes to the database. By using objects, we could first make the changes and then decide whether or not to save these changes to the database.
However, in this example, we will use the class of the previous tutorial to add a method to update the object. If you have not followed that tutorial yet, it is recommended. You can find it here.
Once you have the project opened up, open up the Car.vb from App_Code. There are two things we are going to add: A constructor that takes an int parameter; and an UpdateMethod that takes a Car object parameter.
Let’s go ahead and add the constructor first. This will be different from the constructor we already have; it will be more like a method, without returning anything. The constructor will look something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
''' <summary> ''' Contructor for Car object, set the Car object from ID passed to it. ''' </summary> ''' <param name="theCarID">Int32 value of the ID of the Car to get.</param> Public Sub New(ByVal theCarID As Integer) Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString()) Try Dim cmd As SqlCommand cmd = New SqlCommand("sp_GetCarByID", connection) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@CarID", theCarID) connection.Open() Dim objReader As SqlDataReader = cmd.ExecuteReader() Do While objReader.Read() SetObjectData(objReader) Loop objReader.Close() connection.Close() Catch connection.Close() End Try End Sub |
Notice here we are referencing a new Stored Procedure, one that will retrieve the record from the database that matches the ID parameter we pass to it. The Stored Procedure will look something like this:
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE dbo.sp_GetCarByID @CarID int AS SELECT * FROM Cars WHERE ID = @CarID |
We also need another Stored Procedure – one to Update the Car object we pass. It should look something like this:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE dbo.sp_UpdateCar @ID int, @Make varchar(50), @Model varchar(50) AS UPDATE Cars SET Make = @Make, Model = @Model WHERE ID = @ID |
Now we have our Stored Procedures, we can finish up with the class by adding the Update method:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
''' <summary> ''' Updates a Car in the database. ''' </summary> ''' <returns>Returns Boolean value indicating if the method finished without errors.</returns> Public Shared Function UpdateCar(ByVal theCar As Car) As Boolean Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString()) Try Dim cmd As New SqlCommand("sp_UpdateCar", connection) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@ID", theCar.ID) cmd.Parameters.AddWithValue("@Make", theCar.Make) cmd.Parameters.AddWithValue("@Model", theCar.Model) connection.Open() cmd.ExecuteNonQuery() connection.Close() Return True Catch connection.Close() Return False End Try End Function |
Notice we are passing in a Car object, and then using its Properties to pass to the Stored Procedure. We return true if the Update was successful.
We are now done with modifying the class. We can move back to our ASPX page and make changes to the Repeater. What we are going to do is to display all the records in a Repeater, and provide a LinkButton for record to be edited. We will also provide two textboxes to edit the Make and Model, and then a button to commit the changes. The Repeater will look something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<asp:Repeater ID="repeater_Cars" runat="server"> <HeaderTemplate> <table width="350"><tr> <td><strong>ID</strong></td> <td><strong>Make</strong></td> <td><strong>Model</strong></td> <td><strong>Action</strong></td> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td><%# Eval("ID") %></td> <td><%# Eval("Make") %></td> <td><%# Eval("Model") %></td> <td><asp:LinkButton ID="lnk_Edit" runat="server" CommandArgument='<%# Eval("ID") %>' CommandName="EditCar" Text="Edit" /></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> |
Notice we are using the ItemCommand event of the Repeater to handle the edits. By doing this, we are able to set a CommandName and CommandArgument for each LinkButton that is rendered, for each object in the collection. We will use the ID for the CommandArgument, because the ID is the unique identifier. Let’s finish the form and add our textboxes and button:
|
1 2 3 4 |
ID: <asp:Literal ID="lit_ID" runat="server" /><br /> Make: <asp:TextBox ID="fld_Make" runat="server" /><br /> Model: <asp:TextBox ID="fld_Model" runat="server" /><br /> <asp:Button ID="btn_Update" runat="server" Text="Update" Enabled="false" /> |
We also include a Literal control to display the ID, as this is not editable. We disable the button on load, and will enable when an edit is clicked.
Moving to the code-behind, we will first need to make sure we are referencing our custom class:
|
1 |
Imports DBTutorials.Car |
Next, we bind the Repeater on Page_Load:
|
1 2 3 4 5 6 7 8 9 10 |
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) If (Not IsPostBack) Then BindRepeater() End If End Sub Protected Sub BindRepeater() repeater_Cars.DataSource = Car.GetAllCars() repeater_Cars.DataBind() End Sub |
Next, we create the OnItemCommand handler. This can be added manually, or by clicking on the Repeater in Design view, going to Properties and double-click on the ItemCommand Event field.
This first checks to see if the correct command is being called, and then it converts the CommandArgument to the ID of the Car object, which it then retrieves from the database using our new constructor, and then fills in the literal and textboxes as well as enabling the button.
Finally, we add the handler for the button click event:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Protected Sub btn_Update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Update.Click If (Not String.IsNullOrEmpty(fld_Make.Text)) AndAlso (Not String.IsNullOrEmpty(fld_Model.Text)) Then Dim carToEdit As New Car(Convert.ToInt16(lit_ID.Text)) carToEdit.Make = fld_Make.Text carToEdit.Model = fld_Model.Text If Car.UpdateCar(carToEdit) Then lit_ID.Text &= " [Updated]" fld_Make.Text = "" fld_Model.Text = "" BindRepeater() btn_Update.Enabled = False End If End If End Sub |
Now if this application is run, you will see that all Cars are displayed from the collection straight away. We are then able to click on the Edit link of each object, and edit them right on the page. We can even improve the functionality of this by including a ScriptManager and UpdatePanel. The editing will then seem instantaneous.
