Find rocord(s) from below table which contains last characters as 'aaa' in Sql?

Find rocord(s) from below table which contains last characters as 'aaa'?

ProductID ProductName
1            Fan aab
2            Fan abc
3            Fan g a
4            Fan g aaa

SELECT     ProductId,SUBSTRING(ProductName, CHARINDEX('aaa',
ProductName, 1), LEN(ProductName)) AS Expr1, CHARINDEX('aaa',
ProductName, 1) AS indexid
FROM         Products
WHERE     (RIGHT(ProductName, 3) = 'aaa')

Result: 4 aaa 7

Write a query for find Rank value/Duplicated values using with condition in sql ?

;with abc(pid,pname,PPID)
as
(
SELECT     ProductID, ProductName,ROW_NUMBER() OVER(partition BY
ProductName ORDER BY ProductName) AS PPID
FROM         Products
)
--DELETE FROM ABC WHERE PID=2
SELECT * FROM ABC

Linq to SQL Like Operator

Linq to SQL Like Operator:

 Using String.StartsWith or String.Endswith

var query = from c in Customers
            where c.City.StartsWith("Lo")
            select c;

var query = from c in Customers
            where c.City.StartsWith("L") && c.City.Contains("n")
            select c;

var query = from c in ctx.Customers

            where SqlMethods.Like(c.City, "L_n%")

            select c; 

NetPeerTCPBinding / Peer to Peer binding in WCF

Peer-2-Peer using WCF NetPeerTCPBinding:

Using WCF NetPeerTcpBinding we can develop a peer-to-peer networking applications that use a TCP-level peer-to-peer mesh infrastructure.

Example for the Peer-to-Peer Application: IM, File Sharing Application, Games etc.

In Traditional Client Server model we have single and 1 to N number of clients making request to the server which is configured in centralized location. 

In Traditional Client/Server application.

In peer to peer (p2p) application each participate (node) acts as both a client and a server to the other participants in the network.

In peer – to- peer network environment peers rely on name resolution systems to resolve each other’s network locations (addresses, protocols, and ports) from names.  Peer to peer name resolution has been complicated by transient connectivity and shortcomings in the Domain Name system (DNS).

The Microsoft windows Peer –to – Peer networking platform solves this problem with the Peer name resolution protocol (PNRP).

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.ServiceModel;

namespace P2pWCF

{

    [ServiceContract]

    public interface IChat

    {

        [OperationContract(IsOneWay=true)]

        void SendMessage(string who, string message);

   

    }

    class ChatImp:IChat

    {

        public void SendMessage(string who, string message)

        {

            Console.ForegroundColor = ConsoleColor.Cyan;

            Console.WriteLine("{0} Says : {1}", who, message);

            Console.ForegroundColor = ConsoleColor.Green;

        }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.ServiceModel;

namespace P2pWCF

{

    class Program

    {

        static void Main(string[] args)

        {

            Console.WriteLine("Enter Your name");

            string name = Console.ReadLine();

            Console.WriteLine("Opening P2P Server host...");

            ServiceHost svc = new ServiceHost(typeof(ChatImp));

            svc.Credentials.Peer.MeshPassword = "paramesh@123";

            svc.Open();

            //Both Server and Client binding should match

            NetPeerTcpBinding np2p = new NetPeerTcpBinding();

            np2p.Security.Mode = SecurityMode.None;

            EndpointAddress ep = new EndpointAddress("net.p2p://ParamiChat");

            IChat pr = ChannelFactory<IChat>.CreateChannel(np2p, ep);

            pr.SendMessage(name,string.Format("{0} Joined the Mesh network.",name));

            string msg=string.Empty;

            while(msg!="Q")

            {

                Console.ForegroundColor=ConsoleColor.Yellow;

                msg=Console.ReadLine();

                pr.SendMessage(name,msg);

            }

            svc.Close();

        }

    }

}

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <system.serviceModel>

    <services>

      <service name="P2pWCF.ChatImp">

        <endpoint address="net.p2p://ParamiChat" binding="netPeerTcpBinding"

                  bindingConfiguration="NewBinding" contract="P2pWCF.IChat"></endpoint>

      </service>    </services>

   

    <bindings>

      <netPeerTcpBinding>

        <binding name="NewBinding">

          <security mode="None"></security>

          <resolver mode="Pnrp"></resolver>

        </binding>

      </netPeerTcpBinding>     

    </bindings>

    <client>

      <remove contract="IMetadataExchanage" name="sb"/>

      <endpoint address="net.p2p://ParamiChat" binding="netPeerTcpBinding"

                bindingConfiguration="NewBinding" contract="P2pWCF.IChat" name="Client">      </endpoint>

     

    </client>

  </system.serviceModel>

</configuration>

index fragmentation in sqlserver, When to reorganize and when to rebuild indexes? How to defragment indexes?

Query shows index fragmentation information:

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'
)
WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 


--> Analyzing the result, you can determine where the index fragmentation has occurred, using the following rules:

    ExternalFragmentation value > 10 indicates external fragmentation occurred for the corresponding index
    InternalFragmentation value < 75 indicates internal fragmentation occurred for the corresponding index


When to reorganize and when to rebuild indexes?

You should "reorganize" indexes when the External Fragmentation value for the corresponding index is between 10-15 and the Internal Fragmentation value is between 

60-75. Otherwise, you should rebuild indexes.


How to do it?
Go to Indexes --> Right click on specific indexes --> we can see Rebuid and Reorganize options.


How to defragment indexes?

1.Reorganize the fragmented indexes: execute the following command to do this
ALTER INDEX ALL ON TableName REORGANIZE

2.Rebuild indexes: execute the following command to do this
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON) 

