Call Stored Procedure from Entity Framework

Microsoft have introduced entity framework to connect to the database. When you work with the database, you have to call your stored procedure. This blog explain you how to execute your stored procedure from Entity Framework.

Assume that your stored procedure insert/update/delete record into database. It returns nothing just do as per the parameter value.

Follow the below steps:
1. Open Entity Framework Model page in Visual Studio IDE
2. Right click on blank area and choose “Add” -> “Function Import” option
3. In Add Function Import dialog, select your stored procedure, give function name and choose return type
4. Click on Ok button to add the function in Entity Framework
5. In your page, Create instance of entities class and call your function with proper parameter value

If your stored procedure is return multiple record then you have to create one custom entity, which have the same schema as your stored procedure return the data. To create the custom entity, right click on Entity Framework Model page and choose “Add” -> “Entity…” It will open “Add Entity” dialog box where you have to give Entity Information.

You can add/edit/delete properties into custom activity. To add, Select “Scalar Properties” section under custom activity, right click and choose “Add” -> “Scalar Property”.

Just create custom entity. After creating custom entity, Add Function Import to your entity framework model.

Follow the below steps:
1. Open Entity Framework Model page in Visual Studio IDE
2. Right click on blank area and choose “Add” -> “Function Import” option
3. In Add Function Import dialog, select your stored procedure, give function name and choose return type as Entities and select your custom entity from dropdown list.
4. Click on Ok button to add the function in Entity Framework
5. In your page, Create instance of entities class and call your function with proper parameter value


TestDBEntities1 testdb = new TestDBEntities1();
var p = from d in testdb.GetOrderInfo() select d;

Entity Framework Stored Procedure mapping

Introduction

Now days, Microsoft have changed lots of thing in accessing database from .Net application. Microsoft has introduced Entity Framework to access database from the application. Entity framework is just a small wizard which helps to create classes to access the database. To know basic of Entity Framework please click on here.

In Part 1, I explained you how to create entity framework class in your project and how to insert, update or delete the record. Now in part 2, I explain you how to map your stored procedure with insert, update and delete functionality.

If you will not map your stored procedure then Entity Framework will create concrete SQL statement for insert, update and delete. If you will map your stored procedure then Entity Framework will execute your stored procedure with parameter value.

Let take an example of contact table which I explained schema in part 1.

After creating entity framework file (.edmx), Select “Contact” entity and open “Mapping Details” window. In Mapping Details window, click on “Map Entity to Function” button on left side. Refer below screen shot:




In Insert/Update/Delete function, Select your stored procedure and map your parameter value. Refer image below:



Now you have to just add/update/remove data using Entity Framework and It will execute your mapped stored procedure.

Following code is used to add new record into Contact table. When user will execute the code below, Entity Framework will execute “SPE_ContactInsert” stored procedure to insert contact information.

TestEntityEntities testEntityEntities = new TestEntityEntities();
Contact contact = new Contact();
contact.FirstName = "test1";
contact.LastName = "test2";
contact.Title = "Mr.";
testEntityEntities.AddToContact(contact);
testEntityEntities.SaveChanges();

As the same way you can update or delete the record. For more information please refer Entity Framework part 1 here.

Register .net assembly as COM

Introduction
We have used VB6.0 DLL or OCX component in our .Net application. But sometime we need to use our .Net DLL into VB6.

In the following scenario you need to use .Net DLL into VB6.

• Any third party module is available in .Net and you need to integrate it with your VB6.0 application


• Your some modules are already developed into .Net and you have to integrate it with VB6.0 application

This blow will explain you how to create COM compatible .Net assembly step by step.

First we need to create one class library project. Open Visual Studio and create new Class Library project. Give “DemoClassLibrary1” name to your class library.

Create new class which needs to be expose as a COM object. Just add new class into your class library project. Give “DemoClass1” to your class name.


Public Class DemoClass1

Public Function GetWelcomeString(ByVal name As String) As String
GetWelcomeString = "Welcome " + name
End Function

End Class

Now to create it COM compatible, you have to add ComClass attribute on your class definition. So updated code will look like below:



Public Class DemoClass1

Public Function GetWelcomeString(ByVal name As String) As String
GetWelcomeString = "Welcome " + name
End Function

End Class

I have created one method which will be exposed as a COM method.

You also need to set your project as a Com compatible. To configure it, open project property. Right click on project in solution explorer and select properties option. In compile tab, select checkbox “Register for COM Interop”.

Now when you will build your project, project will create DLL as well as TLB file. This TLB file needs to use your .Net DLL into VB6.

Let’s use this DLL into VB6. Open your VB6 project and open reference dialog to add your .Net class reference. You will get option for “DemoApplication1”, select the option and click on ok.

Now you can create object of “DemoClass1” and use your function “GetWelcomeString”.

If you want to use this component on another machine then you have to copy the DLL file on that machine and register your DLL file to the registry so your VB6 application can find your component.

To register the component, Open Visual Studio 2008 command prompt and execute following command.


Regasm <<DLL file full path>> /tlb:<<tlbfilename>> /codebase

Regasm command generate TLB file and also register it with the system. Codebase option also set the code base with the registry.

SQL Server 2005 Integration Service Basic

Introduction

SQL Server Integration service is mainly used to transfer data from one database to another in different format. Also it is used to manipulate data before storing on destination.

