Passing Multiple-value With XML In SQL Server 2005

The XML functions has been improved a lot in SQL Server 2005. Suppose there's a simple Contacts table:

Contacts(Name varchar(100), Phone varchar(100), Email varchar(100))

The application is sending following XML data to SQL Server for Contacts update (simply string type for ADO.NET parameter):

<Contacts>
   <Contact>
      <Name>Name ABC</Name>
      <Phone>111-222-3333</Phone>
      <Email>abc@abc.com</Email>
    </Contact>
    <Contact>
      <Name>Name BCD</Name>
      <Phone>222-333-4444</Phone>
      <Email>bcd@bcd.com</Email>
    </Contact>
</Contacts>

The stored procedure takes the XML as parameter and parse it internally:

CREATE PROCEDURE [dbo].[Update_ContactsByXml]
(
    @Contacts xml
)
AS
BEGIN
    SET NOCOUNT ON 
    -- Create a temp table to hold the values
    declare @tmpItems table
    (
        Name varchar(100),
        Phone varchar(100),
        Email varchar(100)
    )
    -- Insert all Ids to temp table
    INSERT INTO @tmpItems
    SELECT  TMPXML.Nodes.value('./Name[1]',  'varchar(100)') as Name,
            TMPXML.Nodes.value('./Phone[1]', 'varchar(100)') as Phone,
            TMPXML.Nodes.value('./Email[1]', 'varchar(100)') as Email
    FROM  @Contacts.nodes('//Contacts/Contact') TMPXML (Nodes)
        
    -- Update existing contacts
    UPDATE C SET Phone = T.Phone, Email = T.Email
    FROM @tmpItems T INNER JOIN Contacts C ON T.Name = C.Name

    -- Insert new contacts
    INSERT INTO Contacts 
    SELECT * FROM @tmpItems WHERE Name NOT IN (SELECT Name FROM Contacts)
END

One advantage of using XML to pass multi-value is that it's not vulnerable to SQL injection attack.

A Few Releases

After long time of Beta the final release date of Java 6 (1.6) is confirmed to be released next month, which is a big news for Java community. In the Linux world, Fedora Core 6 was released last month, and its previous version Fedora Core 5 was released less than 7-month ago. Fedora looks like trying to catch up the installation/UI improvement brought by Ubuntu, who announced latest 6.10 release two weeks ago.

I used to work with C/Java in Unix/Linux, but most of my work in recent years is with Microsoft solutions, .NET and C# specifically. Now I pay more attention on MS technologies. So what's new in Microsoft world? Well Microsoft has long product lines, and new releases or updates are just too frequent: Team Foundation Server (TFS) 2006, BizTalk 2006, Office 2007, Windows Server 2008, etc...

What else? Microsoft has just released .NET 3.0 and announced the completion of SharePoint 2007 RTM today!

What's new in .NET 3.0? Mainly four components are added and Microsoft named them foundations:

• Windows Presentation Foundation (WPF): a new user interface subsystem and API based on XML and vector graphics, which uses 3D computer graphics hardware and Direct3D technologies.
• Windows Communication Foundation (WCF): a service-oriented messaging system which allows programs to interoperate locally or remotely similar to web services.
• Windows Workflow Foundation (WF): a workflow engine for task automation and integrated transactions.
• Windows CardSpace: a component securely storing a person's digital identities and provides a unified interface for choosing the identity for a particular transaction, such as logging in to a website.

What's the SharePoint 2007? SharePoint 2007 includes a free version of Windows SharePoint Services (WSS) 3.0, and a not free (actually quite expensive) product called Microsoft Office SharePoint Server (MOSS) 2007. MOSS 2007 is built on top of WSS 3.0 and offers many features that are not included in WSS 3.0.

