Dictionary in c#

A Dictionary class represents a dictionary in C# that is used to represent a collection of keys and values pair of data. This article demonstrates how to use a dictionary in C#.

Creating a Dictionary

The Dictionary class is a generic class and can store any data types. This class is defined in the System.Collections.Generic namespace. Before you use a Dictionary class in your code, you must import the System.Collections.Generic namespace using the following line.

using System.Collections.Generic;

As other collection classes, the Dictionary class has Add, Remove, RemoveAt and other collection methods. The Key and Value property are used to extract a key and a value from an item in a Dictionary.

Adding data in the dictionary -

Dictionary AuthorList = new Dictionary();

AuthorList.Add("Mahesh Chand", 35);
AuthorList.Add("Mike Gold", 25);
AuthorList.Add("Praveen Kumar", 29);
AuthorList.Add("Raj Beniwal", 21);
AuthorList.Add("Dinesh Beniwal", 84);

Reading data from dictionary -

foreach( KeyValuePair author in AuthorList )
{
Response,Write(author.Key, author.Value);
}

Hashtable in c#

The Hashtable class in C# represents a hashtable. The following code creates a Hashtable:

private Hashtable hshTable = new Hashtable();

Adding Items to a Hashtable

Add method of Hashtable is used to add items to the hashtable. The method has index and value parameters. The following code adds three items to hashtable.

hshTable .Add("Author1", "Mahesh Chand");
hshTable .Add("Author2", "James Brand");
hshTable .Add("Author3", "Mike Gold");


Retrieving an Item Value from Hashtable

The following code returns the value of "Author1" key:

string name = hshTable["Author1"].ToString();

Removing Items from a Hashtable

The Remove method removes an item from a Hashtable. The following code removes item with index "Author1" from the hashtable:

hshTable.Remove("Author1");

Looking through all Items of a Hashtable

The following code loops through all items of a hashtable and reads the values.

// Loop through all items of a Hashtable
IDictionaryEnumerator en = hshTable.GetEnumerator();
while (en.MoveNext())
{
string str = en.Value.ToString();
}

Using Foreach loop -

foreach (DictionaryEntry entry in hshTable)
{
Response.Write(entry.Key);
Response.Write(entry.Value);
}

Generic List in C#

Using a Generic List in C# is an efficient method of storing a collection of variables. And probably the best part is that the List is strongly typed and casting (which degrades performance) will no longer be necessary. Your collection of variables should be of the same data type. Generic Lists were first introduced into the .NET Framework 2.0. Before we begin our Generic List example, first remember to include the correct namespace below:

using System.Collections.Generic;

In our example, we will store the integers 5, 8, 435, 76, 3256, and -85. Our next step is to initialize a List using Generics. Because we are storing integers, our it with be an int Generics List. If you wish to create the List as another variable type, simply replace int with double, decimal, string, or another type of element. We will give our List the name myGenericIntegerList.

ListmyGenericIntegerList = new List();

Now that the List has been created and initialized, we easily add integer elements. Since we stated this list as an int, the input will now be strongly typed to only accept integer values. We will now add the integeres5, 8, 435, 76, 3256, and -85 iin the example below:

myGenericIntegerList.Add(5);
myGenericIntegerList.Add(8);
myGenericIntegerList.Add(435);
myGenericIntegerList.Add(76);
myGenericIntegerList.Add(3256);
myGenericIntegerList.Add(-85);

Your generic int List is now complete! Another advantage to using the generics List is there are helper functions such as average, contains, and counts, and toArray. As brought up by Khalid, the average function is an extension method.

double average = myGenericIntegerList.Average();
bool contains = myGenericIntegerList.Contains(4);
int count = myGenericIntegerList.Count;
int[] intArray = myGenericIntegerList.ToArray();

You can print it out like -
Response.Write(myGenericIntegerList[1]);

For more functions, you can either visit the MSDN and search for “List(T) Methods” or better just give the function a try in Visual Studio and let Intellisense show you other functions.

Pivot Magic in SQL

PIVOT is a relational operator used to provide a more readable or simpler view of a table’s data. This is achieved by rotating a rows data into columns using the query response to simply the query results.

UNPIVOT is the opposite. It rotates columns into rows.

PIVOTING can make the sales data below -













Using PIVOT

Specify the desired values that you want to see in SELECT statement and FROM clause contains the PIVOT statement. Let us take an example

The following query uses ADVENTUREWORKS database which uses the order years as columns.


