Basic Keywords,Funtions,Joins in SQL

Hi Friends .. Take a look ..Hope you will like it


Refrence Northwind database

--Select
--------------
select * from Categories
select * from Suppliers
Select * from Products

--InnerJoin (inner join is same as Join )
------------------------------------------
Select prod.ProductID,prod.ProductName,prod.UnitPrice,sup.CompanyName,sup.City From Products as prod
inner join   Suppliers as sup 
on  prod.productId = sup.SupplierId

--Left Join 
------------------------------------------
Select prod.ProductID,prod.ProductName,prod.UnitPrice,sup.CompanyName,sup.City From Products as prod
left join   Suppliers as sup 
on  prod.productId = sup.SupplierId
--Right Join 
------------------------------------------
Select prod.ProductID,prod.ProductName,prod.UnitPrice,sup.CompanyName,sup.City From Products as prod
Right join   Suppliers as sup 
on  prod.productId = sup.SupplierId
--Full Join 
------------------------------------------
Select prod.ProductID,prod.ProductName,prod.UnitPrice,sup.CompanyName,sup.City From Products as prod
Full join   Suppliers as sup 
on  prod.productId = sup.SupplierId
--Self Join 
--A self-join is a query in which a table is joined (compared) to itself. 
-----------------
Select emp.EmployeeId ,emp.FirstName ,emp.ReportsTo from Employees as emp
Select emp.EmployeeId ,emp.FirstName ,emp.ReportsTo ,emp1.FirstName as Mgr from Employees as emp
 Left outer join  employees as emp1
on  emp1.EmployeeId =emp.ReportsTo 

--UNION
--The SQL UNION Operator
--The UNION operator is used to combine the result-set of two or more SELECT statements.
--Notice that each SELECT statement within the UNION must have the same number of columns. 
--The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
------------------------------------------
Select CategoryId,CategoryName from Categories
Union 
Select SupplierId,CompanyName from Suppliers

--Select Into
-- use to Create Temp data in temp Table 
-- Temp table is started By '# ' Keyword Like #TableName
--------------------------------------------------------
Select * Into Categories in anilTest
from Categories
drop Table #Categories1

--Select Into
-- use to Create Temp data in temp Table 
-- Temp table is started By '# ' Keyword Like #TableName
--------------------------------------------------------
Select * Into Categories in anilTest
from Categories
drop Table #Categories1

select * Into #Categories 
From Categories
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
select * from #Categories 
--Constraint
--------------------------------------------------------
Alter Table #Categories
Add Unique (Categoryid)

Alter Table #Categories
Drop Constraint Unique Categoryid
ALTER TABLE #Categories
DROP PRIMARY KEY
--Nth Highest salary
------------------------
SELECT distinct emp1.Extension from Employees emp1
Where 3 =(Select Count (Distinct(emp2.Extension))From Employees emp2 Where  emp2.Extension<= emp1.Extension)
---
Select distinct Top 1 Extension From (Select distinct Top 3 Extension From Employees Order by  Extension Desc) Employees Order by Extension Asc

--SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions
--------------------------------------------------------
Select * from Suppliers
SELECT CompanyName,ContactName*(City+ISNULL(Country,0))FROM Suppliers
--DateTime Functions
--Function         Description
--GETDATE()     Returns the current date and time
--DATEPART()     Returns a single part of a date/time
--DATEADD()     Adds or subtracts a specified time interval from a date
--DATEDIFF()     Returns the time between two dates
--CONVERT()     Displays date/time data in different formats
---------------------------------------------------------------

--Some other Funtion 
-----------------------
SELECT FORMAT(column_name,format) FROM table_name

Builing Msbuild Projects/Solution by CMD/DOS

Note: MSBuild.exe should be in  your system path.
1. open Command Prompt
2.locate the path "%systemroot%\Microsoft.NET\Framework\version_Installed(You need to put Framework version here)\MSBuild.exe


3.Put the path of your project/solution(proj/sln) and then Press Enter .

thats it .. Check the  Build status on CMD Screen

Constructor Chaining in CSharp

We starts with basics....
Basically ,it is a mehtod in the calss which executed when its object is created. We put the initialization code in the constructor. Creating a constructor in the class is pretty simple.
look at the following sample :