I happen to have a chance to take some BizTalk 2006/SharePoint 2007 courses during the past 6 months. The course materials were based on the SharePoint 2007 Beta. I am very impressive of how SharePoint technologies can help a business to streamline line their process. Common IT tasks in company, such as creating/editing pages, adding/configuring web parts, configuring people permissions, document management, team collaborations, etc., now can be easily done by business people without IT guys' assistance.

Well you would say, what about developers and IT specialists? In this radically changing world, we need to learn more and more, right? True. New releases and new products indicate changes and new stuff. Change can be scary and you have to face it. But no matter C, Java or C#, Windows or Linux, your existing skills can transition in new technologies in some forms. Those fancy technologies and buzz words can be easily adopted since you have the fundamental of understanding how the computer world works. People living in this world all face too much information, especially in IT world. It would be great if we could filter all information around us efficiently.

Is there a course of "IT 101 - How To Avoid Being Overwhelmed" in school?

.NET TableAdapters Inside TransactionScope

Both TableAdapter and TransactionScope are new in .NET 2.0. Auto-generated TableAdapter and DataTable could play an ORM (Object-relational Mapping) role and act as DAL (Data Access Layer) in an application. TransactionScope wraps the complexity of transaction, and it allows you to put DB interactions inside a transactional block. Using TableAdapter and TransactionScope properly can significantly simplify developers work.

I have just read an article Managing Transactions using TransactionScope that provides an example of using TableAdapter and TransactionScope together:

public static int AddDepartmentWithEmployees(Department dept)
{
    int res = 0;

    DepartmentAdapter deptAdapter = new DepartmentAdapter();
    EmployeeAdapter empAdapter = new EmployeeAdapter();
    using (TransactionScope txScope = new TransactionScope())
    {
        res += deptAdapter.Insert(dept.DepartmentName);
        //Custom method made to return Department ID after inserting the department "Identity Column"
        dept.DepartmentID = deptAdapter.GetInsertReturnValue();
        foreach(Employee emp in dept.Employees)
        {

            emp.EmployeeDeptID = dept.DepartmentID;
            res += empAdapter.Insert(emp.EmployeeName, emp.EmployeeDeptID);
        }
        txScope.Complete();
    }
    return res;
}

The code demos using TableAdapter and TransactionScope to insert a Department record and a collection of Employee records in that department into database, presuming SQL Server 2005 in this case to take advantage of lightweight transaction.

Looks really great and simple. But there's an issue of such implementation: Distributed Transaction Coordinator (DTC) is promoted at run time to complete this transaction. A run time error page will show up if DTC is not properly setup in the server; even with DTC configured, overhead of DTC would introduce big performance issues.

The reason is that each TableAdapter maintains its own database connection, and multiple operations with the same TableAdapter would lead to multiple database connections. A lightweight transaction is used in TransactionScope by default with SQL Server 2005, but DTC is promoted if multiple connections exist inside the same TransactionScope, which is the case in the above example.

We all know the cost of DTC is too expensive. Thus it's recommended to avoid using TableAdapters with TransactionScope, or avoid letting TableAdapters to manage the connections. You should manually and explicitly maintain all connections that are involved in a transaction scope if you have to use them together.

.NET Object Construction Sequence

How's a .NET object constructed and what's the order of initialization of object fields & static fields? Let's do a simple test:

class Program
{
    static void Main()
    {
        new SubClass();
        Console.Read();
    }

    class BaseClass
    {
        Logging baseField = new Logging("BaseClass field initializer");
        static Logging baseStaticField = new Logging("BaseClass static field initializer");

        static BaseClass()
        {
            Logging.Write("BaseClass static constructor");
        }

        public BaseClass()
        {
            Logging.Write("BaseClass constructor");
        }
    }
    class SubClass : BaseClass
    {
        Logging subClassield = new Logging("SubClass field initializer");
        static Logging subClassStaticField = new Logging("SubClass static field initializer");

        static SubClass()
        {
            Logging.Write("SubClass static constructor");
        }

        public SubClass()
        {
            Logging.Write("SubClass constructor");
        } 
    }