SSIS (SQL server Integration Service) is specially designed for ETL process. ETL stands for Extract, Transform and Loading.

SSIS create package which you can execute from the SQL server management studio, stored procedure or SQL server job which extract data from one database, manipulate it and store it in another database. Here both databases may be different.

You can use any of the following database connection with SSIS package:

• SQL Server


• Flat File


• OLEDB


• ADO.Net


• Analysis Service

Prerequisite:
We need SQL Server Business intelligence development studio (BIDS) tool to develop SSIS package. BIDS tools come with Microsoft SQL Server Installation. Installing Microsoft SQL Server, Please check “Analysis Service” and “Integration Service” option.

In this blog, I will explain you how to create simple SSIS package and execute it from the SQL Server Business Intelligence development studio.

Open SQL Server Business Intelligence Development studio. Follow the following steps to create new SSIS package.

• Create new project from “File” menu -> “New Project”.


• Select “Business Intelligence Projects” from Project types in New Project dialog box


• On right side, you can see different Template available for “Business Intelligence Projects” type. Select “Integration Service Project” template from the available template.


• Give appropriate project name. Here I am giving “DemoIntegrationProject”


• Select Location where you want to save the file. And also give solution name. I am giving “DemoIntegrationSolution”.


• Click on Ok button to create new SSIS project.

The New project dialog box will display as below:



BIDS creates new project and open that project. You can see the available files and objects in Solution explorer. Open solution explorer if it is not opened.

You get four folder in solution explorer included into the project.

• Data Sources: This folder contains information about all data source used into the project.


• Data Source Views: This folder contains the views (UI) of used schema from the data sources.


• SSIS Packages: This folder contains all SSIS packages.


• Miscellaneous

Projects template add one blank SSIS package “Package.dtsx” into the project. We will first understand “Data sources” and “Data Source Views” to understand SSIS package.

Data Sources:
Select “Data Sources” folder in solution explorer. Right click on that. You will get “New Data Source…” option to create new data source. Just click on “New Data Source…” option.

“New Data Source…” option open wizard to create and add new data source into project. Follow the below steps to create new data source:

• On welcome screen, click on Next button


• On “Select how to define the connection” screen, you can see existing data connections (if you used SSIS before and added into any project). Refer screen below
  
  
  


• Select data connection if exist in the list. If your required connection is not available then you can create new connection using “New…” button.


• If connection is not already exist then click on “New…” button. It will open connection manager dialog. On Connection Manager dialog, select appropriate provider. Provide information necessary to create new data connection. Test your input information using “Test Connection” button. If your connection test successfully then Click on Ok to create new connection. Refer screen below
  
  
  


• You will get your connection on “Select how to define the connection” dialog. Select your connection and click on Finish button


• Give your connection name and click on Finish button.

Now you will get new data source file under “Data Sources” folder in solution explorer.

Data Source Views:
You have to create data source views before using data source into SSIS package. To create new “data source view”, select “Data Source Views” folder in solution explorer and right click on it. Select “New Data Source View…” option.

New Data Source View dialog open. Follow the below steps create new data source view:

• On welcome screen, click on Next button


• On “Select a data source” screen, select the data source which you have created. You can create new data source using “New Data Source…” button.


• After selecting data source, click on next button


• On “Select Tables and Views” dialog box, Add required tables to Included objects list box. You can add related objects using “Add Related Tables” button. You can also filter available objects using Filter text box. Add all required tables and views and click on Next button. Refer screen below:
  
  
  


• On “Completing the wizard” screen, give “Data source view” name (Here I gave it to “Test DB”) and click on Finish button.


• It will add new data source view file in the project.

Data Source View editor have three panes: Diagram Organizer, Tables, and Diagram.

Diagram Organizer: Diagram organizer pane allows you to create new Diagram and organize table into sub set. If you have 500 tables/views on Data Source view, then it will be difficult to refer any particular table or related tables. So you can make different Diagram using Diagram organizer pane. Just right click on “” and select “New Diagram” option. It will open new diagram where you can add only your required tables from “Tables” pane.

Tables: Tables pane contains all tables and views selected while creating data source view. After creating data source view, you can add/remove any objects. To add new table/view, just click on “Add/Remove objects” button in tool bar. It is displayed just above Diagram organizer pane.

Diagram: Diagram pane displays all tables and views schema and relation between them.

You can rename table/view and your field name. Sometime your database table/view name is not user friendly like the above diagram “tblProducts” table name is not user friendly. To rename table/view/field name, just select it in Diagram pane and open properties windows. In properties window, change FriendlyName property to give some meaningful name. I have change table names and the updated diagram is displayed as below:



SSIS Package:
You can create new SSIS package using right clicking on “SSIS Packages” folder in Solution explorer and select “New SSIS Package” option.

After package file is added into the solution you can rename to give proper name. When you will rename the package, it gives message to open the SSIS package click on yes to open the package.

SSIS package editor have four tabs:

• Control Flow


• Data Flow


• Event Handlers


• Package Explorer

Control Flow: You can add any number of control flows on your SSIS package. Control flow is just like a one task which you want to accomplish using SSIS package. BIDS tool have many integrated tools available to work with your project. Following are the most usable control flow tasks:

• Bulk Insert Task


• Data Flow Task


• Data Mining Query Task


• Execute Package Task


• Execute Process Task


• Execute SQL Task


• For loop container


• Foreach loop container


