Before moving to creation of database and application you must know database creation and programming skills. 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.
No comments:
Post a Comment