    class Logging
    {
        static int count = 1;
        public Logging(string info)
        {
            Write(info);
        }

        public static void Write(string info)
        {
            Console.WriteLine("{0}: {1}", count++, info);
        }
    }
}

Result:

1: SubClass static field initializer
2: SubClass static constructor
3: SubClass field initializer
4: BaseClass static field initializer
5: BaseClass static constructor
6: BaseClass field initializer
7: BaseClass constructor
8: SubClass constructor

The result shows a few interesting things:
1. Derived class field initializer first and base class field initializer next.
2. Class field initializer first and class constructor next.
3. Base class constructor first and derived class constructor next.
4. Static field initializer first and static constructor next.
5. Static constructor first and class constructor next.
5. Derived class static constructor first and base class static constructor next.

What's the reason for such order? Class constructor would reference those fields or static fields thus those fields must be initialized before the class constructor; same as static fields need to be initialized before the static constructor; on the other hand, base class constructor runs before derived class constructor because subclass construction may depend on the the state initialized by the base class, and base class usually has no knowledge about the subclass.

A common mistake is that a class field inialializer is using another non-static field, property or method. For example following code snippet will get compilation error:

public int GetTotalCount()
{
    return  Service.GetTotalCount();
}

// Compile error: "A field initializer cannot reference the non-static field, method, or property..."
private int _totalNum = GetTotalCount();

// Compile error: "An object reference is required for the non-static field, method, or property..."
static int _totalNum = GetTotalCount();  

The easy way to resolve above problem would be making the static GetTotalCount method. You can also initialize the field inside the class constructor if you don't like the static approach.

Observer Pattern In .NET

Observer pattern is a publish-subscribe pattern. It allows an object (observer) to watch another object (subject), and be notified when subject is changed.

Java has defined java.util.Observable class and java.util.Observer interface since SDK 1.0:

public class Observable extends Object
{
  public void addObserver(Observer o) {}
  public void deleteObserver(Observer o) {}
  public void notifyObservers() {}
  //...
}

public interface Observer
{
  public void update(Observable o, Object arg){}
}

By inheriting Observable class and implementing Observer interface, the observer pattern is enforced. There's no Observer and Observable interface/class in .NET BCL. However it's not a difficult task to implement the observer pattern in .NET. Following code demos a simple .NET implementation of observer pattern:

using System;
using System.Text;
using System.Collections;
using System.Threading;

class Program
{
    public interface IObserver
    {
        void Update(string state);
    }

    public interface IObservable
    {
        void AddObserver(IObserver o);
        void NotifyObservers();
    }

    public class WeatherObservable : IObservable
    {
        private volatile int _temperature;
        private ArrayList _observers = new ArrayList();
        private Random rand = new Random();

        public WeatherObservable()
        {
            new Thread(new ThreadStart(WeatherWatcher)).Start();
        }

        public void WeatherWatcher()
        {
            Console.WriteLine("Weather is changing ...");
            while (true)
            {
                _temperature = rand.Next(-20, 40); 
                NotifyObservers();
                Thread.Sleep(1000);
            }
        }

        public void AddObserver(IObserver obj)
        {
            lock (_observers.SyncRoot)
            {
                _observers.Add(obj);
            }
        }

        public void NotifyObservers()
        {
            lock (_observers.SyncRoot)
            {
                foreach (IObserver observer in _observers)
                {
                    observer.Update(_temperature.ToString());
                }
            }
        }
    }

    public class WeatherDisplayer : IObserver
    {
        public void Update(string state)
        {
            int temperature = int.Parse(state);
            Console.WriteLine("Temperature now is {0}", temperature);
        }
    }

    public class WeatherAlarm : IObserver
    {
        public void Update(string state)
        {
            int temperature = int.Parse(state);
            if (temperature < -10)
            {
                Console.WriteLine("\nCold Alert! Temperature is {0}\n", temperature);
            }
            else if (temperature > 30)
            {
                Console.WriteLine("\nHot Alert! Temperature now is {0}\n", temperature);
            }
        }
    }

    static void Main(string[] args)
    {
        WeatherObservable ww = new WeatherObservable();
        ww.AddObserver(new WeatherDisplayer());
        ww.AddObserver(new WeatherAlarm());
        Console.Read();
    }
}

The WeatherObservable class simulates the weather changes, and keeps sending the updated temperatures to observers (subscriber). There are two subscribers in the demo code; one shows the latest temperature and the other shows alert information when temperature is in certain condition. When running the application, a console would look like:

The above code is just a clone of Java implementation. For concise demonstration purpose, not all functions are included and synchronized notification is used.

Actually .NET uses event concept to resolve the publish-subscribe problem. An observable subject publishes its events to its subscribers which registered those events by safe and typed functions (delegate). The implementation of observer pattern can be simplified by using .NET event:

using System;
using System.Text;
using System.Collections;
using System.Threading;

class Program
{
    public class WeatherObservable
    {
        private volatile int _temperature;
        Random rand = new Random();

        public delegate void UpdateDelegate(int temperature);
        public event UpdateDelegate TemperatureUpdateEvent;

        public WeatherObservable()
        {
            new Thread(new ThreadStart(WeatherWatcher)).Start();
        }

        public void WeatherWatcher()
        {
            Console.WriteLine("Weather is changing ...");
            while (true)
            {
                _temperature = rand.Next(-20, 40);

                if (TemperatureUpdateEvent != null)
                {
                    TemperatureUpdateEvent(_temperature);
                }
                Thread.Sleep(1000);
            }
        }
    }

    public class WeatherDisplayer
    {
        public void TemperatureUpdated(int temperature)
        {
            Console.WriteLine("Temperature now is {0}", temperature);
        }
    }

    public class WeatherAlarm
    {
        public static void TemperatureUpdated(int temperature)
        {
            if (temperature < -10)
            {
                Console.WriteLine("\nCold Alert! Temperature is {0}\n", temperature);
            }
            else if (temperature > 30)
            {
                Console.WriteLine("\nHot Alert! Temperature now is {0}\n", temperature);
            }
        }
    }

    static void Main(string[] args)
    {
        WeatherObservable ww = new WeatherObservable();
        WeatherDisplayer displayer = new WeatherDisplayer();
        ww.TemperatureUpdateEvent += new WeatherObservable.UpdateDelegate(displayer.TemperatureUpdated);
        ww.TemperatureUpdateEvent += new WeatherObservable.UpdateDelegate(WeatherAlarm.TemperatureUpdated);
        Console.Read();
    }
}

Note: as shown in the code, both class member functions or static functions can be registered to an event.

Simple iFrame Popup Window

<style type="text/css">
.popup {
        position:fixed;
        clear:both;
        height: 400px;
        width: 600px;
        z-index: 2;
        border: solid;
        background-color: white;
}
.grayBG {
        position: fixed;
        clear:both;
        top: 0px;
        left: 0px;
        right: 0px;
        bottom: 0px;
        overflow: hidden;
        padding: 0;
        margin: 0;
        background-color: #000;
        z-index: 1;
        filter:alpha(opacity=70);
        opacity:0.7;
        -moz-opacity:0.7;
}
</style>

<script language="javascript" type="text/javascript">
    function showDiv(id)
    {
        var div = document.getElementById("divPop");
        div.style.display = "block";
        var divFrame = document.getElementById("divFrame");
        divFrame.src = "PopupPage.aspx?id=" + id;        
        //document.body.style.scrolling = "no";
    }
    function hideDiv()
    {
        var div = document.getElementById("divPop");
        div.style.display = "none";
        //document.body.style.scrolling = "auto";
    } 
</script>