• FTP Task


• Script Task


• Send Mail Task


• Web service Task


• Xml Task

There are many other tasks available to create SSIS package as per your requirement.

Data Flow: Data flow is detailed definition of “Data Flow Task” which is available to add in Control Flow. You have seen “Data Flow Task” item available in above list to add into Control Flow tab. When you will add Data Flow Task, you can see those data flow task name in “data flow task” combo box in Data Flow tab.





Now we will create one sample SSIS package, deploy it and execute it from Business Intelligence development studio.

• Open your SSIS Package and open “Control Flow” tab.


• In Control Flow tab, Right click in “Connection Managers” pane and select “New Connection from Data Source…”


• It will open “Select Data Source” dialog box where you can see the Data sources added in your project.


• Select the proper data source and click Ok button. It will add that data connection into your SSIS package. Refer screen below:

• After successfully adding connection manager, Add Data Flow task control on your control flow.


• Right click on Data Flow Task in Control Flow tab and select Property. Change name property from “Data Flow Task” to “Export Customer Data”


• Go to Data Flow Tab


• Add “OLE DB Source” Task on “Data Flow” tab


• Double click on “OLE DB Source” task to open OLE DB Source Editor


• In editor, Select Connection Manager, Data Access mode and other required items. For demo purpose select “Test DB” as a connection manager, “Table or View” as a data access mode and select tblCustomers as a table.


• Click on Ok button.


• Now drag and drop “Flat File Destination” control on Data Flow tab


• Select “Ole Db Data Source” control, you can see green and red arrow. Just hold green arrow and put it on “Flat file destination” control.
  
  Green arrow – Green arrow is used to follow the process if preceding task is completed successfully. In our case, if we get customer data successfully then only go to flat file destination to export it.
  
  Red arrow – Red arrow is used to follow the process if preceding task is completed with error. You can use “Send Mail” task to send the error information or save those error in another destination.

• Double click on “Flat File Destination” control to open control editor


• Create new connection for Flat file. Click on New button to create new connection.


• Select “Delimited” option to generate Flat file connection manager.


• Give proper name and browse the file path


• Click on Ok to add connection manager to your project.


• Click on ok in “Flat File destination editor”

Now you simple package is ready. This package will read customer data from the database and put it in Flat file (Comma separated). You can also use another controls to process the data. You can also filter the data in OLEDB Source control.

When you will execute SSIS package, Business Intelligence development studio will execute your task and transfer customer data from database to your flat file.

Still you have not deployed SSIS package. You can deploy it in SQL server. Follow the below steps:

• Open SQL Server and login on server using Integration service. (In login dialog box, you have to select server type as a Integration service)


• After successfully connecting, expand “Stored Packages” in object explorer.


• Expand “File System”.


• Right click on “File System” and choose “Import Package”


• In Import package dialog, Select “File System” as a Package location


• Select the Package path. Browse to the path where we have created the above SSIS package project and select DTSX file.


• Give the package name and click on ok button to create the package.

You can see your SSIS package under “File System” node in object explorer of SQL server management studio.

You can execute SSIS package from SQL server management studio. Right click on “OrderSSIS” package and choose “Run Package” option. It will open “Execute Package Utility” dialog. Click on Execute button to run the package.

Implementing Singleton Design Pattern in C# application

Introduction

Sometime we need to create only one object of any class in our application then we need to implement Singleton design pattern to achieve this.

For an example, you have logging functionality which takes value as a parameter and store information in external device (like database or xml file or flat file). This logging class doesn’t have any state value. You can design that class using singleton design pattern.

Problem and solution

First problem is that you need to be sure that your class must be initializing once and the reference is available at anytime so your application can use that reference to use singleton class method.

You can solve the above problem using private constructor. When you define private constructor then nobody can initialize object of that class. (Make sure that singleton class doesn’t have any public constructor).

public class SingletonTest
{

private SingletonTest()
{

}

}


Second problem, if you create private constructor then how can we initialize the class first time when we need it?

We can create and initialize object of singleton class (which have only private constructor) within that class. To achieve this we need to implement one public static method which will return object of Singleton class.

public class SingletonTest
{

private static SingletonTest singletonTest = null;

private SingletonTest()
{

}

public static SingletonTest RetrieveObject()
{
if (singletonTest == null)
singletonTest = new SingletonTest();
return singletonTest;
}

}

Now, we can create and initialize object of our singleton class using below code:


SingletonTest singletonTest = SingletonTest.RetrieveObject();

When user will call this method first time, this method will create new object of SingletonTest class and assign it to private static variable and return that variable.

When user will call the same method again, this method will check that the private static variable is initialized. If private static variable is initialized then it will return it. In our case it initialized already when user called this first time.

So it confirms that our SingletonTest class has only one reference during whole application life cycle.

.Net Remoting

Introduction

Sometime we need to call method on remote machine or on the same machine but in different Application Domain. In this scenario, we need to implement Remoting.

We can also use the following options in behalf of Remoting:

• Web service


• MSMQ


• WSE

Remoting is faster than the above things. MSMQ is depended on Microsoft Message Queue. Web service and WSE is slower than .Net Remoting. But Remoting have also some constraint:

• You can use Remoting only when both side application is develop using .Net Framework


• Remoting uses binary serialization. Microsoft has changed their binary serialization in 2003 and 2005. So your both application must be developed using same version of .Net Framework.

