Interview Question: March 2014

Monday, March 31, 2014

AutoComplete with Jquery and database

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

<!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">
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
<script language="javascript" type="text/javascript">
$(function () {
$('#<%=txtCompanyName.ClientID%>').autocomplete({
source: function (request, response) {
$.ajax({
url: "autowithjquery.aspx/GetCompanyName",
data: "{ 'pre':'" + request.term + "'}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
success: function (data) {
response($.map(data.d, function (item) {
return { value: item }
}))
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(textStatus);
}
});
}
});
});
</script>

</head>
<body>
<form id="form1" runat="server">
<h3>Auto Complete Textbox without using Web Service</h3>
<table>
<tr>
<td>Type Company Name: </td>
<td>
<div class="ui-widget" style="text-align:left">
<asp:TextBox ID="txtCompanyName" runat="server" Width="350px" CssClass="textboxAuto" Font-Size="12px" />
</div>
</td>
</tr>
</table>

</form>
</body>
</html>

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.Services;

using System.Web.Script.Services;

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

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

{

protected void Page_Load(object sender, EventArgs e)

{

}

[WebMethod]

[ScriptMethod(ResponseFormat = ResponseFormat.Json)]

public static List<string> GetCompanyName(string pre)

{

List<string> allCompanyName = new List<string>();

DataTable dt = new DataTable();

string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

SqlConnection con = new SqlConnection(constr);

con.Open();

SqlCommand cmd = new SqlCommand("select * from TblCountry where CountryName like @City+'%'", con);

cmd.Parameters.AddWithValue("@City", pre);

SqlDataAdapter adp = new SqlDataAdapter(cmd);

adp.Fill(dt);

List<string> CityNames = new List<string>();

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

{

allCompanyName.Add(dt.Rows[i][1].ToString());

}

return allCompanyName;

}

AutoComplete in ASP.net C# Ajax

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>

<asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="txtCity"

MinimumPrefixLength="1" EnableCaching="true" CompletionSetCount="1" CompletionInterval="1000"

ServiceMethod="GetCity" >

</asp:AutoCompleteExtender>

</asp:Content>

///

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

using System.Data.SqlClient;

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

{

protected void Page_Load(object sender, EventArgs e)

{

}

[System.Web.Script.Services.ScriptMethod()]

[System.Web.Services.WebMethod]

public static List<string> GetCity(string prefixText)

{

DataTable dt = new DataTable();

string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

SqlConnection con = new SqlConnection(constr);

con.Open();

SqlCommand cmd = new SqlCommand("select * from TblCountry where CountryName like @City+'%'", con);

cmd.Parameters.AddWithValue("@City", prefixText);

SqlDataAdapter adp = new SqlDataAdapter(cmd);

adp.Fill(dt);

List<string> CityNames = new List<string>();

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

{

CityNames.Add(dt.Rows[i][1].ToString());

}

return CityNames;

}

Sunday, March 30, 2014

AdRotator in asp.net

<asp:ScriptManager ID="ScriptManager1"
runat="server">
</asp:ScriptManager>

<asp:Timer ID="Timer1" runat="server"
Interval="1000">
</asp:Timer>

<asp:UpdatePanel ID="UpdatePanel1"
runat="server">
<Triggers>
<asp:AsyncPostBackTrigger ControlID="Timer1"
EventName="Tick" />
</Triggers>

<ContentTemplate>
<asp:AdRotator ID="AdRotator1" runat="server" AdvertisementFile="~/XMLFile.xml">
</asp:AdRotator>
</ContentTemplate>
</asp:UpdatePanel>

XML File

<Advertisements>
<Ad>
<ImageUrl>http://localhost:2564/Ecommerce35Web/img/11.jpg</ImageUrl>
<NavigateUrl>http://www.asp.net</NavigateUrl>
<AlternateText>ASP.NET Logo</AlternateText>
<Keyword>A</Keyword>
<Impressions>Technology</Impressions>
<Caption>This is the caption for Ad#1</Caption>
</Ad>

<Ad>
<ImageUrl>http://localhost:2564/Ecommerce35Web/img/12.jpg</ImageUrl>
<NavigateUrl>http://www.sulekha.net</NavigateUrl>
<AlternateText>www.Sulekha.net</AlternateText>
<Keyword>S</Keyword>
<Impressions>Web Site</Impressions>
<Caption>This is the caption for Ad#2</Caption>
</Ad>

<Ad>
<ImageUrl>http://localhost:2564/Ecommerce35Web/img/13.jpg</ImageUrl>
<NavigateUrl>AdRotator.aspx
</NavigateUrl>
<AlternateText>www.neostream.net</AlternateText>
<Keyword>S</Keyword>
<Impressions>Flash Site</Impressions>
<Caption>This is the caption for Ad#2</Caption>
</Ad>
</Advertisements>

Friday, March 28, 2014

Split Function in Sql server

create FUNCTION [dbo].[SplitFun](
@sInputList VARCHAR(2000) -- List of delimited items
, @sDelimiter VARCHAR(1) = '|' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(50))

BEGIN

DECLARE @sItem VARCHAR(50)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

Difference between CTE and Temp Table and Table Variable

1) Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
2) CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.
3) Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

