Wednesday, May 28, 2008

Useful operations with sp_MSforeachtable Stored Procedure

Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase:


---------------------
--Delete all data in the database
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

EXEC sp_MSForEachTable
'BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
DELETE FROM ?
END CATCH;'
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
---------------------
--Perform delete operation on all table for cleanup
exec sp_MSforeachtable 'DELETE ?'
---------------------
--Drop all Tables
exec sp_MSforeachtable 'DROP TABLE ?'
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
---------------------


Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase with printed message in Messages window of SQL Server Management Studio:


---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL PRINT'? constraint altered'"
exec sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL PRINT'? trigger altered'"
---------------------
--Perform delete operation on all table for cleanup
exec sp_MSforeachtable "DELETE ? PRINT'? deleted'"
--Delete all data in the database
EXEC sp_MSForEachTable "DELETE FROM ? PRINT'? deleted'"
EXEC sp_MSForEachTable "TRUNCATE TABLE ? PRINT'? truncated'"
EXEC sp_MSForEachTable
"BEGIN TRY
TRUNCATE TABLE ? PRINT'? truncated'
END TRY
BEGIN CATCH
DELETE FROM ? PRINT'? deleted'
END CATCH;"
---------------------
--Drop all Tables
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped'"
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL PRINT'? constraint altered'"
exec sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL PRINT'? trigger altered'"
---------------------


Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase with printed message in Messages window of SQL Server Management Studio and for a particular schema:


---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL PRINT'? constraint altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? DISABLE TRIGGER ALL PRINT'? trigger altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------
--Drop table of particular shcemaID/shemaName
Exec sp_MSforeachtable
@command1 = "DROP TABLE ? PRINT '? dropped'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? CHECK CONSTRAINT ALL PRINT'? constraint altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? ENABLE TRIGGER ALL PRINT'? trigger altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------


Following displays how to execute Drop operation on a DataBase for a particular schema and with Like condition:


---------------------
--Drop table of particular shcemaID/shemaName and with name starting with 'Temp_'
Exec sp_MSforeachtable
@command1 = "DROP TABLE ? PRINT '? dropped'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')
and o.name LIKE 'Temp_%'"
---------------------

Thursday, May 15, 2008

Passing data between two Workflow Activities in ASP.Net Workflow

To test the passing of data between two activities we require following functionalities:
1) A sender activity that sends the data.
2) A receiver activity that receives the data.
3) A workflow that consume both sender and receiver activities.
4) A console application to test the workflow.

Let’s start to implement above steps one-by-one.

1) Creating the sender activity

I created a sender activity named SenderActivity.
In this activity, I am creating two properties, named UserFirstName and UserMiddleName whose CategoryAttribute are set to Input. These two properties will accept/take First Name and Middle Name from designer’s properties box.
Next I created a property names UserName, whose CategoryAttribute is set to Ouput. This property will be used in receiver activity, for getting the processed data of sender activity.
Here I am just combining the FirstName and MiddleName by string operation and assigning it to UserName.

Following is the code snippet of SenderActivity.cs



using System;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Collections;
using System.Drawing;
using System.Linq;
using System.Workflow.ComponentModel;
using System.Workflow.ComponentModel.Design;
using System.Workflow.ComponentModel.Compiler;
using System.Workflow.ComponentModel.Serialization;
using System.Workflow.Runtime;
using System.Workflow.Activities;
using System.Workflow.Activities.Rules;

namespace MyWorkFlow.Activity
{
public partial class SenderActivity: SequenceActivity
{
string userName = string.Empty;

public SenderActivity()
{
InitializeComponent();
}

protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
if (null == executionContext)
throw new ArgumentNullException("executionContext");

Console.WriteLine("SenderActivity Started ...");
Console.WriteLine("User FirstName is : " + this.UserFirstName);
Console.WriteLine("User MiddleName is : " + this.UserMiddleName);

this.userName = this.UserFirstName + " " + this.UserMiddleName;

Console.WriteLine("SenderActivity End.");
Console.ReadLine();

return base.Execute(executionContext);
}


#region Browsable Attributes


public static DependencyProperty UserFirstNameProperty = DependencyProperty.Register("UserFirstName", typeof(string), typeof(SenderActivity), new PropertyMetadata(DependencyPropertyOptions.Metadata, new Attribute[] { new ValidationOptionAttribute(ValidationOption.Required) }));

[DescriptionAttribute("UserFirstName")]
[CategoryAttribute("Input")]
[BrowsableAttribute(true)]
[DesignerSerializationVisibilityAttribute(DesignerSerializationVisibility.Visible)]
public string UserFirstName
{
get
{
return ((string)(base.GetValue(SenderActivity.UserFirstNameProperty)));
}
set
{
base.SetValue(SenderActivity.UserFirstNameProperty, value);
}
}



public static DependencyProperty UserMiddleNameProperty = DependencyProperty.Register("UserMiddleName", typeof(string), typeof(SenderActivity), new PropertyMetadata(DependencyPropertyOptions.Metadata, new Attribute[] { new ValidationOptionAttribute(ValidationOption.Required) }));

[DescriptionAttribute("UserMiddleName")]
[CategoryAttribute("Input")]
[BrowsableAttribute(true)]
[DesignerSerializationVisibilityAttribute(DesignerSerializationVisibility.Visible)]
public string UserMiddleName
{
get
{
return ((string)(base.GetValue(SenderActivity.UserMiddleNameProperty)));
}
set
{
base.SetValue(SenderActivity.UserMiddleNameProperty, value);
}
}


[DescriptionAttribute("UserName")]
[CategoryAttribute("Output")]
[BrowsableAttribute(true)]
[DesignerSerializationVisibilityAttribute(DesignerSerializationVisibility.Visible)]
public string UserName
{
get
{
return this.userName;
}
}

#endregion Browsable Attributes
}
}







