Dot Net Sea: September 2012

Wednesday, 19 September 2012

using view state previous page property

                    first take default.aspx page and default2.aspx page then do this coding



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;

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

{protected void Page_Load(object sender, EventArgs e)
{
}protected void Button1_Click(object sender, EventArgs e)
{

Label1.Text = DateTime.Now.ToString();
Server.Transfer("Default2.aspx");
}
}

on default2.aspx page

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;

public
partial class Default2 : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{if(!IsPostBack)
{Label tb = (Label)PreviousPage.FindControl("Label1");
Response.Write(tb.Text);}

}
}
output will come current date of your system

Tuesday, 18 September 2012

working with crystal report in asp.net

take web application ,then go to add new item and take crysatlreport1.rpt file
on default.aspx page take button

and add refrence of these two namespace

using

CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

and do the following code->



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 CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using System.Data.SqlClient;

namespace WebApplication1

{

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

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);

SqlDataReader dr;

DataTable dt = new DataTable();

protected void Page_Load(object sender, EventArgs e)

{

}

public DataTable getdata()

{

con.Open();

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

// cmd.Parameters.AddWithValue("@name", TextBox1.Text); For parameterized report

cmd.CommandType = CommandType.StoredProcedure;

dr= cmd.ExecuteReader();

dt.Load(dr);

return dt;

}protected void Button1_Click(object sender, EventArgs e)
{
dt = getdata();

ReportDocument doc = new ReportDocument();

doc.Load(Server.MapPath("CrystalReport1.rpt"));

doc.SetDataSource(dt);

// doc.SetParameterValue("@name", TextBox1.Text); For parameterized report

CrystalReportViewer1.ReportSource = doc;

CrystalReportViewer1.RefreshReport();

}

}

}

                                output will come like this

1 / 1

name‎

age‎

id‎

sanjeev

yogi

Sunday, 16 September 2012

sql important queries for interview purpose

Q1.find out manager of each employee.

empid empname managerid

1 mukesh 3

2 rakesh 0

3 yogi 2

4 sanjeev 1

5 hari 1

Ans.

select e.empid ,e.empname,M.empname as manager from

empM e left outer join empM M on

m.empid=e.managerid

empid empname manager

1 mukesh yogi

2 rakesh NULL

3 yogi rakesh

4 sanjeev mukesh

5 hari mukesh

Q2. Find out output of this query

select e.empid ,e.empname,M.empname as manager from

empM e left outer join empM M on

e.empid=m.managerid

ans.

empid empname manager

1 mukesh sanjeev

1 mukesh hari

2 rakesh yogi

3 yogi mukesh

4 sanjeev NULL

5 hari NULL

Q3.find out that manager who has more than 1 employee under him.

empid empname managerid

1 mukesh 3

2 rakesh 0

3 yogi 2

4 sanjeev 1

5 hari 1

Ans.

declare @manager as varchar(23),@count as int

declare @name table(empid int ,empname varchar(23),managerid varchar(34))

insert into @name(empid,empname,managerid) select e.empid ,e.empname,M.empname as manager from

empM e left outer join empM M on

m.empid=e.managerid

select @manager=managerid,@count=COUNT(managerid)from @name group by managerid having COUNT(managerid)>1

select @manager as manager

Q4. HOW TO USE OUPUT PARAMETER IN PROCEDURE

ANS.

create proc sp_out

(

@a int,

@b int,

@c int output

)

begin

set @c=@a+@b

end

declare @p as int

exec sp_out 1,2,@p output

select @p

Q5 WHAT WOULD BE OUTPUT OF LEFT OUTER JOIN IF WE HAVE TWO TABLE LIKE THIS

1.TABLE PK

ID NAME

1 MUKESH

2 YOGI

NULL YOGI

2. TABLE JK

REFID ADDRESS

NULL NULL

1 KANPUR

NULL JHANSI

ANS.

SELECT PK.ID,PK.NAME,JK.[ADDRESS] FROM PK LEFT OUTER JOIN JK ON

PK.ID=JK.REFID

ID NAME ADDRESS

1 MUKESH KANPUR

2 YOGI NULL

NULL YOGI NULL

NULL YOGI JHANSI

Q6. GIVE YOUR VIEW WHETHER SP_TB PROCEDURE

1 WILL RUN OR NOT BCZ TEMP TABLE IS NOT CREATED IN PROCEDURE SP_PO

