Click here to Skip to main content
Click here to Skip to main content

Insert, Update, Search and Delete (CRUD operation) using ASP.Net and MySQL

, 17 Aug 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Create, Read, Update and Delete operations in ASP.Net with MySQL database with N-Tier Architecture

Introduction

This article is intended for understanding of Insert, Update, Search and Delete operation in ASP.Net with MySQL as database. So many novices having problems with this basic operations. I am going to use stored procedure for all database operations. Here we will take simple Country example for better understanding. This operation is similar for most of the application. There are so many ORM available that performs the similar task. But I am not going to cover ORM stuff. Check MySQL Database backup/Restore using GUI Tool. You also need to change ConnectionString from Web.Config according to your database settings.

Background

- Visual Studio /Visual Web developer (it would be nice if you have latest version)
- Dot Net Framework (it would be nice if you have latest version)
- MySQL + MySQL GUI Tool (it will be easy for you if you have MySQL GUI tool)
- MySQL .NET Connector
- You should have basic knowledge of SQL, C# and ASP.NET

Using the code  

Hope you already have database knowledge. Creating tables, Stored procedures and writing queries. Let’s start with creating Country table and related stored procedures.

Creating Database Table for Country Master
CREATE TABLE `tbl_countrymaster` (`NM_COUNTRY_ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'UNIQUE NO TO IDENTIFY COUNTRY',`VC_COUNTRY_NAME` varchar(45) NOT NULL COMMENT 'NAME OF THE COUNTRY',PRIMARY KEY (`NM_COUNTRY_ID`)); 

Creating Database table for Error Log

CREATE TABLE `tbl_error_log` (`dt_error` datetime NOT NULL,`vc_error` longtext,`vc_error_stack` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Stored Procedure for Add Country

CREATE PROCEDURE `InsertCountry`(
IN     _CountryName                       varchar(200),
OUT    _ReturnValue                       int
)
BEGIN
 
DECLARE IsCountryExist int(5);
 
SELECT COUNT(NM_COUNTRY_ID) INTO IsCountryExist FROM
       TBL_COUNTRYMASTER WHERE VC_COUNTRY_NAME = _CountryName;
 
START TRANSACTION;
 
IF IsCountryExist = 0 THEN
   INSERT INTO TBL_COUNTRYMASTER(VC_COUNTRY_NAME)
          VALUES (_CountryName);
 
END IF;
 
SELECT LAST_INSERT_ID() INTO _ReturnValue;
 
COMMIT;
END   

Stored procedure for Get All Country

CREATE PROCEDURE `getAllCountry`()
BEGIN
  SELECT NM_COUNTRY_ID,
         VC_COUNTRY_NAME
         FROM
         TBL_COUNTRYMASTER
         ORDER BY
         VC_COUNTRY_NAME;
END   

Stored procedure for Delete Country

CREATE PROCEDURE `DeleteCountry`(
IN     _CountryID                         int,
OUT    _ReturnValue                       int
)
BEGIN
 
Declare referenceCount int;
 
SELECT COUNT(NM_STATE_ID) INTO referenceCount
       FROM
       TBL_STATEMASTER
       WHERE NM_COUNTRY_ID = _CountryID;
 
IF referenceCount=0 THEN
   DELETE FROM TBL_COUNTRYMASTER
       WHERE NM_COUNTRY_ID = _CountryID;
 
   set _ReturnValue=1;
ELSE
   set _ReturnValue=-2;
END IF;
 
END 

Stored procedure to select country by Name

CREATE PROCEDURE `SelectCountryByName`(
IN     _CountryName                             varchar(200)
)
BEGIN
 
SELECT NM_COUNTRY_ID,
       VC_COUNTRY_NAME
       FROM
       TBL_COUNTRYMASTER
       WHERE
       VC_COUNTRY_NAME like CONCAT('%',_CountryName,'%')
       ORDER BY
       VC_COUNTRY_NAME;
 
END

Stored procedure to Update Country Name

CREATE PROCEDURE `UpdateCountryName`(
IN     _CountryName                             varchar(200),
IN     _CountryID                               int
)
BEGIN
 
UPDATE TBL_COUNTRYMASTER SET VC_COUNTRY_NAME = _CountryName WHERE NM_COUNTRY_ID = _CountryID;
 
END

Stored procedure to Insert Log

CREATE PROCEDURE `InsertLog`(
IN     _Datetime                       datetime,
IN     _ErrorMessage                   varchar(1000),
IN     _ErrorStack                     varchar(5000),
OUT    _ReturnValue                       int
)
BEGIN
INSERT INTO TBL_ERROR_LOG(dt_error, vc_error, vc_error_stack)
VALUES (_Datetime,_ErrorMessage,_ErrorStack);
SET _ReturnValue = 0;
END

'C' stand for Create

Let's Start .NET Stuff

Now we have Database ready to do all Operations. You can create simple web application without any code separation like DataAccess Layer or Business Layer. But here i am going to follow 3-tier Architecture. This will also help you to understand how data flows in 3-tier architecture. You can also called it N-Tier architecture. Here we can add more layers for Logging or Licensing. But we are not going to cover Licensing part in this article. We will see overview of Noggin. Let's start by creating Projects like UI (User interface, ASP.NET application), DAL (Data Access Layer, mostly it contains SQLHelper), BAL (Business Access Layer), PROP(Properties) and finally Logging. I prefer this structure and naming convention. you can also use other Way/Naming convention for your project. DAL, BAL, PROP and Logging are the Class projects. We can also create one more layer of Properties. We can use that class for passing information from one layer to another.

PROP : Country property list
public class PROPCountry
{
    public int CountryID { get; set; }
    public string CountryName { get; set; }

    public PROPCountry()
    {

    }

    public PROPCountry(int countryID, string countryName)
    {
        this.CountryID = countryID;
        this.CountryName = countryName;
    }        
}

Above class can be used for the communication between each Tier. when we need to pass all the information for particular class, we used to pass it as an object by filling all the properties.

Let Create User Interface

Here we are not going to cover development of attractive User Interface. We will see how CRUD operations can be performed in ASP.NET. So forgive me for not good UI. Code project have many articles to improve User Interface.

UI Page : CountryMaster.aspx

<div>Create Country</div>
<span style="height:20px; vertical-align:top">Country Name :</span> 
<asp:TextBox ID="txtCountryName" runat="server"></asp:TextBox>
<asp:Button ID="btnCreate" runat="server" Text="Create" onclick="btnCreate_Click" />

We will get txtCountryName information and pass it to the Business layer for Creating Country.

CodeBehind : CountryMaster.aspx.cs btnCreate Click Event

protected void btnCreate_Click(object sender, EventArgs e)
{
BALCountry balCountry = new BALCountry();
            
            try
            {
                throw new ArgumentException();
                int returnValue = balCountry.CreateCountry(txtCountryName.Text);
                if (returnValue > 0)
                {
                    Page.RegisterClientScriptBlock("message", "<script>alert('Country is created successfully')</script>");
                    txtCountryName.Text = string.Empty;
                }
                else
                {
                    Page.RegisterClientScriptBlock("message", "<script>alert('Incorrect User Inputs.')</script>");
            }   }
            catch (Exception ex)
            {
                clsLogging logError = new clsLogging();
                logError.WriteLog(ex);
            }
}   

BAL : BALCountry.cs CreateCountry function

public int CreateCountry(string countryName)
{
   if (string.IsNullOrEmpty(countryName))
   {
       return -1;
   }
   else
   {
        DALCountry dalCountry = new DALCountry();
        return dalCountry.CreateCountry(countryName);
   }            
} 

DAL : DALCountry.cs CreateCountry function

public int CreateCountry(string countryName)
{            
    SQLHelper sqlHelper = new SQLHelper();
    List<MySqlParameter> parameters = new List<MySqlParameter>();
    parameters.Add(new MySqlParameter("_CountryName", countryName));
 
    return sqlHelper.executeSP<int>(parameters, "InsertCountry");
}

As you can see all above code Button click event call BAL function with CountryName as parameter and then BAL will do some validation part. If validation is correct then it will call DAL function and insert Data through our Stored Procedure.

Hope you have clear idea about how 3-Tier architecture works. Above all code will do Create operation for the Country. We have not covered Logging code yet. Let's see logging.

Logging : clsLogging.cs WriteLog function

public void WriteLog(Exception ex)
{
    SQLHelper sqlHelper = new SQLHelper();
    List<MySqlParameter> parameters = new List<MySqlParameter>();
    parameters.Add(new MySqlParameter("_DateTime", DateTime.Now));
    parameters.Add(new MySqlParameter("_ErrorMessage", ex.Message));
    parameters.Add(new MySqlParameter("_ErrorStack", ex.StackTrace));

    sqlHelper.executeSP<int>(parameters, "InsertLog");
}  

All above code gives you basic idea about performing Create(Insert) database operation with N-Tier architecture. for all other operations like Read, Update and Delete we will see code step by step. And for all further topics are are not going to discuss all basic steps in detail instead will go though it very quickly by looking at the code.

'R' stand for Read

User Interface

<div style="font-weight:bold">Search Country</div>
<span style="height:20px; vertical-align:top">Country Name :</span>
<asp:TextBox ID="txtSearchName" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" onclick="btnSearch_Click" />
<asp:GridView ID="gvCountryList" runat="server">
</asp:GridView> 

Code behind file for Search button click event and all other related code

protected void btnSearch_Click(object sender, EventArgs e)
{
    binding(txtSearchName.Text);
} 

private void binding(string searchCountry)
{
    try
    {
        BALCountry balCountry = new BALCountry();
        gvCountryList.DataSource = balCountry.getCoutry(searchCountry);
        gvCountryList.DataBind();                
    }
    catch (Exception ex)
    {
        clsLogging logError = new clsLogging();
        logError.WriteLog(ex);                                                 
    }            
} 

As you can see in above code we have used binding function for binding our datagrid to display search result. we have also implemented search parameter so user can search for relevant country name. binding function is created to used for multipurpose. it can be used for get particular search result as well as it can get all the country list by passing null parameter.

Let's create getCountry function in BAL and DAL

BAL function

public List<PROPCountry> getCoutry(string searchCountry)
{
    DALCountry dalCountry = new DALCountry();

    if (string.IsNullOrEmpty(searchCountry))
    {
        return dalCountry.getAllCountry();
    }
    else
    {
        return dalCountry.getCountry(searchCountry);
    }
}

In above code we are getting List<PROPCountry> that can be directly bound with datagrid. Here you can see if we have Null searchCountry variable then it will call getAllCountry function otherwise it will call getCountry for search pattern. Now we will see how above both described funciton implemented in DAL layer.

DAL functions

public List<PROPCountry> getAllCountry()
{
    List<PROPCountry> countryList = new List<PROPCountry>();
    SQLHelper sqlHelper = new SQLHelper();
    List<MySqlParameter> parameters = new List<MySqlParameter>();
    var resultSet = sqlHelper.executeSP<DataSet>(parameters, "getAllCountry");


    PROPCountry country;
    foreach (DataRow drow in resultSet.Tables[0].Rows)
    {
        country = new PROPCountry(Convert.ToInt32(drow[0].ToString()) , drow[1].ToString());
        countryList.Add(country);
    }

    return countryList;
}

public List<PROPCountry> getCountry(string searchCountry)
{
    List<PROPCountry> countryList = new List<PROPCountry>();
    SQLHelper sqlHelper = new SQLHelper();
    List<MySqlParameter> parameters = new List<MySqlParameter>();
    parameters.Add(new MySqlParameter("_CountryName", searchCountry));
    var resultSet = sqlHelper.executeSP<DataSet>(parameters, "SelectCountryByName");

    PROPCountry country;
    foreach (DataRow drow in resultSet.Tables[0].Rows)
    {
        country = new PROPCountry(Convert.ToInt32(drow[0].ToString()), drow[1].ToString());
        countryList.Add(country);
    }

    return countryList;
} 

As you can see in above both methods the only difference is the name of the Stored procedure and parameter we are passing. we can also combine both the stored procedure to work for different function. but here for understanding we have made those changes in C# side. We have also used constructor of PROPCountry to assing CountryId and CountryName property value. In both the function we are iterating though the result and generating List<PROPCountry> list and pass it to BAL layer.

This is how we can do the read operation on the database. we have covered normal reading the data as well as searching mechanism in a single functionality.

'U' stand for Update/Edit

Update operation is most critical operation. if we are doing it incorrectly that cause incorrect result in database. we need to choose correct update query for modifying particular data. Country example is very easy and it will not look like complicated as i have just explained but when we have Bill with subitems and updating subitems, it become more complicated. we will not cover those complex things in this article. Scope of this article is just to understand how basic functions can be performed.

User Interface

<div style="font-weight:bold">Edit Country</div>
<span style="height:20px; vertical-align:top">Country ID :</span>
<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="True" onselectedindexchanged="ddlCountry_SelectedIndexChanged">    
</asp:DropDownList>        
<br />
<span style="height:20px; vertical-align:top">Country Name :</span>
<asp:TextBox ID="txtEditCountryName" runat="server"></asp:TextBox>
<asp:Button ID="btnEditCountryName" runat="server" Text="UpdateCountry" onclick="btnEditCountryName_Click" /> 

We have used DropDown list to select particular record for Update. we can also add Update link in gridview and do the similar operation. For simplicity on change event of DropDownList we will make our record in Update mode. once btnEditCountryName is clicked country will be updated. As you can see we have changed AutoPostBack="True" to onSelectedIndexChanged work otherwise event will not fired.

Code behind for DropdownList and Button events

protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
    string countryName = string.Empty;
    BALCountry balCountry = new BALCountry();
    try
    {
        countryName = balCountry.getCountryByID(ddlCountry.SelectedValue);
        if (string.IsNullOrEmpty(countryName))
        {
            Page.RegisterClientScriptBlock("message", "<script>alert('Country Id is not found.')</script>");
        }
        else
        {
            txtEditCountryName.Text = countryName;
        }
    }
    catch (Exception ex)
    {
        clsLogging logError = new clsLogging();
        logError.WriteLog(ex);
    }   
}

When user will change dropDownList it will get particular country record from the database and fill txtEditCountryName textbox. we can directly get it from drowDownList but for understanding we have done this. Here we have only one value to set but when we have Customer information we need to get Customer class and all it's properties from the database. So above functionality will give you idea about how we can fetch data from CountryID.

protected void btnEditCountryName_Click(object sender, EventArgs e)
{
    try
    {
        PROP.PROPCountry country = new PROP.PROPCountry();
        country.CountryID = Convert.ToInt16(ddlCountry.SelectedValue);
        country.CountryName = txtEditCountryName.Text;

        BALCountry balCountry = new BALCountry();
        bool result = balCountry.updateCountry(country);

        if (!result)
        {
            Page.RegisterClientScriptBlock("message", "<script>alert('Invalid Inputs for update.')</script>");
        }
        else
        {
            Page.RegisterClientScriptBlock("message", "<script>alert('Country is updated successfully.')</script>");
            binding(null);
            ddlCountry.SelectedIndex = 0;
            txtEditCountryName.Text = string.Empty;
        }
    }
    catch (Exception ex)
    {
        clsLogging logError = new clsLogging();
        logError.WriteLog(ex);                                                 
    }
}

When user modify information from txtEditCountryName textbox and we click the Edit buttion it will update information in the database. we have made some validation in BAL for not allowing Empty countryname. updateCountry will return false if we are passing Empty countryName.

BAL function for update operation

public string getCountryByID(string stringCountryID)
{
    int countryID;
    DALCountry dalCountry = new DALCountry();
    int.TryParse(stringCountryID, out countryID);
    if (countryID == 0)
    {
        return string.Empty;
    }
    else
    {
        return dalCountry.GetCountryById(countryID);
    }
}

public bool updateCountry(PROPCountry country)
{
    if (string.IsNullOrEmpty(country.CountryName) || country.CountryID <= 0)
    {
        return false;
    }
    else
    {
        DALCountry dalCountry = new DALCountry();
        dalCountry.UpdateCountry(country);
        return true;
    }
} 

getCountryById gives the name of the country from it's CountryID. updateCountry will do validation and pass the filtered data in DAL for further update operation.

DAL functions for calling Stored procedure

public string GetCountryById(int countryID)
{
    SQLHelper sqlHelper = new SQLHelper();
    List<MySqlParameter> lstParameter = new List<MySqlParameter>();
    lstParameter.Add(new MySqlParameter("_CountryID", countryID));
    return sqlHelper.executeScaler(lstParameter, "SelectCountryByID");                
}

public void UpdateCountry(PROPCountry country)
{
    SQLHelper sqlHelper = new SQLHelper();
    List<MySqlParameter> lstParameter = new List<MySqlParameter>();
    lstParameter.Add(new MySqlParameter("_CountryID", country.CountryID));
    lstParameter.Add(new MySqlParameter("_CountryName", country.CountryName));
    sqlHelper.executenonquery(lstParameter, "UpdateCountryName");
} 

Here in DAL we have UpdateCountry function for updation on the database for given Country. We can also add validation from stored procedure. We can also add checking if database is updated and depending on the result we can return result instead of void type. Hope all other code looks very similar for you to undestand.

'D' stand for Delete

Delete operation should be done using identity field in where clause from the query. we need to make sure the field involved in delete operation shold be identity/GUID. Delete operation is also as critical as Update operation. we need to make extra attention when deleting any information. Here in given example we will get input as CountryID from the user and will give confirmation of the delete to the user.

User Interface

<div style="font-weight:bold">Delete Country By ID</div>
<span style="height:20px; vertical-align:top">Country ID :</span>
<asp:TextBox ID="txtDeleteID" runat="server"></asp:TextBox>
<asp:Button ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click" /> 

On Delete button click event we can write delete code and check the status from the database. Delete operation can be done by the delete link from the gridView or more other ways.

Code behind for Delete button click event

protected void btnDelete_Click(object sender, EventArgs e)
{
    BALCountry balCountry = new BALCountry();
    try
    {
        int returnValue = balCountry.deleteCountry(txtDeleteID.Text);
        if (returnValue == 0)
        {
            Page.RegisterClientScriptBlock("message", "<script>alert('Incorrect Country Id')</script>");
        }
        else if(returnValue == -2)
        {
            Page.RegisterClientScriptBlock("message", "<script>alert('Country ID could not found.')</script>");
        }
        else if (returnValue == 1)
        {
            Page.RegisterClientScriptBlock("message", "<script>alert('Country is deleted successfully.')</script>");
            binding(null);
            txtDeleteID.Text = string.Empty;
        }
        else
        {
            Page.RegisterClientScriptBlock("message", "<script>alert('Unspecified error.')</script>");
        }
    }
    catch (Exception ex)
    {
        clsLogging logError = new clsLogging();
        logError.WriteLog(ex);                
    }
}

As you can see in above button click event. we have different returnValue and it's corresponding messages. this all status information are comming from Stored procedure or from the Business layer. most of the validation should be done from the either business layer or from the stored procedure. we have seperated validation layer to identify all the validation from one place.

Let's see BAL implementation for the Delete operation

public int deleteCountry(string stringCountryID)
{
    int countryID;
    DALCountry dalCountry = new DALCountry();
    int.TryParse(stringCountryID, out countryID);

    if (countryID == 0)
    {
        return 0;
    }
    else
    {
        return dalCountry.DeleteCountry(countryID);
    }
}


Here we are validating CountryId information. We need to validate this for int type because user can enter any kind of information from the user interface. we can add validation from user interface for numeric value but that can be bypassed by hacker. So we made extra precaution for delete operation.

DAL implemetation for Delete operation

public int DeleteCountry(int countryID)
{
    SQLHelper sqlHelper = new SQLHelper();
    List<MySqlParameter> lstParameter = new List<MySqlParameter>();
    lstParameter.Add(new MySqlParameter("_CountryID", countryID));
    return sqlHelper.executeSP<int>(lstParameter, "DeleteCountry");                
}  

DAL implementation for delete operation is as simple as we have done for other operations.

Final Words 

This is all about CRUD operations and N-Tier architecture. In above all code i have placed small functions for your understanding. please find attached code and database script for properly execution. hope you all enjoy reading this article. There are some interesting books you may want to read like, 

Beginning ASP.NET 4.0

C# 4.0 Nutshell  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

@AmitGajjar
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberjvelluet21-Aug-12 21:52 
GeneralRe: My vote of 5 Pinmember@amitgajjar21-Aug-12 22:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 17 Aug 2012
Article Copyright 2012 by @AmitGajjar
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid