Wednesday, January 12, 2011

Retrieving or Limiting the First N Records from a SQL Query

In SQL server 2005, SET ROWCOUNT n has the same behavior as SQL server 2000. It’s recommended to use TOP (n) instead of SET ROWCOUNT n.But in SQL server 2008 you are able to use variable at place of n in TOP (n).

Example:
for RowCount
DECLARE @RowsCount INT
SELECT @RowsCount = 0

SET ROWCOUNT @RowsCount
SELECT * FROM MyTable
SET ROWCOUNT 0

for TOP (n)

DECLARE @RowsCount INT
SELECT @RowsCount = 0

SELECT TOP (@RowsCount ) * FROM MyTable

Wednesday, January 5, 2011

Message Queue in .Net

Queue is nothing but FIFO (First In First Out).

Before going to Message Queue concept, why we need Message Queuing?
Answer is that, we require Message Queue mechanism when we want to store insignificant data which is not required to store and we want to do some operations on it.

For example, we want to accept data from multiple users and we have to do some operations on that data, after that we require writing data to some files. Then in this situation we need not require data to store in database, we just want to do some operations on that data and copy that to some files. This requirement will be fulfilled by Message Queue.

If we take another example, where we have two applications in two different systems. One application will send the data and another will need to process the data.

Here I am explaining second example but two applications are in same system.

To run Message Queue concept in .Net, we need Message Queue service to be installed in our system. By default installation of Operating System, it will not install. To install Message Queue service
Start->settings->control panel ->Add/Remove programs-> Click on Add/Remove window components-> Check Message Queuing ->Click Next button

If you unable install, please refer documentation of corresponding Operating System.

After successful installation of Message Queue, you have to write the code to use it.
Here I am explaining Message Queue by using VB.Net.

In this example we need two applications, one is to store data in Message Queue and another is to retrieve data from Queue.

namespace is "System.Messaging"

Step 1: Push Data in Queue

static void PushDatainQueue(string QueueData, string QueueLable)
{
#region Queue
string strQuePath = string.Empty;
MessageQueue objMessageQueue = new MessageQueue();
strQuePath = @".\Private$\syccrm";

if (MessageQueue.Exists(strQuePath))
//creates an instance MessageQueue, which points
//to the already existing MyQueue
objMessageQueue = new System.Messaging.MessageQueue(strQuePath);
else
//creates a new private queue called MyQueue
objMessageQueue = MessageQueue.Create(strQuePath);
#endregion

#region Message
System.Messaging.Message objMessage = new System.Messaging.Message();
objMessage.Priority = MessagePriority.Normal;
objMessage.Body = QueueData;
objMessage.Label = QueueLable;
#endregion

#region Push Message in Queue
objMessageQueue.Send(objMessage);
#endregion
}

Step 2: Receive Data from Queue

static void ReceiveDataFromQueue(string strQuePath)
{
MessageQueue objMessageQueue = new MessageQueue();
if (MessageQueue.Exists(strQuePath))
objMessageQueue = new System.Messaging.MessageQueue(strQuePath);
else
objMessageQueue = MessageQueue.Create(strQuePath);
Message objMessage = new Message();
objMessageQueue = objMessageQueue.Receive();
String strOperation = objMessageQueue.Label.ToString();
string queueData = objMessageQueue.Body.ToString();
}

Note:for private and public queue syntax
// References public queues.
public void SendPublic()
{
MessageQueue myQueue = new MessageQueue(".\\myQueue");
myQueue.Send("Public queue by path name.");

return;
}

// References private queues.
public void SendPrivate()
{
MessageQueue myQueue = new
MessageQueue(".\\Private$\\myQueue");
myQueue.Send("Private queue by path name.");

return;
}

Tuesday, January 4, 2011

Parallel ForEach on DataTable

i would like to use the new Parallel.ForEach function to loop through a datatable and perform actions on each row.

Example:

Normal foreach loop
foreach (DataRow dr in objDS.Tables[0].Rows)
{
if (!objHT.Contains(dr["MessageKey"].ToString()))
objHT.Add(dr["MessageKey"].ToString(), dr["MessageValue"].ToString());
}

Parallel.ForEach loop for above DataTable
Parallel.ForEach(objDS.Tables[0].AsEnumerable(), dr =>
{
if (!objHT.Contains(dr["MessageKey"].ToString()))
objHT.Add(dr["MessageKey"].ToString(), dr["MessageValue"].ToString());
});

Split the String values with a special character in MS Flow to convert this into Array

 Many times we have a requirement to prepare the Mailing address for some of the documents, suppose there are Address Line1, Address Line2, ...