How to get the table size in SQL server 2008

To get the table size in SQL Server 2008, we need to use a system stored procedure viz. sp_spaceused

If we pass Table Name as an argument, it gives the disk space used by the table and some other information like: Number of rows existing in the table, Total amount of reserved space for Table, Total amount of space reserved for table but not yet used, Total amount of space used by indexes in Table.

                                                                       

Example: 

For the ADDRESS table in our database, if we run

  sp_spaceused      'TADDRS'

it will give following result:

name	rows	reserved	data	index_size	unused
TADDRS	4726	392 KB	320 KB	16 KB	56 KB

               

                                               

How to Get the Selected Cells and Rows in the Windows Forms DataGridView Control

Here basically I am showing how to get selected cells/rows in DataGridView Control and use of IEnumerator while accessing them.

                DataGridViewSelectedRowCollection m = dataGrid_ZipList.SelectedRows;

                DataGridViewRow row;

                IEnumerator Enumerator = m.GetEnumerator();

                Enumerator.Reset();

                while (Enumerator.MoveNext())

                {

                    row = (DataGridViewRow)Enumerator.Current;

                    lstPostalCodes.Add(row.Cells["POSTAL_CODE"].Value.ToString());                   

                }

SQL Query to find out the frequency of each element in a column

Suppose we have a table "Person" as:

Id	LastName	FirstName	Age	City
1	Smith	John	33	Newyork
2	Smith	Mike	34	Boston
3	Paul	Ryan	39	Chicago
4	William	Brian	45	Baltimore
5	Rao	Sunita	41	Dayton
6	Zing	Sue	33	Owings Mills
7	Smith	Robert	45	Newyork
8	Paul	Zean	33	Chicago
9	Srivastava	Hemant	33	Baltimore
10	Rao	Venkat	45	Dayton

and we need to find out the frequency of the each age.

   SELECT Age, COUNT(Age)AS Frequency    

   FROM Persons   

   GROUP BY Age   

   ORDER BY    

   COUNT(Age) DESC

We get following result:

Age	Frequency
33	4
45	3
34	1
39	1

Now suppose we want to get the ‘Age’ which occurs more than equal to 2. For this query, we need to add HAVING clause after GROUP BY clause.

SELECT Age, COUNT(Age)AS Frequency

FROM Persons

GROUP BY Age 

HAVING  COUNT(Age) >= 2

That gives result as:

Age	Frequency
33	4
45	3

Selecting Columns in a Data Table in C#

“How to select multiple columns in a Data Table” … This is one of the questions asked by a reader in my article published at C# Corner site:
http://www.c-sharpcorner.com/UploadFile/0f68f2/querying-a-data-table-using-select-method-and-lambda-express/

So, I am showing here how we can do this. Here are three ways by which we could select multiple columns in a DataTable object.

(1) Using LINQ

This is a quite flexible way to do this. In this approach, we could do a lot using LINQ e.g. setting filter criteria over the rows, making joins of the table etc.     

   1:  var query = (

   2:                              from r in dt.AsEnumerable()

   3:                              select new

   4:                              {

   5:                                  SSN = r["SSN"].ToString(),

   6:                                  NAME = r["NAME"].ToString(),

   7:                              }

   8:                          ).ToList();

   9:   

  10:              foreach (var item in query)

  11:              {

  12:                  Console.WriteLine(item.SSN + "\t" + item.NAME);

  13:              }

(2) Using DataView class:

This is also a way to get a the view on Data table.

   1:              DataView view = new DataView(dt);

   2:              DataTable distinctValues = view.ToTable("dt", true, "SSN", "NAME");

   3:   

   4:              foreach (DataRow o in distinctValues.Select())

   5:              {

   6:                  Console.WriteLine("\t" + o["SSN"] + "\t" + o["NAME"]);

   7:              }

(3) Using DataTable.Rows collection

If we use this approach, we don’t have much scope. We could only enumerate the rows, read the rows, edit the rows like that…

   1:              for (int i = 0; i < dt.Rows.Count; i++)

   2:              {

   3:                  Console.WriteLine(dt.Rows[i]["SSN"]+ "\t"+ dt.Rows[i]["NAME"]);

   4:              }

C# DataTable Select Examples - using Lambda Expressions

Querying a Data Table Using Select Method and Lambda Expressions in C# 

For this article, Click the link below. A few days before, I wrote this article for C-Sharp Corner site:

http://www.c-sharpcorner.com/UploadFile/0f68f2/querying-a-data-table-using-select-method-and-lambda-express/

Setting Timeouts in WCF Service-Client side for a long running process

With a WCF service and its client, often I forget which timeout should be set on which side..?
There are four types of timeout parameters which we set in a WCF service:
• closeTimeout (Default value: 00:01:00)
• openTimeout (Default value: 00:01:00)
• receiveTimeout (Default value: 00:10:00)
• sendTimeout (Default value: 00:01:00)

