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

What is the difference between For(C#) and Parallel.For(.NE 4.0) Loop.

The difference is that with the C# for statement, the loop is run from a single thread. However the Parallel class uses multiple threads. Moreover the order of the iteration in the parallel version is not necessarily in order.

Let us see an example:
1) Add one web page with Name "ParallelForVSFor"
2) Add two label controls on the page "ParallelForVSFor.aspx" file

<body>
<form id="form1" runat="server">
<div>
<table width="100%">
<tr>
<td><asp:Label ID="parallelfor" runat="server" Text=""></asp:Label></td>
<td><asp:Label ID="csharpfor" runat="server" Text=""></asp:Label></td>
</tr>
</table>
</div>
</form>
</body>

3) Add the following code in "ParallelForVSFor.aspx.cs" file
using System;
using System.Threading;
using System.Threading.Tasks;

protected void Page_Load(object sender, EventArgs e)
protected void Page_Load(object sender, EventArgs e)
{
parallelfor.Text += "Using Parallel.For \n";
Parallel.For(0,10,i =>
{
parallelfor.Text += "i="+i.ToString()+"\n";
Thread.Sleep(10);
});

csharpfor.Text += "Using C# For Loop \n";
for (int i = 0; i < 10; i++)
{
csharpfor.Text += "i=" + i.ToString() + "\n";
Thread.Sleep(10);
}
}

As you can see, the Parallel.For method is defined as Parallel.For Method (Int32, Int32, Action(Of Int32)). Here the first param is the start index (inclusive), the second param is the end index (exclusive) and the third param is the Action delegate that is invoked once per iteration. Here’s the output of running the code:

Using Parallel.For i=0 i=5 i=1 i=6 i=2 i=7 i=3 i=8 i=4 i=9
Using C# For Loop i=0 i=1 i=2 i=3 i=4 i=5 i=6 i=7 i=8 i=9

As you can see, with the C# for loop statement, the results are printed sequentially and the loop is run from a single thread. However with the Parallel.For method uses multiple threads and the order of the iteration is not in order.

The Parallel.For() construct is useful if you have a set of data that is to be processed independently. The construct splits the task over multiple processor.

SqlCacheDependency

following Steps those are required are given below:

Prepare the database:
In order to use the SqlCacheDependency class, the database has to be configured to use the service broker (notification services).
First check if the service broker is enabled:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'Northwind'

When this query returns 1 the broker is enabled and we are done with SQL Server.
If not, enable the broker by using this alter table statement:

ALTER DATABASE Northwind SET ENABLE_BROKER

Two problems I have encountered using this statement:
1. It keeps running.
2. It returns error 9772: The Service Broker in database 'Northwind' cannot be enabled because there is already an enabled Service Broker with the same ID.

This problems can be solved by using resp.:
1. Change the statement to ALTER DATABASE Northwind SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
2. First execute statement ALTER DATABASE Northwind SET NEW_BROKER and then again try to enable the broker.

Prepare the web application:
In the Global.asax file add the following statement to the Application_Start subroutine:
System.Data.SqlClient.SqlDependency.Start("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")
And allmost the same statement in Application_Stop:
System.Data.SqlClient.SqlDependency.Stop("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")

Using the Cache:
1) in Global.asax file under Application_Start method
SqlDependency.Start(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
2) in Global.asax file under Application_Stop method
SqlDependency.Stop(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
3)Code

DataSet objDS = new DataSet();

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
SqlCommand cmd = new SqlCommand("select top 100 ID,CRN_ID from C1", con);
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
SqlCacheDependencyAdmin.EnableNotifications(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
if(!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString).Contains("C1"))
{ SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString, "C1");
}

SqlCacheDependency dependency = new SqlCacheDependency(cmd);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(objDS);

_cache.Insert("C1", objDS, dependency);


for more information please visit:
http://www.dotnetcurry.com/ShowArticle.aspx?ID=263&AspxAutoDetectCookieSupport=1

.NET,ASP.NET: Application level data caching (File dependency) with callbacks

state management(cache management)

Now let's discuss different aspects of cache management (or state management) in ASP.NET.

Although cache management is not an issue in Windows applications, it has always been a challenge in the web environment. Since HTTP is a stateless protocol and a web server doesn't recognize users between different requests, it becomes very important for us to recognize a particular user between different requests and also store data so that it can be re-used between different requests. ASP.NET provides many features for storing data both in the client (browser) and the server (web server) sides, but sometimes we get confused with when to use what. In ASP.NET we come across features like Session, Application and Cache objects, but it is important for us to understand the difference between them in order to effectively use them in web applications.

