.Net Programming in Simple: January 2014

Thursday, 30 January 2014

Retrieve Salary With Range in sql

Declare @MaxSal bigint,
@NoOfRange int,@lowerlimit bigint,@higherlimit bigint,
@Initial int,
@RangeQuery nvarchar(max),@No_Of_Employee int,@UpdateQuery nvarchar(max)
set @Initial=0
set @MaxSal=(select Max(sal) from tbl_Salary)
--print @MaxSal
set @NoOfRange=(@MaxSal/999)+1
--print @NoOfRange
if OBJECT_ID('tempdb..#tblRange') is not null
drop table #tblRange
create Table #tblRange(LowerRange varchar(max),HigherRange varchar(max),
No_Of_Employee int)
--set @RangeQuery='insert into #tblRange(LowerRange,HigherRange) values '
while(@Initial<@NoOfRange)
begin
set @lowerlimit=@Initial*1000
set @higherlimit=@Initial*1000+999
--print @lowerlimit
--print @higherlimit
--set @RangeQuery=@RangeQuery+'('+@lowerlimit+','+@higherlimit+'),'
insert into #tblRange(LowerRange,HigherRange) values(@lowerlimit,@higherlimit)
print @RangeQuery
set @Initial=@Initial+1
end
--select * from #tblRange
Declare C1 Cursor for select LowerRange,HigherRange from #tblRange
open C1
fetch next from C1 into @lowerlimit,@higherlimit
while(@@FETCH_STATUS=0)
begin
set @No_Of_Employee=(select count(*) from tbl_Salary where sal between @lowerlimit and @higherlimit)
print @No_Of_Employee
--set @UpdateQuery='Update #tblRange set No_Of_Employee = '+@No_Of_Employee+' where LowerRange ='+@lowerlimit+' and HigherRange ='+@higherlimit+''
--print @UpdateQuery
--exec (@UpdateQuery)
Update #tblRange set No_Of_Employee =@No_Of_Employee where LowerRange=@lowerlimit and HigherRange=@higherlimit
fetch next from C1 into @lowerlimit,@higherlimit
end
close C1
deallocate C1
select LowerRange+'-'+HigherRange as Range, No_Of_Employee from #tblRange
--select LowerRange,HigherRange, No_Of_Employee from #tblRange

Wednesday, 22 January 2014

Get the records separated by comma in sqlserver

CREATE Procedure sp_GetMobileNo_CommaSeparated

@Fcode varchar(20) ,

@FromDate varchar(20),

@ToDate varchar(20)

as

begin

DECLARE @List VARCHAR(max)

SELECT @List = ISNULL(@List + ',', '') + CAST(F_MOBILE AS VARCHAR)

FROM XStudentMasterCopy

WHERE FranchiseCode = @Fcode

and ADMNDATE between convert(varchar(15),@FromDate,103) and convert(varchar(15),@ToDate,103)

SELECT @List

end

Paging in sqlserver

DECLARE @RowsPerPage INT = 10, @PageNumber INT = 15

SELECT RowNum,AutoID,ADMNNO,STDFIRSTNAME,F_MOBILE,ADMNDATE

FROM (

SELECT AutoID,ADMNNO,STDFIRSTNAME,F_MOBILE,ADMNDATE,

ROW_NUMBER() OVER (ORDER BY AutoID) AS RowNum

FROM XStudentMasterCopy where ADMNDATE

between convert(varchar(15),'06/14/2013',103) and convert(varchar(15),'06/15/2013',103) ) AS SOD

WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1

AND @RowsPerPage*(@PageNumber)

Sunday, 12 January 2014

Get the Procedure name in which particular table is used

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id WHERE sc.text LIKE '%BatchMaster%'

Export Radgrid to Excel in .Net

protected void btn_Export_Click(object sender, EventArgs e)
{
try
{
if (grd_AdmnReg.Items.Count > 0)
{
grd_AdmnReg.MasterTableView.AllowPaging = false;
grd_AdmnReg.PageSize = grd_AdmnReg.Items.Count + 1;
grd_AdmnReg.ExportSettings.FileName = "ServerApps Online";
grd_AdmnReg.GridLines = GridLines.Both;
grd_AdmnReg.ExportSettings.ExportOnlyData = true;
grd_AdmnReg.ExportSettings.OpenInNewWindow = false;
grd_AdmnReg.MasterTableView.ExportToExcel();
}
else
{
Alert.WithoutUpdatePanelMessage("Sorry,there is no records.");
}

}
catch (Exception ex)
{

}
}

Tuesday, 7 January 2014

Upload Multiple File at a Time in Asp.Net

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Multiple FileUpload Example</title>
    <script type = "text/javascript">
        var counter = 0;
        function AddFileUpload() {
            var div = document.createElement('DIV');
            div.innerHTML = '<input id="file' + counter + '" name = "file' + counter + '" type="file" /><input id="Button' + counter + '" type="button" value="Remove" onclick = "RemoveFileUpload(this)" />';
            document.getElementById("FileUploadContainer").appendChild(div);
            counter++;
        }
        function RemoveFileUpload(div) {
            document.getElementById("FileUploadContainer").removeChild(div.parentNode);
        }
    </script>
</head>
<body>
    <form id="form1" runat="server" enctype="multipart/form-data" method = "post">
        <span style ="font-family:Arial">Click to add files</span>  
        <input id="Button1" type="button" value="add" onclick = "AddFileUpload()" />
        <br /><br />
        <div id = "FileUploadContainer">

        </div>
        <br />
        <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
    </form>
</body>
</html>

protected void btnUpload_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < Request.Files.Count; i++)
            {
                HttpPostedFile PostedFile = Request.Files[i];
                if (PostedFile.ContentLength > 0)
                {
                    string FileName = System.IO.Path.GetFileName(PostedFile.FileName);
                    PostedFile.SaveAs(Server.MapPath("Worksheet\\") + FileName);
                }
            }
        }