Interview Question: 2014

Thursday, December 18, 2014

search keyword in sql

declare @CityPostCode varchar(500)
set @CityPostCode='hghgh gtr rt gtr yy'
declare @substring varchar(15)

if @CityPostCode <> ''

begin

WHILE (CHARINDEX(' ',@CityPostCode ,1)<>0)
BEGIN
SET @substring = substring(@CityPostCode ,1,CHARINDEX(' ',@CityPostCode ,1)-1)
-- Find Substring up to Separator
if (isnull(@substring,' ') <> ' ')
begin
select @substring ,1
end
SET @CityPostCode = substring(@CityPostCode ,Len(@substring)+2,Len(@CityPostCode ))
end

end

Tuesday, October 28, 2014

conversion number to word in javascript

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript" language="javascript">
function test_skill() {
// debugger;
var junkVal = document.getElementById("<%= this.txtGuardianTotalIncome.ClientID %>").value;
junkVal = Math.floor(junkVal);
var obStr = new String(junkVal);
numReversed = obStr.split("");
actnumber = numReversed.reverse();

if (Number(junkVal) >= 0) {
//do nothing
} else {
alert('wrong Number cannot be converted');
return false;
}
if (Number(junkVal) == 0) {
document.getElementById('container').innerHTML = 'Rupees Zero Only';
return false;
}
if (actnumber.length > 9) {
alert('Oops!!!! the Number is too big to convert');
return false;
}

var iWords = ["Zero", " One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine"];
var ePlace = [' Ten', ' Eleven', ' Twelve', ' Thirteen', ' Fourteen', ' Fifteen', ' Sixteen', ' Seventeen', ' Eighteen', ' Nineteen'];
var tensPlace = ['dummy', ' Ten', ' Twenty', ' Thirty', ' Forty', ' Fifty', ' Sixty', ' Seventy', ' Eighty', ' Ninety'];

var iWordsLength = numReversed.length;
var totalWords = "";
var inWords = new Array();
var finalWord = "";
j = 0;
for (i = 0; i < iWordsLength; i++) {
switch (i) {
case 0:
if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
inWords[j] = '';
} else {
inWords[j] = iWords[actnumber[i]];
}
inWords[j] = inWords[j] + ' Only';
break;
case 1:
tens_complication();
break;
case 2:
if (actnumber[i] == 0) {
inWords[j] = '';
} else if (actnumber[i - 1] != 0 && actnumber[i - 2] != 0) {
inWords[j] = iWords[actnumber[i]] + ' Hundred and';
} else {
inWords[j] = iWords[actnumber[i]] + ' Hundred';
}
break;
case 3:
if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
inWords[j] = '';
} else {
inWords[j] = iWords[actnumber[i]];
}
if (actnumber[i + 1] != 0 || actnumber[i] > 0) {
inWords[j] = inWords[j] + " Thousand";
}
break;
case 4:
tens_complication();
break;
case 5:
if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
inWords[j] = '';
} else {
inWords[j] = iWords[actnumber[i]];
}
if (actnumber[i + 1] != 0 || actnumber[i] > 0) {
inWords[j] = inWords[j] + " Lakh";
}
break;
case 6:
tens_complication();
break;
case 7:
if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
inWords[j] = '';
} else {
inWords[j] = iWords[actnumber[i]];
}
inWords[j] = inWords[j] + " Crore";
break;
case 8:
tens_complication();
break;
default:
break;
}
j++;
}

function tens_complication() {
if (actnumber[i] == 0) {
inWords[j] = '';
} else if (actnumber[i] == 1) {
inWords[j] = ePlace[actnumber[i - 1]];
} else {
inWords[j] = tensPlace[actnumber[i]];
}
}
inWords.reverse();
for (i = 0; i < inWords.length; i++) {
finalWord += inWords[i];
}

document.getElementById('container').innerHTML = finalWord;
return false;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<span id="container" runat="server"></span>
<br />
<asp:TextBox id="txtGuardianTotalIncome" runat="server"></asp:TextBox>
<asp:Button id="btn" runat="server" Text="Submit" OnClientClick="return test_skill();" OnClick="btn_Click"/>
</div>
</form>
</body>
</html>

.NET supports two kind of coding

Managed Code

Unmanaged Code

Managed Code

The resource, which is with in your application domain is, managed code. The resources that are within domain are faster.

The code, which is developed in .NET framework, is known as managed code. This code is directly executed by CLR with help of managed code execution. Any language that is written in .NET Framework is managed code.

Managed code uses CLR which in turns looks after your applications by managing memory, handling security, allowing cross - language debugging, and so on.

Unmanaged Code

The code, which is developed outside .NET, Framework is known as unmanaged code.