Example
Let’s take an example.

To implement Remoting service in your application, you have one common contract for both client and server application. So first we create one class which will be shared on both client and server application.

Open Visual Studio and create one class library. Create one class. For demo purpose I have created one class “CommonClass”.


public class CommonClass : MarshalByRefObject
{

public string FirstName;

public string LastName;

public string GetWelcomeString()
{
Console.WriteLine("Welcome " + FirstName + " " + LastName);
return "Welcome " + FirstName + " " + LastName;
}

}


Please note that, this class must be derived from MarshalByRefObject class. If you will forget to derive your class from MarshalByRefObject class then you will get runtime error “Trying to create a proxy to an unbound type.”


In the above class, I just declared two public variables, FirstName and LastName and add one public method “GetWelcomeString” which return welcome string with first name and last name.

In GetWelcomeString function, we have writes welcome string on console and also return the same string. This method will execute on server side so it will display text on server side and return value to client side.

Now let’s implement server application. Open visual studio and create one console application.

Add following reference in server application project:

• CommonClass library project


• System.Runtime.Remoting

Add the following code into Program.cs file in main method:


static void Main(string[] args)
{
TcpChannel tcpChannel = new TcpChannel(8084);
ChannelServices.RegisterChannel(tcpChannel);
RemotingConfiguration.RegisterWellKnownServiceType(typeof(ClassLibrary1.CommonClass), "testRemoting", WellKnownObjectMode.Singleton);
Console.WriteLine("Server is ready");
Console.ReadLine();
}


In the above code, first we create TcpChannel class object which will be registered with appropriate port number. Please don’t use the currently used port. If you will use the port number which is already used with another application then you will get runtime error.

After registering TcpChannel, We need to just register our class using RemotingConfiguration. First parameter is which type you want to register; give our class type. Second parameter is URI which is used to communicate with server from client application. And last parameter is WellKnownObjectMode.

WellKnownObjectMode is ENUM and contains two values:

• Singleton – which create only one object of our common class and serve all the request using that object only


• SingleCall – which create object every time when user will send a request to server

Now let’s create client application. Open Visual Studio and create new console application.

Add following reference in server application project:

• CommonClass library project


• System.Runtime.Remoting

Add the following code into Program.cs file in main method:


static void Main(string[] args)
{
CommonClass c = (CommonClass)Activator.GetObject(typeof(CommonClass), "tcp://localhost:8084/testRemoting");
Console.WriteLine("Client is ready");
c.FirstName = "Hardik";
c.LastName = "Patel";
Console.WriteLine("Return string: " + c.GetWelcomeString());
Console.ReadLine();
}


First we create object of our CommonClass but we use Activator.GetObject method to get object from the server.

This method has two parameters, first is type of object which you need to get, pass our CommonClass class type.

Second parameter is URL from which we need to get object.

The URL is: tcp://localhost:8084/testRemoting

We used TCP as a channel service so we need to use TCP protocol.
Server is running on the same machine so I have used “localhost”. If you server application is running on different machine then please use that machine name/IP address instead of localhost.
testRemoting is a URI which is defined in server application when we register our class using RemotingConfiguration (Second parameter).

Now execute server application first. After getting “server is ready” message in server application console, execute client application. Server application will display “Welcome Hardik Patel” message and return the same message to client application. Client application also displays the message “Return string: Welcome Hardik Patel”. Please refer screen below:

Workflow Custom Activity – Part 2

Introduction

Windows Workflow foundation gives many activities to use in workflow like code, delay, IfElse, Listen, Parallel etc. But in some scenario, those activities will not work as per our requirement. So we need to implement custom activity which will work as per our requirement.

To create a custom activity, Please visit http://dotnet-codeguru.blogspot.com/2010/02/windows-workflow-foundation-custom.html

In some scenario, you have to create some dynamic property in custom activity. For an example you are creating custom activity for Order Search. In this activity, User can search order by order id or customer id. So you want to give filter where user can select either “Order ID” or “Customer ID”. If user will select Order ID then new property Order Number will display in property window. And if user will select Customer ID then new property Customer Number will display in property window.

It is similar to IfElse Branch condition property. You get two options “Code Condition” and “Declarative Rule Condition”. As per your selection, you will get sub properties.



Let’s implement same thing in custom activity:

To support sub property for Order Search type we need to implement two classes “OrderSearchByOrderID” and “OrderSearchByCustomerID” which are derived from “ActvityCondition” class.

In the above classes, we need to override “Evaluate” method. In this simple scenario, we always return true from the above classes.

In OrderSearchByOrderID class, we need to add one property for OrderID which will display when user select “OrderSearchByOrderID” option from the selection box. The code for the OrderSearchByOrderID class is look like below:


public class OrderSearchByOrderID : ActivityCondition
{
public override bool Evaluate(Activity activity, IServiceProvider provider)
{
return true;
}

private string orderID;

[Browsable(true)]
public string OrderID
{
get
{
return orderID;
}
set
{
orderID = value;
}
}

}


Code for OrderSearchByCustomerID is similar and looks like below:


public class OrderSearchByCustomerID : ActivityCondition
{
public override bool Evaluate(Activity activity, IServiceProvider provider)
{
return true;
}

private string orderID;

[Browsable(true)]
public string OrderID
{
get
{
return orderID;
}
set
{
orderID = value;
}
}
}