Swap Two Numbers without using Temp Variable

int x = 9;
int y = 10;
Console.WriteLine(x);
Console.WriteLine(y);
1) x = x ^ y;
y = y ^ x;
x = x ^ y;
Console.WriteLine(x);
Console.WriteLine(y);
2)
x = x + y;
y = x - y;
x = x - y;
Console.WriteLine(x);
Console.WriteLine(y);

^=Xor's

Types Of Cursor?

Cursor allow us to retrieve data from a result set in singleton fashion means row by row. Cursor are required when we need to update records in a database table one row at a time. I have already explained the basic of cursor.

A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.

You should avoid the use of cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Types of Cursors

Static Cursors

A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable.

You can't update, delete data using static cursor. It is not sensitive to any changes to the original data source. By default static cursors are scrollable.
Dynamic Cursors

A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.
Forward Only Cursors

A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling. You can update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source.

There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.

A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.

A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source.
Keyset Driven Cursors

A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset. The keyset depends on all the rows that qualified the SELECT statement at the time of cursor was opened. A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations. By default keyset driven cursors are scrollable.

SQL SERVER – Examples of Cursors


 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee

Static Cursor - Example


 SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
 DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF

Dynamic Cursor - Example


 --Dynamic Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empupdate CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Mohan'
 Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empupdate
DEALLOCATE Dynamic_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee


 -- Dynamic Cursor for DELETE
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empdelete CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Deepak'
 DELETE Employee WHERE CURRENT OF Dynamic_cur_empdelete
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empdelete
DEALLOCATE Dynamic_cur_empdelete
SET NOCOUNT OFF
Go
Select * from Employee

Forward Only Cursor - Example


 --Forward Only Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empupdate CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Amit'
 Update Employee SET Salary=24000 WHERE CURRENT OF Forward_cur_empupdate
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 END
END
CLOSE Forward_cur_empupdate
DEALLOCATE Forward_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee


 -- Forward Only Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empdelete CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Sonu'
 DELETE Employee WHERE CURRENT OF Forward_cur_empdelete
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 END
END
CLOSE Forward_cur_empdelete
DEALLOCATE Forward_cur_empdelete
SET NOCOUNT OFF
 Go
Select * from Employee

Keyset Driven Cursor - Example


 -- Keyset driven Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Keyset_cur_empupdate CURSOR
KEYSET
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Keyset_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Pavan'
 Update Employee SET Salary=27000 WHERE CURRENT OF Keyset_cur_empupdate
 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
 END
END
CLOSE Keyset_cur_empupdate
DEALLOCATE Keyset_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee


 -- Keyse Driven Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Keyset_cur_empdelete CURSOR
KEYSET
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Keyset_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Amit'
 DELETE Employee WHERE CURRENT OF Keyset_cur_empdelete
 FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
 END
END
CLOSE Keyset_cur_empdelete
DEALLOCATE Keyset_cur_empdelete
SET NOCOUNT OFF
 Go Select * from Employee

Monday, March 31, 2014

Sunday, March 30, 2014

Friday, March 28, 2014

Types of Cursors

Static Cursors

Dynamic Cursors

Forward Only Cursors

Keyset Driven Cursors

SQL SERVER – Examples of Cursors

Static Cursor - Example

Dynamic Cursor - Example

Forward Only Cursor - Example

Keyset Driven Cursor - Example