Applications that do not run under the control of the CLR are said to be unmanaged, and certain languages such as C++ can be used to write such applications, which, for example, access low - level functions of the operating system. Background compatibility with code of VB, ASP and COM are examples of unmanaged code.

Unmanaged code can be unmanaged source code and unmanaged compile code.

Unmanaged code is executed with help of wrapper classes.

Wrapper classes are of two types: CCW (COM Callable Wrapper) and RCW (Runtime Callable Wrapper).

Wrapper is used to cover difference with the help of CCW and RCW.
COM callable wrapper unmanaged code execution

Description: unmanaged_code_COM.gif

Runtime Callable Wrapper unmanaged code execution

How to make wcf service and how to call by ajax?

step 1: first I make the interface...

using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.Text;

using System.ServiceModel.Web;

[ServiceContract]

public interface IService

{

[OperationContract]

[WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json)]

string GetCustomers(string prefix);

[OperationContract]

[WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json)]

string GetCustomers1();

[OperationContract]

[WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json)]

string GetData();

}

step 2: second Make the Service...

using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.Text;

using System.Data.SqlClient;

using System.Configuration;

using System.Web.Script.Serialization;

using System.ServiceModel.Activation;

[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]

public class Service : IService

{

public string GetCustomers(string prefix)

{
List<object> customers = new List<object>();

using (SqlConnection conn = new SqlConnection())

{

conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlCommand cmd = new SqlCommand())

{

cmd.CommandText = "select FirstName+ ''+ CASE when LastName IS null then '' end as ContactName,CustomerId from tblcustomer Where " +

" email like @prefix + '%'";

cmd.Parameters.AddWithValue("@prefix", prefix);

cmd.Connection = conn;

conn.Open();

using (SqlDataReader sdr = cmd.ExecuteReader())

{

while (sdr.Read())

{

customers.Add(new

{

Id = sdr["CustomerId"],

Name = sdr["ContactName"]

});

}

}

conn.Close();

}

return (new JavaScriptSerializer().Serialize(customers));

}

}

public string GetData()

{

return "pppp";

}

public string GetCustomers1()

{
List<object> customers = new List<object>();

using (SqlConnection conn = new SqlConnection())

{

string prefix = "g";

conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlCommand cmd = new SqlCommand())

{

cmd.CommandText = "select FirstName+ ''+ CASE when LastName IS null then '' end as ContactName,CustomerId from tblcustomer Where " +

" email like @prefix + '%'";

cmd.Parameters.AddWithValue("@prefix", prefix);

cmd.Connection = conn;

conn.Open();

using (SqlDataReader sdr = cmd.ExecuteReader())

{

while (sdr.Read())

{

customers.Add(new

{

Id = sdr["CustomerId"],

Name = sdr["ContactName"]

});

}

}

conn.Close();

}

return (new JavaScriptSerializer().Serialize(customers));

}

}

}

step 3: change web.config
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name="ServiceBehavior1">
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="true" />
</behavior>
</serviceBehaviors>
<endpointBehaviors>
<behavior name="ServiceAspNetAjaxBehavior1">
<enableWebScript />
</behavior>
</endpointBehaviors>
</behaviors>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
<services>
<service behaviorConfiguration="ServiceBehavior1" name="Service">
<endpoint address="" binding="webHttpBinding" contract="IService" behaviorConfiguration="ServiceAspNetAjaxBehavior1">
<identity>
<dns value="localhost" />
</identity>
</endpoint>
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
</service>
</services>
</system.serviceModel>

step 4:
Note: make the service and then deployment in iis and get url like http://localhost/webs
　

step 5: cal any where by ajax....

function LoadStatixText() {

var sId = 0;

$.ajax({

type: "GET",

contentType: "application/json; charset=utf-8",

url: "http://localhost/webs/services/service.svc/GetCustomers1",

data: '{}',

processData: false,

success: function (response){alert(response.d);},

failure: function (response) {

alert(2);

},

error: function (response) {

alert(response.d)

}

});

}

　

Introduction to Database Files in SQL Server 2008

Today, I’m going to talk about the files associated with a SQL Server 2008 database. When we create a new database in SQL Server, there are a few files that are created by default. What are those? What is the use of those files? These are the questions I always come across, especially from beginners.

The database files are physical files on the underlying file system. They are having specific file extensions, which I’m going to talk about below.

There are two types of files that are created by default:

