QueryString through 3-layer architecture
In the ASP.NET
beginners part we learned about how
QueryString transfer data from one page to another page through URL. In this Part we will learn how transfer data using
3-layer architecture by use of QueryString process.
First of all create tables and store procedure in
SQL Server 2008.
--We Create table tblNews
create table tblNews
(
NewsId int identity(1,1) not null primary key,
NewsTitle varchar(300) Not Null,
NewsDisc varchar(500) Null,
NewsDisc_Text text Null,
PostedDate date Not Null constraint tblNews_PostedDate default(getdate()), --get current date/time by use default constraint
ModifyDate date Not Null constraint tblNews_ModifyDate default(getdate()),
IsActive bit Not Null constraint tblNews_IsActive default(0), --set default 0
NewsUrl varchar(500) Null
)
--insert datas into tblNews
Insert into tblNews(NewsTitle,NewsUrl) values('Bollywood','www.web-designing-developing-tutorials.in')
Insert into tblNews(NewsTitle,NewsUrl) values('Bollywood','www.web-designing-developing-tutorials.com')
Insert into tblNews(NewsTitle,NewsDisc,NewsDisc_Text,NewsUrl,IsActive)
values('News Title 3','Lorem Ipsum is simply dummy text of the printing and typesetting industry.....','Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s.', 'www.maashardatechnology.in',1)
--you can similar Insert more rows
We have following data into the table.
 |
| Data for QueryString |
Now create procedure of the table.
--We create procedure for tblNews tables.
create proc sp_tblNews
@NewsId int=0, @Action varchar(50)=null
As
Begin
if(@Action ='SelectAllActiveNews') --select all active news i.e IsActive=1
begin
select NewsId, NewsTitle, NewsDisc, NewsDisc_Text,
CONVERT(varchar(20),PostedDate, 107)As [PostedDate] , --107 is style like June 30, 2017. you can visit for more style
CONVERT(varchar(20),ModifyDate, 107)As ModifyDate, NewsUrl
from tblNews where IsActive=1
order by NewsId desc
end
Else if(@Action='SelectNewsById') --select news by Id
begin
select NewsTitle, CONVERT(varchar(20),PostedDate, 107)As [PostedDate] ,
NewsDisc_Text from tblNews where IsActive=1 and NewsId= @NewsId;
end
End
Open
Asp.net, click on
File menu than select
New:->
Project, Enter project Name as you wish (We enter Blog), Next select location (where you want to save project Name), solution Name automatically set (which is enter in Name).
First of all Open
Server Explorer from
View Menu or press
Ctrl+W+L than Right Click on
Data Connection in server Explorer window, than click on
Add Connection which open Select/change Data Source window than select
Microsoft SQL Server than ok, which again open new window than select
Server name from DropDownlist (if not show Server name than copy server name from SQL Server and paste) than select
database than click Ok.
Step Process
Press Ctrl+W+L :-> Right Click on Data Connection in server Explorer window :-> Add Connection :-> Microsoft SQL Server :-> Ok :-> select Server name :-> select database :-> Ok.
 |
