Programming Best Practices

Friday, August 17, 2012

SQL Query : Update row columns with another row

How to update row columns with another row if column values are null in SQL

Update row column with another row column in same table SQL only if it is null.

Background

To test queries and concept I am using SQL SERVER 2008 R2.

Queries

If you need to update one row columns from another row columns in the same table following approach can be useful.

Create sample table.

CREATE TABLE [dbo].[Employee](

[Title] [varchar](50) NULL,

[FirstName] [varchar](50) NULL,

[Surname] [varchar](50) NULL,

[DOB] [varchar](50) NULL,

[Datestarted] [varchar](50) NULL,

[EmployeeId] [int] IDENTITY(1,1) NOT NULL,

[Interest] [varchar](50) NULL,

[EmpAddress] [varchar](500) NULL

) ON [PRIMARY]

Add some sample rows as follows.

I am just updating 2 columns. Of course you can update more in similar fashion. Here updating 2 columns of 1^st row with 2^nd row column values.

UPDATE n

SET n.EmpAddress = p.EmpAddress

,n.[Interest] = p.Interest

FROM [Employee] n inner join [Employee] p

ON n.employeeid = 1 AND p.employeeid = 2

Select * from Employee

Please see next query if you wish to update only when value in the column is null.

Update column when value is null

I am going to use COALESCE. This is function similar to Case.

It returns the first nonnull expression among its arguments. Example

select coalesce(Interest,'FUN') from [Employee] where EmployeeId = 5

Updating two columns of row 4^th when they are null with column values of row 3^rd.

Precise query can look like

UPDATE n

SET n.EmpAddress = coalesce(n.EmpAddress, p.EmpAddress)

,n.[Interest] = coalesce(n.Interest, p.Interest)

FROM [Employee] n inner join [Employee] p

ON n.employeeid = 4 AND p.employeeid = 3

Select * from Employee

Summary

In this article I have suggested queries to update row with anther row and how you can use coalesce to restrict modification to null fields only. I hope you can use these queries for other purposes too.

Tag: SQL, SQL SERVER, SQL Server 2008, COALESCE, Update column when value is null, How to update row with another row in SQL, update row with another row column in same table SQL, Update table column with data from other columns in same row, SQL UPDATE with sub-query that references the same table, SQL UPDATE from another row in the same table, Update column value based on other columns in same table, Update data in one table with data from another table, update when value is null sql, How can I update a value on a column only if that value is null, update a column value in case null, update a column value when null, SQL UPDATE, but only if the old value is null, Update columns if it is a null, Update only if null, IS NULL and IS NOT NULL, Update to 0 if NULL

Tuesday, August 7, 2012

SQL Server : Data sharing across stored procedures

Table data sharing across stored procedures

This article demonstrates how table data can be shared in two stored procedures. There are several requirements where we need to pass temp table data to called stored procedure for further processing and can return manipulated data to parent stored procedure.

Background

To test queries and concept I am using SQL SERVER 2008 R2.

Introduction

I want to evaluate some of the good ways to accomplish this. I shall test approach for concurrent calling.
Table Valued Function – This can be good approach. But limitation is you can’t call stored procedure inside function if you need to.
Using Temp Table - This approach looks promising. It works for in and out.
Passing table variable – By any chance if you are using insert into in both parent and child stored procedures. Then it fails.
Other approaches like Using Cursor Variables, CLR, Open query or XML are complex, non efficient or having other pitfalls.

Evaluation of data sharing using temp table

Lets evaluate most promising approach. Test - whether temp tables are call dependent and not, causing problem in other calls.
Create table named as mytable.
CREATE TABLE [dbo].[MyTable](
[col1] [int] NOT NULL,
[col2] [char](5) NULL
) ON [PRIMARY]
Lets insert some sample rows for test.

INSERT INTO [MyTable]

([col1],

[col2])

