Sql server tricks

Update table record with a value from another record in same table

UPDATE Ghms_Res_Details

SET 

Request_For=res2.Request_For,

Res_From=res2.Res_From,

Res_To=res2.Res_To,

No_Of_Days=res2.No_Of_Days,

No_Of_Rooms=res2.No_Of_Rooms,

AC_Opt=res2.AC_Opt,

Book_Reason=res2.Book_Reason,

Pay_By=res2.Pay_By,

No_Of_Adults=res2.No_Of_Adults,

No_Of_Childrens=res2.No_Of_Childrens,

Pref_Loc=res2.Pref_Loc,

Pref_GH=res2.Pref_GH,

Alloc_Loc=res2.Alloc_Loc,

Alloc_GH=res2.Alloc_GH,

Book_Remarks=res2.Book_Remarks,

Emp_From=res2.Emp_From,

Emp_Family=res2.Emp_Family,

Emp_Cadre=res2.Emp_Cadre,

Elig_Group=res2.Elig_Group,

Cost_Center=res2.Cost_Center,

Self_App=res2.Self_App,

Guest_Name=res2.Guest_Name,

Guest_Comp=res2.Guest_Comp,

Guest_Type=res2.Guest_Type,

Guest_Natio=res2.Guest_Natio,

Guest_Gender=res2.Guest_Gender,

Guest_Age=res2.Guest_Age,

Med_File_Name=res2.Med_File_Name,

Done_Online=res2.Done_Online,

Base_Res_Code=res2.Base_Res_Code,

Ext_Count=res2.Ext_Count,

L1_Code=res2.L1_Code,

L1_Name=res2.L1_Name,

L1_Email=res2.L1_Email,

L2_Code=res2.L2_Code,

L2_Name=res2.L2_Name,

L2_Email=res2.L2_Email,

App_Rem=res2.App_Rem,

App_Date=res2.App_Date,

Hotel_ID=res2.Hotel_ID,

Confirm_By=res2.Confirm_By,

Confirm_Date=res2.Confirm_Date,

Confirm_Remarks=res2.Confirm_Remarks,

Confirm_Remarks_NA=res2.Request_For,

CheckIn_Date=res2.CheckIn_Date,

CheckOut_Date=res2.CheckOut_Date,

CheckIn_By=res2.CheckIn_By,

CheckIn_Upd_Date=res2.CheckIn_Upd_Date,

CheckOut_By=res2.CheckOut_By,

CheckOut_Upd_Date=res2.CheckOut_Upd_Date,

ECO_Flag=res2.ECO_Flag,

ECO_Remarks=res2.ECO_Remarks,

NoShow_By=res2.NoShow_By,

NoShow_Date=res2.NoShow_Date,

NoShow_Rem=res2.NoShow_Rem,

Cancel_By=res2.Cancel_By,

Cancel_Date=res2.Cancel_Date,

Cancel_Rem=res2.Cancel_Rem,

Feedback_Mail_Flag=res2.Feedback_Mail_Flag,

Trip_ID=res2.Trip_ID,

Yatra_Mail_Flag=res2.Yatra_Mail_Flag,

IsWinHMS=res2.IsWinHMS,

Temp_ID=res2.Temp_ID

FROM Ghms_Res_Details,(select * from Ghms_Res_Details where Res_Code='R454563') as res2

WHERE Ghms_Res_Details.Res_Code='R454562'

How to get only numeric column values?

SELECT column1 FROM table WHERE ISNUMERIC(column1) = 1

Remove space from the Text

SELECT REPLACE(REPLACE(' 11,
sector -2, kharghar, navi mumbai 410210 ', CHAR(13), ''), CHAR(10), '')


Remove time from date

select  CONVERT(date, getdate())

comma separated value

DECLARE @Gate_Desc VARCHAR(8000)
SELECT @Gate_Desc = ISNULL(@Gate_Desc + ',', '') + Gate_Desc
FROM [dbo].[Master_Gate]
ORDER BY Gate_Desc
SELECT @Gate_Desc AS Gate_Desc

Replace Multiple Spaces in String with Single Space

Select REPLACE(REPLACE(REPLACE(@str,' ','{}'),'}{',''),'{}',' ')

Cursor
DECLARE @varSrNo AS NUMERIC,
@varResCode AS VARCHAR (15),
@varExist AS INT,
@MainRecord as varchar(15)='null',
@DummyRecord as varchar(15)='null';
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT Res_Code,isnull(Temp_Res_Code,'') Temp_Res_Code FROM  Ghms_Res_Details WHERE Temp_ID = 2000003582 AND Res_Status = 0 and Temp_Res_Code is null
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @MainRecord,@DummyRecord
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ID : '+ @MainRecord +', Dummy : '+@DummyRecord
FETCH NEXT FROM @MyCursor
INTO @MainRecord,@DummyRecord
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

SET vs SELECT

SET	SELECT
Can only assign one variable at a time. SET @Index = 1 SET @LoopCount = 10 SET @InitialValue = 5	Can assign values to more than one variable at a time. SELECT @Index = 1, @LoopCount = 10, @InitialValue = 5
When assigning from a query and the query returns no result, SET will assign a NULL value to the variable. DECLARE @CustomerID NCHAR(5) SET @CustomerID = 'XYZ' SET @CustomerID = (SELECT [CustomerID] FROM [dbo].[Customers] WHERE [CustomerID] = 'ABC') SELECT @CustomerID -– Returns NULL	When assigning from a query and the query returns no result, SELECT will not make the assignment and therefore not change the value of the variable. DECLARE @CustomerID NCHAR(5) SET @CustomerID = 'XYZ' SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers] WHERE [CustomerID] = 'ABC' SELECT @CustomerID –- Returns XYZ
When assigning from a query that returns more than one value, SET will fail with an error. SET = (SELECT [CustomerID] FROM [dbo].[Customers]) Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.	When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned more than one row. SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers] -- No error generated

