Asp.Net and C sharp: November 2013

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