Friday, 8 May 2015

What is View and how to Create it in SQL server ?

In SQL, a view is a virtual table based on the result-set of an SQLstatement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
Creating view:-
1.      Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.
2.      To create a view, a user must have the appropriate system privilege according to the specific implementation.
3.      The basic CREATE VIEW syntax is as follows:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Aletring Views:-
To modify view we sue ALTER keyword :-
ALTER VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];


4.      You can include multiple tables in your SELECT statement in very similar way as you use them in normal SQL SELECT query.

Create A Database Table

USE [Employee]
GO

/****** Object:  Table [dbo].[Employee_Test]  
 Script Date: 08-05-2015 12:59:52 ******/

CREATE TABLE [dbo].[Employee_Test](
       [Emp_ID] [int] NOT NULL,
       [Emp_name] [varchar](100) NOT NULL,
       [Emp_Sal] [int] NULL,
       [Age] [int] NULL,
       [Gender] [nchar](10) NOT NULL,
       [DeptId] [int] NULL
) ON [PRIMARY]

GO


Insert some records

USE [Employee]
GO
INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])
 VALUES (1, N'Anees', 1000, 30, N'Male', 101)
GO
INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])
 VALUES (2, N'Rinku', 1200, 45, N'Male', 102)
GO
INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])
 VALUES (3, N'John', 1100, 25, N'Male', 101)
GO
INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])
 VALUES (4, N'Stephen', 1300, 30, N'Male', 102)
GO
INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])
 VALUES (5, N'Maria', 1700, 45, N'Female', 101)
GO
INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])
 VALUES (6, N'Ankit', 1000, 25, N'Male', 103)
GO
INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])
 VALUES (7, N'Renu', 1200, 30, N'Female', 101)
GO


Select data from Table

Select * from [dbo].[Employee_Test]






















Now Create A View on the above table

CREATE View [dbo].[vw_Emp]
   as
  Select [Emp_ID] as EmpId
        ,[Emp_name] as EmpName
        ,[Emp_Sal] as EmpSalary
        ,[Age] as Age
        ,[Gender] as Gender
        ,[DeptId] as DeptId
            from [Employee].[dbo].[Employee_Test]


And View work like a real table

Select * from [dbo].[vw_Emp] where EmpSalary>1000








Posted by at No comments:

Thursday, 7 May 2015

How to use C# List Class in System.Collections.Generic namespace ?

One of the classes introduced by the System.Collections.Generics namespace is named List. This is probably the most commonly used generic class of the .NET Framework. It is primarily the  System.Collections.Generics equivalent of the System.Collections's ArrayList. It allows you to create a list of any kind of value.

 List < T > Class
The Collection classes are a group of classes designed specifically for grouping together objects and performing tasks on them. List class is a collection and defined in the System.Collections.Generic namespace and it provides the methods and properties like other Collection classes such as add, insert, remove, search etc.

The C# List < T > class represents a strongly typed list of objects that can be accessed by index and it supports storing values of a specific type without casting to or from object.

Syntex how to create List object:-
          List<OblectType> ItemList = new List<OblectType>();
Operations with parameters :-
             ItemList.Add(Object)
             ItemList.InsertAt(Index, Object)
             ItemList.Remove(Object)
             ItemList.RemoveAt(Index)
   int Count=ItemList.Count;
             ItemList.Sort()
             ItemList.ToArray()


Source code:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleAppDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            List_Operations classObj = new List_Operations();
            while (true)
            {
                // Console.Clear();
                Console.WriteLine("\n");
                Console.WriteLine("1. Add an Item");
                Console.WriteLine("2. Insert Item at any position");
                Console.WriteLine("3. Remove Item by using Name");
                Console.WriteLine("4. Remove Item by using Index");
                Console.WriteLine("5. Display List Items");
                Console.WriteLine("6. Sort List Items");
                Console.WriteLine("7. Copy List Items into Array");
                Console.WriteLine("8. Exit");
                Console.Write("Select your choice: ");
                int choice = Convert.ToInt32(Console.ReadLine());
                switch (choice)
                {
                    case 1:
                        classObj.Add();
                        break;

                    case 2: classObj.InsertItem();
                        break;

                    case 3: classObj.RemoveItem();
                        break;

                    case 4: classObj.RemoveItemAt();
                        break;
                  
                    case 5: classObj.Display();
                        break;

                    case 6: classObj.SortList();
                        break;

                    case 7: classObj.CopyToArray();
                        break;

                    case 8: System.Environment.Exit(1);
                        break;
                }
                Console.ReadKey();
            }
        }
    }


    public class List_Operations
    {
        string item;
        List<string> ItemList = new List<string>();

        // Add an Item ti List
        public void Add()
        {
            Console.WriteLine("\nEnter a Item to Add");
            item = Console.ReadLine();
            ItemList.Add(item);
             Console.Write("\nAdded successfully! \n");
        }
         

        //insert an item into List
        public void InsertItem()
        {
             Console.Write("\nEnter the position to insert an Item: \n");
             int index= int.Parse(Console.ReadLine());
             Console.Write("\nEnter the value to Insert: \n");
             string value= Console.ReadLine();
             ItemList.Insert(index, value);
             Console.Write("\nInserted successfully! \n");
        }


        //sort itemms in the List
        public void SortList()
        {
            ItemList.Sort();
            Console.Write("\nAfter shorting ArrryList: \n");
            for(int i=0; i < ItemList.Count-1; i++)
            {
               Console.Write(ItemList[i]);
               Console.Write("\n");
            }
        }

        //remove an item at any given position
        public void RemoveItemAt()
        {
            Console.Write("\nEnter the position to Remove an Item: \n");
            int index= int.Parse(Console.ReadLine());
            ItemList.RemoveAt(index);
            Console.Write("\nItem Removed successfully!: \n");
           
        }

        //remove an item
        public void RemoveItem()
        {
            Console.Write("\nEnter Item name to Remove an Item: \n");
            string item = Console.ReadLine();
            ItemList.Remove(item);
            Console.Write("\nItem Removed successfully!: \n");

        }

        //Copy  items into Array
        public void CopyToArray()
        {
            string[] arr = new string[ItemList.Count];
            arr = ItemList.ToArray();
            Console.Write("\nItem copied successfully!: \n");

        }

        // Display Items in the List
        public void Display()
        {
            Console.Write("\nItems in the List are: \n");
            for(int i=0; i <= ItemList.Count-1; i++)
            {
               Console.Write(ItemList[i]);
               Console.Write("\n");
            }
        }
      
    }
}

Output:-

Listing:- 1






































Continue Listing:- 2



































Continue Listing:- 3



































Continue Listing:- 4



































Continue Listing:- 5

Posted by at No comments:
Subscribe to: Posts (Atom)