.NET Daily

ADO.NET Tutorial. Create, Update, Delete Operations

Introduction

Darius

Darius

Darius Dumitrescu is a creative Senior CMS Consultant with in depth .NET knowledge, focused on Web Development and Architecture Design.


LATEST POSTS

My View of a JIRA Workflow for Web Development 17th April, 2018

How To Solve: SQL Server detected a logical consistency-based I/O error: invalid protection option. 19th March, 2018

C#

ADO.NET Tutorial. Create, Update, Delete Operations

Posted on .

Nowadays Entity Framework is the most promoted database query technology from .NET ecosystem. For good reason though, because it allows developers without extensive knowledge about SQL to interrogate the database, create complex queries and generate classes with the help of a user-friendly interface.

However, for some complex scenarios, Entity Framework doesn’t always deliver top notch performance so if you do want to speed up your website as much as possible, choosing plain ADO.NET might be the right choice.

On the other hand, ADO.NET comes with a “cost”: it doesn’t have any user interface to help you design stuff. Everything needs to be made manually. Of course there are third party tools that can generate the SQL queries for you or to create classes based on your database structure but comparing to Entity Framework, it doesn’t provide you all these features out of the box.

But hey, That’s the beauty! You have the possibility to design everything from scratch. In the following series of articles you learn how to create database queries, how to consume stored procedures and how to design your own application business layer.

In this tutorial I’ll show you how to make CRUD (Create, Receive, Update, Delete) operations with ADO.NET, how to create classes to map your data and how to use everything to display data into a Repeater or a GridView.

Prerequisites

For this tutorial we will need a simple database with only 2 tables: Articles and ArticleCategories. All the operations presented here will be made on these tables, however, this tutorial will not cover the database creation.

For demonstrating the CRUD operations I have created an ASP.NET WebForms application although ADO.NET can be integrated also with MVC, WPF or WebForms. ADO.NET isn’t dependent on a specific user-facing technology.

For testing purposes, I have attached a dummy database(SQL Server 2012) and the ASP.NET application at the end of the article.

Before we start coding, I want to explain some of the most important ADO.NET objects that are responsible for the CRUD operations described in this tutorial:

  • DataSet. Think about DataSet as a copy of a database stored in server’s memory. It’s used only for querying multiple SQL tables at once.
  • SqlDataReader. It is used for querying data from a single SQL table.
  • DataTable. The DataTable is a subitem of a DataSet and represents a database table stored in the memory.
  • SqlConnection. Object responsible with storing the data
  • SqlCommand. Object responsible with sending the SQL query to the server and returning the results.
  • SqlDataAdapter. SqlDataAdapter is responsible with filling a DataSet with the data returned from database.

Creating Mapping Classes for Database Data

Before we make any CRUD operation, we need to create some classes in our application to map the data that comes from the database and for making the management of the entities easier at application level.

The creation of the mapping class is fairly simple. For example the Article class looks like this:

The other classes can to be created similar with Article class.

ADO.NET Insert Operation

There are multiple possibilities to do a method for an insert operation but since we use mapping classes we will create a method that receives as a parameter an Article object and returns the ID(Primary Key) from database of the newly created article. The code looks like below:

Briefly, I created the SQL query for adding a new article. Then I created and opened a connection with the server. Next I created and executed a command that sends the SQL query to the server and receives the id of the new created item (That’s why @@Identity is there). Finally I close and dispose the command and the connection with the server and I return the result.

The try, catch statement is just for precaution.

Below is an example on how you can use the InsertArticle method:

ADO.NET Update Operation

For this operation I will reuse the insert method created above and change it so that it will also permit edit operations. The changes aren’t that complicated and this way you’ll save some lines of code in your application. The code looks like below:

Briefly, I have added a new SQL query for updating an article and after that based on the value of ArticleId field from article object parameter, I send the correct SQL query (insert or update) to the SQL server.

However, before you’ll want to do an update operation, you need to do a select by id operation first, right? Here is how the get article by id method looks like:

The above method is in fact part of the return operations category so I’ll not give too many details about it here, but it is often omitted from tutorials and I found myself few times searching for how to do it.

ADO.NET Return Operation

The return operation is a bit different that the other ones but not very complicated. We will make use again of the article mapping class but this time for returning the list of articles from database. The code from below shows how to get all the articles from the database.

Briefly, I created an SQL query for getting all the articles from database, then I created an SQL connection and after that I added the returned Article table into a DataReader. Finally, I iterated the each row of the DataReader and filled the article list.

Think about the DataReader as a SQL table stored in the server memory.

Below is an example on how to use it:

ADO.NET Delete Operation

The Delete operation has a similar code with the Insert and Update operation. We will make use again of the ExecuteNonQuery() method of the command object and we will use just the ID of the article as the method input instead of the whole article object.

Take a look at the code from below:

So this is it! These are all the basic database operations made with ADO.NET. Furthermore you can of course try custom insert, list or delete operations but as you can see the complexity of the operations depends very much on your SQL knowledge.

You can download the demo application and the database from here:

Attachments

Darius

Darius

Darius Dumitrescu is a creative Senior CMS Consultant with in depth .NET knowledge, focused on Web Development and Architecture Design.

Comments
  • user

    AUTHOR sachin datey

    Posted on 7:45 am September 15, 2014.
    Reply

    I really found it useful for beginners to understand basics of .net

  • user

    AUTHOR nishzone

    Posted on 9:45 pm January 17, 2016.
    Reply

    How about SQL Injection? I take the example “string createQuery = String.Format(“Insert into Articles (ArticleTitle, ArticleBody ,PublishDate, ArticleCategoryID) Values(‘{0}’, ‘{1}’, ‘{2}’, {3} );”

  • View Comments (2) ...
    Navigation