public class mySampleClass
{
    public mySampleClass()
    {
    // This is the constructor method.
    }
// rest of the class members goes here.
}

When the object of this class is instantiated this constructor will be executed automatically .
Like this :

// At this time the code in the constructor will // be executed
mySampleClass obj = new mySampleClass()

Constructor Overloading :

C# supports overloading of constructors, that means we can have constructors with different set of parameters. So our class can be like this :

public class mySampleClass
{
    public mySampleClass()
    {
    // no parameter constructor method.// First Constructor
    }
    public mySampleClass(int Age)
    {
    // constructor with one parameter.// Second Constructor
    }

    public mySampleClass(int Age, string Name)
    {
    // constructor with two parameters.// Third Constructor
    }

// rest of the class members goes here.
}

Well, note here that call to the constructor now depends on the way you instantiate the object.
For example :

mySampleClass obj = new mySampleClass()
// At this time the code of no parameter // constructor (First Constructor) will be executed

mySampleClass obj = new mySampleClass(12)
// At this time the code of one parameter // constructor(Second Constructor) will be // executed.
The call to the constructors is completely governed by the rules of the overloading here.

Calling Constructor from another Constructor:
You can always make the call to one constructor from within the other.
Say for example :
public class mySampleClass
{
    public mySampleClass(): this(10)
    {
    // No parameter constructor method.// First Constructor
    }

    public mySampleClass(int Age)
    {
    // Constructor with one parameter.// Second Constructor}
    }
}

Very first of all let us see what is this syntax :    

public mySampleClass(): this(10)

Here this refers to same class, so when we say this(10), we actually mean execute the public SampleClass(int Age) method.The above way of calling the method is called initializer. We can have at the most one initializer in this way in the method.
Another thing which we must know is the execution sequence i.e., which method will be executed when. Here if I instantiate the object as 

mySampleClass obj = new mySampleClass()
Then the code of public mySampleClass(int Age) will be executed before the code of mySampleClass(). So practically the definition of the method : 

public mySampleClass(): this(10)
{
    // This is the no parameter constructor method.// First Constructor
}

is equivalent to :
public mySampleClass()
{
    mySampleClass(10)
     // This is the no parameter constructor method.// First Constructor
}

This is sometimes called Constructor chaining.

Types of Design Patterns

Hi Frnds
There are 23 design patterns that are used in the application development.These are divied into thre sub groups: Creational,Structural and Behavioral.

Creational

Creational patterns is used when object construction and referencing are the concerns.In short,the responsibility of instantiating instances of objects from the client, by which keeping the code loosely coupled and the responsibility of creating complex objects in one place having the Single Responsibility and
Separation of Concerns principles.
The patterns which comes  in the Creational group:
Abstract Factory: Provides an interface to create families of related objects.
Factory: Enables a class to delegate the responsibility of creating a valid object.
Builder: Enables various versions of an object to be constructed by separating the construction
for the object itself.
Prototype: Allows classes to be copied or cloned from a prototype instance rather than creating
new instances.
Singleton: Enables a class to be instantiated once with a single global point of access to it.

Structural

Structural patterns is used when the composition and relationships of objects to fulfill the needs of
larger systems.
The patterns which comes  in the Structural group:
Adapter: Enables classes of incompatible interfaces to be used together.
Bridge: Separates an abstraction from its implementation, allowing implementations and
abstractions to vary independently of one another.
Composite: Allows a group of objects representing hierarchies to be treated in the same way
as a single instance of an object.
Decorator: Can dynamically surround a class and extend its behavior.
Facade: Provides a simple interface and controls access to a series of complicated interfaces
and subsystems.
Flyweight: Provides a way to share data among many small classes in an efficient manner.
Proxy: Provides a placeholder to a more complex class that is costly to instantiate.

Behavioral

