Caveman's Blog

My commitment to learning.

Posts Tagged ‘Sudheer Reddy Battula

Dependency Injection: Unity Application Block

leave a comment »


In this post I will show you how to use Microsoft Unit Application Block to achieve Dependency Injection. We will see the two methods of configuring the IOC container, firstly the “.config” way and then the “inline” method. Let us take a look at the following code snippet and see how this code can be improved.

class Program
{
    static void Main(string[] args)
    {
        Service svc = new Service();
        svc.Print();
    }
}

public class Service
{
    public void Print()
    {
        Employee empl = new Employee();
        empl.PrintEmployeeTitle("Test Employee");
    }
}

public class Employee
{
    public void PrintEmployeeTitle(string name)
    {
        Console.WriteLine("Employee Name: {0}, Title:{1}", name, "Some Title");
    }
}

What does this code do?

The above code snippet if from a console application, where we are creating an instance of the Service class and are calling the Print method. The Print method in turn instantiates the Employee class and then calls the PrintEmployeeTitle method of the Employee class to print the employee name and title. The PrintEmployeeTitle method writes the name and title of an employee to the console.

What is wrong?

Nothing. While there is nothing wrong with this code, if we closely observer, we can notice that the Employee class instance could not exist without an instance of the Service class. Basically they both are tightly coupled, meaning to say we can only have one implementation of the Employee class to be consumed by the Service class at any given instance of time.

What if we have a scenario when we want to test more than one implementation of the Employee class or if the Employee class implementation is a work-in-progress? Here is where Dependency Injection design pattern comes to our rescue. I hope I have set some context before I explaining about DI and its implementation.

Solving the problem

Decoupling the Employee class life cycle management from the Service class is the primary objective.  The advantage of decoupling the Employee class are that 1) we will be in a position to provide multiple implementations to the Employee class 2) be able to select the kind of implementation that is suitable for our purpose and 3) manage the life cycle of the Employee class. We define an interface IEmployee with one method PrintEmployeeTitle and define two implementations for this demo purpose. The first implementation is what we already had above and the second is a MockEmployee Class.

public class Employee : IEmployee
{
    public void PrintEmployeeTitle(string name)
    {
        Console.WriteLine("Employee Name: {0}, Title:{1}", name, "Some Title");
    }
}

public class MockEmployee : IEmployee
{
    public void PrintEmployeeTitle(string name)
    {
        Console.WriteLine("Employee Name: {0}, Title:{1}", name, "Some MOCK Title");
    }
}

public interface IEmployee
{
    void PrintEmployeeTitle(string name);
}

Let us see how we can delegate the Employee class instantiation to the client (Class:Program; Method: Main) and then inject the Employee object into the Service object.

Dependency Injection

Service class has a dependency on the Employee class and our objective here is inject this dependency into the Service class from the Client. Dependency injection is a software design pattern that allows a choice of component to be made at run-time rather than compile time [2]. One way to achieve this is via passing the Employee object reference to the Service class constructor like in the code below:

class Program
{
    static void Main(string[] args)
    {
        Employee empl = new Employee();
        Service svc = new Service(empl);
        svc.Print();
    }
}

public class Service
{
    private IEmployee empl;
    public Service(IEmployee empl)
    {
        this.empl = empl;
    }

    public void Print()
    {
        empl.PrintEmployeeTitle("Test Employee");
    }
}

Another way of injecting the Employee reference into the Service object is via setting the instance of Employee class to an I IEmployee property of the Service class.

class Program
{
    static void Main(string[] args)
    {
        Employee empl = new Employee();
        Service svc = new Service();
        svc.empl = empl;
        svc.Print();
    }
}

public class Service
{
    IEmployee _empl;
    public IEmployee empl
    {
        set
        {
            this._empl = value;
        }
    }
    public void Print()
    {
        _empl.PrintEmployeeTitle("Test Employee");
    }
}

We have so far been able to decouple the Service class and the Employee class, however we still have to create an instance of the Employee class to implement dependency injection. Any change in to the Employee class creation mechanism with require a code change and also a code recompile.  This is the point where IOC framework comes handy in automating the creation and injection of the dependency via just one configuration.

Inversion of control

In software engineering, Inversion of Control (IoC) is an object-oriented programming practice where the object coupling is bound at run time by an assembler object and is typically not known at compile time using static analysis [1]. Like discussed earlier we are going to transfer the control of creating the Employee object to the IOC framework rather than keeping it with the Client, mean to say we are performing an “Inversion of Control”.

The configured entities are loaded into an IOC container at run-time and will be injected into the appropriate classes. We can implement .Net Dependency Inject using any one of the following IOC containers:

Microsoft Unity IoC Container

Now let us look at the two ways of configuring and implementing DI using Microsoft Unity Container. Before we can use the Unity container you have to download and install the Microsoft Unit Application Block from the Microsoft Patterns and Practices website. The dll’s necessary for this implementation can be found under the “Drive:/Program Files/Microsoft Unity Application Block x.0/Bin/” folder and should be added as references to your project.

Please accept my apologies for a very crude/rude representation of the client (Class: Program, Method: Main) being able to select one of the three implementations of the Employee class use Dependency Injection via an IOC container.

