Export DataTable to Excel and Download file

public void ExportToSpreadsheet(DataTable table, string name)

{

var resp = Response;

resp.Clear();

foreach (DataColumn column in table.Columns)
{
   resp.Write(column.ColumnName + ",");
}

resp.Write(Environment.NewLine);

foreach (DataRow row in table.Rows)

{

for (int i = 0; i < table.Columns.Count; i++)
{   resp.Write(row[i].ToString().Replace(",", string.Empty) + ",");
}

resp.Write(Environment.NewLine);

}

resp.ContentType = "text/csv";

resp.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".csv");

resp.End();

}

}

IQueryable Vs IEnumerable In LINQ

IQueryable inherited IEnumerable, so it obviously get all the functionality IEnumerable has. The working style of both is still different. There are still lot many differences exists between the two which impact the decision we took in usage of either one. Both of these suits for particular scenarios. Following are some differences using which we can took decision to use anyone of these optimally.

Parameter	IQueryable	IEnumerable
Extension Methods	Extension methods defined for IQueryable take expression objects. Means, the delegate IQueryableextension methods receives is an expression tree.	Extension methods defined for IEnumerable take functional objects. Means, the delegate IEnumerable extension methods receives is a method to invoke.
Great For	IQueryable allows out-of-memory things like remote data source operations, such as working with database or web service.	IEnumerable is great for working with sequences (in-memory collections), that are iterated in-memory.
Supported Queries	IQueryable supports LINQ to SQL queries.	IEnumerable supports LINQ to Object and LINQ to XML queries.
Deferred Execution	IQueryable supports lazy loading (Suited for scenarios like paging and composition based queries).	IEnumerable lost lazy loadnig ability on the external provider.
Moving between items	IQueryable provides many methods to move between the items.	IEnumerable doesn’t have the concept of moving between items, it is forward only collection.
Filtering Mechanism	IQueryable executes query on server side along with all the filters applied.	IEnumerable executes select query on server side, loads data in-memory and then executes filter at client side.
CreateQuery and Execute Methods	IQueryable has these two additional methods. Both takes expression as input. CreateQuery returns IQueryable representing expression tree. Execute returns result of the query.	NA
Custom Querying Capability	IQueryable provides additional functionality to implement custom querying with LINQ.	IEnumerable does not have custom querying capability.
Performance Perspective	IQueryable gives best performance when used for out-of-memory data store operations (that is external to .NET CLR memory).	IEnumerable gives best performance when used to manipulate in-memory collections.

Failed to map the path '/'. Problem with VS 2012 ASP.Net rdlc reports page

We have this problem in VS 2012 when running on Window 7.
Try running visual studio environment as administrator, then you wont get this error.
process:

1. Right-click the application, and then click Properties.
2. Click the Compatibility tab.
3. Select the Run this program as an administrator checkbox, and then click OK. 

Where are my IIS Logs?

Although you may have bucket loads of logging taking place in your web applications/sites, you may still feel the need to make use of the IIS 6.0 logs. Use the following steps to track down these hidden away log files:

1. Open Internet Information Services (IIS 6.0)
2. Right-click the desired web site and select properties
3. On the Web Site tab, at the bottom is the Active Log Format; select the properties button next to it
4. You'll now have a box that contains the log file directory and the log file name. Together they make the full log path.

In IIS 7.0
Click the desired web site
On the right side in the "IIS" section there will be a logging icon. click it
you will find the log related information as below

In this window you can also change useful settings like what request/response details are recorded in the logs. Some useful options not selected by dfault are bytes sent, bytes received and referrer (useful for tracking 404s in certain cases).

How to format Currency (Pound/Dollar/Euro etc) using C#/VB.NET/Csharp

Formatting Currency in C#/VB.NET/Csharp Writeline function

You can use .NET's Composite Formatting to format the currency as shown below

Double Incentive = 234.45;
Console.WriteLine("The agreed incentive in local currency is {0:C} ", Incentive);


If you want to convert it into dollars/pounds etc you need to change the culture info :

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US", false);
Console.WriteLine("The agreed incentive in USD is {0:C} ", Incentive);


Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB", false);
Console.WriteLine("The agreed incentive in GBP is {0:C} ", Incentive);

The above code needs the following directives

1. using System.Threading;
2. using System.Globalization;
   

Database Schema definetion

Today's post is about how we can get schema definition of the current db and how we can get tables names, its columns names, Pk & Fk columns

to get the schema definition of Db

SELECT f.name AS ForeignKey,
SCHEMA_NAME(o.schema_id) AS SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName,
f.update_referential_action_desc AS UpdateAction,
f.delete_referential_action_desc AS DeleteAction
FROM sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
JOIN sys.objects o
ON f.parent_object_id = o.object_id
ORDER BY SchemaName ASC,TableName ASC,ColumnName ASC

To get the list of Tables in Database

select TABLE_NAME from information_schema.tables where Table_Type = 'BASE TABLE' order by TABLE_NAME

To get the columns name, Data type, Length, Null able of a Table

select Column_Name, data_Type, character_maximum_length,IS_nullable
from information_schema.columns
where Table_Name = ''

Get Pk Columns of a Table

EXEC sp_Pkeys 'Table name'

To get the Fks of a table you can use the upper most query

To get the all Sps of the data base use the query

SELECT *
FROM sys.procedures;

To get only user defined Sps use the following query

Select * from sys.procedures where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

To get all User defined function from database, use query below

SELECT name AS [UDFName]
,create_date as [CreationDate]
,modify_date as [ModificationDate]
,type_desc as [FunctionType]
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'
ORDER BY [UDFName]

To get any Constraints on Table use the following query

EXEC sp_helpConstraint 'Table Name'

Customize search results and show in Find result window

Some times you annoyed about the VS show search results like entire file path scroll over to see the name of the file and resultant words

Here’s what you can do.
Note: These involve modifying registry settings. Please use at your own risk!
You do not need to restart VS

1. Go to HKCU\Software\Microsoft\VisualStudio\9.0\Find
2. Add a new string called Find result format with a value of $f$e($l,$c):$t\r\n where

$f is the filename
$e is the extension
$l is the line
$c is the column
$t is the text on the line

Now let’s take a look at that Find Results window again:

And here’s the full list of items you can specify in the registry

Files
$p path
$f filename
$v drive/unc share
$d dir
$n name
$e .ext

Location
$l line
$c col
$x end col if on first line, else end of first line
$L span end line
$C span end col

Text
$0 matched text
$t text of first line
$s summary of hit
$T text of spanned lines

Char
\n newline
\s space
\t tab
\\ slash
\$ $

Remove items from the recent project OR recent file menu.

Do you ever notice? when you open a VS solution or a file in VS, it is added in the File->Recent Files or File->Recent Projects menu. what if i change the solution name or move/delete the solution from the directory and go to the VS and try to open the solution while it is actually moved to some other directory or deleted and the following message is shown, quite some times fighting. What to do it should not be in the Recent file/project list

"The project file or web has been moved or deleted and is not in you computer"

Go to Start->Run->regedit then select the following for project list
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\ProjectMRUList

for files list select

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\FileMRUList

you will see all recently visited project/file of VS. Simply delete any one to have it removed from the list, just restart the VS and check the menu. The same project/file has gone from the list

Note: Must restart VS