Asp.Net and C sharp

Friday 22 November 2013

File Upload , Display in Gridview, and finally bulk insert into Database

TRICK 1 -->

---------------------------Excel-----------

--------------------------------------------------------------
SQL

-----ASPX--
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication4._Default" %>

<!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>
<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<b>Please Select Excel File: </b>
<asp:FileUpload ID="fileuploadExcel" runat="server" />  
<asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />
<asp:Button ID="btnsql" runat="server" Text="Bulk Insert"
OnClick="btn_import_sql" />
<br />
<asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label>
<br />
<asp:Label ID="Label1" runat="server" Visible="true" Font-Bold="True" ForeColor="#009933"></asp:Label>
<asp:GridView ID="grvExcelData" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>

------------.CS-----------

using System;

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Data.SqlClient;

using System.Configuration;

using System.Text;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

namespace WebApplication4

{

public partial class _Default : System.Web.UI.Page

{

string str;

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnImport_Click(object sender, EventArgs e)

{

string connString = "";

string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();

string path = fileuploadExcel.PostedFile.FileName;

//Connection String to Excel Workbook

if (strFileType.Trim() == ".xls")

{

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

}

else if (strFileType.Trim() == ".xlsx")

{

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

}

string query = "SELECT ltrim(rtrim([userid])),[starttime],[endtime] FROM [Sheet1$]";

OleDbConnection conn = new OleDbConnection(connString);

if (conn.State == ConnectionState.Closed)

conn.Open();

OleDbCommand cmd = new OleDbCommand(query, conn);

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

grvExcelData.DataSource = ds.Tables[0];

grvExcelData.DataBind();

da.Dispose();

conn.Close();

conn.Dispose();

}

protected void btn_import_sql(object sender, EventArgs e)

{

string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;

using (SqlConnection con = new SqlConnection(strcon))

{

for (int i = 0; i < grvExcelData.Rows.Count; i++)

{

GridViewRow row = grvExcelData.Rows[i];

con.Open();

str= "insert into temp_adhoc_arihant2(userid,starttime,endtime)values('"+ row.Cells[0].Text +"','"+ row.Cells[1].Text +"','"+ row.Cells[2].Text +"')";

SqlCommand com = new SqlCommand(str, con);

com.ExecuteNonQuery();

con.Close();

}

Label1.Text = "Records successfully inserted";

}

-------If you hit bulk insert--

--database table-->

---Will come up with trick2 and trick3 for bulk insert....

--If it helped you ...kindly give feedback :)

Monday 18 November 2013

Display GridData detail on mouse over

----------------------------FUNCTION TO GET THE HTML FOR TOOL TIP---------------------------

USE [anurag]
GO
/****** Object: UserDefinedFunction [dbo].[testfunction] Script Date: 11/13/2013 23:56:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[testfunction]-- 1
(
@id int
)
RETURNS VARCHAR(max)
AS
BEGIN
declare @detail table
(
total int identity(1,1) not null,
id int,
detail varchar(50)
)

DECLARE @HTML NVARCHAR(max)
DECLARE @HTMLHEADER NVARCHAR(max)
DECLARE @HTMLFOOTER NVARCHAR(max)
DECLARE @HTMLROWS

NVARCHAR(max)
DECLARE @i Int,@MaxI Int,@MyRowCount Int,@id1 int,@details varchar(50)
set @HTMLROWS=''

if(@id is not null and len(@id) > 0)
begin
set @HTMLHEADER='<STRONG></STRONG><Table width=150>'
set @HTMLFOOTER='</Table>'

insert into @detail(id,detail)
select id,place from Places where id=@id

SELECT @MaxI=@@RowCount,@i = 1

IF(@MaxI=0)
Begin
set @HTMLROWS=@HTMLROWS +'<tr><td>'+'No places'
end

else
begin
while @i <=@MaxI
begin
select @id1=id,@details=detail from @detail WHERE total = @i
set @HTMLROWS=
@HTMLROWS +'<tr style=background-color:#808080;><td>' +
CAST(@i AS VARCHAR(10))+ '-->' + cast(@details as varchar(50))+ '<hr><span style=background-color:white;font-weight:bold</span></hr>' + '</td></tr>'
SELECT @i = @i + 1,
@id = null
end
end

END
set @HTML= @HTMLHEADER +@HTMLROWS + @HTMLFOOTER


RETURN @HTML

END

<-----------------------------------------O/P OF FUNCTION------------------------------------------------->
--select [dbo].[testfunction](1)

<STRONG></STRONG>
<Table width=150>

<tr style=background-color:#808080;>
<td>1-->Thoraipakkam
<hr><span style=background-color:white;font-weight:bold</span></hr>

</td></tr><tr style=background-color:#808080;>
<td>2-->Chrompet<hr><span style=background-color:white;font-weight:bold</span></hr>

</td></tr><tr style=background-color:#808080;><td>
3-->Shollinganallur<hr><span style=background-color:white;font-weight:bold</span></hr>

</td></tr><tr style=background-color:#808080;>
<td>4-->Perungudi<hr><span style=background-color:white;font-weight:bold</span></hr>
</td>

</tr>

</Table>

---------------------------------------------Stored Procedure----------------------------------

USE [anurag]

/****** Object: StoredProcedure [dbo].[Distanceprocedure] Script Date: 11/13/2013 23:44:25 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER proc [dbo].[Distanceprocedure]

begin

select id,name,dbo.[testfunction](city.id) as functionhtml from city

end

---------------------------------------------.ASPX---------------------------------------------------------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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">

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

<script type="text/javascript" src="http://cdn.jsdelivr.net/jquery.simpletip/1.3.1/jquery.simpletip-1.3.1.min.js.txt"></script>

<script type="text/javascript">

$(function() {

$('[id*=GridView1] tr').each(function() {

var toolTip = $(this).attr("title");

$(this).find("td").each(function() {

$(this).simpletip(

{

content: toolTip

});

});

$(this).removeAttr("title");

});

});

</script>

<style type="text/css">

#GridView1 tr.rowHover:hover

{

background-color: green;

font-family: Arial;

}

</style>

<style type="text/css">

.tooltip

{

position: absolute;

top: 0;

left: 0;

z-index: 3;

display: none;

background-color: #FB66AA;

color: White;

padding: 2px;

font-size: 10pt;

font-family: Arial;

width: 150PX;

}

td

{

cursor: pointer;

}

</style>

<head runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:GridView ID="GridView1" runat="server" DataKeyNames="id" AutoGenerateColumns="False"

Width="59%" OnRowDataBound="Gridplace_RowDataBound" BackColor="CadetBlue" BorderColor="Black"

RowStyle-CssClass="rowHover">

<Columns>

<asp:TemplateField HeaderText="City" HeaderStyle-HorizontalAlign="Left" HeaderStyle-BackColor="GreenYellow">

<ItemTemplate>

<asp:Label ID="lblID" Font-Bold="true" BorderColor="CadetBlue" BorderStyle="Solid"

Font-Size="Medium" runat="server" Text='<%#Eval("id") %>' Width="283px"></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Place" HeaderStyle-HorizontalAlign="Left" HeaderStyle-BackColor="GreenYellow">

<ItemTemplate>

<asp:Label ID="lblName" Font-Bold="true" BorderColor="CadetBlue" BorderStyle="Solid"

Font-Size="Medium" runat="server" Text='<%#Eval("name") %>' Width="283px"></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<ItemTemplate>

<asp:Label ID="lblfunction" Font-Bold="true" Visible="false" BorderColor="CadetBlue"

BorderStyle="Solid" Font-Size="Medium" runat="server" Text='<%#Bind("functionhtml") %>'

></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

-------------------------------------------------------.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.SqlClient;

using System.Configuration;

using System.Text;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;

using (SqlConnection con = new SqlConnection(strcon))

{

SqlCommand cmd = new SqlCommand("Distanceprocedure", con);

cmd.CommandType = CommandType.StoredProcedure;

con.Open();

GridView1.DataSource = cmd.ExecuteReader();

GridView1.DataBind();

con.Close();

}

}

}

protected void Gridplace_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

e.Row.ToolTip = DataBinder.Eval(e.Row.DataItem, "functionhtml").ToString();

////e.Row.ToolTip = functionhtml;

//e.Row.ToolTip = (e.Row.DataItem as DataRowView)["functionhtml"].ToString();

}

}

}

-------------------------------------------------------O/P-----------------------------------------------------

Sunday 10 November 2013

Multiply two columns in gridview

Backend :

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.SqlClient;
using System.Configuration;
using System.Text;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{

string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;
using (SqlConnection con = new SqlConnection(strcon))
{

SqlCommand cmd = new SqlCommand("USP_datamanipulation", con);
cmd.CommandType = CommandType.StoredProcedure;

con.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
con.Close();
}

}
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{

for (int i = 0; i < GridView1.Rows.Count; i++)
{

GridView1.Rows[i].Cells[3].Text = Convert.ToString(Convert.ToDecimal(GridView1.Rows[i].Cells[1].Text) * Convert.ToDecimal(GridView1.Rows[i].Cells[2].Text));

}

}
}

o/p

Tuesday 29 October 2013

Enable view state made false...see the difference

<--------------------------Enable View State is Made on------------------------------------------------>
<--------------------------------------------------------------------------------------------------------->
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.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{

Getdata();

}
}

private void Getdata()
{

string strcon= ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;

using(SqlConnection con= new SqlConnection(strcon))
{
con.Open();
SqlCommand cmd= new SqlCommand("emp",con);
cmd.CommandType= CommandType.StoredProcedure;
GridView1.DataSource=cmd.ExecuteReader();
GridView1.DataBind();
con.Close();

}

}
}
//if you hit the button the next time....the gridview displays the data....

<-------------------------------Enable View State is Made off-------------------------------------------->
<--------------------------------------------------------------------------------------------------------->

If you hit button ...then no grid
If you hit f5...its pageload...so grid displays

--Webconfig

<connectionStrings>

<add name="ConnectionStringdb" connectionString="Data Source=X.X.X.X; Initial Catalog=anurag; Integrated Security=True;" providerName="System.Data.SqlClient"/>

</connectionStrings>

sql

create table employees
(
name varchar(40),
age int,
sex varchar(30)
)

insert into employees
select 'anurag',24,'m'
union all select 'anurag',24,'m'
union all select 'abhishek',22,'m'
union all select 'ruchi',23,'f'

select * from employees

alter procedure emp
as
begin
select * from employees
end

Friday 25 October 2013

GRIDVIEW + JAVASCRIPT

The basic concept behind this is when the GridView is rendered on the client machine it is rendered as a simple HTML table. Hence what the JavaScript will see a HTML table and not the ASP.Net GridView control.

Thursday 24 October 2013

VIEW STATE AND PRERENDER

STEP 1

using System;

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 System.Collections;

public partial class _Default : System.Web.UI.Page

{

ArrayList PageArrayList;

ArrayList CreateArray()

{

// Create a sample ArrayList.

ArrayList result = new ArrayList(4);

result.Add("item 1");

result.Add("item 2");

result.Add("item 3");

result.Add("item 4");

return result;

}

protected void Page_Load(object sender, EventArgs e)

{

if (ViewState["arrayListInViewState"] != null)

{

PageArrayList = (ArrayList)ViewState["arrayListInViewState"];

}

else

{

// ArrayList isn't in view state, so it must be created and

populated.

PageArrayList = CreateArray();

gridview1.DataSource = PageArrayList;

gridview1.DataBind();

}

// Code that uses PageArrayList.

}

void Page_PreRender(object sender, EventArgs e)

{

// Save PageArrayList before the page is rendered.

ViewState.Add("arrayListInViewState", PageArrayList);

}

protected void Button1_Click(object sender, EventArgs e)

{

PageArrayList.Add("item6");

PageArrayList.Add("item7");

PageArrayList.Add("item8");

PageArrayList.Add("item9");

gridview2.DataSource = PageArrayList;

gridview2.DataBind();

}

}

--WHEN PAGE LOADS

viewstate["abc"]=pagearraylist;
viewstate.add("","");

--both are same

Wednesday 23 October 2013

Populate data in gridview using datatable[SELECT,EDIT,UPDATE,CANCEL]

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

public partial class _Default : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Age");
dt.Columns.Add("Sex");

dt.Rows.Add("Anurag", "24", "M");
dt.Rows.Add("Ruchi", "23", "F");
dt.Rows.Add("Abhi", "22", "M");

GridView1.DataSource = dt;
GridView1.DataBind();

}
}

protected void Button1_Click(object sender, EventArgs e)
{

}
}

-------------------------------------SELECT IN GRID VIEW-------------------------

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

public partial class _Default : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Age");
dt.Columns.Add("Sex");

dt.Rows.Add("Anurag", "24", "M");
dt.Rows.Add("Ruchi", "23", "F");
dt.Rows.Add("Abhi", "22", "M");

GridView1.DataSource = dt;
GridView1.DataBind();

}
}

protected void Button1_Click(object sender, EventArgs e)
{

}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{

}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{

}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{

}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Select")
{

int num = Convert.ToInt32(e.CommandArgument);

TextName.Text= GridView1.Rows[num].Cells[2].Text;
TextAge.Text= GridView1.Rows[num].Cells[3].Text;
TextSex.Text = GridView1.Rows[num].Cells[4].Text;

}
}
}

--SECOND ROW SELECTED

----------------------------EDIT/UPDATE/CANCEL----------------------------------------------------

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

public partial class _Default : System.Web.UI.Page
{

public DataTable dtviewstate
{

get
{
return (DataTable)ViewState["dtview"];
}

set

{

ViewState["dtview"] = value;
}

}

protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Age");
dt.Columns.Add("Sex");

dt.Rows.Add("Anurag", "24", "M");
dt.Rows.Add("Ruchi", "23", "F");
dt.Rows.Add("Abhi", "22", "M");

dtviewstate = dt;
GridView1.DataSource = dt;
GridView1.DataBind();

}
}

protected void Button1_Click(object sender, EventArgs e)
{

}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
HiddenField1.Value = e.NewEditIndex.ToString();
GridView1.DataSource = dtviewstate;
GridView1.DataBind();

}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridView1.DataSource = dtviewstate;
GridView1.DataBind();

}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox t1 = new TextBox();
TextBox t2 = new TextBox();
TextBox t3 = new TextBox();

t1 = (TextBox)GridView1.Rows[Convert.ToInt32( HiddenField1.Value)].Cells[2].Controls[0];
t2 = (TextBox)GridView1.Rows[Convert.ToInt32(HiddenField1.Value)].Cells[3].Controls[0];
t3 = (TextBox)GridView1.Rows[Convert.ToInt32(HiddenField1.Value)].Cells[4].Controls[0];

dtviewstate.Rows[e.RowIndex]["Name"]=t1.Text;
dtviewstate.Rows[e.RowIndex]["Age"]=t2.Text;
dtviewstate.Rows[e.RowIndex]["Sex"]=t3.Text;

GridView1.EditIndex = -1;
GridView1.DataSource = dtviewstate;
GridView1.DataBind();

}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Select")
{

int num = Convert.ToInt32(e.CommandArgument);

TextName.Text = GridView1.Rows[num].Cells[2].Text;
TextAge.Text= GridView1.Rows[num].Cells[3].Text;
TextSex.Text = GridView1.Rows[num].Cells[4].Text;

}
}
}