.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

How to Deliver Accurate Project Status Reports 21st April, 2018

JIRA Workflow for Web Development Example: Simple and Effective 17th April, 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:

/// <summary>
/// Article class that maps Articles table
/// </summary>
public class Article
{
public int ArticleID { get; set; }

public int CategoryID { get; set; }

public string Title{ get; set; }

public string Body { get; set; }

public DateTime PublishDate { get; set; }
}

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:

/// <summary>
/// Method created for inserting an article
/// </summary>
/// <param name="article"></param>
/// <returns></returns>
public int InsertArticle(Article article)
{

   //Create the SQL Query for inserting an article
   string sqlQuery = String.Format("Insert into Articles (ArticleTitle, ArticleBody ,PublishDate, ArticleCategoryID) Values('{0}', '{1}', '{2}', {3} );"
   + "Select @@Identity",article.Title, article.Body, article.PublishDate.ToString("yyyy-MM-dd"), article.CategoryID);

   //Create and open a connection to SQL Server 
   SqlConnection connection = new SqlConnection(DatabaseHelper.ConnectionString);
   connection.Open();

   //Create a Command object
   SqlCommand command = new SqlCommand(sqlQuery, connection);

   //Execute the command to SQL Server and return the newly created ID
   int newArticleID = Convert.ToInt32((decimal)command.ExecuteScalar());

   //Close and dispose
   command.Dispose();
   connection.Close();
   connection.Dispose();

   // Set return value
   return newArticleID;
}

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:

//Create an article object and populate its properties with your article data
Article newArticle = new Article();

newArticle.Title = txtArticleTitle.Text;
newArticle.Body = txtArticleBody.Text;
newArticle.PublishDate = DateTime.Now;
newArticle.CategoryID = convert.ToInt16(ddlArticleCategories.SelectedValue);

//Create a new Article Manager that allows you to insert a new article to database
ArticlesManager articlesManager = new ArticlesManager();

int newArticleID = articlesManager.InsertArticle(newArticle);

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:

/// <summary>
/// Method created for inserting or updating an article
/// </summary>
/// <param name="article"></param>
/// <returns></returns>
public int SaveArticle(Article article)
{

	//Create the SQL Query for inserting an article
	string createQuery = String.Format("Insert into Articles (ArticleTitle, ArticleBody ,PublishDate, ArticleCategoryID) Values('{0}', '{1}', '{2}', {3} );"
		+ "Select @@Identity",article.Title, article.Body, article.PublishDate.ToString("yyyy-MM-dd"), article.CategoryId);

	//Create the SQL Query for updating an article
	string updateQuery = String.Format("Update Articles SET ArticleTitle='{0}', ArticleBody = '{1}', PublishDate ='{2}', ArticleCategoryID = {3} Where ArticleID = {4};",
		article.Title, article.Body, article.PublishDate.ToString("yyyy-MM-dd"), article.CategoryId, article.ArticleId);

	//Create and open a connection to SQL Server 
	SqlConnection connection = new SqlConnection(DatabaseHelper.ConnectionString);
	connection.Open();

	//Create a Command object
	SqlCommand command = null;

	if (article.ArticleId != 0)
		command = new SqlCommand(updateQuery, connection);
	else
		command = new SqlCommand(createQuery, connection);

	int savedArticleID = 0;
	try
	{
		//Execute the command to SQL Server and return the newly created ID
		var commandResult = command.ExecuteScalar();
		if (commandResult!=null)
		{
			savedArticleID = Convert.ToInt32(commandResult);
		}
		else
		{
			//the update SQL query will not return the primary key but if doesn't throw exception 
			//then we will take it from the already provided data
			savedArticleID = article.ArticleId;
		}
	}
	catch (Exception ex)
	{
		//there was a problem executing the script
	}

	//Close and dispose
	command.Dispose();
	connection.Close();
	connection.Dispose();

	return savedArticleID;
}

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:

/// <summary>
/// Method created for returing an article by Id
/// </summary>
/// <returns></returns>
public Article GetArticleById(int articleId)
{
	Article result = new Article();

	//Create the SQL Query for returning an article category based on its primary key
	string sqlQuery = String.Format("select * from Articles where ArticleID={0}", articleId);

	//Create and open a connection to SQL Server 
	SqlConnection connection = new SqlConnection(DatabaseHelper.ConnectionString);
	connection.Open();

	SqlCommand command = new SqlCommand(sqlQuery, connection);

	SqlDataReader dataReader = command.ExecuteReader();

	//load into the result object the returned row from the database
	if (dataReader.HasRows)
	{
		while (dataReader.Read())
		{
			result.ArticleId = Convert.ToInt32(dataReader["ArticleID"]);
			result.Body = dataReader["ArticleBody"].ToString();
			result.CategoryId = Convert.ToInt32(dataReader["ArticleCategoryID"]);
			result.PublishDate = Convert.ToDateTime(dataReader["PublishDate"]);
			result.Title = dataReader["ArticleTitle"].ToString();
		}
	}

	return result;
}

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.

/// <summary>
/// Method created for returning the articles
/// </summary>
/// <param name="article"></param>
/// <returns></returns>
public List<Article> GetArticles()
{
	
	List<Article> result = new List<Article>();

	//Create the SQL Query for returning all the articles
	string sqlQuery = String.Format("select * from Articles");

	//Create and open a connection to SQL Server 
	SqlConnection connection = new SqlConnection(DatabaseHelper.ConnectionString);
	connection.Open();

	SqlCommand command = new SqlCommand(sqlQuery, connection);

	//Create DataReader for storing the returning table into server memory
	SqlDataReader dataReader = command.ExecuteReader();

	Article article = null;

	//load into the result object the returned row from the database
	if (dataReader.HasRows)
	{
		while (dataReader.Read())
		{
			article = new Article();

			article.ArticleId = Convert.ToInt32(dataReader["ArticleID"]);
			article.Body = dataReader["ArticleBody"].ToString();
			article.CategoryId = Convert.ToInt32(dataReader["ArticleCategoryID"]);
			article.PublishDate = Convert.ToDateTime(dataReader["PublishDate"]);
			article.Title = dataReader["ArticleTitle"].ToString();

			result.Add(article);
		}
	}

	return result;

}

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:

ArticlesManager articlesManager = new ArticlesManager();

rptArticles.DataSource = articlesManager.GetArticles();
rptArticles.DataBind();

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:

/// <summary>
/// Method created for deleting an article 
/// </summary>
/// <param name="ArticleID"></param>
/// <returns></returns>
public bool DeleteArticle(int ArticleID)
{
	bool result = false;

	//Create the SQL Query for deleting an article
	string sqlQuery = String.Format("delete from Articles where ArticleID = {0}",ArticleID);

	//Create and open a connection to SQL Server 
	SqlConnection connection = new SqlConnection(DatabaseHelper.ConnectionString);
	connection.Open();

	//Create a Command object
	SqlCommand command = new SqlCommand(sqlQuery, connection);

	// Execute the command
	int rowsDeletedCount = command.ExecuteNonQuery();
	if (rowsDeletedCount != 0)
		result = true;
	// Close and dispose
	command.Dispose();
	connection.Close();
	connection.Dispose();


	return result;
}

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

    Privacy Preference Center