SELECT
CustomerID,
[2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004
FROM
(
SELECT CustomerID, DATEPART(yyyy, OrderDate) AS OrderYear, TotalDue
FROM Sales.SalesOrderHeader
) AS piv
PIVOT
(
SUM(TotalDue) FOR OrderYear IN([2001], [2002], [2003], [2004])
) AS child
ORDER BY CustomerID


The FROM clause create the values that you want to see in rows of the newly created columns. We used the FOR operator to list the values that we want to pivot in the OrderYear Column.

The Result for the above query will look like the following -

How do I find an object/objects which are referencing a particular table

Recently I have been assigned a task to find out all the tables, stored procs, views where are making reference to a particular table.


Query to find out all the tables which are referencing a particular table -

DECLARE @objectname varchar(256)
SET @objectname = 'TB_CON_LogicalOperator'

SELECT OBJECT_NAME(referenced_id)
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(@objectname)


Query to find out stored procs/views which are referencing a particular table -

DECLARE @objectname varchar(256)
SET @objectname = 'TB_CON_LogicalOperator'

SELECT OBJECT_NAME(referencing_id)
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID(@objectname)


Query to find out views which are referencing a particular table -

DECLARE @objectname varchar(256)
SET @objectname = 'TB_CON_LogicalOperator'

SELECT name
FROM sys.views
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@objectname+'%'

Difference between SCOPE_IDENTITY vs @@IDENTITY vs IDENT_CURRENT

SCOPE_IDENTITY, IDENT_CURRENT,@@IDENTITY functions return last-generated identity values.

In most of our application scenario, we need to get latest inserted row information through SQL query. And for that, we have multiple options like:

# @@IDENTITY
# SCOPE_IDENTITY
# IDENT_CURRENT

All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ.

Compare

@@IDENTITY

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table
which inserts a row in another table with generate an identity
column, then @@IDENTITY returns that identity record which is
created by trigger.


SCOPE_IDENTITY

It returns the last identity value generated for any table in the
current session and the current scope.

Let me explain this... suppose we create an insert trigger on table
which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.


IDENT_CURRENT

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

How do I find a stored procedure containing a particular 'text'?

Recently I have been assigned a task to find out all the stored
procs where we have put hardcoded values like 'AL', 'GL', 'TM' etc.

Checking all the stored procs one by one is a tedious task when
you have a few hundred of them in your application and there is a
possiblity of missing some stored procs where we have hardcoded
these values.

Following query I have used to find out all the stored procs where
we have hard coded all these kinda values.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' 'AL' '%'
AND ROUTINE_TYPE='PROCEDURE'

Similarly, we can check in the functions and views -

SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%AL%'

SQL Paging - ROW_NUMBER function

With the introduction of ROW_NUMBER function in SQL 2005 life is a lot easier.

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH tblOrders AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
,OrderID
,OrderDate
,CustomerID
,EmployeeID
FROM dbo.Orders
)

SELECT *
FROM tblOrders
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY OrderDate
,OrderID;

TIPS for writing good optimized SQL Query

Here are some of the TIPS for writing good optimized
SQL Query (apart from analyzing through EXPLAIN PLAN):

## If you specify 2 tables in the FROM clause of a
SELECT statement, the parser will process the tables
from right to left, so the table name you specify last
will be processed first. In this case you have to
choose one table as driving table. Always choose the
table with less number of records as the driving
table.

## If three tables are being joined, select the
intersection tables as the driving table. The
intersection table is the table that has many tables
dependent on it.

## Never compare NULL to anything else. All
expressions return NULL if one of the operands is
NULL. This is applicable for all operators except
Concatenation operator (||).

## Use DECODE when you want to scan same rows
repetitively or join the same table repetitively.

## Always use table alias and prefix all column
names with the aliases when you are using more than
one table.

## Use NOT EXISTS in place of NOT IN.

## Join Tables in place of using EXISTS through
Sub-Query.

## Use EXISTS in place of DISTINCT.

## Never use NOT on an indexed column. Whenever
Oracle encounters a NOT on an index column, it will
perform full-table scan.

## Never use a function / calculation on an
indexed column. If there is any function is used on
an index column, optimizer will not use index. Use
some other alternative.

## There are three good reasons why it is better
to name the columns in a query rather than to use
"select * from ...".

· Network Traffic is reduced

· The Code is easier to understand

· It could save the need for changes in
future.

## It is also better to use bind variables in
queries. That way the query becomes generic and
therefore re-usable.

## It is a good practice to use a standard syntax
for wiring SQL queries. Incase, the same query has
different standard then Oracle will re-parse the
statement again.
## Combine Multiples Scans with CASE Statements.

SQL Server Convert Datetime function

MS SQL Server 2005 provides CONVERT function to change or convert the DateTime formats.

CONVERT Function syntax:

Convert (datatype, datetime string, date style)

DateType parameter accepts the following types:
nchar, nvarchar, char, varchar, binary or varbinary
DataType length is optional.

DateTime String parameter accepts the datatime variable, datetime type column name or getdate() function to convert the datetime format.

Date Style parameter accepts the style number in which you want to convert the datetime.


Following are the SQL query Syntax to convert DateTime format:

SQL query to convert datetime into mon dd yyyy hh:miAM (or PM) format:

Select convert(varchar, getdate(), 100)
SQL query to convert datetime into Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) format:

Select convert(varchar, getdate(), 101)
SQL query to convert datetime into Default mon dd yyyy format:

Select convert(varchar, getdate(), 109)
SQL query to convert datetime into Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) format:

Select convert(varchar, getdate(), 113)
SQL query to convert datetime into ODBC canonical yyyy-mm-dd hh:mi:ss(24h) format:

Select convert(varchar, getdate(), 120)
SQL query to convert datetime into ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h) format:

Select convert(varchar, getdate(), 121)
SQL query to convert datetime into ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces) format:

Examples -

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

SELECT convert(datetime, '20161023', 112) -- yyyymmdd

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113)
-- dd mon yyyy hh:mm:ss:mmm

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11', 120)
-- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121)
-- yyyy-mm-dd hh:mm:ss.mmm

Style 126 is ISO 8601 format: international standard - works
with any language setting

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126)
-- yyyy-mm-ddThh:mm:ss(.mmm)

SSRS - Unwanted repeatation of text in other columns if the text is too long

I don't know if anyone else has run into this problem. I have a report that I designed in a table format with several groups. The detail row has a field that has no size limits. The field could basically contain pages of text. It only seems to be a problem when the size of of the field is larger than the size of a page when it is
exported to a PDF.

For some reason when the text is too large and field cannot fit on one page nicely it causes the other fields to repeat its text on the next page.

After hours of googling, I figured out a solution which I thought I should share it. I have used rectangles. I have put in a rectangle where I have placed my field and in that rectange I further placed a textbox and in that I have further pasted my field. This is how it stopped repeatation of text of other columns in case the text is too long to fit in one page.