Sorting and Searching Using C# Lists

It is a fairly common programming scenario to find ourselves with a list of identical objects. In the past, without adequate support from programming languages, we found ourselves writing a lot of searching and sorting code, and that may have put you off using lists in favour of arrays. All that has changed with C# 2.0 - its implementation of a list makes handling such lists remarkably easy. For example, given the following class Person:

   public class Person

        {

            public int age;

            public string name;

            public Person(int age, string name)

            {

                this.age = age;

                this.name = name;

            }

        }

We can create a list of Person objects and add six people like so:

List<Person> people = new List<Person>();

people.Add(new Person(50, "Fred"));

people.Add(new Person(30, "John"));

people.Add(new Person(26, "Andrew"));

people.Add(new Person(24, "Xavier"));

people.Add(new Person(5, "Mark"));

people.Add(new Person(6, "Cameron"));

C# 2.0's list mechanism provides us with a number of useful methods. Personally, I find ForEach, FindAll and Sort to be very useful. ForEach allows us access to each item in the list. FindAll allows us to search for objects in the list that match a specific condition. Sort allows us to sort the objects in the list. The following code demonstrates how we might use each of these methods:

Console.WriteLine("Unsorted list");

people.ForEach(delegate(Person p) { Console.WriteLine(String.Format("{0} {1}", p.age, p.name)); });

List<Person> young = people.FindAll(delegate(Person p) { return p.age < 25; });

Console.WriteLine("Age is less than 25");

young.ForEach(delegate(Person p) { Console.WriteLine(String.Format("{0} {1}", p.age, p.name)); });

Console.WriteLine("Sorted list, by name");

people.Sort(delegate(Person p1, Person  p2) { return p1.name.CompareTo(p2.name); });

people.ForEach(delegate(Person p) { Console.WriteLine(String.Format("{0} {1}", p.age, p.name)); });

people.Sort(delegate(Person p1, Person  p2) { return p1.age.CompareTo(p2.age); });

Console.WriteLine("Sorted list, by age");

people.ForEach(delegate(Person p) { Console.WriteLine(String.Format("{0} {1}", p.age, p.name)); });

And here is the output that we should expect:

Unsorted list

50 Fred

30 John

26 Andrew

24 Xavier

5 Mark

6 Cameron

Age is less than 25

24 Xavier

5 Mark

6 Cameron

Sorted list, by name

26 Andrew

6 Cameron

50 Fred

30 John

5 Mark

24 Xavier

Sorted list, by age

5 Mark

6 Cameron

24 Xavier

26 Andrew

30 John

50 Fred

Lists are powerful and result in fewer, and more elegant, lines of code. Hopefully this short example has demonstrated their ease and you will find yourself using them in your day-to-day development activities.

 

View all constraint of table in SQL Server.

Query is to view all constraints of the table in SQL Server.

 

 

SELECT * FROM (

Select SysObjects.[Name] As [Contraint Name] ,Tab.[Name] as [Table Name],Col.[Name] As [Column Name]

 From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab

On Tab.[ID] = Sysobjects.[Parent_Obj]

Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID]

Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]

 ) A WHERE A.[TABLE NAME]='TABLENAME'

 

 

 

Thanks,

Siddu

 

P Please consider the environment before printing this e-mail

 

 

Defining SQL Server constraints with TSQL

 

Types of constraints

I focus on four types of constraints: primary key, foreign key, unique, and check. Here's a brief overview of each.

Primary key
This constraint is used to guarantee that a column or set of columns on a table contain unique values for every record in the given table. This lets you ensure data integrity by always being able to uniquely identify the record in the table.

Get SQL tips in your inbox

TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

A table can have only one primary key constraint defined on it, and the rows in the primary key columns cannot contain null values. A primary key constraint can be defined when a table is created, or it can be added later.

This script creates a primary key constraint on a single field when the table is created:

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
 )
 
 

GO

The followings script creates the primary key constraint when the table is created. This method allows you to define a name for the constraint and to create the constraint on multiple columns if necessary.

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] IDENTITY(1,1) NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL, 
       CONSTRAINT pk_SaleID PRIMARY KEY (SaleID)
 
 

)
 
 

GO

This script creates the primary key constraint on the table after it is created:

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] IDENTITY(1,1) NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
 
 

)
 
 

GO
 
 

ALTER TABLE SalesHistory
 
 

ADD CONSTRAINT  pk_SaleID PRIMARY KEY (SaleID)
 
 

GO

Foreign key
This constraint limits the values of columns in one table based upon the values of columns in another table. This link between the two tables requires the use of a "lookup table," which contains the accepted list of values; this list must contain a unique or primary key constraint. After the constraint is established between the two tables, any data modifications to the fields defined in the constraint on the foreign key table will cause a validation to ensure that the data being updated or inserted is contained in the lookup table.

The script in Listing A creates a ProductTypes table, which will serve as the lookup table and the SalesHistory table, which will reference the ProductID in the ProductTypes table. If I had excluded the constraint definition in the table declaration, I could go back later and add it. You can do this with the script in Listing B.

The previous script contains the WITH NOCHECK clause. I use it so that any existing values in the table are not considered when the constraint is added. Any records in the table that violate the newly added constraint will be ignored so that the constraint is created. The constraint will only be applicable to new records entered into the SalesHistory table.

Unique
This constraint guarantees that the values in a column or set of columns are unique. Unique and primary key constraints are somewhat similar because each provide a guarantee for uniqueness for a column or set of columns. A primary key constraint automatically has a unique constraint defined on it.

There are two differences between the constraints: (1) You may have only one primary key constraint per table, yet you may have many unique constraints per table; (2) A primary key constraint will not allow null values but a unique constraint will (although it will only allow one null value per field).

This script creates a unique constraint on the SaleID column when the table is created:

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] NOT NULL UNIQUE,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
 
 

)
 
 

GO

The following script creates a unique constraint on the table at creation, and it allows for constraint naming and for defining the unique constraint on multiple columns if necessary.

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int]  NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL, 
       CONSTRAINT uc_SaleID UNIQUE (SaleID)
 
 

)
 
 

GO

This script creates the unique constraint on the SalesHistory table by altering the table after it has been created:

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
 
 

)
 
 

GO
 
 

ALTER TABLE SalesHistory
 
 

ADD CONSTRAINT  uc_SaleID UNIQUE(SaleID)
 
 

GO

Check
This constraint limits the value range, or domain, in a column. Check constraints check the acceptable values against a logical expression defined in the constraint. These constraints are similar to foreign key constraints in that they both govern the acceptable values for a column or set of columns in a given row in a table. You can create a check constraint at the column or table level. A check constraint on a single column allows only certain values for those columns, while a table check constraint can limit values in certain columns based on values in other fields in the row.

The following script creates a check constraint on the SalePrice column in the SalesHistory table, limiting entries where the SalePrice must be greater than 4. Any attempt to enter a record with the SalePrice present and less than 4 will result in an error.

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int]  NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL CHECK (SalePrice > 4)
 
 

)
 
 

GO

The script in Listing C creates a check constraint on the SalesHistory table, limiting the SalePrice to be greater than 10 and the Product field to have the value Computer. This isn't a very practical constraint, but it does illustrate how you can set constraints on multiple columns for a table. Listing D is effectively the same as the script in Listing C, but it defines the constraint after the table is created.