Application Configuration File

When we have to implement DI using the Application Config file, we have to define Unity block section, define a container and register that namespace and the Class that is getting DI’ed.

<configSections>
<section name="unity" type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection,Microsoft.Practices.Unity.Configuration" />
</configSections>
<unity xmlns="http://schemas.microsoft.com/practices/2010/unity">
<alias alias="singleton" type="Microsoft.Practices.Unity.ContainerControlledLifetimeManager,
Microsoft.Practices.Unity" />
<container name="TestService">
<register type="UnityFrameworkDemo.IEmployee, UnityFrameworkDemo"
mapTo="UnityFrameworkDemo.MockEmployee, UnityFrameworkDemo">
<lifetime type="singleton" />
</register>
</container>
</unity>

I had to also add a reference of the System.Configuration namespace to the project. Once we have this configuration all set, we have to update the client to 1) load the container that we defined in the configuration and 2) generate the Service class based on the configuration that we have defined:

class Program
{
    static void Main(string[] args)
    {
        try
        {
            container.LoadConfiguration("TestService");
            svc = container.Resolve();
            if (svc != null)
                svc.Print();
            else
                Console.WriteLine("Could not load the Service Class");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

public class Service
{
    IEmployee _empl;
    public Service(IEmployee empl)
    {
        this._empl = empl;
    }

    public void Print()
    {
        _empl.PrintEmployeeTitle("Test Employee");
    }
}

You would also have noticed that I have added a constructor that accepts parameter of IEmployee type to the Service class. The IOC framework will use this constructor to generate an instance of the Service class and will also pass a reference of the Employee class into the Service instance. Following is the output of using the default Employee implementation:

Now switching to the Mock Employee implementation from the default implementaion is as simple as updating the register element of the configuration with the MockEmployee class name and then we get the following output


Inline

Lastly let us look at how we can configure an implementation inside the client instead of in the application config file. Basically you have to register the Interface and the implementation with the container in the client and your client is ready to make a call to the Service instance methods like shown in the code below:

container.RegisterType();
   svc = container.Resolve();
   if (svc != null)
      svc.Print();

Unity framework basically provides a fantastic approach to decouple the application layer code. You can define several containers and register several classes with the container and be able to enjoy the flexibility by implementing Dependency Injection using the Unity IOC container.

Happy Fourth of July !

References:
1. Inversion of Control – IOC – Wikipedia
2. Dependency Injection – Wikipedia

 

SQL Server: Table Partitions as an archiving solution

leave a comment »


Problem Statement

How can you design an archiving solution on a large table without deleting any data and also achieve improved performance on CRUD operations (on the same table)? The assumption in this scenario is that the most recent data would be accessed more often that the older data.

Solution

Archiving solutions can be of two types; 1) passive and 2) active. A passive solution is one where the historic data is archived in another table on another database, making the data unavailable. An active solution is one where the historic data is archived and will still be available for access without much of an impact on the application performance. A large table typically contains millions of rows and probable has a size that runs into several gigabytes. Just the size of the table makes it very expensive to perform CRUD operations and difficult to maintain indexes.

Table Partitioning in SQL Server 2005 and up lets a table data and indexes to be stored in several smaller partitions. This feature is very useful in providing a way to easily maintain and perform database operations on that table. Each partition will be stored in a different file which can be part of a filegroup. Data is distributed evenly between the files in a filegroup. Allowed columns can be used as a partition key which is the criteria for partitioning. You will be able to define the boundaries of a partition by defining the limits of the partition key. This division is based on how you access the rows of the table. In addition to identifying the partition key, we also will want to include that key as part of the index and also partition the index. Partitioning the index is called as index alignment. This was when the index portion will be stored along with the data rows stored in the partition.

Dividing a table into several files gives us the flexibility of storing those files on separate drives. We can store the files that contain data from the recent years on faster drives as opposed to storing the older data on slower drives. Going with assumption in the problem statement that the most recent data is accessed more often that the older data, we will have improved the performance on this table because we will have faster response times thanks to the faster drives.

Note: All data types that are valid for use as index columns can be used as a partitioning column, except timestamp. The ntext, text, image, xml, varchar(max), nvarchar(max), or varbinary(max) data types cannot be specified. [1]

References:
1. Partitioned Tables and Indexes
2. Create Partitioned Tables and Indexes

Written by cavemansblog

July 2, 2013 at 9:32 pm

SQL Server: Data transfer for Excel using Linked Server

leave a comment »


Introduction

I will demonstrate yet another way to perform data transfer to and from an excel file using a Linked Server in SQL Server 2008. In this demo I will show you how to:

