DotNetOcean: 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:

.style1{width: 400px;}

.style1 td {width:200px;}

</style>

</head>

<body>

<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">

<tr>

<td>Company Name</td>

</tr>

<tr>

<td>Contact Name</td>

</tr>

<tr>

<td>Contact Title</td>

</tr>

<tr>

<td>Address</td>

</tr>

<tr>

</tr>

<tr>

<td>Region</td>

</tr>

<tr>

<td>Postal Code</td>

</tr>

<tr>

<td>Country</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

Labels

Thursday, May 3

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

No comments:

Post a Comment

.