VALUES (1,

INSERT INTO [MyTable]

([col1],

[col2])

VALUES (2,

INSERT INTO [MyTable]

([col1],

[col2])

VALUES (3,

INSERT INTO [MyTable]

([col1],

[col2])

VALUES (4,

Create child stored procedure named as called_procedure. Here first I am checking whether temp table is existing or not. If exists then insert some data as per passed parameter @par1.

-- If Exist then drop and create

IF EXISTS (SELECT *

FROM sys.objects

WHERE object_id = Object_id(N'[dbo].[called_procedure]')

AND type IN ( N'P', N'PC' ))

DROP PROCEDURE [dbo].[called_procedure]

CREATE PROCEDURE Called_procedure @par1 INT,

@par2 BIT

BEGIN

IF Object_id('tempdb..#mytemp') IS NOT NULL

BEGIN

INSERT INTO #mytemp

SELECT *

FROM Mytable

WHERE col1 = @par1

END

Create caller Stored procedure without creating temp table in scope.

IF EXISTS (SELECT *

FROM sys.objects

WHERE object_id = Object_id(N'[dbo].[caller_procedure1]')

AND type IN ( N'P', N'PC' ))

DROP PROCEDURE [dbo].[caller_procedure1]

CREATE PROCEDURE Caller_procedure1

BEGIN

--Testing for if temp table does not exists in scope

EXEC Called_procedure

IF Object_id('tempdb..#mytemp') IS NOT NULL

BEGIN

SELECT *

FROM #mytemp

END

Another caller stored procedure with temp table. This procedure can be called for different parameters.

IF EXISTS (SELECT *

FROM sys.objects

WHERE object_id = Object_id(N'[dbo].[caller_procedure2]')

AND type IN ( N'P', N'PC' ))

DROP PROCEDURE [dbo].[caller_procedure2]

CREATE PROCEDURE Caller_procedure2 @par1 INT

BEGIN

CREATE TABLE #mytemp

(

col1 INT NOT NULL,

col2 CHAR(5) NULL

)

EXEC Called_procedure

@par1,

SELECT *

FROM #mytemp

END

Execute all following queries same time. You can also execute these procedures same time from different systems.

CREATE TABLE #mytemp (col1 int NOT NULL,

col2 char(5) NULL

)

Exec caller_procedure2 2

Exec caller_procedure2 4

Exec caller_procedure2 2

Exec caller_procedure2 4

drop table #mytemp

Exec caller_procedure1

Caller_procedure2 takes it’s own temp table and drops when scope ends. It does not take temp table which we have created outside the proc.

Summary

This little interesting exercise can help you to understand how you can pass temp table to child stored procedures. Using this concept you can write multipurpose stored procedures to increase reusability of code.

If this article helps you in designing/coding SQL logic don’t forget to hit voting option. Please comment your suggestions and improvements.

Happy Coding!!

Search Tags:

SQL, DML, DDL, SQL SERVER 2008, SQL SERVER 2005, SQL SERVER 2000, SP, Stored Procedure, Function, Temp Table, Table Variable, Passing temp table to Stored Procedure, Table data sharing across stored procedures, Data sharing in SQL, Writing reusable Stored Procedures, Passing Rows to Stored Procedures

Thursday, June 7, 2012

Best Practice Architecture with Workflow Foundation (WF)

This article demonstrates how WF can be consumed as business layer.

Download BestPracticeArchitectureWWF.zip

When to go for Workflow Foundation

1. For long-running business process.

2. Frequently Changing Business Logic or Rules.

3. When need visual model/execution of the process.

Introduction

In This post I am going to demonstrate how you can architect enterprise application using WWF. You can find lots of learning stuff for Window Workflow Foundation but difficult to find is how to adjust and where to adjust it in architecture.

Architecture diagram

Have a look to architecture diagram, in this UI is interacting with business layer via WCF service. WF is used as part of business layer. You can also put WF service in front of WCF but then you will not know the entities of WCF in UI. As per best practice nothing should be directly exposed to UI layer, so it is not good idea to put WF as library in UI Layer. Generally people use WF for business rules, State persistence, Transaction or for some kind of time taking process so I would suggest to put it in Business layer. In my attached sample, for simplicity I am not involving entity framework and DB part. But off course you can add Entity framework with POCO classes. There are lot of articles are available on this so I don’t think you should face any kind of difficulty in implementation. In future I shall also come up with some sample on this.

Background

To implement this application, I am going to use the following technologies:

Microsoft Visual Studio 2010
Microsoft Visual Studio 2010 Service Pack 1 (Installer)

Update 4.0.3 for Microsoft .NET Framework 4 – Design-time Update for Visual Studio 2010 SP1 (KB2600214)

Update 4.0.3 for Microsoft .NET Framework 4 – Runtime Update (KB2600211)

To implement this project person should have basic knowledge of .net technologies like C#, ASP.NET, WCF, WF.

Sample Proof of Concept

ADD blank solution name as ‘BestPracticeArchitectureWWF’.

Business Layer (C# Library)

Add C# library project to existing solution. Add following code for method in ‘Class1.cs’. This method will take one parameter and return it with some modification. This is the place where you can call to database by using entity framework.

public static string BusinessMethod(string parm1)

{

return parm1 + " Returned by Business layer.";

}

Business Layer (WF Activity)

Add WF Activity library project to existing solution.

In Activity1.xaml add flowchart.

Drag Sequence to indicated place.

Select Sequence and declare 2 arguments in argument tab as shown in screen shot. One is to accept argument and another to return.

Now drag two more activities to Sequence as shown in above screen shot. One is Assign activity and another is Invoke Method from Primitive group panel. In Assign activity will assign Argument2 variable to Argument1 + “Modified in WF”. Invoke method is used to call business C# library. Add reference to C# library. To call library method set Target Type as class. Method name simply type ‘BusinessMethod’. No need to fill TargetObject(In TargetType and TargetObject one need to be filled). One strange thing is even though you work in c# project in Activities expression it takes only vb code.

Finally XAML of this activity will look like this. No need to modify anything in XAML but some time you rename something it fails to change in XAML then we need to look into it.

<x:Members>

<x:Property Name="argument1" Type="InArgument(x:String)" />

<x:Property Name="argument2" Type="OutArgument(x:String)" />

</x:Members>

<sap:VirtualizedContainerService.HintSize>654,676</sap:VirtualizedContainerService.HintSize>

<mva:VisualBasic.Settings>Assembly references and imported namespaces for internal implementation</mva:VisualBasic.Settings>

<sap:WorkflowViewStateService.ViewState>

<scg3:Dictionary x:TypeArguments="x:String, x:Object">

<x:Boolean x:Key="IsExpanded">False</x:Boolean>

<av:Point x:Key="ShapeLocation">270,2.5</av:Point>

<av:Size x:Key="ShapeSize">60,75</av:Size>

<av:PointCollection x:Key="ConnectorLocation">300,77.5 300,127.5</av:PointCollection>

</scg3:Dictionary>

</sap:WorkflowViewStateService.ViewState>

<Flowchart.StartNode>

<sap:WorkflowViewStateService.ViewState>

<scg3:Dictionary x:TypeArguments="x:String, x:Object">

<av:Point x:Key="ShapeLocation">200,127.5</av:Point>

<av:Size x:Key="ShapeSize">200,51</av:Size>

</scg3:Dictionary>

</sap:WorkflowViewStateService.ViewState>

<sap:WorkflowViewStateService.ViewState>

<scg3:Dictionary x:TypeArguments="x:String, x:Object">

<x:Boolean x:Key="IsExpanded">True</x:Boolean>

</scg3:Dictionary>

</sap:WorkflowViewStateService.ViewState>

<Assign.To>

<OutArgument x:TypeArguments="x:String">[argument1]</OutArgument>

</Assign.To>

<Assign.Value>

<InArgument x:TypeArguments="x:String">[argument1 + "Modified in WF"]</InArgument>

</Assign.Value>

</Assign>

<InvokeMethod.Result>

<OutArgument x:TypeArguments="x:String">[argument2]</OutArgument>

</InvokeMethod.Result>

<InArgument x:TypeArguments="x:String">[argument1]</InArgument>

</InvokeMethod>

</Sequence>

</FlowStep>

</Flowchart.StartNode>

<x:Reference>__ReferenceID0</x:Reference>

</Flowchart>

</Activity>

Build the WF Activity and add reference to WCF Service.

Service Layer (WCF)

Add WCF service. In Service1.svc.cs add following code to call activity. Don’t forget to update contract in IService1.cs.

public string GetData(string value)

{

ActivityLibrary1.Activity1 ca = new ActivityLibrary1.Activity1();

ca.argument1 = value.ToString();

IDictionary<string, object> dr = new Dictionary<string, object>();

dr.Add("argument1",value);

var output = WorkflowInvoker.Invoke(ca,dr);

string st = output["argument2"].ToString();

return string.Format("You entered: {0}", st);

}

This method takes string variable from UI and process it in WF Activity and in C# business library. Build WCF service.

UI(ASP.NET)

Add Asp.net project to existing solution. In place of ASP.NET you can use Silverlight/WPF project too. If you want to see how WCF can be called in Silverlight project see my other Article.

Add service reference to this project. Now come to Default.aspx to add some controls as per following code.

<h2>

WF Calling Sample

<br />

<asp:Label ID="Label2" runat="server" Text="Enter Your Name: ">

</asp:Label><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

<br />

<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />

<br />

<asp:Label ID="Label1" runat="server" Text=""></asp:Label>

</h2>

Add click event for button. In code behind we are going to call WCF service.

protected void Button1_Click(object sender, EventArgs e)

{

Service1Client sr = new ServiceReference1.Service1Client();

int i = 0;

if (int.TryParse(TextBox1.Text, out i))

{

Label1.Text = sr.GetData(i);

}

else

Label1.Text = "Please enter Number only.";

}

Set Web Application as startup project and Default.aspx as Startup page. Run the application. Enter name and click Button to get modified from WF activity and C# Business library. Hope this idea will help you in designing applications.

Summary

In this Article you have learned how you can utilize WF activities in business layer. Hope this would be pretty simple example to elaborate architecture.

If this article helps you in designing/coding application don’t forget to hit voting option. Please comment your suggestions and improvements.

Happy Coding!!

Search Tags:

C#, MVVM, Silverlight, Silverlight4, Silverlight5, Window, WPF, WPF4, Business Layer, ASP.NET, WCF, WF, Workflow Foundation, WWF, Window Workflow Foundation, Architecture, Design, Best Practice, n-tier application, Activity, Sequence, flowchart, when to use WF, where to use WF