Behavioral patterns is used when the communication between objects in terms of responsibility and
algorithms. The patterns in this group encapsulate complex behavior and abstract it away from the
flow of control of a system, thus enabling complex systems to be easily understood and maintained.
The patterns which comes  in the Behavioral group:
Chain of Responsibility: Allows commands to be chained together dynamically to handle a
request.
Command: Encapsulates a method as an object and separates the execution of a command
from its invoker.
Interpreter: Specifies how to evaluate sentences in a language.
Iterator: Provides a way to navigate a collection in a formalized manner.
Mediator: Defines an object that allows communication between two other objects without
them knowing about one another.
Memento: Allows you to restore an object to its previous state.
Observer: Defines the way one or more classes can be alerted to a change in another class.
State: Allows an object to alter its behavior by delegating to a separate and changeable state
object.
Strategy: Enables an algorithm to be encapsulated within a class and switched at run time to
alter an object’s behavior.
Template Method: Defines the control of flow of an algorithm but allows subclasses to override
or implement execution steps.
Visitor: Enables new functionality to be performed on a class without affecting its structure.

These all are the patterns which are used in the aplication development.
Happy Coding :)

How to Debug Stored Procedures

Debugging Tools and Techniques

Modern development environments contain sophisticated tools to help you debug your applications. The Transact-SQL Debugger in Visual Studio will help you to identify and fix problems in your code. However, even if your development environment does not support the Transact-SQL Debugger (if you do not have Visual Studio 2005), there are techniques you can employ to achieve the same results.

Transact-SQL Debugger in Visual Studio 2005

This section demonstrates the use of the Transact-SQL Debugger from Visual Studio 2005. The major difference between debugging stored procedures and debugging within other programming languages is that you do not need to run the application to debug a single procedure.

The process for starting the debugger is to point to the stored procedure and invoke the Step Into Stored Procedure command:

1. Open Visual Studio.

2. Select View | Server Explorer.

3. In Server Explorer, right-click to open the context-sensitive menu and choose Add Connection.

4. In the Choose Data Source window, select Microsoft SQL Server and verify that .NET Data Provider for SQL Server is also selected. Click Continue.

5. In the Add Connection window, specify server name, authentication, and database name (as usual, use AssetS).

6. Test the connection and if everything is working, click OK to close each window.

7. Server Explorer now contains a node showing the connection that you have described. It functions like nodes in the Object Browser of Management Studio. Expand the nodes until you find Stored Procedures.

8. Right-click dbo.ap_InventoryProperties_Get_TempTblOuter to display the context-sensitive menu.

9. Choose Step Into Stored Procedure (instead of the usual Execute) and the program will initiate the debugging session.

10. Fill in the input parameters in the Run Stored Procedure window:

11. When you click OK, the program will display a window with the stored procedure in Debugging mode



Fig:A stored procedure in Debugging mode

The Transact-SQL Debugger opens the source code of the procedure and pauses on the first executable statement. A small yellow arrow on the left border marks the position of the statement to be executed next.

Debug Windows

The commands in the Debug menu become enabled, as do several more docked windows displayed as tabbed panes, described here, that enable you to examine the state of the environment.

• Locals window Allows you to scroll through the local variables and parameters of the stored procedure and to see its current contents and data type:

• As the stored procedure's code is executed, the values of variables change. To help you follow the execution, the Transact-SQL Debugger colors the values of variables that were changed in the previous statement. The Locals window allows you to change values of variables interactively during execution of the code (through the context-sensitive menu for the Value column).

• Watch window Has a similar function to the Locals window. You can type, or drag from the code, a Transact-SQL expression to be evaluated in this window. This feature is useful when you want to investigate the values of expressions in If, While, Case, and other similar statements. It is possible to work with up to four Watch windows.

• Output window Displays result sets returned by the Select statement and messages sent from the Print statement
  

• Breakpoints window Allows the user to define code locations (lines) and conditions for pausing execution. However, you can also associate it with some additional conditions. For example, you may want to create a breakpoint that pauses execution when a specified location is reached a specified number of times:
  

• These panes have more than four tabs, but only these mean anything for the Transact-SQL Debugger. The other tabs are used to debug client applications.

Breakpoints

Breakpoints are markers in code that serve to stop execution when certain conditions are met. In the Transact-SQL Debugger, you can use only a very limited set of conditions. Typically, you can only specify a condition in which the execution has reached the position of the breakpoint or in which a breakpoint has been reached a selected number of times. In other .NET languages such as C# and Visual Basic, the condition can apply when a variable changes value, when a Boolean expression is true, or even when execution starts a specified process or thread.