  • Create a Linked Server using Excel as the data server
  • Import data to a SQL Server table from a spreadsheet
  • Export data to a spreadsheet from a SQL Server table

Create a Data Source

As the first step in this process I have created an Excel file with a spreadsheet named “Employee”, followed by defining the headers and inserting 5 records as showing in following illustration:

Create a Linked Server

Now that we have a data source let us create a linked server using an the Excel file created in the previous step as the data source. Open SQL Server Management Studion (SSMS) and expand “Server Objects” under the intended SQL Server, to find the Linked Servers item.Right click on Linked Servers and click on “New Linked Server” to see following dialog box. EXCEL_LINKED_SERVER is the name that I have chosen to call this linked server. Then we need to populate the Provider, Product Name, Data Source and Provider String like in this example and click the OK button.

A linked server should have been created successfully at this time. Right click on the “Linked Server” tree view item and click on refresh to see the newly created linked server. Expand the linked server to see the Exployee$ spreadsheet under the list of tables.

Import Data

At this point you should be able to access the Excel spread sheet just like any other SQL Server database table.  Let us import data into a table named DataFeed in the Demo database. It is important to note that the spreadsheet name in the SQL query has to be accessed only by preceding spreadsheet name  with “…”  Only one dot is used to represent the current database, two dots to represent another database in the SQL Server instance and three dots represent a Linked Server. When we would have ran the following SQL query, a table named “DataFeed” would have been created and 5 records from this table would be displayed in the results pane.

Export Data

Let us insert a couple of records into the DataFeed table that was created in the above step, followed by exporting those two rows to the spreadsheet. When the following query us run, you will observe that the spread sheet will have 7 records.

Now Let us look at the new data in the spreadsheet:

Written by cavemansblog

July 28, 2012 at 5:47 pm

SQL Server: Incorrect SET options on a stored procedure error

leave a comment »


We had to put out a another fire at work when a Stored Procedure that was not modified in ages started to fail. Following is the error that was caught by the application.

INSERT failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

As specified in the error above, there was something wrong with the SET options. After a little bit of research I figured out that if the SET options are not correctly defined, this error could occur. Especially when a filtered index is added to a table, sql server requires it to be created with SET QUOTED_IDENTIFIER setting as ON. Take a look at the following blog post to recreate this error.

First attempt at fixing the error by SET ing the correct options on the stored procedure did not help the cause:


SET NUMERIC_ROUNDABORT OFF
GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

ALTER procedure [dbo].[STORED PROCEDURE NAME]

Solution: Apparently a new index was added to a table, was causing the issue. This index was interfering with a row insert on this table. Disabling the filtered index fixed the issue. This to me seems like a temporary solution, we still have to figure out how to make the filtered index work for this table.

SQL Server: Restore a database from a .mdf file.

leave a comment »


In this blog post I will show you how to restore a database from a .mdf file alone. I am working with the AdventureWorks database in this demonstration. Download the .mdf file for the AdventureWorks database from CodePlex.

1. Open SQL Server Management Studio (SSMS).
2. Right click the Databases folder. select Attach from the context menu.
3. Click Add and select the appropriate .mdf file. Click Ok, and then click Ok again. You will get an error at this time because SSMS could not find the corresponding .ldf file.
4. Select the .ldf file entry and click Remove and click Ok.
5. You have successfully restored a database from the .mdf file.

Check out the steps as a pictorial in the slide show below:

This slideshow requires JavaScript.

SQL Server: How to select data from a stored procedure?

with one comment


In his post I will show you two ways of using SQL to query the data that is generated by the execution of a stored procedure.

Method 1: Using OPENQUERY [1] to be able to select the intended data. OPENQUERY executes the specified pass-through query on the specified linked server. “getEmployees” stored procedure returns all the records from the employee table. OPENQUERY executes the “getEmployees” stored procedure in the Demo database and also allows us to  execute a select command on the result set.


SELECT * FROM OPENQUERY ([SERVERNAME],'exec demo.dbo.getEmployees')

First time I ran the above query I got an error: Server ‘[SERVERNAME]’ is not configured for DATA ACCESS. We have to set the DATA ACCESS option of the SQL Server Instance to true, to be able to use OPENQUERY and that can be done as follows:


exec sp_serveroption @server = '[SERVERNAME]', @optname = 'DATA ACCESS', @optvalue = 'TRUE'

How you can find the SQL Server instance name?

The select statement with OPENQUERY executed successfully. At this point we should be able to select all or specific columns from the resultset of the stored procedure like in the example below:

Method 2: Using table variable to select the intended data. In this method we first create a table variable with a schema that matches the output of stored procedure execution. We then capture the output of the stored procedure execution in a table variable. At this point we will be able to scout through all the output data using DML.


declare @tempTable table(EmpID int, EmpName varchar(50), Salary int)

insert into @tempTable
exec demo.dbo.getEmployees

select * from @tempTable

References:
1. OPENQUERY – MSDN Online

SQL Server: Size of a Table and Database

leave a comment »


We can use sp_spaceused, a system stored procedure, to know the size of a table or a database. Here is a sample usage on a table. sp_spaceused displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

sp_spaceused 'Employee'

We can use sp_msForEachTable in combination with sp_spaceused to iterate over all the tables and list the corresponding size of each table of a given database. The sample query lists the attributes of the 2 tables that exist in my sample database.

EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

The following example summarizes space used in the current database and uses the optional parameter @updateusage to ensure current values are returned for the Demo database.

EXEC sp_spaceused @updateusage = N'TRUE';
GO

References:
1. MSDN Online