CREATE PROC SP_TB

BEGIN

CREATE TABLE #TEMP(NAME VARCHAR(23))

EXEC SP_PO

END

CREATE PROC SP_PO

BEGIN

INSERT INTO #TEMP VALUES('MUKESH')

END

ANS THIS WILL RUN SMOOTHLY

Q7. JOINS

TABLE 1. EMPM

empid empname managerid

1 mukesh 3

2 rakesh 0

3 yogi 2

4 sanjeev 1

5 hari 1

TABLE 2. EMPM2

REFID ADDRESS

1 KANPUR

TABLE 3 . EMPM3

FID SEX

2 MALE

NOW SEE THE RESULT OF QUERY

LEFT OUTER JOIN

SELECT PM.empid,PM.empname,PM2.[ADDRESS] FROM EMPM PM LEFT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID

WILL PRODUCE THE RESULT

empid empname ADDRESS

1 mukesh KANPUR

2 rakesh NULL

3 yogi NULL

4 sanjeev NULL

5 hari NULL

NOW APPLY AGAIN INNER JOIN THEN RESULT

SELECT PM.empid,PM.empname,PM2.[ADDRESS] FROM EMPM PM LEFT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID INNER JOIN

EMPM3 PM3 ON PM.empid=PM3.FID

empid empname ADDRESS

2 rakesh NULL

IF APPLY AGAIN LEFT OUTER THEN RESULT

SELECT PM.empid,PM.empname,PM2.[ADDRESS],PM3.SEX FROM EMPM PM LEFT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID LEFT OUTER JOIN

EMPM3 PM3 ON PM.empid=PM3.FID

empid empname ADDRESS SEX

1 mukesh KANPUR NULL

2 rakesh NULL MALE

3 yogi NULL NULL

4 sanjeev NULL NULL

5 hari NULL NULL

NOW WHEN WE APPLY RIGHT JOIN IN PREVIOUS TWO TABLE THEN RESULT

SELECT PM.empid,PM.empname,PM2.[ADDRESS] FROM EMPM PM RIGHT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID

empid empname ADDRESS

1 mukesh KANPUR

NOW WHEN WE APPLY LEFT JOIN IN PREVIOUS QUERY THEN RESULT

SELECT PM.empid,PM.empname,PM2.[ADDRESS],PM3.SEX FROM EMPM PM RIGHT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID LEFT OUTER JOIN

EMPM3 PM3 ON PM.empid=PM3.FID

empid empname ADDRESS SEX

1 mukesh KANPUR NULL

Q8. USING JOIN IN UPDATE

UPDATE PM1 SET PM1.EMPNAME='yogi' FROM EMPM PM1

INNER JOIN EMPM2 PM2 ON PM1.empid=PM2.REFID

Q9. how to use cursor.

sql query Copying table data to another table that should not be repeating terms using Cursor

sql query Copying table data to another table that should not be repeating terms using Cursor

DECLARE @ACCOUNTID BIGINT
DECLARE @NAME VARCHAR(30)
DECLARE @TEB TABLE(NAME VARCHAR(34),NUM BIGINT)
DECLARE @GETACCOUNTID CURSOR
SET @GETACCOUNTID = CURSOR FOR
SELECT NAME, MBNO
FROM EMP1
OPEN @GETACCOUNTID
FETCH NEXT
FROM @GETACCOUNTID INTO @NAME,@ACCOUNTID
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1 FROM @TEB WHERE NAME=@NAME AND NUM=@ACCOUNTID)
zzBEGIN
DECLARE @P AS BIGINT
INSERT INTO @TEB VALUES(@NAME,@ACCOUNTID)
END

FETCH NEXT
FROM @GETACCOUNTID INTO @NAME,@ACCOUNTID
END
CLOSE @GETACCOUNTID
DEALLOCATE @GETACCOUNTID
SELECT* FROM @TEB

Q10. how to use case statement in sql
ans.

select name,case(age) when '23' then '101'
when '27'then '102'
else
'mukesh'
end

from emp

Q11.HERE WE HAVE A TABLE LIKE THIS

TABLE EMP12

name     gender
mukesh    m
yogi       f
rakesh    m

UPDATE THE TABLE OF COLUMN GENDER WHERE M THEN F AND WHERE F THERE M

ANS

UPDATE emp12

SET
gender = CASE
WHEN gender = 'm'
THEN 'f'
ELSE 'm'
END