2) Creating the receiver activity

I created a receiver activity named ReceiverActivity. In this activity, I am creating two properties, named UserLastName and SenderActivityName whose CategoryAttribute are set to Input. UserName property will accept/take Last Name from designer’s properties box.SenderActivityName property will be used to get the sender activity name.
Here I am just combining the LastName of receiver’s activity with the Name data of sender’s activity, to form user’s FullName.
To get the data from sender activity, we are using GetActivityByName function to get the activity instance and then calling its property to get the data.




string userName = ((SenderActivity)this.GetActivityByName(this.SenderActivityName, false)).UserName;






Following is the code snippet of ReceiverActivity.cs




using System;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Collections;
using System.Drawing;
using System.Linq;
using System.Workflow.ComponentModel;
using System.Workflow.ComponentModel.Design;
using System.Workflow.ComponentModel.Compiler;
using System.Workflow.ComponentModel.Serialization;
using System.Workflow.Runtime;
using System.Workflow.Activities;
using System.Workflow.Activities.Rules;

namespace MyWorkFlow.Activity
{
public partial class ReceiverActivity: SequenceActivity
{
public ReceiverActivity()
{
InitializeComponent();
}

protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
if (null == executionContext)
throw new ArgumentNullException("executionContext");

Console.WriteLine("ReceiverActivity Started ...");

string userName = ((SenderActivity)this.GetActivityByName(this.SenderActivityName, false)).UserName;

Console.WriteLine("User Name from SenderActivity is : " + userName);
Console.WriteLine("User LastName is : " + this.UserLastName);

userName += " " + this.UserLastName;

Console.WriteLine("User FullName is : " + userName);
Console.WriteLine("ReceiverActivity End.");
Console.ReadLine();

return base.Execute(executionContext);
}

#region Browsable Attributes

public static DependencyProperty UserLastNameProperty = DependencyProperty.Register("UserLastName", typeof(string), typeof(ReceiverActivity), new PropertyMetadata(DependencyPropertyOptions.Metadata, new Attribute[] { new ValidationOptionAttribute(ValidationOption.Required) }));

[DescriptionAttribute("UserLastName")]
[CategoryAttribute("Input")]
[BrowsableAttribute(true)]
[DesignerSerializationVisibilityAttribute(DesignerSerializationVisibility.Visible)]
public string UserLastName
{
get
{
return ((string)(base.GetValue(ReceiverActivity.UserLastNameProperty)));
}
set
{
base.SetValue(ReceiverActivity.UserLastNameProperty, value);
}
}

public static DependencyProperty SenderActivityNameProperty = DependencyProperty.Register("SenderActivityName", typeof(string), typeof(ReceiverActivity), new PropertyMetadata(DependencyPropertyOptions.Metadata, new Attribute[] { new ValidationOptionAttribute(ValidationOption.Required) }));

[DescriptionAttribute("SenderActivityName")]
[CategoryAttribute("Input")]
[BrowsableAttribute(true)]
[DesignerSerializationVisibilityAttribute(DesignerSerializationVisibility.Visible)]
public string SenderActivityName
{
get
{
return ((string)(base.GetValue(ReceiverActivity.SenderActivityNameProperty)));
}
set
{
base.SetValue(ReceiverActivity.SenderActivityNameProperty, value);
}
}

#endregion Browsable Attributes
}
}






3) Creating Workflow to consume both Sender and Receive activities

After building the project with above two activities, project’s toolbox will displays both custom sender and receiver activities.



Add a Sequential Workflow in project, here I named it SenderReceiverWF.cs.
First select SenderActivity in toolbox, drag-n-drop it to workflow designer area. Then select ReceicerActivity in toolbox, drag-n-drop it to workflow designer, just below the SenderActivity (as shown in above figure).Now, select SenderActivity in workflow designer, open its property box. Set the Activity’s name, UserFirstName, and UserMiddleName properties in it (here I am writing senderActivity, Avinash, and Kumar respectively in properties values).



Now, select ReceiverActivity in workflow designer, open its property box. Set Activity’s name, SenderActivityName, and UserLastName properties in it (here I am writing receiverActivity, senderActivity (same name that I used for sender activity), and Thakur respectively in properties values).



Rebuild the project to use it in a console application.


4) Create a Console application to test above workflow

Add a new console project in solution explorer, to test the above workflow. Just consume the workflow in it (explained in detail, in my earlier post).

Following is the code snippet for console application class:




using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Threading;
using System.Workflow.Runtime;
using System.Workflow.Runtime.Hosting;

namespace MyConsole
{
class Program
{
static void Main(string[] args)
{
using (WorkflowRuntime workflowRuntime = new WorkflowRuntime())
{
AutoResetEvent waitHandle = new AutoResetEvent(false);

workflowRuntime.WorkflowCompleted += delegate(object sender, WorkflowCompletedEventArgs e)
{
waitHandle.Set();
};

workflowRuntime.WorkflowTerminated += delegate(object sender, WorkflowTerminatedEventArgs e)
{
Console.WriteLine(e.Exception.Message);
waitHandle.Set();
};

WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(MyWorkFlow.SenderReceiverWF));
instance.Start();

waitHandle.WaitOne();
}

Console.ReadLine();
}
}
}







Now it’s time to see the output of above work. When we run the application, first SenderActivity will get executed and then ReceiverActivity, as per the sequence defined in the workflow.

Following is the snapshot of SenderActivity’s output (here it’s wait for user input to execute second activity).



After pressing any key, the second activity i.e. Receiver activity get executed (as shown in below figure).

Google