Update Records using ADO.NET in ASP.NET Core Application

Update Records using ADO.NET in ASP.NET Core Application

I should also have the Update Inventory feature in my project. With this feature I will have the power to update any previously created Inventory records. Like, I can update the Name, Price and Quantity of a record that I want.

Note: Check my previous tutorial – Read Records using ADO.NET in ASP.NET Core Application

You know that in the Index View the records are shown in a HTML table. In this HTML table I can create a new column with an update link, on clicking this link the user is redirected to another View called Update.cshtml where he can update the old values with new one.

So add this new column in the HTML table of Index View as shown below:

<table class="table table-bordered table-sm table-striped">
    <thead>
        <tr><th>Id</th><th>Name</th><th>Price</th><th>Quantity</th><th>Added On</th><th>Update</th></tr>
    </thead>
    <tbody>
        @if (Model == null)
        {
            <tr><td colspan="6" class="text-center">No Model Data</td></tr>
        }
        else
        {
            @foreach (var p in Model)
            {
                <tr>
                    <td>@p.Id</td>
                    <td>@p.Name</td>
                    <td>@string.Format(new System.Globalization.CultureInfo("en-US"), "{0:C2}", p.Price)</td>
                    <td>@p.Quantity</td>
                    <td>@string.Format("{0:dddd, dd MMMM yyyy}", p.AddedOn)</td>
                    <td><a asp-action="Update" asp-route-id="@p.Id">Update</a></td>
                </tr>
            }
        }
    </tbody>
</table>

You can see I added a new ‘th’ element inside the ‘thead’ element:

<th>Update</th>

I also added a new ‘td’ element inside the foreach loop:

<td><a asp-action="Update" asp-route-id="@p.Id">Update</a></td>

The asp-action=”Update” is a tag helper to set the href attribute of the anchor tag to the Update Action, based on the routing system of the project.

I also added asp-route-id=”@p.Id” to the anchor so that the Id of the clicked records is also added to the href value.

This means the links formed in the update column in the html table will be:

<a href="/Home/Update/1">Update</a>
<a href="/Home/Update/2">Update</a>
<a href="/Home/Update/3">Update</a>

Check the below image which illustrates the Update links in the HTML table:

update link index view

Next, I need to create Update Actions (GET and POST type) in my Controller. There codes are shown below:

public IActionResult Update(int id)
{
    string connectionString = Configuration["ConnectionStrings:DefaultConnection"];

    Inventory inventory = new Inventory();
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = $"Select * From Inventory Where Id='{id}'";
        SqlCommand command = new SqlCommand(sql, connection);

        connection.Open();

        using (SqlDataReader dataReader = command.ExecuteReader())
        {
            while (dataReader.Read())
            {
                inventory.Id = Convert.ToInt32(dataReader["Id"]);
                inventory.Name = Convert.ToString(dataReader["Name"]);
                inventory.Price = Convert.ToDecimal(dataReader["Price"]);
                inventory.Quantity = Convert.ToInt32(dataReader["Quantity"]);
                inventory.AddedOn = Convert.ToDateTime(dataReader["AddedOn"]);
            }
        }

        connection.Close();
    }
    return View(inventory);
}

[HttpPost]
public IActionResult Update(Inventory inventory, int id)
{
    string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = $"Update Inventory SET Name='{inventory.Name}', Price='{inventory.Price}', Quantity='{inventory.Quantity}' Where Id='{id}'";
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }

    return RedirectToAction("Index");
}

The first one is the GET type of Update View. It has an ‘int’ parameter called id, which gets the id of the records sent in the URL.

For example – if the URL is – “/Home/Update/2”, then the id parameter will get the value of 2.

ASP.NET Core does this thing automatically by Model Binding technique.

I then created by SQL query using the id parameter as:

string sql = $"Select * From Inventory Where Id='{id}'";

This will help me to fetch only the record of the particular ‘id’ which is sent on the URL of the page.

Next, I execute this query with the SqlCommand object and get a SqlDataReader object. Using this object I fill the record values to a variable of type Inventory class, and at the last returns this variable as the Model to the View.

The codes which I am talking about are:

using (SqlDataReader dataReader = command.ExecuteReader())
{
    while (dataReader.Read())
    {
        inventory.Id = Convert.ToInt32(dataReader["Id"]);
        inventory.Name = Convert.ToString(dataReader["Name"]);
        inventory.Price = Convert.ToDecimal(dataReader["Price"]);
        inventory.Quantity = Convert.ToInt32(dataReader["Quantity"]);
        inventory.AddedOn = Convert.ToDateTime(dataReader["AddedOn"]);
    }
}

Now coming to the HttpPost type of Update View, where I am doing the updation of the record. This action receives 2 arguments and both of them gets their value from the model binding feature, these arguments are:

  • Inventory inventory – it will get it’s value from the form which the user will submit in the Update View.
  • int id – it will get it’s value from the URL.

In this action I create the update SQL Query text from the values the user submits in the form, which is given in the Update View:

string sql = $"Update Inventory SET Name='{inventory.Name}', Price='{inventory.Price}', Quantity='{inventory.Quantity}' Where Id='{id}'";

Next, this SQL query is executed by the SqlCommand object like this:

using (SqlCommand command = new SqlCommand(sql, connection))
{
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

In the last line I am redirecting the user to the Index action method so that they can see the new value gets displayed in the Index View:

return RedirectToAction("Index");

Testing the Update Operation

You can now test the Update functionality. Run your project and click the Update link of the 3rd record i.e. the Shoes record. When it opens in the update view, change it’s:

1. Name to ‘Shoes’
2. Price to ‘69’
3. Quantity to ‘100’

Click the Update button to save the changes. You will be redirected to the Index View where you will find the records is now updated. Check the below large image which explains this feature.

update inventory record

You can download the source code using the below link:

Download

Conclusion

In this tutorial you learned to Update Records from a database table using ADO.NET. In the next tutorial I will teach you how to do the deletion of these records – Delete Records using ADO.NET in ASP.NET Core Application

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "ASP.NET Core, jQuery, EF Core, SEO, jQuery, HTML" and more.