We need to develop “TypeConvertor” which helps to display the option in dropdown box in property window.


public class CustomOrderSearchTypeConverter : TypeConverter
{

private Hashtable _conditionDecls = new Hashtable();

public CustomOrderSearchTypeConverter()
{
try
{
AddTypeToHashTable(typeof(OrderSearchByCustomerID));
AddTypeToHashTable(typeof(OrderSearchByOrderID));
}
catch (Exception ex)
{
throw;
}
}

private void AddTypeToHashTable(Type typeToAdd)
{
string key = typeToAdd.FullName;
object[] attributes = typeToAdd.GetCustomAttributes(typeof(DisplayNameAttribute), false);
if (attributes != null && attributes.Length > 0 && attributes[0] is DisplayNameAttribute)
key = ((DisplayNameAttribute)attributes[0]).DisplayName;
this._conditionDecls.Add(key, typeToAdd);
}

public override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType)
{
try
{
if (sourceType == typeof(string))
{
return true;
}
return base.CanConvertFrom(context, sourceType);
}
catch (Exception ex)
{

throw;
}
}

public override object ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, object value)
{
try
{
string strVal = value as string;
if (strVal != null)
{
if (strVal.Length == 0 || strVal == "")
return null;
else
return Activator.CreateInstance(this._conditionDecls[value] as Type);
}
return base.ConvertFrom(context, culture, value);
}
catch (Exception ex)
{

throw;
}
}

public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType)
{
try
{
if (destinationType == typeof(string))
{
return true;
}
else
{
return base.CanConvertTo(context, destinationType);
}
}
catch (Exception ex)
{

throw;
}
}

public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
{
try
{
if (value == null)
return "";
object convertedValue = null;
if (destinationType == typeof(string) && value is ActivityCondition)
{
foreach (DictionaryEntry conditionTypeEntry in this._conditionDecls)
{
if (value.GetType() == conditionTypeEntry.Value)
{
convertedValue = conditionTypeEntry.Key;
break;
}
}
}
if (convertedValue == null)
convertedValue = base.ConvertTo(context, culture, value, destinationType);
return convertedValue;
}
catch (Exception ex)
{

throw;
}
}

public override StandardValuesCollection GetStandardValues(ITypeDescriptorContext context)
{
try
{
ArrayList conditionDeclList = new ArrayList();
conditionDeclList.Add(null);
foreach (object key in this._conditionDecls.Keys)
{
Type declType = this._conditionDecls[key] as Type;
conditionDeclList.Add(Activator.CreateInstance(declType));
}
return new StandardValuesCollection((ActivityCondition[])conditionDeclList.ToArray(typeof(ActivityCondition)));
}
catch (Exception ex)
{

throw;
}
}

public override bool GetStandardValuesSupported(ITypeDescriptorContext context)
{
try
{
// Always return true.
return true;
}
catch (Exception ex)
{

throw;
}
}

public override bool GetStandardValuesExclusive(ITypeDescriptorContext context)
{
try
{
// Always return true.
return true;
}
catch (Exception ex)
{

throw;
}
}

public override PropertyDescriptorCollection GetProperties(ITypeDescriptorContext context, object value, Attribute[] attributes)
{
try
{
PropertyDescriptorCollection props = new PropertyDescriptorCollection(new PropertyDescriptor[] { });
TypeConverter typeConverter = TypeDescriptor.GetConverter(value.GetType());
if (typeConverter != null && typeConverter.GetType() != GetType() && typeConverter.GetPropertiesSupported())
{
return typeConverter.GetProperties(context, value, attributes);
}
return props;
}
catch (Exception ex)
{

throw;
}
}

public override bool GetPropertiesSupported(ITypeDescriptorContext context)
{
try
{
// Always return true.
return true;
}
catch (Exception ex)
{

throw;
}
}

}


Now we need to use these classes in our custom activity. Just create a new class “OrderSearchActivity” and derive it from “Activity” class.

To give support, we need to implement one dependency property which is ActivityCondition type. Here we declared “OrderSearchTypeProperty” dependency property.

And also implement property for the same “OrderSearchType”.


public static DependencyProperty OrderSearchTypeProperty = DependencyProperty.Register("OrderSearchType", typeof(ActivityCondition), typeof(OrderSearchActivity));

[Description("Order Search Type")]
[Browsable(true)]
[DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)]
[TypeConverter(typeof(CustomOrderSearchTypeConverter))]
public ActivityCondition OrderSearchType
{
get
{
return ((ActivityCondition)(base.GetValue(OrderSearchTypeProperty)));
}
set
{
base.SetValue(OrderSearchTypeProperty, value);
}
}


Now when you will use this OrderSearchActivity in your workflow then you will see option for OrderSearchType.




If you select “OrderSearchByCustomerID” then you will see “+” sign in front of “OrderSearchType” property which indicate that you have some sub properties. When you click on “+” sign you will get “Customer ID” as a sub property.





Now let’s see how we can use the above property in your custom activity. For this I have override Execute method in OrderSearchActivity.


protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{

if (OrderSearchType.GetType() == typeof(OrderSearchByCustomerID))
{
OrderSearchByCustomerID orderSearchByCustomerID = (OrderSearchByCustomerID)OrderSearchType;
string CustomerID = orderSearchByCustomerID.CustomerID;
// User CustomerID variable to search orders
}
else if (OrderSearchType.GetType() == typeof(OrderSearchByOrderID))
{
OrderSearchByOrderID orderSearchByOrderID = (OrderSearchByOrderID)OrderSearchType;
string OrderID = orderSearchByOrderID.OrderID;
// User OrderID variable to search orders
}

return base.Execute(executionContext);
}