MVC vs Sharepoint

ASP.NET MVC	Sharepoint Product
Less Salary	More Salary
More Jobs	Less Jobs
More development time	less development time
long term technology	-

Interface

namespace Interface
{
    interface IBankAccount
    {
        bool Deposit(decimal amount);
        bool Withdraw(decimal amount);
        decimal Balance { get; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            // Declare an interface instance
            IBankAccount savingAccount = new SavingAccount();
            Console.WriteLine("Saving Account");
            savingAccount.Deposit(200);
            savingAccount.Withdraw(300);
            savingAccount.ToString();

            IBankAccount currentAccount = new CurrentAccount();
            Console.WriteLine("Current Account");
            currentAccount.Deposit(500);
            currentAccount.Withdraw(200);
            currentAccount.ToString();
            Console.ReadLine();
        }
    }
    public class SavingAccount : IBankAccount
    {
        private decimal _balance;
        private decimal _perDayLimit;
        public bool Deposit(decimal amount)
        {
            _balance += amount;
            return true;
        }
        public bool Withdraw(decimal amount)
        {
            if (_balance < amount)
            {
                Console.WriteLine("Insufficient balance!");
                return false;
            }
            else if (_perDayLimit + amount > 5000) //limit is 5000
            {
                Console.WriteLine("Withdrawal attempt failed!");
                return false;
            }
            else
            {
                _balance -= amount;
                _perDayLimit += amount;
                Console.WriteLine(String.Format("Successfully withdraw: {0,6:C}", amount));

                return true;
            }
        }
        public decimal Balance
        {
            get { return _balance; }
        }

        public override string ToString()
        {
            return String.Format("Saving Account Balance = {0,6:C}", _balance);
        }
    }
    public class CurrentAccount : IBankAccount
    {
        private decimal _balance;
        public bool Deposit(decimal amount)
        {
            _balance += amount;
            return true;
        }
        public bool Withdraw(decimal amount)
        {
            if (_balance < amount)
            {
                Console.WriteLine("Insufficient balance!");
                return false;
            }
            else
            {
                _balance -= amount;
                Console.WriteLine(String.Format("Successfully withdraw: {0,6:C}", amount));

                return true;
            }
        }
        public decimal Balance
        {
            get { return _balance; }
        }
        public override string ToString()
        {
            return String.Format("Current Account Balance = {0,6:C}", _balance);
        }
    }
}

When to use IEnumerable, ICollection, IList and List

IEnumerable uses IEnumerator internally.

Whenever we pass IEnumerator to another function ,it knows the current position of item/object.

Whenever we pass IEnumerable collection to another function ,it doesn't know the current position of item/object OR current state.

IEnumerator have one Property current and two methods Reset and MoveNext.

IEnumerable have one method GetEnumerator()

If you want to loop through with the collection one by one and you are not interested in the current cursor position then Ienumerable is nice option.

If you are keen to know the current position of object then should go with IEnumerator.

I think that the question when to use IEnumerable, ICollection, IList or List is a common one that hasn’t often being answered in an easy way. I not only want to do this within this article, but I also want to give you some further information to understand the things more deeply.

If you get the understanding for the principle, you’ll be automatically really confident when you have to do this decision for the next time.

If you only want to know when to use which type, scroll down and have a look at the table providing the scenarios and the relevant types. I strongly recommend reading of the entire article to get a deeper understanding.

Let’s first take a look at the individual types and more importantly its members. It’s generally a good idea to have a look at the types in question if you want to decide which type you should use in a specific situation in your code.

IEnumerable

First of all, it is important to understand, that there are two different interfaces defined in the .NET base class library. There is a non-generic IEnumerable interface and there is a generic type-safe IEnumerable<T> interface.

The IEnumerable interface is located in the System.Collections namespace and contains only a single method definition. The interface definition looks like this:

public interface IEnumerable { IEnumerator GetEnumerator(); }

The GetEnumerator method must return an instance of an object of a class which implements the IEnumerator interface. We won’t have a look at the definition of the IEnumerator interface this time, but if you are interested, please visit the official msdn documentation.

It is important to know that the C# language foreach keyword works with all types that implement the IEnumerable interface. Only in C# it also works with things that don’t explicitly implement IEnumerable or IEnumerable<T>. I believe you have been using the foreach keyword many times and without worrying about the reason why and how it worked with that type.