Data File:This file contains the actual data saved into the table in a database. However, we can sub-divide this category into two:
- Primary Data File: Default data file for the database, also known as Primary Data File. It is having (.mdf) extension and default size of 2 MB. Primary data file cannot have size less than 2 MB. This is created by the SQL Server be default even if we don’t mention the file specification in our database creation script. It contains the database data as well as pointers to other data file i.e. secondary data files, if any. Every database can have one and only one primary data file (.mdf). It is created on the primary file group by default.
- Secondary Data File: Contains data but is optional to have this file. It is having (.ndf) extension. We can have multiple secondary data file in a database. By default, it is created on primary file group. However, we’ve liberty to create it on secondary file group as well, which is the normal convention followed by DBAs.
Transaction Log: Contains most of the T-SQL statements fired against the database. It is very useful to recover a database up to certain point in time. However, don’t misinterpret that it contains all the T-SQL statements fired against a database. There are certain T-SQL statements, which are not logged into this file. However, I would like not to get into details of that over here, may be into another blog. Also, we don’t use it as a backup option. We have separate backup mechanism in SQL Server. We can create this file either on primary file group or secondary file group. By default, this is created on primary file group. We can omit specification for transaction log file during database creation. However, SQL Server will create a transaction log file for you in that case, giving it size of 1 MB by default.

Types of Functions in SQL Server 2008

Today, I’m going to talk about types of functions in the SQL Server 2008 over here. At first, I would like to mention that we may categorize functions into various different types of categories based upon parameters, which we are considering for categorization. However, in general we can categorize SQL Server functions into two broad categories:

1. Built-In Functions: As it says, these functions are ready-made. SQL Server has offerred us set of functions, which we can just call and make use of it. However, we can further sub-categorize these functions into four sub-categories:

Aggregate Functions: I’m sure everyone is aware of this category. These fucntions accept group of values and return a single, summarized value. We usually use these functions either into SELECT or GROUP BY clause. Example, Avg, Min, Max, Count, etc.
Ranking Functions: This category is unique one as it gives a row number to a row in a group or partition of rows. These functions are very useful and handy when we are doing some sort of statistical analysis with data. Example, RANK, DENSE_RANK, NTILE and ROW_NUMBER.
Rowset Functions: Return an object that we can use in place of a table reference i.e WHERE clause or in a subquery. Example, FREETEXTTABLE, OPENDATASOURCE, OPENQUERY, OPENXML, etc.
Scalar Functions:The word ‘Scalar’ says it all. It return a single value and, of course, it operates on a single value only. Here, we can again sub-categorize the Scalar functions into ten subcategories:
- String Functions: Operate on char or varchar input types and return a single value of either string or numetic data type.
- Mathematical Functions: Operate on numetic data type input values and return a numeric value.
- Data & Time Functions: Operate on date and time data type and return either string or numetic or date and time data type value.
- System Functions: Though, these are built-in function but we may keep them over here based upon their return type. Return values, objects or setting in an instance of SQL Server.
- Cursor Functions: Return information about a cursor.
- Security Functions: Return information about roles or users in SQL Server.
- System Statistical Functions: Return statistical information about the system. Example, @@CPU_BUSY, @@IDLE, etc.
- Text & Image Functions: These functions would be dropped off in coming version of SQL Server. Operate on text and image data type and return information about value.
- Metadata Functions: Return information about database and its objects.
- Configuration Functions: Returns information about the current configuration of an instance of SQL Server.

2. User-Defined Functions: SQL Server offers a feature where users can create their own functions and make use of it inside T-SQL statements. There are three types of user-defined functions:

Scalar Functions: Here again, we’ve got scalar category but user-defined scalar functions, and not built-in ones. It returns a single value and, of course, it operates upon a single input value.
Inline Table-Valued Functions: Return a set of rows as TABLE data type. It can have only single SELECT statement. A user doesn’t have liberty to define the table structure. Whatever SELECT clause returns, returned table takes that structure.
Multistatement Table-Valued Functions: Returns a set of rows as TABLE data type. Unlike Inline Table-Valued function, it can have multiple SELECT statements and user can define the structure of the table being returned.

We may categorize functions as Deterministic and Non-Deterministic as well but, as I mention in the beginning, it requires different set of parameters for categorization. I’ll definitely discuss about Deterministic and Non-Deterministic types but in a separate thread, in another blog. So, follow my blogs to catch it…

Miscellaneous Data Types in SQL Server 2008

Here, I’m going to discuss miscellaneous data types in SQL Server 2008. We can organize the miscellaneous data types in the following 7 types:

Cursor: Used to create a recordset by SQL Server, which is primarily used as OUTPUT parameter to a stored procedure.

HierarchyID: It is system data type and variable-length. It is primarily used to represent position in a hierarchy. This is very useful data type when pulling the data out for hierarchical analysis.

Sql_Variant: This stores values of various SQL Server supported data types. This data type has been introduced to cater advanced and complex database design issues.

Table: It is used to stored a result set or rows and manipulate later on. This has been introduced to counter cursor data type, which, in most cases, degrades performance.

Timestamp: Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

Uniqueidentifier: It is a 16-byte GUID. There are two ways to initialize a variable of this data type. It is always unique across SQL Server instance.

XML: It stores XML type of data.