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

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;
}

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());
});

How to write some text in file from Sql Server

alter PROCEDURE spWriteStringToFile
(
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)

--
)
AS
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream


for more information please CLICK HERE

How to read a file from sql server

Create FUNCTION [dbo].[uftReadfileAsTable]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS
@File TABLE
(
[LineNo] int identity(1,1),
line varchar(8000))

AS
BEGIN

DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@String VARCHAR(8000),
@YesOrNo INT

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT


if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'OpenTextFile'
, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII

WHILE @hr=0
BEGIN
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

IF @YesOrNo<>0 break
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='reading from the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT
INSERT INTO @file(line) SELECT @String
END

if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'


if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
insert into @File(line) select @strErrorMessage
end
EXECUTE sp_OADestroy @objTextStream
-- Fill the table variable with the rows for your result set

RETURN
END

Parallel Tasks in .NET 4.0 – Methods that Return value

For methods that return a value, you would need to use the Task(TResult) class which represents an asynchronous operation that can return a value. We will explore how to use this class in this article.

For Example:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Threading;
using System.Threading.Tasks;

namespace URLRoutingDemoApp.Parallel_For
{
public partial class ParallelInvokeWithReturnValue : System.Web.UI.Page
{
static string stValues = string.Empty;
string strTaskResult = string.Empty;
string strTaskResult1 = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
stValues = "";
strTaskResult = "";
strTaskResult1 = "";
//Two Approaches
//Approach 1-> First Initialize the task and then execute
Task Task Task
Task1.Start();
Task2.Start();
Task3.Start();

strTaskResult += "Task1=" + Task1.Result.ToString() + "</br>";
strTaskResult += "Task1=" + Task2.Result.ToString() + "</br>";
strTaskResult += "Task1=" + Task3.Result.ToString() + "</br>";

lblparallelInvokewithreturnvalue.Text = stValues + "::::</br>" + strTaskResult;

//Approach 2-> Initialize and execute task at the same time
stValues = "";
var TaskApp1=Task var TaskApp2 = Task var TaskApp3 = Task
strTaskResult1 += "Task1=" + TaskApp1.Result.ToString() + "</br>";
strTaskResult1 += "Task1=" + TaskApp2.Result.ToString() + "</br>";
strTaskResult1 += "Task1=" + TaskApp3.Result.ToString() + "</br>";

lblparallelInvokewithreturnvalue1.Text = stValues + "::::</br>" + strTaskResult1;
}

static int GenerateNumbers()
{
int i;
for (i = 0; i < 10; i++)
{
stValues += "Method1 - Number " + i.ToString() + " </br>";
Thread.Sleep(1000);
}
return i;
}

static string PrintCharacters()
{
string str = "blog";
for (int i = 0; i < str.Length; i++)
{
stValues += "Method2 - Character " + str[i].ToString() + " </br>";
Thread.Sleep(1000);
}
return str;
}

static int PrintArray()
{
int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8 };
foreach (int i in arr)
{
stValues += "Method3 - Array " + i.ToString() + " </br>";
Thread.Sleep(1000);
}
return arr.Count();
}
}
}

Used the Task(TResult) class to call methods that returned a value.

Parallel Tasks in .NET 4.0

In .NET 4.0, we have a set of new API's to simplify the process of adding parallelism and concurrency to applications. This set of API's is called the "Task Parallel Library (TPL)" and is located in the System.Threading and System.Threading.Tasks namespaces.

For Example:
using System.Threading;
using System.Threading.Tasks;

public partial class ParallelInvoke : System.Web.UI.Page
{
static string stValues = string.Empty;

protected void Page_Load(object sender, EventArgs e)
{
stValues = "";
Parallel.Invoke(new Action(GenerateNumbers), new Action(PrintCharacters), new Action(PrintArray));
parallelInvokelbl.Text = stValues;
}

static void GenerateNumbers()
{
for (int i = 0; i < 10; i++)
{
stValues += "Method1 - Number " + i.ToString() + "
";
Thread.Sleep(1000);
}
}

static void PrintCharacters()
{
string str = "blog";
for (int i = 0; i < str.Length; i++)
{
stValues += "Method2 - Character " + str[i].ToString() + "
";
Thread.Sleep(1000);
}

}

static void PrintArray()
{
int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8 };
foreach (int i in arr)
{
stValues += "Method3 - Array " + i.ToString() + "
";
Thread.Sleep(1000);
}
}
}