In Execute method, we first check OrderSearchType property value type, if it is OrderSearchByCustomerID then we convert it in that and find customer id which is assigned by user. If type is OrderSearchByOrderID then we convert it in that and find order id which is assigned by user.

Windows Workflow Foundation Custom Activity - Part 1

Introduction

Windows Workflow foundation gives many activities to use in workflow like code, delay, IfElse, Listen, Parallel etc. But in some scenario, those activities will not work as per our requirement. So we need to implement custom activity which will work as per our requirement.

To create a custom activity, we need to implement one class which is derived from “Activity” base class because every workflow activity is directly/indirectly derived from “Activity” class.

Example

Let’s take one example. We have to create one workflow for Employee management. For this workflow, we need one activity which has some properties like FirstName, LastName, Age etc… (For employee entity) and have one function which will insert employee data into database.

For the above scenario, we need to create our own activity which takes care of above functionality.

Here below I created one class “EmployeeActivity” which is derived from the “Activity” class.

public class EmployeeActivity : Activity

{

}

Now we need to add properties for Employee entities. Here I took only 3 properties for demo purpose.

public class EmployeeActivity : Activity

{

private string firstName;

public string FirstName

{

get { return firstName; }

set { firstName = value; }

}

private string lastName;

public string LastName

{

get { return lastName; }

set { lastName = value; }

}

private int age;

public int Age

{

get { return age; }

set { age = value; }

}

}

In above code, I have created three private variables and public properties for FirstName, LastName and Age. You can add any number of variables and properties which you need.

Now we need to override “Execute” method of “Activity” class. This method will take care of inserting employee data into database.

Let’s implement “Execute” method:

protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)

{

/// Code to insert employee data into database

return base.Execute(executionContext);

}

Now you can put your custom activity on workflow and set the property. Whenever this activity will execute, it will insert employee data into database.

Now think that user wants to do some pre authorization before data will store into database. Means you have to implement one event which will execute before employee information will store in employee database.

Let’s create one event and integrate it with EmployeeActvity:

public delegate void PreAuthorizationEventHandler(object sender, CustomActivityEventArgs e);

public event PreAuthorizationEventHandler PreAuthorizationEvent;

Here we created one delegate and one event. We create one class “CustomActivityEventArgs” which is derived from “EventArgs” base class.

[Serializable]

public class CustomActivityEventArgs : EventArgs

{

private string firstName;

public string FirstName

{

get { return firstName; }

set { firstName = value; }

}

private string lastName;

public string LastName

{

get { return lastName; }

set { lastName = value; }

}

private int age;

public int Age

{

get { return age; }

set { age = value; }

}

}

Now we need to change “Execute” method code to execute event. Updated code is look as below:

protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)

{

CustomActivityEventArgs customActivityEventArgs = new CustomActivityEventArgs();

customActivityEventArgs.FirstName = FirstName;

customActivityEventArgs.Age = Age;

customActivityEventArgs.LastName = lastName;

//Here we are executing event

if (PreAuthorizationEvent != null)

PreAuthorizationEvent(this, customActivityEventArgs);

/// Code to insert employee data into database

return base.Execute(executionContext);

}

When you will use this EmployeeActivity in you workflow, you will get one event “PreAuthorization” which will execute before employee data inserting in the database.

Basic of Entity Framework

Introduction

Microsoft has evolved new technology “Entity Framework” to work with Database. Before this, Microsoft has introduced LINQ to work with the objects which derived from the IEnumerable.

Entity framework is easy to use for developer to generate entity classes for tables/views. Entity framework also generates methods to execute stored procedure which you have created into database.

Entity Framework needs Visual Studio 2008 Service Pack2 installed on the machine.

Developer can easily drag and drop the entity object from server explorer to Entity Framework UI. You can easily generate Entity Framework classes from the existing database. Reverse engineering is not possible in Visual Studio 2008. We can expect this functionality from Visual Studio 2010.

Let’s start with Entity Framework

I have used following database structure to understand Entity framework in .Net application.

Table Name	Contact
Field Name	Data Type	Constraint
ContactID	Int	PK, Auto Increment
FirstName	Varchar(100)	Not Null
LastName	Varchar(100)	Not Null
Title	Varchar(50)	Not Null
AddDate	DateTime	Not Null, Default Value = GetDate()
ModifiedDate	DateTime	Not Null, Default Value = GetDate()
Table Name	Address
Field Name	Data Type	Constraint
AddressID	Int	PK, Auto Increment
Street1	Varchar(50)
Street2	Varchar(50)
City	Varchar(50)
StateProvince	Varchar(50)
CountryRegion	Varchar(50)
PostalCode	Varchar(50)
AddressType	Varchar(50)
ModifiedDate	DateTime
ContactID	Int	FK

Here we took example of Contact and Address table. One contact person has many addresses like permanent address, office address, Farm-house address etc… So we took “ContactID” field as a foreign key in Address table.

Now let’s create Entity Framework project in .Net IDE.