Here is a thumb rule (at least for my need to run a long process :)

To execute a long running process, we need to set “receiveTimeout” on Service side same as “sendTimeout” on the client side.
For example we could set “00:30:00” (30 minutes)

For more details, here are definitions from MDSN:

Client-side Timeouts

On the client side:
1. SendTimeout – used to initialize the OperationTimeout, which governs the whole process of sending a message, including receiving a reply message for a request/reply service operation. This timeout also applies when sending reply messages from a callback contract method.
2. OpenTimeout – used when opening channels when no explicit timeout value is specified
3. CloseTimeout – used when closing channels when no explicit timeout value is specified
4. ReceiveTimeout – is not used

Service-side Timeouts

On the service side:
1. SendTimeout, OpentTimeout, CloseTimeout are the same as on the client
2. ReceiveTimeout – used by the Service Framework Layer to initialize the session-idle timeout which controls how long a session can be idle before timing out.

Lambda Expression over List in C#

Sometimes if you have a set of records in a List, it becomes quite easy to query on list using Lamda Expression. This article exemplify that how we can perform several tasks and queries over list.

Suppose we have a “Person” class that has following members:
class Person
    {
        public string SSN;
        public string Name;
        public string Address;
        public int Age;

        public Person(string ssn, string name, string addr, int age)
        {
            SSN = ssn;
            Name = name;
            Address = addr;
            Age = age;
        }
    }
Now we create a list of the Person objects in which we have to perform several operations like finding a person on certain conditions, removing person’s record etc. These types of operation can be easily performed by using “Lambda Expression”. We create the list and populate them in following way:
List<Person> listPersonsInCity = new List<Person>();

         listPersonsInCity.Add(new Person("203456876", "John",    "12 Main Street, Newyork, NY",      15));
         listPersonsInCity.Add(new Person("203456877", "SAM",     "13 Main Ct, Newyork, NY",          25));
         listPersonsInCity.Add(new Person("203456878", "Elan",    "14 Main Street, Newyork, NY",      35));
         listPersonsInCity.Add(new Person("203456879", "Smith",   "12 Main Street, Newyork, NY",      45));
         listPersonsInCity.Add(new Person("203456880", "SAM",     "345 Main Ave, Dayton, OH",         55));
         listPersonsInCity.Add(new Person("203456881", "Sue",     "32 Cranbrook Rd, Newyork, NY",     65));
         listPersonsInCity.Add(new Person("203456882", "Winston", "1208 Alex St, Newyork, NY",        65));
         listPersonsInCity.Add(new Person("203456883", "Mac",     "126 Province Ave, Baltimore, NY",  85));
         listPersonsInCity.Add(new Person("203456884", "SAM",     "126 Province Ave, Baltimore, NY",  95));

Now we see how we can do various complex operations on the list using one liner simple lambda expression.

(1) Following code retrieves the first two persons from the list who are older than 60 years:
Console.WriteLine("\n-----------------------------------------------------------------------------");
Console.WriteLine("Retrieving Top 2 aged persons from the list who are older than 60 years\n");
foreach (Person person in listPersonsInCity.FindAll(e => (e.Age >= 60)).Take(2).ToList())
{
Console.WriteLine("Name : " + person.Name + " \t\tAge: " + person.Age);
}
The output is:
-----------------------------------------------------------------------------
Retrieving Top 2 aged persons from the list who are older than 60 years
Name : Sue             Age: 65
Name : Winston         Age: 65
-----------------------------------------------------------------------------
(2) Following code checks any person’s age falls between 13 to 19 years:
Console.WriteLine("\nChecking whether any person is teen-ager or not...");
if (listPersonsInCity.Any(e => (e.Age >= 13 && e.Age <= 19)))
{
Console.WriteLine("Yes, we have some teen-agers in the list");
}
The output is:
-----------------------------------------------------------------------------
 Checking whether any person is teen-ager or not...
 Yes, we have some teen-agers in the list
-----------------------------------------------------------------------------

(3) Following code checks whether all the person’s age is greater than Ten years or not:
Console.WriteLine("\nCheking whether all the persons are older than 10 years or not...");
if ( listPersonsInCity.All(e => (e.Age > 10)))
{
Console.WriteLine("Yes, all the persons older than 10 years");
}
The output is:
-----------------------------------------------------------------------------
Cheking whether all the persons are older than 10 years or not...
 Yes, all the persons older than 10 years
-----------------------------------------------------------------------------

(4) Following code gets average of all the person’s age:
Console.WriteLine("\nGetting Average of all the person's age...");
double avgAge = listPersonsInCity.Average(e => e.Age);
Console.WriteLine("The average of all the person's age is: "+ avgAge);
The output is:
-----------------------------------------------------------------------------
 Getting Average of all the person's age...
 The average of all the person's age is: 53.8888888888889
