ADO.NET – Update Records in ASP.NET Core

ADO.NET – Update Records in ASP.NET Core

Learn how to Update Database Records with ADO.NET in any ASP.NET Core application by adding an Update Records feature to the Inventory project. Using SqlConnection and SqlCommand with a parameterized UPDATE statement, you will edit any previously created record and save the changes to SQL Server. This is the Update step of CRUD operations.

Is ADO.NET fast? Yes, ADO.NET is one of the fastest ways to access data in .NET because it works directly with the database and adds very little overhead. ORMs such as Entity Framework Core and Dapper are built on top of ADO.NET. Entity Framework Core adds some overhead for features like change tracking and query translation, while Dapper is a lightweight micro-ORM that performs close to raw ADO.NET. The trade-off is that ADO.NET requires more code to write and maintain.

In the previous tutorial we displayed all the database records on the Index View in the form of an HTML table. Now, in this HTML table, we can create a new column with an update link. On clicking this link the user will be redirected to another View called Update.cshtml. In this update view the user can update the record with new values.

So, we add this new column in the HTML table of Index View as shown by highlighted code given below:

@model IEnumerable<Inventory>
@{
	ViewData["Title"] = "Read Inventory";
}

<h1>Inventory <a asp-action="Create" class="btn btn-sm btn-secondary">Create</a></h1>
<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 we added a new th element called “Update” inside the thead element:

<th>Update</th>

We 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" attribute is one of the built-in Tag Helpers in ASP.NET Core. On an anchor (<a>) tag, it automatically generates the href attribute that points to the Update action method of the controller, so each link opens the update page without a hard-coded URL.

The asp-route-id="@p.Id" attribute adds the Id of each record to the link’s URL as a route value. This lets the Update action know which record the user clicked, so the correct Inventory item loads for editing. The links generated in the Update column look like this:

<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 records ado.net asp.net core project

Next, we need to create Update Actions (GET and POST type) in the Home Controller. Their 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 HTTP GET type of Update Action Method. It has an int parameter called id, which gets the id value of the records sent in the URL.

For example – if the URL is – /Home/Update/2, then the id parameter will get the value “2”. ASP.NET Core does this thing automatically by Model Binding technique.

We then create an SQL query by using the id parameter as:

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

This will help to fetch only the record of the particular id which is sent in the URL of the page.

Next, the query with the SqlCommand object is executed and it gives a SqlDataReader object. With this object, we add values to the Inventory class variable, and at the last ,return this variable as the Model.

The codes which do this work 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 action, where we are doing the updation of the records. 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 we create the update SQL Query text from the values the user submits in the form.

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.

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

In the last line users are redirecting to the Index Action method so that they can see the updated values of the records.

return RedirectToAction("Index");

Finally, we have to add the Update View razor file inside the Views ➤ Home folder. It’s code is given below.

@model Inventory

@{
	ViewData["Title"] = "Update Inventory";
}

<h1>Update an Inventory <a asp-action="Index" class="btn btn-sm btn-secondary">Back</a></h1>
<form method="post">
	<div class="form-group">
		<label asp-for="Name"></label>
		<input class="form-control" asp-for="Name" />
	</div>
	<div class="form-group">
		<label asp-for="Price"></label>
		<input class="form-control" asp-for="Price" />
	</div>
	<div class="form-group">
		<label asp-for="Quantity"></label>
		<input class="form-control" asp-for="Quantity" />
	</div>
	<div class="text-center panel-body">
		<button type="submit" class="btn btn-sm btn-primary">Update</button>
	</div>
</form>
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. We will be redirected to the Index View where we will find the record is now updated. Check the below large image which explains this feature.

update records ado.net example

You can download the source code using the below link:

Download

Conclusion

In this tutorial, you learned how to update records in a SQL Server database table using ADO.NET in ASP.NET Core. You added an Update link to each row of the Inventory table, passed the record Id to the Update action with asp-route-id, loaded the existing values into a form, and saved the changes with a parameterized UPDATE statement using SqlCommand. This completes the Update part of CRUD operations.

Key Takeaways

  • An UPDATE statement with a WHERE clause on the record Id changes only the selected row.
  • The asp-route-id Tag Helper passes the clicked record’s Id to the controller action through the URL.
  • Parameterized queries keep user input separate from the SQL text and protect against SQL injection.
  • ExecuteNonQuery() runs the update and returns the number of rows affected, which you can use to confirm the change.

What’s Next?

You can now create, read, and update records. The final CRUD operation is removing data. In the next tutorial, you will learn how to delete records with ADO.NET in ASP.NET Core from the database.

Frequently Asked Questions

How do I update a record in SQL Server using ADO.NET in ASP.NET Core? – Open a SqlConnection, create a SqlCommand with an UPDATE statement and a WHERE Id = @Id condition, add the values as SqlParameter objects, and call ExecuteNonQuery().

Why is the WHERE clause important in an UPDATE query? – Without a WHERE clause, the UPDATE statement changes every row in the table instead of just the selected record.

What is the next step after updating records? – Deleting records, which is covered in the next tutorial.

SHARE THIS ARTICLE

  • linkedin
  • reddit
yogihosting

ABOUT THE AUTHOR

I hope you enjoyed reading this tutorial. If it helped you then consider buying a cup of coffee for me. This will help me in writing more such good tutorials for the readers. Thank you. Buy Me A Coffee donate