Caching helps us to achieve three important aspects of QoS (Quality Of Service):

•Performance - Caching improves application performance by minimizing data retrieval and formatting operations.
•Scalability - Since caching minimizes data retrieval and formatting operations, it reduces the load on server resources thus increasing the scalability of the application.
•Availability - Since the application uses data from a cache, the application will survive failures in other systems and databases.

Server side cache management

ASP.NET session state is used to cache data per user session. It means that the data cannot be shared across multiple users and the data usage is limited to the user session it is created in. ASP.NET Session object is used for this purpose.

ASP.NET session state can be managed in three different ways:

•InProc - Stored in the aspnet_wp.exe process area. The session data is lost when the process or the application domain is recycled.
•StateServer - Session state is stored in a separate process (aspnet_state.exe) which can be stored in a different machine. Since it can be stored in a different machine, this option will work in a web farm scenario.
•SQLServer - Session state is stored in a SQL Server database. This option will also work in a web farm scenario.

ASP.NET application object
ASP.NET provides an object called Application object to store data that is accessible to all user requests. The life span of this object is tied to the application and it is re-created every time the application starts. Unlike ASP.NET Session object this object is accessible to all user requests. Since this storage is created and maintained in an application domain space, this should not be used for data storage in a web farm scenario. This option is very useful to store data like the application metadata (CONFIG files data) that can be loaded to the Application object during application start up and can be used during the life of the application without reloading it every time for each user request. But if there is a requirement to invalidate the cached data whenever there is any change to the CONFIG files while the application is running, this option should not be used as it doesn't provide any feature to expire the cached data. So in this case other options like the ASP.NET Cache object should be used, which is explained below.

ASP.NET cache object
ASP.NET provides a key-value pair object - the Cache object which is available in the System.Web.Caching namespace. The scope of it is the application domain and the life span is tied to the application. Unlike the ASP.NET Session object, this object is accessible to all user requests.

Although both Application and Cache objects look the same, the key difference between them is the added features provided by the Cache object like the expiration policies and dependencies. It means that the data stored in the cache object can be expired/removed based on some predefined time limit set by the application code or when the dependent entity gets changed whereas this feature is not available in the Application object.

Let us discuss the different expiration policies and the dependencies that are supported.

Dependency
Dependency means that an item can be removed from the cache when a dependent entity gets changed. So a dependent relationship can be defined on an item whose removal from the cache will depend on the dependent. There are two types of dependencies supported in ASP.NET.

•File dependency - This provides an option to remove an item automatically from the cache whenever a disk file changes. Let's say in my application I am using an XML file to store error details (error number and error message mapping) which is used to retrieve an error message for a given error number at runtime. So instead of reading the file from the disk each time I need an error message, let's say I decide to load it once at application startup and store it in the ASP.NET cache for further use. So, if I need to change the CONFIG file to add new error messages or change some of the existing error messages while the application is running, then what will happen to my cached data? Do I need to stop the application and start it again to reflect the file changes in the application? The answer is no. The cache data can be invalidated whenever the file changes by using the File dependency option.

Example: in Global.asax file
void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
//Common.ServiceManger.constant.LoadConstants();
_cache = HttpContext.Current.Cache;
FillConstantCache();
}

private void FillConstantCache()
{
//if (errorconstant == null)
//{
DataSet objDS = new DataSet();
string constantFile = string.Empty;
constantFile = "D:/DOTNETPROJECTS/TFSPROJECTS53/Test/AppConstants.xml";
CacheDependency dependency = new CacheDependency(constantFile);
objDS.ReadXml(constantFile);

//SqlConnection con = new SqlConnection("connectionString");
//SqlCommand cmd = new SqlCommand("select key,value from table", con);
//SqlCacheDependency dependency = new SqlCacheDependency(cmd);
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//da.Fill(objDS);

errorconstant = new Hashtable();
foreach (DataRow dr in objDS.Tables[0].Rows)
{
if (!errorconstant.Contains(dr[0].ToString()))
errorconstant.Add(dr[0].ToString(), dr[1].ToString());
}
_cache.Insert("appconstant", objDS, dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Default, new CacheItemRemovedCallback(this.CacheItemRemoved));
//}

}

//this method used for callBack when you modified an AppConstants.xml
private void CacheItemRemoved(string key, object val, CacheItemRemovedReason reason)
{
FillConstantCache();
}

You call this from any web page like that:
lblname.Text = Global.errorconstant["keyname"].ToString();