| Make connection in ASP.NET with SQL server |
Now in web Config file write connection string code.
<?xml version="1.0"?>
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0"/>
<customErrors mode="Off"/>
</system.web>
<connectionStrings> <!---Make connection string with database-->
<add name="ConStr" connectionString="Data Source=RKSHARMA\RKSHARMA;Initial Catalog=AspDotNetBasic;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
We have to add file and folder in project as above see in picture
Now add
App_code folder by right click on
blog project than click on
add than click on
Add ASP.NET Folder than click on
App_code folder. Now Add a
Class File by Right Clicking on DAL folder within
App_Code folder to access
database.
Step Process
Right Click on Blog(My project Name) :-> Add :-> Add ASP.NET Folder :-> App_Code :-> Add Folder DAL within App_code :-> Right Click on DAL folder :-> Add :-> Add New Item :-> Class Library File (Class.cs) :-> Name it (DAL.cs) :-> ok.
DAL.cs
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace MaaShardaTechnology.DAL //namespace
{
public class NewsDataAccess
{
SqlCommand cmd;
SqlConnection _sqlCon;
SqlDataAdapter da;
DataSet ds;
SqlConnection SqlCon //properties
{
get { return _sqlCon; }
set { _sqlCon = value; }
}
public NewsDataAccess() //constructor
{
SqlCon = new SqlConnection(); //Object create
CreateConnection(ConfigurationManager.ConnectionStrings["ConStr"].ToString()); //Create connection, "ConStr" is connection String name in web.config file
}
public SqlConnection CreateConnection(string connectionstring) //return type SqlConnection, and pass Connectionstring as parameter
{
SqlCon.ConnectionString = connectionstring;
return this.SqlCon;
}
private void Open_Con()
{
if (SqlCon.State == ConnectionState.Closed) //if connection closed than open connection
SqlCon.Open();
}
private void Close_con()
{
if (SqlCon.State == ConnectionState.Open) //if connection open than close connection
SqlCon.Close();
}
public DataSet GetDataSet(SqlCommand cmd) //Retrun type is Dataset type, and pass sqlcommand as parameter
{
ds = new DataSet();
cmd.CommandType = CommandType.StoredProcedure;
ds.Clear();
cmd.Connection = this.SqlCon;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds;
}
}
}
Similarly add a new
Class File by Right Clicking on
BAL folder within
App_Code folder to process Business access.
Step Process
By right click Add Folder BAL within App_code :-> Right Click on BAL folder :-> Add :-> Add New Item :-> Class Library File (Class.cs) :-> Name it (BAL.cs) :-> ok.
BAL.cs
using System.Data.SqlClient;
using MaaShardaTechnology.DAL; //this namespace for DAL.cs
using System.Data;
namespace MaaShardaTechnology.BAL //namespace
{
public class NewsBAL
{
SqlCommand cmd;
NewsDataAccess OBJNDA;
DataTable dt;
public NewsBAL()
{}
public DataTable GetAllActiveNews() //return type is datatable
{
OBJNDA = new NewsDataAccess();
dt = new DataTable();
cmd = new SqlCommand("sp_tblNews"); //sp_tblNews is store procedure name
cmd.Parameters.AddWithValue("@Action", "SelectAllActiveNews"); //return Sql command from strore procedure where action is SelectAllActiveNews.
dt = OBJNDA.GetDataSet(cmd).Tables[0]; //"GetDataSet(cmd)" call the GetDataSet method from DAL.csand pass sql command as parameter, and store dataset into first table (Tables[0])
return dt;
}
public DataSet GetAllActiveNewsById(string id) //return type is Dataset
{
OBJNDA = new NewsDataAccess();
cmd = new SqlCommand("sp_tblNews");
cmd.Parameters.AddWithValue("@Action", "SelectNewsById");
cmd.Parameters.AddWithValue("@NewsId", id);
return OBJNDA.GetDataSet(cmd);
}
}
}
Now in asp.net get query string process to understand we are adding two pages
News.aspx and
NewsDetails.aspx.
News.aspx page keep brief detail of all news in order to latest news, when click on heading of news or Read More button it move to new page (NewsDetails.aspx) with that news id using query string, where detail about news show.
News.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="News.aspx.cs" Inherits="News" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<meta name="view-port" content="width=device-width, initial-scale=1, maximum-scale=1" />
<link href="Style/StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<asp:Label ID="lblMsg" runat="server"></asp:Label>
<asp:UpdatePanel ID="UP1" runat="server">
<ContentTemplate>
<asp:DataGrid ID="DataGrid1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:TemplateColumn>
<ItemTemplate>
<div class="newdiv">
<h2 class="heading"><a href='NewsDetails.aspx?id=<%# Eval("NewsId") %>'><%# Eval("NewsTitle") %></a></h2> <!--bind newsid with a tag, and than bind for dispaly news title. when click on news title move to NewsDetail page with id-->
<span class="date"><%# Eval("PostedDate") %></span> <!--Eval bind the PostedDate column -->
<div class="para"><%# Eval("NewsDisc") %></div>
<span class="readmore"><a id="lnkbtnReadMore" href='NewsDetails.aspx?id=<%# Eval("NewsId") %>'>Read More..</a></span>
</div>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
</body>
</html>
Now write the code to bind the all the news brief detail in code behind of
News.aspx.
News.aspx.cs
using System;
using System.Data;
using MaaShardaTechnology.BAL; //this namespace for BAL.cs
public partial class News : System.Web.UI.Page
{
NewsBAL ObjNBal;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindNews(); //call News Method
}
}
private void BindNews()
{
DataTable dt = new DataTable();
ObjNBal = new NewsBAL(); //object of the NewsBAL class from BAL.cs
dt = ObjNBal.GetAllActiveNews(); //call GetAllActiveNews() method from NewsBAL class
if (dt.Rows.Count > 0) //check condition number of row in table greater than 0
{
DataGrid1.DataSource = dt;
DataGrid1.DataBind(); //bind datatable in DataGrid
}
else
{
lblMsg.Text = "There is no data in rows";
}
}
}
Create a page to receive the data from
News.aspx
NewsDetails.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="NewsDetails.aspx.cs" Inherits="NewsDetails" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<meta name="view-port" content="width=device-width, initial-scale=1, maximum-scale=1" />
<link href="Style/StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<div class="newdiv">
<h2 class="heading"><a><asp:Label ID="lbltitle" runat="server"></asp:Label></a></h2>
<span class="date"> Posted On:-<asp:Label ID="lbldate" runat="server" ></asp:Label><br /></span>
<div class="para"><asp:Label ID="lblMsg" runat="server" ></asp:Label></div>
</div>
</div>
</form>
</body>
</html>
Now write the some code in
code behind of NewsDeatails.aspx in query string asp net c# to receive the news id after click on heading or Read More button to show detail about news.
NewsDetails.aspx.cs
using System;
using MaaShardaTechnology.BAL; //namespace for BAL.cs
using System.Data;
public partial class NewsDetails : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindNewsDetail();
}
}
private void BindNewsDetail()
{
NewsBAL OBJbal = new NewsBAL();
DataTable dt = new DataTable();
string strId = Request.QueryString["id"]; //receive requested QueryString Id, id is here string type because recieve from query
if (strId != null) //if id is not null
{
DataSet ds = new DataSet();
ds = OBJbal.GetAllActiveNewsById(strId); //call GetAllActiveNewsById() method from NewsBAL class and pass id as parameter
if (ds.Tables.Count > 0) //dataset is collection of table , and table is > 0
{
dt = ds.Tables[0];
if (dt.Rows.Count > 0) //Datatable is collection of row , row is > 0
{
lbltitle.Text = dt.Rows[0][0].ToString(); //[0][0] indicate first row first column of table
lbldate.Text = dt.Rows[0][1].ToString(); //[0][1] indicate first row second column of table
lblMsg.Text = dt.Rows[0][2].ToString();
}
}
}
else
{
Response.Redirect("NewsDetails.aspx?id=1");
}
}
}
We are adding
StyleSheet.css in style folder to set style of the pages.
Step Process
Right Click on Blog(My project Name) :-> Add :-> NET Folder :-> Name it (Style) :-> Right Click on Style folder :-> Add :-> Add New Item :-> StyleSheet.css :-> ok.
StyleSheet.css
body {
}
.container {
width: 1170px;
margin: 0 auto;
border: 1px solid #186433;
}
#DataGrid1 {
border: 2px solid black;
width: 1000px;
margin: 10px auto;
}
.newdiv {
width: 900px;
background: #186433;
margin: 55px auto;
border: 2px solid red;
box-shadow: 2px 2px 6px 6px #ccc;
}
h2.heading a {
font-family: Arial, Helvetica, sans-serif;
font-size: 20pt;
font-style: normal;
color: Red;
text-decoration: none;
padding: 0 2%;
}
.date {
color: black;
font-size: 18px;
padding: 0 2%;
}
.para {
color: #ffffff;
font-size: 11pt;
font-family: Arial, Helvetica, sans-serif;
padding: 2% 2%;
margin: 0 0 55px 0;
}
.readmore {
background-color: rgba(24,10,51,0.73);
float: right;
margin: 0px 30px;
padding: 9px 14px;
position: relative;
bottom: 46px;
}
.readmore a {
color: #fff;
text-decoration: none;
}
.readmore:hover {
background: #fff;
color: rgba(24,10,51,0.73);
}
.readmore:hover a {
color: rgba(24,10,51,0.73);
}
@media only screen and (min-width:970px) and (max-width:1169px) {
.container {
width: 970px;
}
#DataGrid1 {
width: 800px;
}
.newdiv {
width: 700px;
}
h2.heading a {
font-size: 18pt;
}
.readmore {
margin: 0px 15px;
}
}
@media only screen and (min-width:750px) and (max-width:969px) {
.container {
width: 750px;
}
#DataGrid1 {
width: 580px;
}
.newdiv {
width: 480px;
}
h2.heading a {
font-size: 18pt;
}
.readmore {
margin: 0px 13px;
}
}
@media only screen and (min-width:450px) and (max-width:749px) {
.container {
width: 450px;
}
#DataGrid1 {
width: 280px;
}
.newdiv {
width: 180px;
}
h2.heading a {
font-size: 16pt;
}
.para {
font-size: 11pt;
margin: 0 0 50px 0;
}
.readmore {
margin: 0 35px;
}
}
@media only screen and (max-width:449px) {
.container {
width: 269px;
}
#DataGrid1 {
width: 100px;
}
.newdiv {
width: 100px;
}
h2.heading a {
font-size: 16pt;
}
.para {
font-size: 9pt;
}
.readmore {
margin: 0 6%;
}
.readmore a {
font-size: 12px;
}
}
Now build the project by right click on project or Shift+F6 than run the project by pressing F5.
Now click on any Read more button or any title which move to next page with id of the news and show all detail of the news.