Breakpoints can be set three ways:

• Using the toolbar in the Breakpoints window

• Clicking the left edge of the code window

• Pressing F9 when the cursor is on the line in which you want to place a breakpoint

Visual Studio marks the breakpoint with a big red dot at the beginning of the line. If the dot contains a plus (+), the breakpoint contains additional criteria.

The toolbar and the Debug menu can contain additional commands for managing breakpoints. You can temporarily enable or disable all breakpoints and permanently remove them. A single breakpoint can be removed by clicking the dot or by pressing F9 when the cursor is on the line containing the breakpoint.

Stepping Through Execution

The majority of commands available on the Debug menu target execution control. Most of the time, you will use the Step Into or Step Over command to step through a stored procedure. These commands execute one Transact-SQL statement at a time. The difference between them is in the way they behave when they encounter a nested stored procedure:

• Step Into (F11) Opens the code of the nested stored procedure and lets you step through it.

• Step Over (F10) The nested stored procedure is treated as any other Transact-SQL statement and is executed in a single step.

• Step Out (SHIFT-F11) Enables you to execute the rest of the nested stored procedures without pause and halts only when the stored procedure is completed in the calling stored procedure.

• Run To Cursor (CTRL-F10) Enables you to position the cursor somewhere in the code and to execute everything to that point in a single step. In essence, this command lets you set a temporary breakpoint.

• Continue (FS) Resumes execution of the code until the (original entry) procedure is completed or until the next breakpoint is reached.

• Stop Debugging (SHIFT-FS) Discontinues execution.

Note

Visual Studio menus can be customized to contain different sets of options. If your configuration does not contain options that are mentioned in this section, go to Tools | Customize, open the Commands tab, click the Debug category, and drag commands from the list to the Debug menu. Additionally, you can access these options using keyboard shortcuts even when the commands are not on the menu.

One of my favorite features in the Visual Studio debugger is the ability to continue execution from the position of the cursor. Unfortunately, due to the architecture of the Transact-SQL Debugger, the Set Next Step command is not available. Another cool feature that was originally available in Visual Basic and then missing in early versions of Visual Studio .NET was the ability to modify code on the fly (during debugging).

Unmanaged vs. Managed Code

Unmanaged executable files are binary images of x86 code loaded into memory. The role of the operating system in this case is to load the application into memory and let it run. Although there are some protections regarding memory management and I/O ports, the operating system does not have any idea what the application is doing.

By contrast, managed code is under full control of the operating system. At any point in time, the operating system can stop running the CPU and investigate the runtime state. It can also insert exception handling, array bounds and indexes, traps, garbage collection hooks, and other things. In this way, it mitigates a whole class of security concerns and typical programming errors. All programming languages that support the .NET Framework produce managed code. There are currently over 30 such languages. Several are provided by Microsoft (C#, Visual Basic .NET, managed C++, J#, JScript .NET), while others are developed by third parties (everything from COBOL to Pascal).

Table Manipulation by Javascript

Hi Frnds . Today i am putting some Javascript basic table manipulation code . hope you like it
Delete Row from a table through Java script
Syntax : table Object.delete Row(index) 

function delRow()
{
document.getElementById('myTable').deleteRow(0)
}

Row1 cell1
Row1 cell2


Row2 cell1
Row2 cell2

Insert Row in A table 
Syntax: tableObject.insertRow(index)

function insRow()
{
document.getElementById('myTable').insertRow(0)
}

Row1 cell1
Row1 cell2


Row2 cell1
Row2 cell2

Create Cells in a Table 
Syntax: table Object.cells

function cell()
{
var x=document.getElementById('myTable').rows[0].cells;
alert(x[0].innerHTML);
}

cell 1
cell 2


cell 3
cell 4

Basic Simple Programs for Interview

Hi Friends this time I am sharing you a very simple basic programs that generally asked at the time of interview. I used to asked it some times a very very simple programs ..we know that we can make it out those program but in interview time if you know how to do that then you can make it out very easyly and make a face that you are thinking about it ;) .. but you know already . and write it after some moments . ;) So just take a look before the interview ..!! All the best friends..!! Prog1:Retrun Vowel of Not. Solution: ======