IEnumerable<T>

Let’s now take a look at the definition of the generic and type-safe version called IEnumerable<T> which is located in the System.Collections.Generic namespace:

public interface IEnumerable<out T> : IEnumerable { IEnumerator<T> GetEnumerator(); }

As you can see the IEnumerable<T> interface inherits from the IEnumerable interface. Therefore a type which implements IEnumerable<T> has also to implement the members of IEnumerable.

IEnumerable<T> defines a single method GetEnumerator which returns an instance of an object that implements the IEnumerator<T> interface. We won’t have a look at this interface this time. Please take a look at the official msdn documentation if you would like to get some more information.

ICollection

As you can imagine, there are also two versions of ICollection which are System.Collections.ICollection and the generic version System.Collections.Generic.ICollection<T>.

Let’s take a look at the definition of the ICollection interface type:

public interface ICollection : IEnumerable { int Count { get; } bool IsSynchronized { get; } Object SyncRoot { get; } void CopyTo(Array array, int index); }

ICollection inherits from IEnumerable. You therefore have all members from the IEnumerable interface implemented in all classes that implement the ICollection interface.

I won’t go much into details of the defined methods and properties this time. I just want to let you know about the official description from the msdn documentation:

Defines size, enumerators, and synchronization methods for all nongeneric collections.

ICollection<T>

When we look at the generic version of ICollection, you’ll recognize that it does not look exactly the same as the non-generic equivalent:

?

public interface ICollection<T> : IEnumerable<T>, IEnumerable { int Count { get; } bool IsReadOnly { get; } void Add(T item); void Clear(); bool Contains(T item); void CopyTo(T[] array, int arrayIndex); bool Remove(T item); }

The official msdn documentation looks like this:

Defines methods to manipulate generic collections.

In fact, we have some more methods to add, remove and clear a collection. The way synchronization was implemented differs also. I believe that this happened because the generic version of this interface was introduced with .NET 2.0 whereas the non-generic version was already introduced in .NET 1.1.

IList

The IList interface has of course a non-generic and a generic version. We start with looking at the non-generic IList interface:

?

public interface IList : ICollection, IEnumerable { bool IsFixedSize { get; } bool IsReadOnly { get; } Object this[int index] { get; set; } int Add(Object value); void Clear(); bool Contains(Object value); int IndexOf(Object value); void Insert(int index, Object value); void Remove(Object value); void RemoveAt(int index); }

IList implements ICollection and IEnumerable. In addition it provides method definitions for adding and removing elements and to clear the collection. It also provides methods for handling the positioning of the elements within the collection. It also provides an object indexer to allow the user to access the collection with square brackets like:

?

myList[elementIndex]

IList<T>

Now let’s take a look at the generic version of IList:

?

public interface IList<T> : ICollection<T>, IEnumerable<T>, IEnumerable { T this[int index] { get; set; } int IndexOf(T item); void Insert(int index, T item); void RemoveAt(int index); }

As we mentioned before when discussing the ICollection<T> interface, there are some more methods defined in the ICollection<T> interface than in the ICollection interface. Therefore the member list of the IList<T> interface is a bit shorter than the non-generic equivalent. We only have some new methods for accessing a collection with specific positioning.

Conclusion

Take a look at the following graphic. Not every interface member is displayed, but it should be enough to give you an overview about all types we discussed.

Which type should you depend on?

Now that we have looked at all of the interfaces in question, we are ready to decide which interface we should depend on in a certain situation. Generally, it’s a great idea to depend only on things we really need. I am going to show you how this decision can be made very easily and what you can expect to gain if you do so.

If you use a narrower interface type such as IEnumerable instead of IList, you protect your code against breaking changes. If you use IEnumerable, the caller of your method can provide any object which implements the IEnumerable interface. These are nearly all collection types of the base class library and in addition many custom defined types. The caller code can be changed in the future and your code won’t break that easily as it would if you had used ICollection or even worse IList.

If you use a wider interface type such as IList, you are more in danger of breaking code changes. If someone wants to call your method with a custom defined object which only implements IEnumerable, it simply won’t work and will result in a compilation error.

Generally you should always use that type that provides a contract for only the methods you really use.

The following table gives you an overview of how you can decide which type you should depend on.

Interface	Scenario
IEnumerable, IEnumerable<T>	The only thing you want is to iterate over the elements in a collection. You only need read-only access to that collection.
ICollection, ICollection<T>	You want to modify the collection or you care about its size.
IList, IList<T>	You want to modify the collection and you care about the ordering and / or positioning of the elements in the collection.
List, List<T>	Since in object oriented design you want to depend on abstractions instead of implementations, you should never have a member of your own implementations with the concrete type List/List.

Recommendation

If you are now curious about interfaces, how they work, how to implement them by your own and how their use can improve your design and therefore the quality of your entire code base, I highly recommend watching C# Interfaces by Jeremy Clark on Pluralsight. He manages to explain several really important concepts in a very easily understandable way.