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 MasterCREATE 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 listpublic 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
