For ASP.Net beginner, it will slightly difficult to understanding of basic insert, update, delete & get operation. You can find several tutorials over internet, but they many of them have used entity framework’s code first or database first approach. But this article will follow a simple step that will allow us to understand & perform CRUD operations in ASP.Net using ADO.Net and C#. We will use normal ADO.Net syntax to perform all basic insert, update, delete & get operation in ASP.Net. We will not use any stored procedure for now as it will become more easy for beginners. Once you will able to understand these basic concepts, you can use stored procedure too.
Basics of ADO.Net Classes
Before we start coding, let me 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 Table for CRUD operation using ADO.Net
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 very simple, 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. 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 operation on records in our table. We are using simple ADO.Net basics to create this. Our Class name will be ArticleData, and following are the sample code of this class. We have a private member ConnectionString which will be assigned by connectionstring defined in web.config using constructor. The InsertArticle method takes object of Article class, perform insert operation in table and return the ArticleId, which will be created. Our SaveArticle will take object of Article as parameter and perform the update operation in database. DeleteArticle will take ArticleId as parameter and perform delete operation. GetArticles method will return all the list of articles and GetArticleById will return any article by ArticleId from 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.