<div id="divPop" style="display: none;">
 <div class="grayBG" runat="server"></div>
 <div runat="server" class="popup">
    <a onclick="hideDiv();"></a><br />
    <iframe id="divFrame" runat="server" width="100%" height="100%">
    </iframe>
 </div>
</div>

HPCBench Now Supports Linux Kernel 2.6.X

I just updated the HPCBench utility last week. It now can work in latest Linux distributions with kernel 2.6.x.

You can visit http://hpcbench.sourceforge.net for more information about HPCBench.

Replacing Tokenized String Using Regular Expression In .NET

using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;

class Program
{
    static void Main(string[] args)
    {
        string text = "Contact name:$NAME$, address:$ADDRESS$, phone:$PHONE$, email:$EMAIL$...";
        Dictionary<string, string> tokenDictionaries = new Dictionary<string, string>();
        tokenDictionaries.Add("NAME", "Rob");
        tokenDictionaries.Add("PHONE", "123456789");
        tokenDictionaries.Add("EMAIL", "Rob@abc.com");
        Console.WriteLine(ReplaceToken(text, tokenDictionaries));
        Console.Read();
    }

    public static string ReplaceToken(string text, Dictionary<string, string> tokenDictionaries)
    {
        string pattern = @"\$.*?\$";
        Match mc = Regex.Match(text, pattern);
        if (mc.Success)
        {
            while (mc.Success)
            {
                if (!string.IsNullOrEmpty(mc.Value))
                {
                    string tokenName = mc.Value.Substring(1, mc.Length - 2);
                    string tokenValue = string.Empty;
                    if (!string.IsNullOrEmpty(tokenName) && tokenDictionaries.ContainsKey(tokenName))
                        tokenValue = tokenDictionaries[tokenName];
                    text = text.Substring(0, mc.Index) + tokenValue + text.Substring(mc.Index + mc.Length);
                    mc = Regex.Match(text, pattern);
                }
            }
        }
        return text;
    }
}

Result:

Contact name:Rob, address:, phone:123456789, email:Rob@abc.com...

Avoid Table Scan In SQL Server

How SQL Server handles a query exactly? Its query optimizer takes the query, performs some analysis of related objects, and comes up a execution plan. The worst scenario is a full table scan with large amount of data. The full table scan is not efficient because every row in the table will be searched no matter it's qualified or not; it's also not scalable as data grows.

We can use indexing to avoid the full table scan. With indexed column(s), SQL Server will do the binary search (Index Seek) which is log2(N) complexity. A single record search with full table scan against 1 million records results in 1 million of row process, and the same search using indexed column(s) could have maximum of 20 row process (log2(1000000) < 20). It's a big difference when the row number is huge.

An index should include all columns that are involved in the Where clause. Also we need to write our queries carefully so that SQL Server can understand to use the index for searching. Suppose we have an Employee table with FirstName, LastName, StartDate and Country columns. All columns except Country column have been indexed (non-cluster indexing). Following queries will be index-based and fast:

SELELCT * FROM Employee WHERE FirstName = 'Mike'
SELELCT * FROM Employee WHERE FirstName = 'Mike' AND LastName = 'Bauer'
SELELCT * FROM Employee WHERE FirstName = 'Mike' OR LastName = 'Bauer'
SELELCT * FROM Employee WHERE FirstName = 'Mike' AND Country = 'Canada'
SELELCT * FROM Employee WHERE FirstName LIKE 'M%'
SELELCT * FROM Employee WHERE StartDate BETWEEN '20000101' AND '20001231'

But following queries will be less efficient because of the table scan:

SELELCT * FROM Emoloyee WHERE Country = 'Canada'
SELELCT * FROM Emoloyee WHERE FirstName = "Mike' OR Country = 'Canada'
SELELCT * FROM Employee WHERE FirstName LIKE '%M'
SELELCT * FROM Employee WHERE FirstName <> 'Mike'
SELELCT * FROM Employee WHERE StartDate = DATEPART(yyyy, OrderDate) = 2000

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).