In my previous example we talked about how to fetch data from database and how to populate the form with data using L2S. In this example I’m going to extend a little bit of what I have demonstrated in my previous example. Basically I’m going to show you the basic way on how to edit and delete data from the form and update the database using L2S technology. Since this is a continuation of my previous example so I would suggest you to refer that first before you go any further.
STEP 1: Setting up the GUI
Just for the purpose of this demo, I set up the GUI like below:
| <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>LINQ to SQL Demo Part 3</title> <style type="text/css"> .style1{width: 400px;} .style1 td {width:200px;} </style> </head> <body> <form id="form1" runat="server"> <asp:DropDownList ID="DropDownListCustomerID" runat="server" AutoPostBack="true" onselectedindexchanged="DropDownListCustomerID_SelectedIndexChanged"> </asp:DropDownList> <br /> <asp:Literal ID="LiteralMessage" runat="server"></asp:Literal><br /> <asp:Button ID="ButtonEdit" runat="server" Text="Edit" Enabled="false" onclick="ButtonEdit_Click" /> <asp:Button ID="ButtonDelete" runat="server" Text="Delete" Enabled="false" /> <asp:Button ID="ButtonUpdate" runat="server" Text="Update" Enabled="false"/> <asp:Button ID="ButtonCancel" runat="server" Text="Cancel" Enabled="false"/> <asp:Panel ID="PanelCustomerInfo" runat="server" Enabled="false"> <table class="style1"> <tr> <td>Company Name</td> <td><asp:TextBox ID="TextBoxCompanyName" runat="server"/></td> </tr> <tr> <td>Contact Name</td> <td><asp:TextBox ID="TextBoxContactName" runat="server"/></td> </tr> <tr> <td>Contact Title</td> <td><asp:TextBox ID="TextBoxContactTitle" runat="server" /></td> </tr> <tr> <td>Address</td> <td><asp:TextBox ID="TextBoxAddress" runat="server"/></td> </tr> <tr> <td>City</td> <td><asp:TextBox ID="TextBoxCity" runat="server" /></td> </tr> <tr> <td>Region</td> <td><asp:TextBox ID="TextBoxRegion" runat="server" /></td> </tr> <tr> <td>Postal Code</td> <td><asp:TextBox ID="TextBoxPostalCode" runat="server" /></td> </tr> <tr> <td>Country</td> <td><asp:TextBox ID="TextBoxCountry" runat="server" /></td> </tr> </table> </asp:Panel> </form> </body> </html> |
If you look at my previous example you will notice that the html markup above is a bit similar. The things that are added above are Buttons for Edit, Delete, Update and Cancel which is by default set their enable property to false, this is to prevent users from doing certain actions when the page is loaded in the browser. Aside from that I have also move the form fields inside a Panel control for validation purposes and removed the ReadOnly attribute for each TextBox.
STEP 2: Populating the DropDownList with the list of Customers and populate the form with the Customers information.
Now the next step if we are going to populate the DropDownList with the list of customers and populate the form with the customers information based on the customer ID selected from the DropDownList. Here are the code blocks below:
| private List<Customer> GetCustomers(){ using (NorthwindDataContext context = new NorthwindDataContext()){ return (from c in context.Customers select c).ToList(); } } private List<Customer> GetCustomerInfo(string customerID) { using (NorthwindDataContext context = new NorthwindDataContext()){ return(from c in context.Customers where c.CustomerID == customerID select c).ToList(); } } private void BindCustomersToList(){ DropDownListCustomerID.DataSource = GetCustomers(); DropDownListCustomerID.DataTextField = "ContactName"; DropDownListCustomerID.DataValueField = "CustomerID"; DropDownListCustomerID.DataBind(); } protected void Page_Load(object sender, EventArgs e){ if (!Page.IsPostBack) { BindCustomersToList(); } } protected void DropDownListCustomerID_SelectedIndexChanged(object sender, EventArgs e) { var customerInfo = GetCustomerInfo(DropDownListCustomerID.SelectedValue); TextBoxCompanyName.Text = customerInfo[0].CompanyName; TextBoxContactName.Text = customerInfo[0].ContactName; TextBoxContactTitle.Text = customerInfo[0].ContactTitle; TextBoxAddress.Text = customerInfo[0].Address; TextBoxCity.Text = customerInfo[0].City; TextBoxRegion.Text = customerInfo[0].Region; TextBoxPostalCode.Text = customerInfo[0].PostalCode; TextBoxCountry.Text = customerInfo[0].Country; ButtonEdit.Enabled = true; ButtonDelete.Enabled = true; } |
In this step I will not elaborate more on details because I have already demonstrated this in my previous example here. The only thing that’s added in the code above is we are setting the Enabled attribute of the Edit and Delete Button to true so that by the time users select certain Customer from the DropDownList then that’s the time that they can do certain operations like editing and deleting.
Running the code above will show something like this in the browser:
Selecting customers from the DropDownList
After selecting customers from the DropDownList
STEP 3: Editing the Form
Here’s the code for the Edit Button
| protected void ButtonEdit_Click(object sender, EventArgs e) { PanelCustomerInfo.Enabled = true; DropDownListCustomerID.Enabled = false; ButtonEdit.Enabled = false; ButtonDelete.Enabled = false; ButtonUpdate.Enabled = true; ButtonCancel.Enabled = true; LiteralMessage.Text = string.Empty; } |
As you can see there’s nothing special about the codes above. It just basically does some basic validations when you hit the Edit button on the form.
STEP 4: Updating the Form
Here’s the code for the Update method
| private void UpdateCustomerInfo(string ID) { using (NorthwindDataContext context = new NorthwindDataContext()) { var customer = (from c in context.Customers where c.CustomerID == ID select c).Single(); customer.CompanyName = TextBoxCompanyName.Text; customer.ContactName = TextBoxContactName.Text; customer.ContactTitle = TextBoxContactTitle.Text; customer.Address = TextBoxAddress.Text; customer.City = TextBoxCity.Text; customer.Region = TextBoxRegion.Text; customer.PostalCode = TextBoxPostalCode.Text; customer.Country = TextBoxCountry.Text; context.SubmitChanges(); LiteralMessage.Text = "<p style='color:Green;'>Information Updated!</p>"; } } |
As you can see, the code above is very straight forward and self explanatory. What happened there is we created a new instance of the DataContext and then we query the Customer object based on the ID using the LINQ syntax and passed it in a variable customer. The Single function is an eager function which returns the only element of a sequence that satisfies a specified condition. Once the LINQ Single function is invoked then DataContext will issue a parameterize SQL query to the database in which the SQL Server can understand and then bring back the results to the DataContext.
From there, we can then assign the customer fields based on the TextBox values and then call the context.SubmitChanges() method to update the database with the changes we made.
Now let’s try to run the code and see what happens:
Invoking the Edit Button
On Editing
After Invoking the Update Button
STEP 5: Deleting Customer
Here’s the code for the Delete method
| private void DeleteCustomerInfo(string ID) { using (NorthwindDataContext context = new NorthwindDataContext()) { var customer =(from c in context.Customers where c.CustomerID == ID select c).First(); context.Customers.DeleteOnSubmit(customer); context.SubmitChanges(); LiteralMessage.Text = "<p style='color:Green;'>Information Deleted!</p>"; } } |
Just like in the update method, the code above creates a new instance of the DataContext and then query the customer entity based on the ID. Note that since I am using the northwind database in this demo, then deleting of customer data directly will throw an exception because this table is being referenced to other table like Orders . So in order for the code above to work and just for the simplicity of this demo, I remove the relationships to the table that referenced it. If you wan't to implement cascade delete then you have to delete the related information to the other table. So for example if you have customer and this customer has orders then you'll have to perform delete in both tables to avoid exceptions. Here's an article that you can refer:Cascading Deletes in LINQ to SQL
Since we don’t want users to delete the information right away, we need to prompt them a confirmation message if they wish to continue the deletion or not. To do this we could simply hook up the javascript confirm function in the delete button. Take a look at the highlighted code below:
| <asp:Button ID="ButtonDelete" runat="server" Text="Delete" Enabled="false" onclick="ButtonDelete_Click" OnClientClick="return confirm('The selected customer will be deleted. Do you wish to continue?');return false;" /> |
Now let’s create the method for clearing the text fields and then call the method created above on click on the delete button. Here are the code blocks below:
| public static void ClearFormFields(Control Parent) { if (Parent is TextBox) { (Parent as TextBox).Text = string.Empty; } else { foreach (Control c in Parent.Controls) ClearFormFields(c); } }
|
| protected void ButtonDelete_Click(object sender, EventArgs e) { //Call the DELETE Method DeleteCustomerInfo(DropDownListCustomerID.SelectedValue); //Rebind the DropDownList to reflect the changes after deletion BindCustomersToList(); //Clear the fields ClearFormFields(Page); } |
Here's the output below when running the page and perform the delete:
On Deletion
After Deletion
STEP 6: Cancelling Operation
Here’s the code for the Cancel Button
| protected void ButtonCancel_Click(object sender, EventArgs e) { PanelCustomerInfo.Enabled = false; DropDownListCustomerID.Enabled = true; ButtonEdit.Enabled = true; ButtonDelete.Enabled = true; ButtonUpdate.Enabled = false; ButtonCancel.Enabled = false; } |
There's nothing fancy about the code above, It just basically toggles the enable property of the button when you hit the Cancel button on the form so that it will return to its default state.
SUMMARY:
In this demo we have learned about the basics on how to perform Edit,Update and Delete using the LINQ to SQL technology.
That's it! I hope someone find this useful!