If you are ASP.Net beginner, it will slightly difficult to understand of a basic insert, update, delete & retrieve operation. You can search some tutorials over the Internet, but, most of them have used the entity framework’s code first or database first approach. But this article will follow a simple step that will allow us to learn & perform CRUD operations in ASP.Net using ADO.Net and C#. Here we will use normal ADO.Net syntax to implement all basic insert, update, delete & retrieve operation in ASP.Net. We will not use any stored procedure for now as it will become easy for beginners. Once you will able to know these fundamental concepts, you can use the stored procedure too.

ADO.NET Classes:

Before we start our coding, we must know about some common ADO.NET objects those are responsible for the CURD  operations explained in this tutorial:

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

Creating Table

CREATE TABLE [dbo].[Article]
(
    [ArticleId] INT NOT NULL PRIMARY KEY,
    [CategoryId] INT NOT NULL,
    [Title] VARCHAR(2000) NOT NULL,
    [Body] NVARCHAR(MAX) NULL,
    [PublishDate] DATETIME NULL
)

Mapping Classes for Database Data

The creation of the mapping class is pretty simple before we perform 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 an application level. We have taken Article class as an example and following are the class.

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; }
    }

Creating Insert, Update, Get, and Delete method:

Let’s create our all the method which allow us to perform an operation on records in our table. We are using simple ADO.Net basics to create this. Our Class name is ArticleData, and following are the sample code of this class. We have a private member ConnectionString which will be assigned by connectionstring is defined in web.config using constructor. The InsertArticle method uses an object of Article class, perform an insert operation in the table and return the ArticleId, which will be created. Our SaveArticle will take an object of Article as a parameter and perform the update operation in the database. DeleteArticle will take ArticleId as a parameter and perform the delete operation. GetArticles method will return all the list of articles and GetArticleById will return any article by ArticleId from the database. Here are the sample class.

public class ArticleData
    {
        private string ConnectionString = string.Empty;
 
        public ArticleData()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }
 
        public int InsertArticle(Article article)
        {
 
            //Create the SQL Query for inserting an article
            string sqlQuery = String.Format("Insert into Article (Title, Body ,PublishDate, CategoryID) 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(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;
        }
 
        public int SaveArticle(Article article)
        {
 
            //Create the SQL Query for inserting an article
            string createQuery = String.Format("Insert into Article (Title, Body ,PublishDate, CategoryID) 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 Article SET Title='{0}', Body = '{1}', PublishDate ='{2}', CategoryID = {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(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;
        }
 
 
 
        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 Article where ArticleID={0}", articleId);
 
            //Create and open a connection to SQL Server
            SqlConnection connection = new SqlConnection(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["Body"].ToString();
                    result.CategoryId = Convert.ToInt32(dataReader["CategoryID"]);
                    result.PublishDate = Convert.ToDateTime(dataReader["PublishDate"]);
                    result.Title = dataReader["Title"].ToString();
                }
            }
 
            return result;
        }
 
        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 Article");
 
            //Create and open a connection to SQL Server
            SqlConnection connection = new SqlConnection(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["Body"].ToString();
                    article.CategoryId = Convert.ToInt32(dataReader["CategoryID"]);
                    article.PublishDate = Convert.ToDateTime(dataReader["PublishDate"]);
                    article.Title = dataReader["Title"].ToString();
 
                    result.Add(article);
                }
            }
 
            return result;
 
        }
 
 
        public bool DeleteArticle(int ArticleID)
        {
            bool result = false;
 
            //Create the SQL Query for deleting an article
            string sqlQuery = String.Format("delete from Article where ArticleID = {0}", ArticleID);
 
            //Create and open a connection to SQL Server
            SqlConnection connection = new SqlConnection(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;
        }
 
    }

You can use above methods to perform CRUD operation by creating object in ArticleData Class and calling its methods. This class can be used as DataAccessLayer for small applications. Following is a sample code that will be used to perform insert operation in database.

var objArticle = new Article();
objArticle.CategoryId = 1;
objArticle.Title = “Sample Title”;
objArticle.Body = “Sample Article body here”;
objArticle.PublishDate = DateTime.Now;
ArticleData objArticleData = new ArticleData();
objArticleData.InsertArticle(objArticle);

Summary: This article will be very useful for beginners, I am not including the other Asp.Net Code here. This article has only the DataAccessLayer Part, which is usually the main problem for ASP.net beginners when they started to perform CRUD operation using ADO.Net. I hope this article will be very useful for you.