public bool CheckVowel(object sender,KeyEventArgs e)
{
    if(e.KeyCode ==Keys.A||e.KeyCode ==Keys.E||e.KeyCode ==Keys.I||e.KeyCode ==Keys.O||e.KeyCode ==Keys.U)
    {    
    return true;
    }
    else
    return false;
}

Prog 2: Even Or Odd Solution: ======

public string EvenOrOdd( int Num)
{
    if(num%2 ==0)
    retrun "evenNumber";
    else
    return "OddNumber"
}

Prog3:PrimeNumber Soultion: ======

public string PrimeOrNot(int num)
{
    int i = 0;
    for (i = 3; i < num; i++)
    {
        if (num % i == 0)
        {
            Console.WriteLine("{0} is not a prime number", num);
            break;
        }
    }
    if (i == num)
    {
        Console.Writeline("{0} is a prime Number", num);
    }
}

Prog4:Fibonacci Series Solution: =======

public void FibonacciSeries(int number)
{
    int f_0 = 0;
    int f_1 = 1;
    int sum;
    Console.Write("{0} {1} ", f_0, f_1);
    for (int i = 2; i < number; i++)
    {
        sum = f_0 + f_1;
        f_0 = f_1;
        f_1 = sum;
        int value = f_0 + f_1;
        Console.Write("{0} ", value);
    }
}

Prog5: Factorial Solution: =======

public int Factorial(int number)
{
    int value = 1;
    for (int i = 1; i <= number; i++)
    {
        value = value * i;
    }
    return value;
}

Prog:6 Permutation (nPr) Solution: ====== Refrence Solution 5;

public double Permutation(int n, int r)
{
    double nPr = Factorial(n) / Factorial(n - r);
    return nPr;
}
 

Prog:7 Combination(nCr) Solution: ====== Refrence Solution 5;