-----------------------------------------------------------------------------

(5) Following code checks whether a person having name 'SAM' exists or not:
Console.WriteLine("\nChecking whether a person having name 'SAM' exists or not...");
if (listPersonsInCity.Exists(e => e.Name == "SAM"))
{
Console.WriteLine("Yes, A person having name  'SAM' exists in our list");
}
The output is:
-----------------------------------------------------------------------------
 Checking whether a person having name 'SAM' exists or not...
 Yes, A person having name  'SAM' exists in our list
-----------------------------------------------------------------------------

(6) Following code checks at what position a person having name 'Smith' exists in the list:
Console.WriteLine("\nChecking the index position of a person having name 'Smith' ...");
int indexForSmith = listPersonsInCity.FindIndex(e => e.Name == "Smith");
Console.WriteLine("In the list, The index position of a person having name 'Smith' is : " + indexForSmith);
The output is:
-----------------------------------------------------------------------------
 Checking the index position of a person having name 'Smith' ...
 In the list, The index position of a person having name 'Smith' is : 3
-----------------------------------------------------------------------------

(7) Following code retrieves  the oldest person in the list:
Console.WriteLine("\nGetting the name of the most aged person in the list ...");
Person p = listPersonsInCity.First(m=> m.Age == (listPersonsInCity.Max(e => e.Age)));
Console.WriteLine("The most aged person in our list is: "+ p.Name +" whose age is: "+ p.Age);
The output is:
-----------------------------------------------------------------------------
 Getting the name of the most aged person in the list ...
 The most aged person in our list is: SAM whose age is: 95
-----------------------------------------------------------------------------

(8) Following code gets the total of all the person’s age:
Console.WriteLine("\nGetting Sum of all the person's age...");
int sumOfAges = listPersonsInCity.Sum(e => e.Age);
Console.WriteLine("The sum of all the persons's age = "+ sumOfAges);
The output is:
-----------------------------------------------------------------------------
 Getting Sum of all the person's age...
 The sum of all the persons's age = 485
-----------------------------------------------------------------------------

(9) Following code skips each person  whose age is less than 60:
Console.WriteLine("\nSkipping every person whose age is less than 60 years...");
foreach (Person pers in listPersonsInCity.SkipWhile(e => e.Age < 60))
{
Console.WriteLine("Name : "+ pers.Name + " \t\tAge: "+ pers.Age);
}

The output is:
-----------------------------------------------------------------------------
 Skipping every person whose age is less than 60 years...
 Name : Sue             Age: 65
 Name : Winston         Age: 65
 Name : Mac             Age: 85
 Name : SAM             Age: 95
-----------------------------------------------------------------------------

(10) Following code retrieves all the person until we find a person with name beginning other than letter ‘S’ :
Console.WriteLine("Displaying the persons until we find a person with name starts with other than 'S'");
foreach (Person pers in listPersonsInCity.TakeWhile(e => e.Name.StartsWith("J")))
{
Console.WriteLine("Name : " + pers.Name + " \t\tAge: " + pers.Age);
}
The output is:
-----------------------------------------------------------------------------
 Displaying the persons until we find a person with name starts with other than 'S'
 Name : John            Age: 15
-----------------------------------------------------------------------------
(11) Following code checks whether all the person have their SSN or not :
Console.WriteLine("\nChecking all the persons have SSN or not ...");
if(listPersonsInCity.TrueForAll(e => e.SSN != null))
{
Console.WriteLine("No person is found without SSN");
}
The output is:
-----------------------------------------------------------------------------
 Checking all the persons have SSN or not ...
 No person is found without SSN
-----------------------------------------------------------------------------

(12) Following code removes all the persons having “SAM” name :
Console.WriteLine("\nRemoving all the persons record from list that have “SAM” name");
listPersonsInCity.RemoveAll(e => (e.Name == "SAM"));
if (listPersonsInCity.TrueForAll(e => e.Name != "SAM"))
{
Console.WriteLine("No person is found with 'SAM' name in current list");
}

The output is:
-----------------------------------------------------------------------------
 Removing all the persons record from list that have "SAM" name
 No person is found with 'SAM' name in current list
-----------------------------------------------------------------------------

(13) Following code searches person having “203456876” SSN :
Console.WriteLine("\nFinding the person whose SSN = 203456876 in the list");
Person oPerson = listPersonsInCity.Find(e => (e.SSN == "203456876"));
Console.WriteLine("The person having SSN '203456876' is : " + oPerson.Name + " \t\tAge: " + oPerson.Age);
The output is:
-----------------------------------------------------------------------------
 Finding the person whose SSN = 203456876 in the list
 The person having SSN '203456876' is : John            Age: 15
-----------------------------------------------------------------------------