BAL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;
namespace BAL
{
public class DataAcess
{
Connection objcon = new Connection();
public object SelectCategory()
{
return objcon.SelectCategory();
}
public DataTable getdata()
{
return objcon.getdata();
}
public DataTable getdata1(string name)
{
return objcon.getdata1(name);
}
public DataTable getdata2(string sal)
{
return objcon.getdata2(sal);
}
public DataTable getdata3(string fromdate,string todate)
{
return objcon.getdata3(fromdate,todate);
}
}
}
DAL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
public class Connection
{
SqlConnection con = new SqlConnection("Data Source=TUSHAR-PC;Initial Catalog=mm;Integrated Security=True");
// private string conn = ConfigurationManager.ConnectionStrings["Mythreetier"].ToString();
public object SelectCategory()
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT emplyee.*, deparment.*, salry.* FROM deparment INNER JOIN emplyee ON deparment.DId = emplyee.EmpID INNER JOIN salry ON deparment.DId = salry.salID", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
public DataTable getdata()
{
con.Open();
SqlCommand cmd = new SqlCommand("sp_getdata", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
public DataTable getdata1(string name)
{
SqlCommand cmd = new SqlCommand("GET_NAME", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", name);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
{
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
public DataTable getdata2(string sal)
{
SqlCommand cmd = new SqlCommand("GET_SAL", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sal", sal);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
{
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
public DataTable getdata3(string fromdate, string todate)
{
SqlCommand cmd = new SqlCommand("GET_DATE", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fromdate", fromdate);
cmd.Parameters.AddWithValue("@todate", todate);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
{
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
}
}
asp.net
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1._Default" %>
c#
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using BAL;
using DAL;
using System.Data.SqlClient;
using System.Drawing;
namespace WebApplication1
{
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=TUSHAR-PC;Initial Catalog=mm;Integrated Security=True");
DataAcess objbal = new DataAcess();
protected void Page_Load(object sender, EventArgs e)
{
DataAcess objbal = new DataAcess();
if (!Page.IsPostBack)
{
// GridView1.DataSource = objbal.SelectCategory();
//GridView1.DataBind();
GridView1.DataSource = objbal.getdata();
GridView1.DataBind();
}
}
protected void Filter_Click(object sender, EventArgs e)
{
con.Open();
string Query = string.Empty;
// try
// {
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (selection.SelectedValue.ToString() == "Name")
{
// Query = "SELECT emplyee.*, deparment.*, salry.* FROM emplyee INNER JOIN deparment ON emplyee.EmpID = deparment.DId INNER JOIN salry ON deparment.DId = salry.salID where emplyee.name Like '" + Des.Text + "%'";
DataTable dt = objbal.getdata1(Des.Text);
GridView1.DataSource = dt;
GridView1.DataBind();
// }
}
else if (selection.SelectedValue.ToString() == "salary")
{
// Query = "SELECT emplyee.*, deparment.*, salry.* FROM emplyee INNER JOIN deparment ON emplyee.EmpID = deparment.DId INNER JOIN salry ON deparment.DId = salry.salID where salry.salary Like '" + Des.Text + "%'";
DataTable dt = objbal.getdata2(Des.Text);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
// SqlDataAdapter sqlDa = new SqlDataAdapter(Query, con);
//DataTable dt = new DataTable();
//sqlDa.Fill(dt);
//GridView1.DataSource = dt;
//GridView1.DataBind();
//}
//catch (Exception ex)
//{
// HttpContext.Current.Response.Write("" + ex.Message);
//}
//finally
//{
// con.Close();
//}
// Des.Text = "";
// selection.Text = "";
//}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
int quantity = int.Parse(e.Row.Cells[6].Text);
foreach (TableCell cell in e.Row.Cells)
{
if (quantity == 0)
{
cell.BackColor = Color.Red;
}
if (quantity > 0 && quantity <= 50)
{
cell.BackColor = Color.Yellow;
}
if (quantity > 1500 && quantity <= 2000)
{
cell.BackColor = Color.Green;
}
}
{
//This is a Test Condition
if (e.Row.RowIndex % 2 == 0)
{
GridViewRow g = new GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal);
for (int i = 0; i < e.Row.Cells.Count; i++)
{
//creating table Cells
TableCell tCell = new TableCell();
tCell.Text = " ";
//Add Cells
g.Cells.Add(tCell);
//Set Color for Empty Cell
g.BackColor = System.Drawing.Color.White;
}
Table tbl = e.Row.Parent as Table;
//add rows
tbl.Rows.Add(g);
}
}
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
Calendar1.Visible = false;
Calendar2.Visible = false;
con.Open();
string Query = string.Empty;
/// try
//{
//if (con.State == ConnectionState.Closed)
//{
// con.Open();
//}
if ((txtfromdate.Text != "") && (txttodate.Text != ""))
{
// Query = " SELECT emplyee.*, deparment.*, salry.* FROM emplyee INNER JOIN deparment ON emplyee.EmpID = deparment.DId INNER JOIN salry ON deparment.DId = salry.salID where emplyee.jod Between convert(date,'" + txtfromdate.Text + "',105) AND convert(date,'" + txttodate.Text + "',105)";
DataTable dt = objbal.getdata3(txtfromdate.Text,txttodate.Text);
GridView1.DataSource = dt;
GridView1.DataBind();
}
//SqlDataAdapter sqlDa = new SqlDataAdapter(Query, con);
//DataSet Ds = new DataSet();
//sqlDa.Fill(Ds);
//GridView1.DataSource = Ds;
//GridView1.DataBind();
// }
// catch (Exception ex)
// {
// HttpContext.Current.Response.Write("" + ex.Message);
// }
// finally
// {
// con.Close();
// }
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
Calendar1.Visible = true;
Calendar2.Visible = false;
}
protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
{
Calendar2.Visible = true;
Calendar1.Visible = false;
}
protected void Calendar1_SelectionChanged(object sender, EventArgs e)
{
txtfromdate.Text = Calendar1.SelectedDate.ToShortDateString();
Calendar1.Visible = false;
}
protected void Calendar2_SelectionChanged(object sender, EventArgs e)
{
txttodate.Text = Calendar2.SelectedDate.ToShortDateString();
Calendar2.Visible = false;
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
string sortingDirection = string.Empty;
if (direction == SortDirection.Ascending)
{
direction = SortDirection.Descending;
sortingDirection = "Desc";
}
else
{
direction = SortDirection.Ascending;
sortingDirection = "Asc";
}
DataView sortedView = new DataView(BindGridView());
sortedView.Sort = e.SortExpression + " " + sortingDirection;
Session["SortedView"] = sortedView;
GridView1.DataSource = sortedView;
GridView1.DataBind();
}
private DataTable BindGridView()
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT emplyee.*, deparment.*, salry.* FROM deparment INNER JOIN emplyee ON deparment.DId = emplyee.EmpID INNER JOIN salry ON deparment.DId = salry.salID", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
throw new NotImplementedException();
}
public SortDirection direction
{
get
{
if (ViewState["directionState"] == null)
{
ViewState["directionState"] = SortDirection.Ascending;
}
return (SortDirection)ViewState["directionState"];
}
set
{
ViewState["directionState"] = value;
}
}
}
}
sp
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[GET_DATE]
@fromdate varchar(max),
@todate varchar(max)
AS
BEGIN
SELECT emplyee.*, deparment.*, salry.* FROM emplyee INNER JOIN deparment ON emplyee.EmpID = deparment.DId INNER JOIN salry ON deparment.DId = salry.salID where emplyee.jod Between convert(date,@fromdate,105) AND convert(date,@todate,105)
END
GO
/****** Object: StoredProcedure [dbo].[GET_NAME] Script Date: 03/01/2017 03:53:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery1.sql|0|0|C:\Users\Tushar\AppData\Local\Temp\~vsE1F5.sql
CREATE PROCEDURE [dbo].[GET_NAME]
/*Type of this variables should be their column types*/
@name varchar(max)
AS
BEGIN
SELECT emplyee.*, deparment.*, salry.* FROM emplyee INNER JOIN deparment ON emplyee.EmpID = deparment.DId INNER JOIN salry ON deparment.DId = salry.salID where emplyee.name LIKE '%' + @name + '%'
END
GO
/****** Object: StoredProcedure [dbo].[GET_SAL] Script Date: 03/01/2017 03:54:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GET_SAL]
/*Type of this variables should be their column types*/
@sal varchar(max)
AS
BEGIN
SELECT emplyee.*, deparment.*, salry.* FROM emplyee INNER JOIN deparment ON emplyee.EmpID = deparment.DId INNER JOIN salry ON deparment.DId = salry.salID where salry.salary LIKE @sal+'%'
END
GO
/****** Object: StoredProcedure [dbo].[sp_getdata] Script Date: 03/01/2017 04:04:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_getdata]
AS
SELECT emplyee.*, deparment.*, salry.* FROM deparment INNER JOIN emplyee ON deparment.DId = emplyee.EmpID INNER JOIN salry ON deparment.DId = salry.salID
Return
GO