public double Combination(int n , int r)
{
double nCr = Factorial(n)/Factorial(r)*(Factorial(n-r);
return nCr;
}

Prog8 : Add two numbers without using the plus operator Solution: ======

public int Add (int x , int y )
{
    return x-(-y);
}    

Prog9: Multiply two numbers without usering * operator Solution: =======

public int MulWithOutStarOperator( int x ,int y)
{
    for(int z= 1 ; z<=y ; z++)
    {
        int value = value +x;
    }
    return value;
} 

Prog:10 String Reverse Solution: =======

public string ReverseString(string str)
{
    int strLenght = str.Lenght;
    char strArray[] = new char[strLength];
    for ( int i = 0 ; i

Prog:11 Return multiple values from a function

Solution:
========

public int Add(int a,int b,out int c)
{
     c=a+1;
     return a+b;
}

Description :
---------------
when we call the Add method we will get the sum of the a 
and b and can also use the value of c
like
int d=0;
int e= Add(2,3,d) ;
then the value of e will be 5 and the value of d will be 3.

Lazy Loading in C# 4.0(Object On Demand)

Object on Demand is also called as Lazy loading pattern ,Lazy loading  delay the initialization of object. This is a new feature of C# 4.0 its can be used when we are working with large objects when its not in use. This article will explain you about "Lazy" class.
Suppose Candidate class and EducationProfile class . One candidate can have more than one EducationProfile (like: Bachelors (BBA) ,Master(MBA)). if you want to show the EducationProfile which the respective Candidate.You need to load EducationProfiles associated with that Candidate. If  You are loading a Education Profile it with the respective candidate . you need to initialize a Candidate object and that is suppose to be huge .
For avoiding the situation you can use the Lazy Loading Pattern. Loading of EdutioanProfile will only happen when you will use EducationProfile list . and this will make sure fast action  in comparison to the normal one and performance will also increase.
Explaining example of Candidate and  EducationProfile relationship by Lazy loading:
First we need to create ay classes of Candidate and EducationProfile.

public class EducationProfile
{
    public int Id { get; set; }
    public string Digree { get; set; }
    public DateTime PassingYear { get; set; }
}

//Hope this will give you a little reference of lazy loading 

public class Candiate
{
    public string Name { get; set; }
    public int EducationProfileId { get; set; }
    public List GetAllEducationProfile()
    {
        return educationProileList.value;
    }

    Lazy> educationProileList;

    public Candiate(string name, int id)
    {
        //Initializing Candiate Object
        Name = name;
        EducationProfileId = id;
        educationProileList = new Lazy>(() => { return GetEducationProfileList(id); });
        //Initialization done
    }

    private List GetEducationProfileList(int id)
    {
        //Loading EducationProiles
        List list = new List();
        Parallel.For(100, 110, (int i) =>
        {
            EducationProile educationprofile = new EducationProile();
            educationprofile.Id = i;
            list.Add(educationprofile);
        });
        return list;
    }
}

In the constructor of Candidate class, properties are initializing and declaring educationProileList object, which is generic List of educationProile and filled by GetEducationProfileList method. This method will only call when educationProileList object will be use. Below is main method which shows behavior of lazy loading:

public static void Main(string[] args) 
{
    Candidate candidate = new Candidate("AnyName", 1);
    foreach(EducationProile eduProfile in candiate.GetAllEducationProfile())
    // it will actually load accounts, ie. lazy loading
    Console.WriteLine("Id:{0}",eduProfile.Id);
    Console.Read();
}

Get your Divice Serial Number(ESN)

Hi all.. So this time i got a work to get a Divice serial number for some securites pourpose. it was not a very big effort to getit done .. it was so easy .. i have put one sample application to Get ESN number.. Look into it for your reference.

please check the little application  :)

 //Symbol.ResourceCoordination class
TerminalInfo terminalIfo = new TerminalInfo();
if (!string.IsNullOrEmpty(terminalIfo.ESN))
{
label1.Text = terminalIfo.ESN;
}
else
{
label1.Text = "Not able to Get ESN";
}

Note: You Need To Refer Symbol & Symbol.ResourceCoordination.dll

Error:No Compatible FusionInterface dll Found.

Error:No Compatible FusionInterface dll Found.Expected Version is 4.1.0.4 or higherversion having the format 4.x.x.x

This same error i was getting some days before. but after some effort i check my device control panel and check that those libraries are installed or not

but unfortunately libraries was not there.. :(

Solution: so  i think you got the soltuion . You just need to install Symbol.fusion Libaries (a cab file)

and check it out again .. I think it will work fine .

WiFi Radio Toggle in smart device (Symbol.Fusion dll)

I was working on to Enable/Disable it Wifi on Motorola MC17 device . After some effort I found the solution to enable/Disable it wifi Radio. I am Using Symbol.Fusion dll to get toggle wifi radio. there are some other ways also to Like P/invoke with .Net etc. here I will put both ways . you can take the reference of that. Here I am putting sourcecode for your reference.

public class WiFiRadio
{
    WLAN myCommandModeWlan = null;
    public WiFiRadio()
    {
        InitializeWiFi();
    }
    public void InitializeWiFi()
    {
        try
        {
            myCommandModeWlan = new WLAN(FusionAccessType.COMMAND_MODE);
        }
        catch (OperationFailureException ex)
        {
            System.Windows.Forms.MessageBox.Show("Command mode is in use", "WiFiMgr");
        }
    }
    public void ToggleWiFi(bool enableWiFi)
    {
        try
        {
            if (myCommandModeWlan != null)
            {
                if (enableWiFi)
                {
                    myCommandModeWlan.Adapters[0].PowerState = Adapter.PowerStates.ON;
                }
                else
                {
                    myCommandModeWlan.Adapters[0].PowerState = Adapter.PowerStates.OFF;
                }

            }

        }
        catch (OperationFailureException ex)
        {
            System.Windows.Forms.MessageBox.Show("Command mode is in use", "WiFiMgr");
        }
    }
    public bool GetCurrentStatus()
    {
        if (myCommandModeWlan.Adapters[0].PowerState == Adapter.PowerStates.ON)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}

Note: You Need To Take The Reference Of Symbol.fusion dll

Getting the current version C#

As you know are aware of the version constrain there are four numbers mentioned in the every version.Example : 2.0.1.67 1.Major 2.Minor 3.Build 4.Revision
Here is the example to get the current build version

public void GetCurrentBuildVersion()
{
var version = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version;
view.VersionNumber = version.Major + "." + version.Minor + "." + version.Build + "." + version.Revision;
}