First open Visual Studio 2008 and select File -> New -> Project option. It will open “New Project” dialog box. Select Project Type “Visual C#” -> “Windows”. Select “Windows Form Application” from Template options. Provide project name, project location and solution name as you want.

Note: You can also take console application or another type of project. But for demo purpose I have chosen windows form application.

After successfully loading project, Right click on project in solution explorer and add new item. In New Item Dialog box select “ADO.Net Entity Data Model” and give name ContactDataModel.edmx. Click on Add button. It will start Entity Framework wizard.

• In entity framework wizard home page you will get two options “Generate from database” and “Entity model”. You can generate Entity Model using existing database or can use blank entity model to generate your classes manually. [Reverse engineering is not possible in Visual Studio 2008]. Select “Generate from Database” option and click next button
• In next screen, it allows to create new connection or use existing connection which created before. You can create new connection using “New connection” option. After selecting the proper data connection, Connection string text box will display the connection string used to communicate with the database. You can save the connection string in App.config file. To save connection string in App.Config file just give proper name to “Save entity connection settings in App.config As” textbox. Give “TestEntityEntities” name as connection string name. Click on Next button.
• In Next screen, you have option to choose which object you want to include in entity framework. You can select Tables, Views and Stored Procedures. Select Address and Contact Table. Click on Finish Button.
• Entity Framework Wizard will create Entity Framework Model file in your project and option UI in Visual studio. You can see Contact and Address entities.

Entity Framework also generates classes in code behind file for Contact and Address entities. It will generate three classes:

• Address: This class contains private variables and public properties for each field.
• Contact: This class contains private variables and public properties for each field.
• TestEntityEntities: This class is derived from ObjectContext class and has property for Address and Contact entities. This class has method to add new objects in Contact and Address classes.

Now let’s use it in our windows application:

Create new windows form in our windows form application and drag DataGridView control on windows form. Write following code to display all contact information in data grid view.


TestEntityEntities entities = new TestEntityEntities();
var query = from c in entities.Contact select c;
dataGridView1.DataSource = query;

In above code, we created object of the TestEntityEntities class which is generated by Entity Framework wizard in code-behind file. Just use LINQ syntax to get all the contact information from the TestEntityEntities object and display all the contact information in DataGridView object.

Add new record using Entity Framework:

Let go with the Contact Entity. The below code is useful to add new record in contact entity.


TestEntityEntities testEntityEntities = new TestEntityEntities();
Contact contact = new Contact();
contact.FirstName = "test1";
contact.LastName = "test2";
contact.Title = "Mr.";
testEntityEntities.AddToContact(contact);
testEntityEntities.SaveChanges();

First of all we create object of Entities class which is created by Entity Framework. Then after we create object of Contact and assign value to the required properties. After successfully assign value to contact entity, we will call “AddToContact” method of TestEntityEntities class. This method will store the data in Contact table but still the data is not committed. So commit the added data, we need to call “SaveChanges” method of TestEntityEntities class.

Edit existing record using Entity Framework:

To edit any existing record, we need to fetch that record and update it in memory and call SaveChanges method to save the changes. Below code is used to edit existing record.


TestEntityEntities testEntityEntities = new TestEntityEntities();
Contact contact = testEntityEntities.Contact.First(c => c.FirstName == "test1");
contact.FirstName = "test1_Changed";
testEntityEntities.SaveChanges();


First we create object of TestEntityEntities class. Then after we get contact entity record using TestEntityEntities class method. After getting the record, we just need to update the property values and call SaveChanges function.

Deleting existing record:

To delete the record, we need to load the record and delete it using Entity Framework. Below code is used to delete the record.


TestEntityEntities testEntityEntities = new TestEntityEntities();
Contact contact = testEntityEntities.Contact.First(c => c.FirstName == "test1");
testEntityEntities.DeleteObject(contact);
testEntityEntities.SaveChanges();

We just load the record and then call DeleteObject method. This method will not commit your changes to database. To commit the changes, we need to call SaveChanges method.

For more information on how to map stored procedure with Entity, click here.

Data Warehousing Basic

What is Data Warehousing?

Data warehouse is mainly used to store bulk data and generate analysis report. These analysis reports are very important for taking business decision.

Following are constraint to develop analysis report from the relational database:
• Performance is very poor
• Difficult to develop
• Put very heavy load on database

If you will go with data warehousing then you can easily use SQL server analysis service to generate analysis report.

Generally Data ware housing use two type of database structure:

• Star schema
• Snow flake schema

Key Terms:

Dimension: Dimensions are the entity using it you want to analysis the data. For an example, Product is an entity; it is dimension which is used to analysis the sales report product wise.

Fact: Fact is the entity on which you are analysis the data. For an example sales data is a fact. Fact data must be numeric data so you can analysis the report. For an example, you can calculate minimum/maximum/average of the data. You can use many function on that numeric data.

Dimension tables are joined with fact table directly or in-directly.

Star Schema
If all the dimension tables are joined directly to fact table then that schema is called star schema. Here I took an example of Sales management system.




Snow Flake Schema
If any dimension table is not directly joined with fact table but it is joined through the other dimension tables then that schema is called snow flake schema.



In above diagram, you can see “DimState” and “DimCountry” are dimension tables and connected to Fact table “FactSales” through the DimCity dimension table.

Send HTTP Web Request using C# Application

