Asp.Net Ado.Net, C#.Net,Javascript Jquery Entity Framework,Ajax,MVC..! Happy coding..! :): Gridview Searching textBox using OLEDB Connection Excel

Wednesday, June 18, 2014

Gridview Searching textBox using OLEDB Connection Excel

Aspx
------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel.aspx.cs" Inherits="Excel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtsearch" runat="server"></asp:TextBox>
<asp:Button ID="btnsearch" runat="server" Text="Search" onclick="btnsearch_Click" />

<asp:GridView ID="grd" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3"
CellSpacing="1" GridLines="None">
<Columns>
<asp:TemplateField HeaderText="empid">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# bind("empid") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField >
<asp:TemplateField HeaderText="empname">
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# bind("empname") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="salary">
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# bind("salary") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>

<FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
<RowStyle BackColor="#DEDFDE" ForeColor="Black" />
<SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#594B9C" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#33276A" />

</asp:GridView>

</div>
</form>
</body>

</html>

--------------------------------------------------------------------------------------------------

ASPX.CS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

public partial class Excel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Session.Clear();
grd.DataSource = getData();
grd.DataBind();
}
}
public DataSet getData()
{
OleDbConnection con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='D://Naresh.xlsx';Extended Properties=\'Excel 8.0;'");
OleDbCommand cmd = new OleDbCommand("select * from [emp$]");
cmd.Connection = con;
OleDbDataAdapter dap = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
dap.Fill(ds);
return (ds);
}
protected void btnsearch_Click(object sender, EventArgs e)
{

//grd.DataSource = getValues(txtsearch.Text);
//grd.DataBind();

foreach (GridViewRow objgrd in grd.Rows)
{

Label lblname = (Label)objgrd.FindControl("Label2");
Label lblid = (Label)objgrd.FindControl("Label1");
Label lblsalary = (Label)objgrd.FindControl("Label3");
if (txtsearch.Text == lblname.Text)
{
DataTable dt = new DataTable();
dt.Columns.Add("empid", typeof(string));
dt.Columns.Add("empname", typeof(string));
dt.Columns.Add("salary", typeof(string));
dt.Rows.Add(lblid.Text, lblname.Text, lblsalary.Text);
grd.DataSource = dt;
grd.DataBind();
}
}
//}
//// //DataSet ds =
//// grd.DataSource = getValues(lblsalary.Text);
//// grd.DataBind();
//// //DataTable dt = new DataTable();
//// //dt.Columns.Add("empid", typeof(string));
//// //dt.Columns.Add("empname", typeof(string));
//// //dt.Columns.Add("salary", typeof(string));
//// //if (Session["dt"] == null)
//// //{
//// // dt.Rows.Add(ds.Tables[0].Rows[0][0].ToString(), ds.Tables[0].Rows[0][2].ToString(), ds.Tables[0].Rows[0][2].ToString());
//// // Session["dt"] = dt;
//// // grd.DataSource = dt;
//// // grd.DataBind();

//// //}
//// //else
//// //{
//// // DataTable dt2 = (DataTable)Session["dt"];
//// // dt2.Rows.Add(ds.Tables[0].Rows[0][0].ToString(), ds.Tables[0].Rows[0][2].ToString(), ds.Tables[0].Rows[0][2].ToString());
//// // Session["dt"] = dt2;

//// // grd.DataSource = dt2;
//// // grd.DataBind();

//// //}

////}

}
public DataSet getValues(string name)
{
OleDbConnection con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='D://Naresh.xlsx';Extended Properties=\'Excel 8.0';");
OleDbCommand cmd = new OleDbCommand("select * from [emp$] Where empname like '" + name + "%'");
cmd.Connection = con;

OleDbDataAdapter dap = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
dap.Fill(ds);
return (ds);
}

}