for more information visit the following links:
http://weblogs.asp.net/kwarren/archive/2004/05/20/136129.aspx
http://www.codeproject.com/KB/web-cache/cachemanagementinaspnet.aspx

Difference between Implicit and Explicit Interface

There are 4 basic difference between Implicit and Explicit interface. To understand all four of them see the below example first:

There is a class C1 which implements two interfaces I1 and I2. and Both the class have method with same name i.e. sum().

public class C1 : I1, I2
{
//Explicit Implementation of interface
void I2.sum()
{
}

//Implicit Implementation of interface
public void sum()
{
}
}

public interface I1
{
void sum();
}
public interface I2
{
void sum();
}

The Interface I1 is implemented implicitly and I2 is explicitly. Below are the difference based on above example.

1) In Implicit implementation, no need to specify Interface name with its method.
e.g. public void sum() { }
Where as in Explicit implementation, you need to specify Interface name with its method
e.g. void I2.sum() { }

2) In implicit, access specifier is required with method name and it must be public, even protected will not work.
Where as in Explicit, no access specifier will be required.

3) You can not make virtual method in explicit implemention and same will work in implicit implemention.

4) You can not make abstract method in explicit implemention and same will work in implicit implemention.

Practical Example

Now you wonder, by considering above scenario, what is the use of these two implementation because only Implicit implementation will also work for all type of method then why these two has been introduced by Microsoft. or what should be the practical scenario where we need to use either Implicit or Explicit.

Suppose you need to implement 2 interface and both interface will have method with same name. At that time you can not create one method in your class for both interfaces, rather you must implement one interface Implicit and one Explicit (or both Explicit) for showing two different method in your class. (Check above example).

Hiding Interface Details:

If you implement interface explicitly then it will not show in class Intellisense list. So, if you want to hide your method name to display when creating object of class then you need to go for Explicit interface implementation and in this case you need to create object of your interface to know the method name.

E.g.: In our above example if I want to invoke method of I2 interface:
I2 obj = new C1();
obj.sum();

NOTE: for more please click http://developerssolutions.wordpress.com/2010/07/22/difference-between-implicit-and-explicit-interface/

Is it possible to declare multiple Interfaces with same name and method?

Yes, You can have same name for all the interfaces if you declare with partial keyword.

But they can have same method but with different signature.

partial interface AAA
{
void TestAAA();
}

partial interface AAA
{
string TestAAA(parameter);
}

Implementing Two interface having the same method signature in the same class

There can be scenario when we would have two interface with the same method name and same type of arguments, same number of arguments and even the return type can be same and we need to implement both the interface in the same class. How to implement the interface in our class? Most of you will be thinking whats so tough in this, just implement the interface and the method and move on. If the signature of the methods in the interface were different then there would have been no problem but here the signature of the methods in two different interface are same and both the interfaces are gonna be implemented in the same class. The two interface are as below.
public interface IA
{
string PrintName();
}
public interface IB
{
string PrintName();
}
From the above code we can infer that we have two interface with names IA and IB and both have a single method named “PrintName”. The signature of both the methods are same and we need to implement the interfaces in our class say “A”. One way of impelementing the interface is as shown below i.e. just having a “public” implementation of the interface method only once.
public class A : IA, IB
{
public A()
{
}
public string PrintName()
{
return this.GetType().Name;
}
}
The above implementation has got a limitation i.e the method “PrintName” is considered to be a common method for all i.e commong method for the class, and for the interfaces IA and IB. If you are writing a code shown below
A a = new A();
IA ia = new A();
IB ib = new A();
Console.WriteLine(a.PrintName());
Console.WriteLine(ia.PrintName());
Console.WriteLine(ib.PrintName());
all the calls to method “PrintName” will give you the same result, the name of the class i.e. “A”. This is because all call to the method goes to the same definition. Now if you want to give different implementation to the methods in interface IA and IB, what will you do? Its also simple, just have two impelementation of the same method and prefix the method names with the interface name as shown below.
public class A : IA, IB
{
public A()
{
}
string IA.PrintName()
{
return “IA PrintName Method”;
}
string IB.PrintName()
{
return “IB PrintName Method”;
}
}
Now the below code will give you different output.
IA ia = new A();
IB ib = new A();
Console.WriteLine(ia.PrintName());// Will print "IA PrintName Method"
Console.WriteLine(ib.PrintName());// Will print "IB PrintName Method"
So this is how two interfaces having the same method signature can be given different implementation in the same class.

For more information please visit : http://sandblogaspnet.blogspot.com/2008/05/implementing-two-interface-having-same.html