In some application, we need to send HTTP web request to web application server. While sending http request to server our .Net code block the execution till the response come back to us. But in some scenario we need not wait for response on the same time because we need that output later on.


For the above problem, we can send asynchronous HTTP web request to web application server. When we send web request asynchronous then our application resume the execution and when we need the output of that web request then we can get is using IAsyncResult object.

Here I explained come sample to send asynchronous HTTP web request to web application.

Before starting it, we need to understand the web request types. Actually, any browser send HTTP request using two methods:

• GET
• POST

In GET method, Browser sends form data into query string, so user can easily view the data in address bar and also change the data.

In POST method, Browser sends form data with URL as an attachment. User can’t see the POST data in address bar so it is not possible to change the data from address bar.

First we go through asynchronous HTTP web request method using “GET”. Please check the code below:

///

/// Gets the response.
///

/// <param name="StrURL">The URL.
/// HTML source
public string GetResponse(string StrURL)
{
string strReturn = "";
HttpWebRequest objRequest = null;
IAsyncResult ar = null;
HttpWebResponse objResponse = null;
StreamReader objs = null;
try
{

objRequest = (HttpWebRequest)WebRequest.Create(StrURL);
ar = objRequest.BeginGetResponse(new AsyncCallback(GetScrapingResponse), objRequest);

//// Wait for request to complete
ar.AsyncWaitHandle.WaitOne(1000 * 60, true);
if (objRequest.HaveResponse == false)
{
throw new Exception("No Response!!!");
}

objResponse = (HttpWebResponse)objRequest.EndGetResponse(ar);
objs = new StreamReader(objResponse.GetResponseStream());
strReturn = objs.ReadToEnd();
}
catch (Exception exp)
{
throw exp;
}
finally
{
if (objResponse != null)
objResponse.Close();
objRequest = null;
ar = null;
objResponse = null;
objs = null;
}
return strReturn;
}

///

/// Gets the scraping response.
///

/// <param name="result">The result.
protected void GetScrapingResponse(IAsyncResult result)
{

}


Let’s understand the above code:

We have created the method “GetResponse” which take URL as a parameter. You have to pass query string data with URL in this method.

For example: you can call this method like

GetResponse(“http://www.codeproject.com/?cat=4”);


In this method, I created object for HttpWebRequest, HttpWebResponse, StreamReader and IAsyncResult.

• HttpWebRequest: This object is used to send http web request to web application server. You can use BeginGetResponse and EndGetResponse method of HttpWebRequest object to get response asynchronously.
• HttpWebResponse: This object is used to get response from web application server.
• StreamReader: This object is used to get response stream.
• IAsyncResult: This object is used to send and retrieve asynchronous http web request.

We created HttpWebRequest object using WebRequest.Create method. After creating object, I called BeginGetResponse method to send the http request to web application server. After calling this method, our application execution resume because we used asynchronous method for Http Web Request.

When you need web response then you can use the HttpWebRequest object and call EndGetResponse method. After getting response successfully, we converted it in stream and later on in string variable.

If web applications use “POST” method to send the request data then you have to change the method little bit.

///

/// Gets the response with post.
///

/// <param name="StrURL">The URL.
/// <param name="strPostData">The post data.
/// HTML Result
protected string GetResponseWithPost(string StrURL, string strPostData)
{
string strReturn = "";
HttpWebRequest objRequest = null;
ASCIIEncoding objEncoding = new ASCIIEncoding();
Stream reqStream = null;
HttpWebResponse objResponse = null;
StreamReader objReader = null;
try
{
objRequest = (HttpWebRequest)WebRequest.Create(StrURL);

objRequest.Method = "POST";
byte[] objBytes = objEncoding.GetBytes(strPostData);
objRequest.ContentLength = objBytes.Length;
objRequest.ContentType = "application/x-www-form-urlencoded";
reqStream = objRequest.GetRequestStream();
reqStream.Write(objBytes, 0, objBytes.Length);

IAsyncResult ar = objRequest.BeginGetResponse(new AsyncCallback(GetScrapingResponse), objRequest);
//// Wait for request to complete
ar.AsyncWaitHandle.WaitOne(1000 * 60 * 3, true);
if (objRequest.HaveResponse == false)
{
throw new Exception("No Response!!!");
}
objResponse = (HttpWebResponse)objRequest.EndGetResponse(ar);
objReader = new StreamReader(objResponse.GetResponseStream());
strReturn = objReader.ReadToEnd();

}
catch (Exception exp)
{
throw exp;
}
finally
{
objRequest = null;
objEncoding = null;
reqStream = null;
if (objResponse != null)
objResponse.Close();
objResponse = null;
objReader = null;
}
return strReturn;
}

///

/// Gets the scraping response.
///

/// <param name="result">The result.
protected void GetScrapingResponse(IAsyncResult result)
{

}


I created one more method “GetResponseWithPost”. This method actually sends data using Post method to web server. This method takes two parameters: URL and PostData. URL is just an URL of any web application. URL is not contains any query string data. Another parameter PostData contains data to post on the web server.

We need following changes to work with POST method:

• We need one more parameter to pass Post Data
• Need to assign “POST” to Http Web Request method property
• Need to set ContentLength and ContentType
• Create Stream before sending actual web request to web server

I think you have question, how to know the POST data while submitting web request to web server using browser. For this you can use LiveHTTPHeader add-ons in Firefox. This add-ons display all the post data when you access the web application using Firefox.