Free Tools For Developers

Unlike Scott Hanseleman's long list of useful tools, following are just a few free utilities I use most during my work:

.NET related:

• Reflector (http://www.aisto.com/roeder/dotnet/): an excellent tool to view the source code from .NET assemblies.

Web related:

• IE Developer Toolbar Beta (http://www.microsoft.com/downloads/details.aspx?FamilyID=e59c3964-672d-4511-bb3e-2d5e1db91038): an IE add-on to view/edit page's DOM objects, JavaScript, CSS, etc.
  
• FireBug(http://www.joehewitt.com/software/firebug/): a FireFox add-on with similar functions of IE developer toolbar. You can debug JavaScript easily with latest version of 0.4.
  
• Fiddler (http://www.fiddlertool.com/): a traffic monitor tool to view all data transferred between your IE browser and a server.
  

File related:

• WinMerge (http://sourceforge.net/projects/winmerge/): a tool to compare and merge the files.
  
• NotePad++ (http://sourceforge.net/projects/notepad-plus/): a notepad that's much powerful than Microsoft's.

SQL Server 2005 Tips

List All Recent Changes

SELECT * FROM sys.objects WHERE create_date >= '2006-04-01' OR modify_date >= '2006-04-01'

List all Stored Procedures

SELECT * FROM sys.procedures WHERE [type] = 'P' AND is_ms_shipped = 0 AND [name] NOT LIKE 'sp[_]%diagram%'
--Or
SELECT * FROM sys.objects WHERE [type]='p' AND is_ms_shipped=0 AND [name] NOT LIKE 'sp[_]%diagram%'
--Note: 'NOT LIKE' is to skip stored procedures created during database installation.

Delete All User Created Stored Procedures

SELECT 'Drop Procedure ' + name FROM sys.procedures WHERE [type] = 'P' AND is_ms_shipped = 0 AND [name] NOT LIKE 'sp[_]%'

List Schemas Owned By A Login

SELECT * FROM sys.schemas WHERE principal_id = user_id('DBUser')
--Note: To delete a login we need to change owner of the schemas owned by that login

Cross Apply

--Select top 5 quantity of production:
CREATE FUNCTION dbo.GetOrderDetail(@OrderID AS int, @MaxRow)

RETURNS TABLE AS
RETURN
SELECT TOP(MaxRow) * FROM OrderDetails WHERE OrderID = @OrderID ORDER BY Quantity DESC
GO

SELECT O.OrderID, O.Date, D.ProductName, D.Quantity
FROM Orders AS O CROSS APPLY GetOrderDetail(O.OrderID, 5) AS D

CTE(Common Table Expressions), ROW_NUMBER And RANK

--Efficient Paging:
DECLARE @PageNumber int
SET @PageNumber = 2;
DECLARE @PageSize int
SET @PageSize = 10;
WITH CTE_ORDER (OrderID, TotalAmount, Ranking, PageNumber) AS
(
SELECT O.OrderID, O.TotalAmount,
RANK() OVER (ORDER BY O.TotalAmount DESC) AS Ranking,
CEILING((ROW_NUMBER() OVER (ORDER BY O.TotalAmount DESC)) * 1.0 / @PageSize) AS PageNumber
FROM
(SELECT OrderID, SUM(Amount) AS TotalAmount FROM OrderDetails GROUP BY OrderID) AS O
)
SELECT * FROM CTE_ORDER WHERE PageNumber = @PageNumber
--Row_NUMBER() is incremental and unique but Rank() can be duplicate

--Feb. 2007 Updated: Concatenate column values using CTE
--http://www.projectdmx.com/tsql/rowconcatenate.aspx
;WITH CTE (CategoryID, JoinName, Name, length )
AS
(
SELECT CategoryID, CAST('' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM Products GROUP BY CategoryId
UNION ALL
SELECT p.CategoryId, CAST( JoinName +
CASE WHEN length = 0 THEN '' ELSE ',' END + p.Name AS VARCHAR(8000)),
CAST(p.Name AS VARCHAR(8000)), length + 1
FROM CTE c INNER JOIN Products p ON c.CategoryID = p.CategoryID
WHERE p.Name > c.Name
)
SELECT CategoryId, JoinName
FROM ( SELECT CategoryId, JoinName,
RANK() OVER ( PARTITION BY CategoryID ORDER BY length DESC) AS Ranking
FROM CTE) AS r
WHERE r.Ranking = 1

Configure Firewall Setting with Netsh

Check machine firewall setting:
Netsh firewall show state
Netsh firewall show config
Netsh firewall show allowedprogram
Netsh firewall show portopening

If firewall blocks access to the SQL Server:

Netsh firewall set portopening tcp 445 SQLNP ENABLE ALL
Netsh firewall set portopening tcp 1433 SQL_PORT_1433 ENABLE ALL
Netsh firewall set portopening udp 1434 SQLBrowser enable ALL

Check Connection Status

Select * --P.spid, P.status, P.program_name, P.cmd
FROM
master.dbo.sysprocesses P with (nolock) JOIN
master.dbo.sysdatabases D with (nolock) ON P.dbid = D.dbid
WHERE D.Name = 'Northwind'


Efficiently Get Total Row Number

SELECT rowcnt FROM sysindexes WHERE OBJECT_NAME(id) = 'NorthWind'
AND indid IN (1,0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1

SQL Server Tips

Ordering varchar Column Numerically

SELECT * FROM ProductSales WHERE CustormerID = '12345'
ORDER BY Description, CASE WHEN QuantityOfItems LIKE '%[^0-9]%' THEN 9E99 ELSE CAST(QuantityOfItems AS INTEGER) END

Only Compare Date Without Time

DECLARE @selectedDate datetime
SET @selectedDate = '04/20/2006'
SELECT * FROM ProductSales WHERE datediff(day, @selectedDate, PurchaseDate) = 0
--Get sales on current day:
SELECT * FROM ProductSales WHERE PurchaseDate >= dateadd(day, datediff(day, 0, getdate()), 0)
--Get sales on last 24 hours:
SELECT * FROM ProductSales WHERE PurchaseDate > DateAdd(d,-1,GetDate())

Insert Data Returned From Stored Procedure Into a Table

INSERT INTO ProductAnalysis EXEC('spGetProductsByTime "2005/1/1", "2005/12/31"')

Select And Insert Into New Table

INSERT INTO OrdersBackup(Customer, OrderDate, ShippingCost)
SELECT Customer, OrderDate, ShippingCost FROM Orders;

Copy Table Definition

SELECT * INTO OrdersBackup FROM Orders WHERE 1 IS NULL
--Following will also copy data:
SELECT * INTO OrdersBackup FROM Orders

Identity Handling

SET IDENTITY_INSERT Industry ON
INSERT Department(DepartmentID, Name, Description) Values(1, 'ABC', 'BCD')
SET IDENTITY_INSERT Industry OFF
GO

Delete FROM Department
DBCC CHECKIDENT('Department', RESEED, 0)
Set IDENTITY_INSERT Department OFF
INSERT Department (Name) Values ('IT') -- DepartmentID = 1

Handling Null Field/Parameter

SELECT * FROM Users WHERE LastNam LIKE IsNull(@LastName,'%')
SELECT * FROM Users WHERE LastNam LIKE COALESCE(@LastName,'%')
SELECT COALESCE(BusinessPhone, CellPhone, HomePhone) AS Phone From Users

Change Column Data Type

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers' AND COLUMN_NAME = 'Notes' AND DATA_TYPE = 'varchar' )
ALTER TABLE Customers ALTER COLUMN Notes TEXT
--or:
IF (SELECT type_name(xtype) FROM syscolumns
WHERE id = object_id('tblname') AND name = 'colname'
ALTER TABLE Customers ALTER COLUMN Notes TEXT

Case When

SELECT Country = CASE
WHEN CountryCode = 1 THEN 'USA
WHEN CountryCode = 2 THEN 'CANADA'
ELSE 'Other' END
FROM Users

SELECT CASE CountryCode
WHEN 1 THEN 'USA'
WHEN 2 THEN 'CANADA'
ELSE 'Other' END AS Country
FROM Users

SELECT OrderID, SUM(Quantity), SUM
(CASE DiscountID
WHEN DiscountID IS NOT NULL THEN Quantity
ELSE 0 END
) AS DiscountQuantity
FROM Sales GROUP BY OrderID

SELECT FirstName, LastName, RegisterDate FROM Users ORDER BY CASE
WHEN CountryCode = 1 THEN 2
WHEN CountryCode = 2 THEN 1
ELSE 3 END

Multi-Value in One Parameter

CREATE PROCEDURE TestParameters
@idList nvarchar(500)
AS
DECLARE @sql nvarchar(520)
SET @sql = 'SELECT * FROM Products WHERE id IN (' + @idList + ')'
EXEC (@sql)
GO

--Note: potential SQL injection issue with above command.

Table Insertion Trigger

CREATE TRIGGER [dbo].[OrderInsertTrigger]
On [dbo].[OrderDetails]
FOR INSERT
AS
BEGIN
DECLARE @OrderID int, @TotalItem int
SELECT @OrderID = OrderID FROM INSERTED
SELECT @TotalItem = TotalItem FROM Orders WHERE OrderID = @OrderID
IF @TotalItem IS NULL
SET @TotalItem = 1
ELSE
SET @TotalItem = @TotalItem + 1
UPDATE Orders SET TotalItem = @TotalItem WHERE OrderID = @OrderID
END

Select 10 Random Rows From A Table

SELECT TOP 10 * FROM Orders ORDER BY newid()

Interact With Shell Commands

--First you need to turn on xp_cmdshell option:
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
--Inserting c:\data.txt data into a temp table:
CREATE TABLE #tmp(line varchar(2000))
INSERT INTO #tmpEXEC xp_cmdshell 'more <>

Delete Data From SQL Server Tables

Deleting huge set of data from a table in SQL Server by DELETE command is costly, because the deletion is logging each row in the transaction log, and it consumes noticeable resources and locks. Use TRUNCATE table command instead if you want to quickly deleting the data without locking the table and writing the log file. It took almost 1 minute to delete 2-million records in a test database with DELETE command, and only 1 second with TRUNCATE command.

Note that it's possible to rollback the data after DELETE command is executed, but not for TRUNCATE command.

Oracle Database Acess in .NET

I wanted to import a Oracle database backup to a test environment and used .NET to talk with it.

First import the database backup:
C:> imp system/orcl@odbdev file= dbbackup.dmp fromuser=orms touser=orms

Configure .NET Data Provider for Oracle:
Data Source: Oracle Database (Oracle Client);
Data Provider: .NET Framework Data Provider for Oracle;
Server Name: DBServer (This is the service name configured in tnsname.ora which is set using oracle client tools)
User Name:[User]
Password: [Password]

Connection String:

<add key="OracleDBConnString" value="user ID=[USER];Password=[PASSWORD];data source=ODBDEV;" />

Both Oracle and Microsoft make their Oracle data providers available for free. Microsoft Oracle data provider is available in .NET 1.1 and .NET 2.0 framework, but it still requires Oracle client software installed; Oracle Data Provider for .NET (ODP.NET) is included with the Oracle database installation. The recommendation is use ODP.NET since it's optimized for Oracle by Oracle, and we did find ODP.NET running faster than Microsoft's in our test.

To use ODP.NET, you need to add reference of Oracle.DataAccess.dll from GAC, import the name space, and the rest is identical to the regular ADO.NET with SQL Server:

using System.Configuration;
using Oracle.DataAccess.Client;

public class DAL
{
    public static DataTable GetAllUsers()
    {
         DataTable dtUser = new DataTable();
         string connString= ConfigurationSettings.AppSettings("OracleDBConnString");
         string sqlText = "SELECT * FROM Users";
         try
         {      
             using (OracleConnection conn = New OracleConnection(connString))
             {
                  OracleAdapter adapter= new OracleDataAdapter(sqlText, conn);
                  adapter.Fill(dtUser);
             }
         }
         catch (Exception ex)
         {
             ErrorLog.Write(ex);
         }

         return dtUser;
    }
}

Concatenate Generic String List To A String

How to convert .NET 2.0 Generic List<string> to a string like "str1, str2,..."? Of course we can loop through each items inside the generic list, and add each to a string builder. But that doesn't look very elegant. The easier way is use string.Join static method and Generic ToArray method:

using System;
using System.Collections.Generic;

class Program
{
    static void Main(string[] args)
    {
        List<string> Names = new List<string>();
        Names.Add("Bob");
        Names.Add("Rob");
        string strNames = string.Join(", ", Names.ToArray());
        Console.WriteLine(strNames);
        Console.Read();
    }
}

The result is:

Bob, Rob

.NET DateTime Format String

You can display a DateTime with custom format using DateTime.ToString() or String.Format() methods in .NET. The custom format string follows a set of naming convention such as y for year, M for month, d for day, h for hour 12, m for minute, s for second, and z for time zone. Let's create a simple ASP.NET page to exam the DateTime format string:

    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string dtString = string.Empty;
            DateTime dt = DateTime.Now;
            StringBuilder sb = new StringBuilder();
            sb.Append("y yy yyy yyyy \t--- (Year) "  + dt.ToString("y yy yyy yyyy") + "<br>");
            sb.Append("M MM MMM MMMM \t--- (Month) "  + dt.ToString("M MM MMM MMMM") + "<br>");
            sb.Append("d dd ddd dddd \t--- (Day) "  + dt.ToString("d dd ddd dddd") + "<br>");
            sb.Append("h hh H HH \t--- (Hour) "  + dt.ToString("h hh H HH") + "<br>");
            sb.Append("m mm \t\t--- (Minute) "  + dt.ToString("m mm") + "<br>");
            sb.Append("z zz zzz \t--- (Zone) "  + dt.ToString("z zz zzz") + "<br>");

            sb.Append("d \t--- " + dt.ToString("d") + "<br>");
            sb.Append("D \t--- " + dt.ToString("D") + "<br>");
            sb.Append("f \t--- " + dt.ToString("f") + "<br>");
            sb.Append("F \t--- " + dt.ToString("F") + "<br>");
            sb.Append("g \t--- " + dt.ToString("g") + "<br>");
            sb.Append("G \t--- " + dt.ToString("G") + "<br>");
            sb.Append("m \t--- " + dt.ToString("m") + "<br>");
            sb.Append("r \t--- " + dt.ToString("r") + "<br>");
            sb.Append("s \t--- " + dt.ToString("s") + "<br>");
            sb.Append("u \t--- " + dt.ToString("u") + "<br>");
            sb.Append("U \t--- " + dt.ToString("U") + "<br>");
            sb.Append("y \t--- " + dt.ToString("y") + "<br>");

            sb.Append("yyyy-MM-dd \t\t--- " + dt.ToString("yyyy-MM-dd") + "<br>");
            sb.Append("yyyy-MM-dd HH:mm \t--- " + dt.ToString("yyyy-MM-dd HH:mm") + "<br>");
            sb.Append("yyyy-MM-dd HH:mm:ss \t--- " + dt.ToString("yyyy-MM-dd HH:mm:ss") + "<br>");
            sb.Append("yyyy-MM-dd HH:mm tt \t--- " + dt.ToString("yyyy-MM-dd HH:mm tt") + "<br>");
            sb.Append("yyyy-MM-dd H:mm \t--- " + dt.ToString("yyyy-MM-dd H:mm") + "<br>");
            sb.Append("yyyy-MM-dd h:mm \t--- " + dt.ToString("yyyy-MM-dd h:mm") + "<br>");
            sb.Append("ddd, dd MMM yyyy HH:mm \t--- " + dt.ToString("ddd, dd MMM yyyy HH:mm") + "<br>");
            sb.Append("dddd, dd MMMM yyyy HH:mm \t--- " + dt.ToString("dddd, dd MMMM yyyy HH:mm") + "<br>");

            lblDateTimeFormat.Text = sb.ToString();
        }
    }

Result:

y yy yyy yyyy  --- (Year) 6 06 2006 2006
M MM MMM MMMM  --- (Month) 1 01 Jan January
d dd ddd dddd  --- (Day) 18 18 Wed Wednesday
h hh H HH      --- (Hour) 9 09 21 21
m mm           --- (Minute) 12 12
z zz zzz       --- (Zone) -5 -05 -05:00
d  --- 1/18/2006
D  --- Wednesday, January 18, 2006
f  --- Wednesday, January 18, 2006 9:12 PM
F  --- Wednesday, January 18, 2006 9:12:36 PM
g  --- 1/18/2006 9:12 PM
G  --- 1/18/2006 9:12:36 PM
m  --- January 18
r  --- Wed, 18 Jan 2006 21:12:36 GMT
s  --- 2006-01-18T21:12:36
u  --- 2006-01-18 21:12:36Z
U  --- Thursday, January 19, 2006 2:12:36 AM
y  --- January, 2006
yyyy-MM-dd           --- 2006-01-18
yyyy-MM-dd HH:mm     --- 2006-01-18 21:12
yyyy-MM-dd HH:mm:ss  --- 2006-01-18 21:12:36
yyyy-MM-dd HH:mm tt  --- 2006-01-18 21:12 PM
yyyy-MM-dd H:mm      --- 2006-01-18 21:12
yyyy-MM-dd h:mm      --- 2006-01-18 9:12
ddd, dd MMM yyyy HH:mm    --- Wed, 18 Jan 2006 21:12
dddd, dd MMMM yyyy HH:mm  --- Wednesday, 18 January 2006 21:12

In reality a DateTime value is often passed through with a formatted string, and is converted back to DateTime at some point. The safest way for parsing a DateTime string is using the TryParseExact method:

string dtString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
DateTime curDate;
DateTime.TryParseExact(dtString, "yyyy-MM-dd HH:mm:ss", DateTimeFormatInfo.InvariantInfo, 
                 DateTimeStyles.None, out curDate); 

.NET 2.0 Yield Usage

The .NET equivalent of Java Iterator is called Enumerators. Enumerators are a collection of objects which provide "cursor" behavior moving through an ordered list of items one at a time. Enumerator objects can be easily looped through by using "foreach" statement in .NET.

Actually .NET framework provides two interfaces relating to Enumerators: IEnumerator and IEnumerable. IEnumerator classes implement three interfaces, and IEnumerable classes simply provide enumerators when a request is made to their GetEnumerator method:

namespace System.Collections
{
    public interface IEnumerable
    {
        IEnumerator GetEnumerator();
    }

    public interface IEnumerator
    {
        object Current { get; }
        bool MoveNext();
        void Reset();
    }
}

.NET 2.0 introduces "yield" keyword to simplify the implementation of Enumerators. With yield keyword defined, compiler will generate the plumbing code on the fly to facilitate the Enumerators functions. Following example demos how to use "yield" in C#:

using System;
using System.Collections;
using System.Collections.Generic;

namespace ConsoleApplication1
{
    public class OddNumberEnumerator : IEnumerable
    {
        int _maximum;
        public OddNumberEnumerator(int max)
        {
            _maximum = max;
        }

        public IEnumerator GetEnumerator()
        {
            Console.WriteLine("Start Enumeration...");
            for (int number = 1; number < _maximum; number++)
            {
                if (number % 2 != 0)
                    yield return number;
            }
            Console.WriteLine("End Enumeration...");
        }

        public static IEnumerable<int> GetOddNumbers(IEnumerable<int> numbers)
        {
            Console.WriteLine("Start GetOddNumbers Method...");
            foreach (int number in numbers)
            {
                if (number % 2 != 0)
                    yield return number;
            }
            Console.WriteLine("End GetOddNumbers Method...");
        }
    }

    class Program
    {
        public static void Main(string[] args)
        {
            OddNumberEnumerator oddNumbers = new OddNumberEnumerator(10);
            Console.WriteLine("Loop through odd number under 10:");
            foreach (int number in oddNumbers)
            {
                Console.WriteLine(number);
            }

            Console.WriteLine();
            int[] numbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
            Console.WriteLine("Using method with yield return:");

            foreach (int number in OddNumberEnumerator.GetOddNumbers(numbers))
            {
                Console.WriteLine(number);
            }
            Console.ReadLine();
        }
    }
}

Result:


Notice we are using two approaches to loop through odd numbers. In the first approach where "yield return" is used in IEnumerable.GetEnumerator, there's no space allocated to hold the odd numbers, and they are all generated on the fly, which is very efficient when dealing with a large list of items (think about if we want to print out or save all 32-bit odd numbers in our example).

Using CrystalReport to Export DataTable With Different Format

We can export data inside a DataTable to an Excel spreadsheet by defining the Response.ContentType:

Response.ContentType = "application/vnd.ms-excel";
string separator = "\t";
foreach (DataColumn dc in dataTable.Columns)
{
       Response.Write(separator + dc.ColumnName );
}
Response.Write("\n");
foreach (DataRow dr in dataTable.Rows)
{
       for (int i = 0; i < dataTable.Columns.Count; i++)
       {
              Response.Write(separator + dr[i].ToString());
       }
       Response.Write("\n"); 
}

What if we need more format such as PDF? You can export data to a few other popular formats by using Crystal's ReportDocument if you have Crystal Reports available:

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.IO;

private void ExportReport()
{
    MemoryStream oStream = new MemoryStream();
    string filename = string.Empty;
    try
    {
        ReportDocument crReport = new ReportDocument();
        string reportFile = "CrystalReport1.rpt";
        string reportPath = string.Format("{0}/{1}", Server.MapPath("."), reportFile);
        crReport.Load(reportPath);

        ReportDataSetTableAdapters.UtilityTableAdapter ad = new ReportDataSetTableAdapters.UtilityTableAdapter();
        ReportDataSet.UtilityDataTable ut = ad.GetAllData();
        crReport.SetDataSource((DataTable)ut);

        switch (SelectedType)
        {
         case "1": //Rich Text (RTF)
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.RichText);
             Response.ContentType = "application/rtf";
             filename = "data.rtf";
             break;
         case "2": //PDF 
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.PortableDocFormat);
             Response.ContentType = "application/pdf";
             filename = "data.pdf";
             //in case you want to export it as an attachment use the line below
             //crReport.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "Your Exported File Name"); 
             break;
         case "3": //MS Word (DOC)
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.WordForWindows);
             Response.ContentType = "application/doc";
             filename = "data.doc";
             break;
         case "4": //MS Excel (XLS)
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.Excel);
             Response.ContentType = "application/vnd.ms-excel";
             filename = "data.xls";
             break;
         default: //PDF 
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.PortableDocFormat);
             Response.ContentType = "application/pdf";
             filename = "data.pdf";
             break;
        }
        //write report to the Response stream
        byte[] dataByte = oStream.ToArray();
        Response.Charset = "";
        Response.AddHeader("content-disposition", "fileattachment;filename=" + filename);
        Response.ContentType = "application/octet-stream";
        Response.BinaryWrite(dataByte);
        Response.Flush();
        Response.Clear();
        Response.End();
    }
    catch (Exception ex)
    {
        ErrorLog.Write(ex);
    }
    finally
    {
        //clear stream
        oStream.Flush();
        oStream.Close();
        oStream.Dispose();
    }
}

Just a side note if you can not import Crystal merge module into your VS2003, then you need to register the dll again:
regsvr32 "C:\Program Files\Common Files\Microsoft Shared\MSI Tools\mergemod.dll"

Check Windows Group Users & Clean-up Network Logon

Often used commands inside Windows domain:

Show users in a local group:
>net localgroup administrators

Show users in a domain group:
>net group All_IT_Staff /domain

Delete credential for a network share
>net use \\shareServerName /del

Delete all credentials for all shares:
>net use * /del

Open Credential manager:
>control keymgr.dll
>rundll32.exe keymgr.dll, KRShowKeyMgr