find duplicate record and delete

WITH tblTemp as

(

SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)

   As RowNumber,* FROM <table_name>

)

DELETE FROM tblTemp where RowNumber >1

Read More

SQL query to find rows that contain only numerical data

Table fields are ID and Value

Select  value  from tblname where ISNUMERIC(value)=1

To fetch ALTERNATE records from a table. (EVEN NUMBERED nd odd   NUMBERED)

Select * from Images where (ID % 2) = 1 odd

Select * from Images where (ID % 2) = 0 even

What is recursive stored procedure?

SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels.

What is the difference between UNION and UNION ALL?

UNION statement is mainly used to combine the tables including the duplicate rows and UNION ALL combine but does not look for duplicate rows. With this, UNION ALL will be very faster than UNION statements.

Given a table TBL with a field Nmbr that has rows with the following values: 1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1

Write a query to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.

update TBL set Nmbr = case when Nmbr=0 then 2 when Nmbr=1 then 3 end

What is the use of SET NOCOUNT ON/OFF statement?

By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).

Read More

what is entity framework in mvc

Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.

What is inheritance?

inheritance means it s a process of creating a new unit with the help of existing unit and by using inheritance we can get the features from base class to derived class

Difference between double equal to (==) and triple equal to(===)?

Main difference between "==" and "===" operator is that former compares variable by making type correction e.g. if you compare a number with a string with numeric literal, == allows that, but === doesn't allow that, because it not only checks the value but also type of two variable, if two variables are not of same type "===" return false, while "==" return true.

Read More

String Interpolation

This one is a simple but very effective improvement in C# 6 that I love. How many times have you used the String.Format() method like this:
var title = String.Format("{0} ({1})", post.Title, post.Comments.Count);

While these placeholders make it easy to define a template, tracing them with their linked arguments causes a bit of distraction. Here, if you want to visualise the output, you have to look at the first placeholder ({0}), and then look at the first argument (post.Title). Then, you look at the second placeholder, and follow it up with the second argument. Your eyes keep moving from left to right.

C# 6 introduces a beautiful way to write the same code in a more direct way:

1	var title = $"{post.Title} ({post.Comments.Count})"; So, you need to prefix your string with $ and then you can replace placeholders with the actual arguments. Isn’t that cleaner?

Read More

Dictionary Initializers

Before C# 6, we could initialize a Dictionary using the { key, value } syntax:

1 2 3 4 5

var dictionary = new Dictionary<string, string> {      { "LAX", "Los Angeles International Airport" },      { "MEL", "Melbourne Tullamarine Airport" } };

While this syntax works fine, if you’re initializing complex objects with nested structures, all these curly braces get in the way and you may easily lose track of them.

C# 6 introduces a new index-like syntax to initialize dictionaries:

1 2 3 4 5

var dictionary = new Dictionary<string, string> {      ["LAX"] = "Los Angeles International Airport",      ["MEL"] = "Melbourne Tullamarine Airport" };

With this syntax, the extra noise due to curly braces is gone. Plus, keys stand out a bit more.

Read More

Below are some examples of our C# coding standards, naming conventions, and best practices. Use these according to your own needs.

Pascal Casing 

The first letter in the identifier and the first letter of each subsequent concatenated word are capitalized. You can use Pascal case for identifiers of three or more characters.

Camel Casing

The first letter of an identifier is lowercase and the first letter of each subsequent concatenated word is capitalized.

Read More

Recreate the default website in IIS

1. open IIS Manager
2. right click Sites node under your machine in the Connections tree on the left side and click Add Website
3. enter "Default Web Site" as a Site name
4. set Application pool back to DefaultAppPool!
5. set Physical path to %SystemDrive%\inetpub\wwwroot
6. leave Binding and everything else as is

Possible issues:

1. If the newly created web site cannot be started with a message:
   "Internet Information Services (IIS) Manager - The process cannot access the file because it is being used by another process. (Exception from HRESULT: 0x80070020)"
   ...it's possible that port 80 is already assigned to another application (Skype in my case :). You can change binding port to e.g. 8080 by right clicking Default Web Site and selecting Edit Bindings... and Edit.... See Error 0x80070020 when you try to start a Web site in IIS 7.0 for details.
2. Some applications require Default Web Site to have an ID 1. In my case it had ID 1 after recreation. If it's not your case, see Re-create “default Website” in IIS after accidently deleting. It's different for IIS 6 and 7.

---

Note: I had to recreate Default Web Site, because I wasn't able to even open a project configured to run under IIS in Visual Studio. I had a solution with couple of projects inside. One of projects failed to load with an error message:

"The Web Application Project is configured to use IIS. The Web server 'http://localhost:8080/' could not be found."

After I have recreated Default Web Site in IIS Manager, I was able to reload and open that specific project.

Read More

DataReader , DataSet,DataAdapter and DataTable

DataReader

DataReader is used to read the data from database and it is a read and forward only connection oriented architecture during fetch the data from database. DataReader will fetch the data very fast when compared with dataset. Generally we will use ExecuteReader object to bind data to datareader.

DataSet

DataSet is a disconnected orient architecture that means there is no need of active connections during work with datasets and it is a collection of DataTables and relations between tables. It is used to hold multiple tables with data. You can select data form tables, create views based on table and ask child rows over relations. Also DataSet provides you with rich features like saving data as XML and loading XML data.

DataAdapter

DataAdapter will acts as a Bridge between DataSet and database. This dataadapter object is used to read the data from database and bind that data to dataset. Dataadapter is a disconnected oriented architecture. Check below sample code to see how to use DataAdapter in code:

DataTable 

DataTable represents a single table in the database. It has rows and columns. There is no much difference between dataset and datatable, dataset is simply the collection of datatables.

Read More

Given a table SALARIES, such as the one below, that has m = male and f = femalevalues. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.

Id  Name  Sex  Salary

1   A     m    2500

2   B     f    1500

3   C     m    5500

4   D     f    500

UPDATE SALARIES SET sex = CASE WHEN sex ='m' THEN 'f' ELSE 'm' END

Read More