You can also rebuild or reorganize individual indexes in the tables by using the index name instead of the 'ALL' keyword in the above queries. Alternatively, you can 

also use SQL Server Management Studio to do index defragmentation.

State Design Patterns - Behavioral Patterns in C#

Allow an object to alter its behavior when its internal state changes. The object will appear to change its class

 class Program
    {
        static void Main(string[] args)
        {
            Context c = new Context(new ConcreteStateA());
            c.Request();
            c.Request();
            c.Request();
            Console.ReadKey();
        }
    }

    class Context
    {
        private State _state;

        public State State
        {
            get { return _state; }
            set
            {
                _state = value;
                Console.WriteLine("State: " + _state.GetType().Name.ToString());
            }
        }
        public Context(State state)
        {
            this.State = state;
        }
        public void Request()
        {
            _state.Handle(this);
        }
    }
    abstract class State
    {
        public abstract void Handle(Context context);
    }
    class ConcreteStateA : State
    {
        public override void Handle(Context context)
        {
            context.State =new ConcreteStateB();
        }
    }
    class ConcreteStateB : State
    {
        public override void Handle(Context context)
        {
            context.State = new ConcreteStateA();
        }
    }

State Design Patterns in C#

State Design Patterns - Behavioral Patterns in  C#

State: Allows an object to change its behavior when its internal state changes.

class Program
    {
        static void Main(string[] args)
        {
            Account account = new Account("paramesh");
            account.Deposite(500.0);

            account.Deposite(500.0);
            account.Deposite(300.0);
            account.Deposite(550.0);
            account.PayInterest();
            account.Withdraw(2000.00);
            account.Withdraw(1100.00);

            // Wait for user
            Console.ReadKey();
        }
    }
    abstract class State
    {
        protected Account account;
        protected double balance;

        protected double interest;
        protected double lowerLimit;
        protected double upperLimit;

        public Account Account
        {
            get { return account; }
            set { account = value; }
        }
        public double Balance
        {
            get { return balance; }
            set { balance = value; }
        }

        public abstract void Deposit(double amount);
        public abstract void Withdraw(double amount);
        public abstract void PayInterest();
    }
    class RedState : State
    {
        private double _serviceFee;
        public RedState(State state)
        {
            this.balance = state.Balance;
            this.account = state.Account;
            Initialize();
        }

        private void Initialize()
        {
            interest = 0.0;
            lowerLimit = -100.0;
            upperLimit = 0.0;
            _serviceFee = 15.00;
        }
        public override void Deposit(double amount)
        {
            balance += amount;
            StateChageCheck();
        }

        private void StateChageCheck()
        {
            if (balance > upperLimit)
            {
                account.State = new SilverState(this);
            }
        }

        public override void Withdraw(double amount)
        {
            amount = amount - _serviceFee;
            Console.WriteLine("No funds available for withdrawal!");
        }

        public override void PayInterest()
        {
            //
        }
    }
    class SilverState : State
    {
        public SilverState(State state) : this(state.Balance, state.Account) { }
        public SilverState(double balance, Account account)
        {
            this.balance = balance;
            this.account = account;
            Initialize();
        }

        private void Initialize()
        {
            interest = 0.0;
            lowerLimit = 0.0;
            upperLimit = 1000.0;
        }
        public override void Deposit(double amount)
        {
            balance += amount;
            StateChangeCheck();
        }

        private void StateChangeCheck()
        {
            if (balance < lowerLimit)
                account.State = new RedState(this);
            else if (balance > upperLimit)
                account.State = new GoldState(this);
        }

        public override void Withdraw(double amount)
        {
            balance -= amount;
            StateChangeCheck();
        }

        public override void PayInterest()
        {
            balance += interest * balance;
            StateChangeCheck();
        }
    }
    class GoldState : State
    {
        public GoldState(State state) : this(state.Balance, state.Account) { }
        public GoldState(double balance, Account account)
        {
            this.balance = balance;
            this.account = account;
            Initialize();
        }

        private void Initialize()
        {
            interest = 0.05;
            lowerLimit = 1000.0;
            upperLimit = 1000000.0;
        }
        public override void Deposit(double amount)
        {
            balance += amount;
            StateChangeCheck();
        }

        private void StateChangeCheck()
        {
            if (balance < 0.0)
                account.State = new RedState(this);
            else if (balance < lowerLimit)
                account.State = new SilverState(this);
        }

        public override void Withdraw(double amount)
        {
            balance -= amount;
            StateChangeCheck();
        }

        public override void PayInterest()
        {
            balance += interest * balance;
            StateChangeCheck();
        }
    }



    class Account
    {
        private State _state;
        private string _owner;

        public Account(string owner)
        {
            this._owner = owner;
            this._state = new SilverState(0.0, this);
        }
        public double Balance
        {
            get { return _state.Balance; }
        }
        public State State
        {
            get { return _state; }
            set { _state = value; }
        }
        public void Deposite(double amount)
        {
            _state.Deposit(amount);
            Console.WriteLine("Deposited {0:C}", amount);
            Console.WriteLine("Balance={0:C}", this.Balance);
            Console.WriteLine("Status={0}", this.State.GetType().Name);
            Console.WriteLine("");
        }
        public void Withdraw(double amount)
        {
            _state.Withdraw(amount);
            Console.WriteLine("Withdres {0:C}", amount);
            Console.WriteLine("Balance={0:C}", this.Balance);
            Console.WriteLine("Status={0}\n", this.State.GetType().Name);
        }
        public void PayInterest()
        {
            _state.PayInterest();
            Console.WriteLine("Interest Paid..");
            Console.WriteLine("Balance ={0:C}", this.Balance);
            Console.WriteLine("State={0}\n", this.State.GetType().Name);

        }

    }