Thursday, May 3

EDITING, UPDATING AND DELETING DATA IN THE FORM USING LINQ TO SQL


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{width400px;}
        .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. 

Thanks Shibashish Mohanty

No comments:

Post a Comment

Please don't spam, spam comments is not allowed here.

.

ShibashishMnty
shibashish mohanty