Application design and programming in .NET

Unit Test a project having external dependency(WCF Proxy) using Fakes & Visual Studio 11 Beta

Atul.Verma — Thu, 03 May 2012 09:05:13 GMT

In this post I’ll explain the steps to generate unit tests for a project which calls a WCF service using Fakes. Microsoft Fakes is an isolation framework for creating delegate-based test stubs and shims in .NET Framework applications. The Fakes framework can be used to shim any .NET method, including non-virtual and static methods in sealed types.

The Fakes framework helps developers create, maintain, and inject dummy implementations in their unit tests. The Fakes framework generates and maintains stub types and shim types for a system under test.

I had previously discussed about creating Unit Tests using Pex, Moles and Visual Studio 2010. The projects inside the sample solution are

DemoService: This project is a WCF Service.

DemoLibrary: This project is a Class library and service reference to DemoService has been added. Unit tests will be generated for this project.

ConsoleApp: This project is a Console application.

DemoLibrary.Tests: This is a Test project and contains unit tests for DemoLibrary.

The solution structure is displayed below

DemoLibrary calls DemoService though proxy as displayed in the Layer diagram

I’ll now discuss in brief the code snippets of each project

WCF Service(DemoService): This service provides only a single operation

     [ServiceContract]

    public interface IDemoService

    {

        [OperationContract]

        string Search(string criteria);

    }

WCF Service Client(DemoLibrary): It calls the Search method of DemoService through proxy as displayed below

    public string GetResults(string s)

    {

        DemoServiceReference.DemoServiceClient client = null;

        try

        {

            client = new DemoServiceReference.DemoServiceClient();

            client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;                

            client.ChannelFactory.Credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;

            s = client.Search(s);

            return s;

        }

        finally

        {

            if (client != null)

            {

                if (client.State == CommunicationState.Opened)

                {

                    client.Close();

                }

                else if (client.State == CommunicationState.Faulted)

                {

                    client.Abort();

                }

            }

        }

    }

Adding Unit Tests for DemoLibrary:

In order to Unit Test WCF Service Client(DemoLibrary) project using Fakes and Visual Studio 11 the steps are

Add a new Unit Test project as displayed below

In order to isolate the dependencies we need to add Fakes for DemoLibrary and System.ServiceModel assemblies and behaviour will be redefined using delegates. Add Fakes assembly as displayed below
A .fakes file will be added to the project under Fakes folder and reference to {Assembly}.Fakes.dll will be added as displayed below
Similarly as explained above we need to generate Fakes for System.ServiceModel assembly. I got “Failed to generate Stub/Shim for type …” error messages on building the project. There are couple of ways to fix it.

Change the Target Framework of the Test project from .NET Framework 4.5 to .NET Framework 4.0

Open the System.ServiceModel.fakes and remove the types for which Shim/Stub generation is failing. Since I’m not using Stubs I’ve disabled the StubGeneration as displayed below

The next step is to Mock the Service call(redefine behaviour using delegates) and add Asserts as displayed in code snippets below. The main points are

I have used Shims to isolate calls to non-virtual functions in unit test methods. Read more about Shim and Stub types.
When using shim types in a unit test framework, you must wrap the test code in a ShimsContext to control the lifetime of your shims.

      [TestMethod]

      public void TestSearch()

      {

          using (ShimsContext.Create())

          {

              ShimWCFService<IDemoService>();

              ShimDemoServiceClient.Constructor = (var1) =>

              {

                  new ShimDemoServiceClient { };

              };

              ShimDemoServiceClient.AllInstances.SearchString = (var1, var2) =>

              {

                  return "Result";

              };

              Search search = new Search();

              string result = search.GetResults("test");

              Assert.IsNotNull(result);

              Assert.AreEqual(result, "Result");

          }

      }

      /// <summary>

      /// Mocks the WCF service.

      /// </summary>

      private void ShimWCFService<TService>() where TService : class

      {            

          ShimClientCredentials.Constructor = (var1) =>

          {

              new ShimClientCredentials();

          };

          ShimClientCredentials.AllInstances.WindowsGet = (var1) =>

          {

              return new ShimWindowsClientCredential();

          };

          ShimWindowsClientCredential.AllInstances.ClientCredentialGet = (var1) =>

          {

              return new System.Net.NetworkCredential();

          };

          ShimWindowsClientCredential.AllInstances.ClientCredentialSetNetworkCredential = (var1, var2) => { };

          ShimWindowsClientCredential.AllInstances.AllowNtlmGet = (var1) => { return true; };

          ShimWindowsClientCredential.AllInstances.AllowNtlmSetBoolean = (var1, var2) => { };

          ShimWindowsClientCredential.AllInstances.AllowedImpersonationLevelGet = (var1) => { return System.Security.Principal.TokenImpersonationLevel.Impersonation; };

          ShimWindowsClientCredential.AllInstances.AllowedImpersonationLevelSetTokenImpersonationLevel = (var1, var2) => { };

          ShimChannelFactory.AllInstances.CredentialsGet = (var1) =>

          { return new ShimClientCredentials(); };

          ShimClientBase<TService>.AllInstances.ClientCredentialsGet = (var1) =>

          {

              return new System.ServiceModel.Description.ClientCredentials();

          };

          ShimClientBase<TService>.AllInstances.ChannelFactoryGet = (var1) =>

          {

              return new ShimChannelFactory<TService>();

          };

          ShimClientBase<TService>.AllInstances.StateGet = (var1) =>

          {

              return CommunicationState.Opened;

          };

          ShimClientBase<TService>.AllInstances.Close = (var1) =>

          { };

          ShimClientBase<TService>.AllInstances.Abort = (var1) =>

          { };

      }

Go to Unit Test explorer and run the tests
You can also Analyze the code coverage. The code coverage results are displayed below

The sample is available for download @ Download Source code

Visual Studio 11 database projects and Unit Testing SQL Server database

Atul.Verma — Sat, 18 Feb 2012 19:02:39 GMT

Please find the slides and demos for the session I presented at Microsoft User Group Hyderabad(MUGH). The agenda of the session was

Overview about Database Projects
Database development with SQL Server Data Tools(Code-named Juneau)
DEMO – Database development using SQL Server Data Tools and Visual Studio 11
Basics of Database Unit Testing
DEMO – Unit Testing Visual Studio 2010 Database projects

Download from

Visual Studio 11 Developer Day – Hyderabad(18th Feb 2012)

Atul.Verma — Mon, 13 Feb 2012 18:51:50 GMT

Microsoft User Group Hyderabad (MUGH ) invites you to Developer Day on 18th Feb 2012 ( 9:30 A.M to 1.30 PM ) at Microsoft Campus, Building 3 MPR Halls, Gachibowli, Hyderabad. Join us to meet Charles Sterling, Senior Program Manager, Visual Studio Team. Charles will deliver 90 min session on “Application Lifecycle Management with Visual Studio 11”. Additionally, we have two sessions on “Debug production hosted application with Intellitrace and Visual Studio 11” and “Visual Studio 11 database projects and Unit Testing SQL Server database”.

Check out the Complete Agenda from here : http://mugh.net/vs11devday/agenda.html

Utility to generate Word documents from templates using Visual Studio 2010 and Open Xml 2.0 SDK - Part 4

Atul.Verma — Tue, 24 Jan 2012 12:31:00 GMT

This is the fourth part of this series. The code is available for download at http://worddocgenerator.codeplex.com/.

In Part 1 I discussed about

Document generation using Content Controls and Open XML 2.0 SDK
Creating Word templates
Implementation and Samples

In Part 2 I discussed about

List of functionalities that can be achieved using the utility/source code
Description regarding Samples provided with utility
New samples added in this update

In Part 3 I discussed about

Document-level customizations for Word 2007 and Word 2010
One of the ways to “Refresh the document from within the Word(e.g. right click on document and click Refresh) using document-level customizations for Word 2007 and Word 2010“

This post will focus on Charts. I’ve added the samples to generate/refresh charts using OpenXML. The screenshot below displays the template having Scatter chart and Line chart and the document generated out of this template using this utility.

Word 2010 Template having Charts –> Generated documents with Charts refreshed:

Code Changes:

Class diagram for Chart specific classes is displayed below

“SampleDocumentWithTableAndChartsGenerator” is the sample that shows how to refresh template having Scatter and Line charts. The code snippets are displayed below

“RefreshCharts” method is added to DocumentGenerator class

     /// <summary>

    /// Refreshes the charts.

    /// </summary>

    /// <param name="mainDocumentPart">The main document part.</param>

    protected virtual void RefreshCharts(MainDocumentPart mainDocumentPart)

    {

    }

“SampleDocumentWithTableAndChartsGenerator” class overrides “RefreshCharts” method

    /// <summary>

    /// Refreshes the charts.

    /// </summary>

    /// <param name="mainDocumentPart">The main document part.</param>

    protected override void RefreshCharts(MainDocumentPart mainDocumentPart)

    {

        if (mainDocumentPart != null)

        {

            foreach (ChartPart chartPart in mainDocumentPart.ChartParts)

            {

                Chart chart = chartPart.ChartSpace.Elements<Chart>().FirstOrDefault();

                if (chart != null)

                {

                    DocumentFormat.OpenXml.Drawing.Charts.ScatterChart scatterChart = chart.Descendants<DocumentFormat.OpenXml.Drawing.Charts.ScatterChart>().FirstOrDefault();

                    DocumentFormat.OpenXml.Drawing.Charts.Line3DChart lineChart = chart.Descendants<DocumentFormat.OpenXml.Drawing.Charts.Line3DChart>().FirstOrDefault();

                    if (scatterChart != null)

                    {                            

                        ScatterChartEx chartEx = new ScatterChartEx(chartPart, this.scatterChartData);

                        chartEx.Refresh();

                    }

                    if (lineChart != null)

                    {                            

                        Line3DChartEx chartEx = new Line3DChartEx(chartPart, this.lineChartData);

                        chartEx.Refresh();

                    }

                }

                chartPart.ChartSpace.Save();

            }

        }

    }

Refresh method is defined in ChartEx<T>

    /// <summary>

    /// Refreshes this instance.

    /// </summary>

    public void Refresh()

    {

        ChartData chartData = this.GetChartData();

        if (chartData != null && chartData.IsValid())

        {

            string sheetName = this.UpdateEmbeddedObject();

            Chart chart = chartPart.ChartSpace.Elements<Chart>().FirstOrDefault();

            if (chart != null)

            {

                this.UpdateChart(chart.Descendants<T>().FirstOrDefault(), sheetName);

            }              

        }

    }

For complete code download the source code.

Summary:

Please provide feedback/comments and I’ll try to incorporate most of them in new releases.

How to use Pex and Moles to generate unit tests for a project having external dependency(WCF Proxy) using Visual Studio 2010 SP1

Atul.Verma — Wed, 18 Jan 2012 03:28:46 GMT

Source Code:

In this post I’ll discuss about writing unit tests using Pex and Moles. Pex and Moles are Visual Studio 2010 Power Tools that help Unit Testing .NET applications.

Pex automatically generates test suites with high code coverage. Right from the Visual Studio code editor, Pex finds interesting input-output values of your methods, which you can save as a small test suite with high code coverage.
Moles allows to replace any .NET method with a delegate. Moles supports unit testing by providing isolation by way of detours and stubs i.e. Generate a Mole type from the original type and redefine its behavior using delegates.

Pex and Moles can be downloaded from http://research.microsoft.com/en-us/projects/pex/.

I’ll explain the steps to generate unit tests for a project which calls a WCF service. Pex will be used to generate unit tests. Moles will be generated to isolate the external dependency(WCF proxy) and behavior will be redefined using delegates.

The projects inside the sample solution are

DemoService: This project is a WCF Service.
DemoLibrary: This project is a Class library and service reference to DemoService has been added. Unit tests will be generated for this project.
ConsoleApp: This project is a Console application.
DemoLibrary.Tests: This is a Test project and contains unit tests for DemoLibrary.

The solution structure is displayed below

DemoLibrary calls DemoService though proxy as displayed in the Layer diagram

I’ll now discuss in brief the code snippets of each project

WCF Service(DemoService): This service provided only a single operation

     [ServiceContract]

    public interface IDemoService

    {

        [OperationContract]

        string Search(string criteria);

    }

WCF Service Client(DemoLibrary): It calls the Search method of DemoService through proxy as displayed below

    public string GetResults(string s)

    {

        DemoServiceReference.DemoServiceClient client = null;

        try

        {

            client = new DemoServiceReference.DemoServiceClient();

            client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;                

            client.ChannelFactory.Credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;

            s = client.Search(s);

            return s;

        }

        finally

        {

            if (client != null)

            {

                if (client.State == CommunicationState.Opened)

                {

                    client.Close();

                }

                else if (client.State == CommunicationState.Faulted)

                {

                    client.Abort();

                }

            }

        }

    }

Unit Testing DemoLibrary using Pex and Moles:

In order to generate unit tests for WCF Service Client(DemoLibrary) project the steps are

Right click on the class for which unit tests needs to be generated and click “Create Parameterized Unit Tests” as displayed below
A popup will be displayed where Filters and Output can be modified. Click Ok to go to next step.

A new test project will be created. Test Class and Test stub will be be added to this project as displayed below

          /// <summary>This class contains parameterized unit tests for Search</summary>

          [PexClass(typeof(Search))]

          [PexAllowedExceptionFromTypeUnderTest(typeof(InvalidOperationException))]

          [PexAllowedExceptionFromTypeUnderTest(typeof(ArgumentException), AcceptExceptionSubtypes = true)]

          [TestClass]

          public partial class SearchTest

          {

              /// <summary>Test stub for GetResults(String)</summary>

              [PexMethod]

              public string GetResults([PexAssumeUnderTest]Search target, string s)

              {

                  string result = target.GetResults1(s);

                  return result;

                  // TODO: add assertions to method SearchTest.GetResults(Search, String)

              }

          }

There is an external dependency(GetResults makes a service call though the WCF Proxy) so “Run Pex Explorations” will not generate unit tests as displayed below
In order to isolate the external dependency we need to generate Moles before running Pex Explorations. Moles will be generated for DemoLibrary and System.ServiceModel assemblies and behavior will be redefined using delegates. There are two ways to generate a mole for an assembly. I’ll show you both the approaches
1. Visual Studio: This is the easiest way.
  1. Right click on the reference and generate Moles for that assembly as displayed below.
  2. A .Moles file will be added to the project. Build the project and Moles.dll will be added to MolesAssemblies folder as displayed below .
2. Command Prompt: Moles can be generated from the command prompt.
  1. Run the moles.exe and specify the assembly path for which Moles needs to be created.
  2. Copy the generated assembly to the Project and add reference to it.
Similarly as explained above we need to generate Moles for System.ServiceModel assembly. For Visual Studio 2010 SP1 this may fail with error message “The type or namespace name 'IHttpCookieContainerManager' does not exist in the namespace 'ssm::System.ServiceModel.Channels' (are you missing an assembly reference?)”. This step however works fine for Visual Studio 2010. The fix is to exclude the type i.e. 'IHttpCookieContainerManager' from StubGeneration as displayed below

The next step is to Mock the Service call(redefine behavior using delegates) and add Asserts as displayed in code snippets below

          /// <summary>Test stub for GetResults(String)</summary>

          [PexMethod]

          public string GetResults([PexAssumeUnderTest]Search target, string s)

          {

              MockWCFService<IDemoService>();

              MDemoServiceClient.Constructor = (var1) =>

                 new MDemoServiceClient { };

              MDemoServiceClient.AllInstances.SearchString = (var1, var2) =>

              {

                  return "Result";

              };

              string result = target.GetResults(s);

              PexAssert.IsNotNullOrEmpty(result);

              PexAssert.AreEqual(result, "Result");

              return result;            

          }

          /// <summary>

          /// Mocks the WCF service.

          /// </summary>

          private void MockWCFService<TService>() where TService : class

          {

              MClientCredentials.Constructor = (var1) =>

                  new MClientCredentials()

                  {

                      WindowsGet = () => { return new MWindowsClientCredential(); }

                  };

              MClientCredentials.AllInstances.WindowsGet = (var1) =>

              {

                  return new MWindowsClientCredential();

              };

              MWindowsClientCredential.AllInstances.ClientCredentialGet = (var1) =>

              {

                  return new System.Net.NetworkCredential();

              };

              MWindowsClientCredential.AllInstances.ClientCredentialSetNetworkCredential = (var1, var2) => { };

              MWindowsClientCredential.AllInstances.AllowNtlmGet = (var1) => { return true; };

              MWindowsClientCredential.AllInstances.AllowNtlmSetBoolean = (var1, var2) => { };

              MWindowsClientCredential.AllInstances.AllowedImpersonationLevelGet = (var1) => { return System.Security.Principal.TokenImpersonationLevel.Impersonation; };

              MWindowsClientCredential.AllInstances.AllowedImpersonationLevelSetTokenImpersonationLevel = (var1, var2) => { };

              MChannelFactory.AllInstances.CredentialsGet = (var1) => { return new MClientCredentials(); };

              MClientBase<TService>.AllInstances.ClientCredentialsGet = (var1) =>

              {

                  return new System.ServiceModel.Description.ClientCredentials();

              };

              MClientBase<TService>.AllInstances.ChannelFactoryGet = (var1) =>

              {

                  return new MChannelFactory01<TService>();

              };

              MClientBase<TService>.AllInstances.StateGet = (var1) =>

              {

                  return PexChoose.EnumValue<CommunicationState>("CommunicationState");

              };

              MClientBase<TService>.AllInstances.Close = (var1) =>

              { };

              MClientBase<TService>.AllInstances.Abort = (var1) =>

              { };              

          }

Run Pex Explorations to generate unit tests for GetResults method as displayed below
Unit tests will be added to the Test Class as displayed below
Go to Test View and Run the unit tests as displayed below

Summary:

In a similar way we can extract out external dependencies using Moles and then run Pex Explorations. A few examples of external dependencies can be data layer, UI layer, server calls etc. You can read more about Pex and Moles at http://research.microsoft.com/en-us/projects/pex/.

Utility to generate Word documents from templates using Visual Studio 2010 and Open Xml 2.0 SDK - Part 3

Atul.Verma — Wed, 11 Jan 2012 17:10:00 GMT

This is the third post of this series. The earlier posts can be read at Part 1 and Part 2. The code is available for download at Utility Source Code.

In Part 1 I discussed about

Document generation using Content Controls and Open XML 2.0 SDK
Creating Word templates
Implementation and Samples

In Part 2 I discussed about

List of functionalities that can be achieved using the utility/source code
Description regarding Samples provided with utility
New samples added in this update

In this part I’ll discuss about the sample that shows one of the ways to “Refresh the document from within the Word(e.g. right click on document and click Refresh) using document-level customizations for Word 2007 and Word 2010“. On click of Refresh Document the content of the document is refreshed as displayed below

Project “WordDocumentGenerator.WordRefreshableDocumentAddin” has been added to the utility for this sample. The steps followed for creating this sample are listed below

Added a new Word 2010 Document project as displayed below
Updated the document by adding the content controls as displayed below
Added a new Command bar button i.e. “Refresh Data” to the Command bar. On click of this button the document will be refreshed. The common scenario will be to refresh data from the Service. “WordDocumentGenerator.WordRefreshableDocumentAddin” is the document level customization project. This project references “WordDocumentGenerator.Library” and “WordDocumentGenerator.Client” projects.

The code snippet to add a new Command bar button is

       /// <summary>

      /// Adds the command bar.

      /// </summary>

      /// <param name="cmdBr">The CMD br.</param>

      /// <param name="handler">The handler.</param>

      /// <param name="index">The index.</param>

      /// <param name="tag">The tag.</param>

      /// <param name="caption">The caption.</param>

      /// <returns></returns>

      private CommandBarButton AddCommandBar(CommandBar cmdBr, _CommandBarButtonEvents_ClickEventHandler handler, int index, string tag, string caption)

      {

          CommandBarButton cmdBtn = (CommandBarButton)cmdBr.FindControl(MsoControlType.msoControlButton, 0, tag, missing, missing);

          if ((cmdBtn != null))

          {

              cmdBtn.Delete(true);

          }

          cmdBtn = (CommandBarButton)cmdBr.Controls.Add(MsoControlType.msoControlButton, missing, missing, index, true);

          cmdBtn.Style = MsoButtonStyle.msoButtonCaption;

          cmdBtn.Caption = caption;

          cmdBtn.Tag = tag;

          cmdBtn.Visible = true;

          cmdBtn.Click -= handler;

          cmdBtn.Click += handler;

          if (!commandBarsTags.Contains(tag))

          {

              commandBarsTags.Add(tag);

          }

          return cmdBtn;

      }

On click of refresh data the main steps are

1. Get package steam from the document
Microsoft.Office.Interop.Word.Document doc = app.ActiveDocument;
 
// Get the active documents as stream of bytes
byte[] input = doc.GetPackageStream(); 
2. Call the Service/Client method that generates/refreshes the document. This can be a Server or a direct call. In this method it’s a direct call.
// Generate document on the Server. AddInService can be a proxy to service, however here it's direct call
byte[] output = AddInService.GenerateDocument(input);

      /// <summary>

      /// Generates the document.

      /// </summary>

      /// <param name="documentStream">The document stream.</param>

      /// <returns></returns>

      public static byte[] GenerateDocument(byte[] documentStream)

      {}

3. Update the document contents

      XDocument xDoc = OPCHelper.OpcToFlatOpc(wordDocument.Package);

      string openxml = xDoc.ToString();                        

      doc.Range().InsertXML(openxml);

These are partial code snippets to show the code flow. For complete sample please download the source code.

References:

For more information about Programming Document level customizations
For more information about methods of OPCHelper class please visit http://msdn.microsoft.com/en-us/library/ff191178.aspx

Utility to generate Word documents from templates using Visual Studio 2010 and Open Xml 2.0 SDK – Part 2 (Samples Updated)

Atul.Verma — Sun, 08 Jan 2012 04:40:36 GMT

This is the second post of this series. The previous post can be read at Part 1. The next post can be read at Part 3. The code is available for download at Utility Source Code.

In Part 1 I discussed about

Document generation using Content Controls and Open XML 2.0 SDK
Creating Word templates
Implementation and Samples

In Part 3 I have explained one of the way to “Refresh the document from within the document(e.g. right click on document and click Refresh) using document-level projects for Word 2007 and Word 2010“

In this post I’ll discuss

List of functionalities that can be achieved using the utility/source code
Description regarding Samples provided with utility
New samples added in this update

The sample document generators are discussed later. The functionalities that can be achieved using the utility/source code are:
Document Generation

Generate document from a Word template using content controls as place holders and populate controls with data(Object)[SampleDocumentGenerator, SampleRefreshableDocumentGenerator, SampleDocumentWithTableGenerator]
Generate document from a Word template using content controls as place holders(data bound content controls) and populate controls with data(Object is serialized to Xml). [SampleDocumentGeneratorUsingDatabinding, SampleDocumentWithTableGeneratorUsingDatabinding, SampleDocumentGeneratorUsingXmlAndDatabinding]
Generate document from a Word template using content controls as place holders and populate controls with data(XmlNode)[SampleDocumentGeneratorUsingXml]
Generate document from a Word template using content controls as place holders(data bound content controls) and populate controls with data(XmlNode) [SampleDocumentGeneratorUsingXmlAndDatabinding]
Refresh the document from within the document(e.g. right click on document and click Refresh) using document-level projects for Word 2007 and Word 2010 [Explained in next post i.e. Part 3]
Generate document that can be
1. Standalone: Once generated document cannot be refreshed.
2. Refreshable: Once generated document can be refreshed. Content controls will be added/updated/deleted and content control's content will be refreshed as per data.
Append documents using AltChunk
Protect Document
UnProtect Document
Removal of Content Controls from a document while keeping contents
Removal of Foot notes
Ensuring the each content control has unique Id's by fixing the duplicate Id's if any for a document
Serializing an Object to Xml using XmlSerializer(Used for document generation using data bound content controls as serialized object is written to CustomXmlPart)

Content Controls

Set text of a content control(not applicable for data bound content controls)
Get text from a content control(not applicable for data bound content controls)
Set text of content control while keeping PermStart and PermEnd elements(not applicable for data bound content controls)
Set Tag of a content control
Get Tag of a content control
Set data binding of a content control
Set text of a data bound content control from CustomXmlPart manually. This is helpful in cases when CustomXmlPart needs to be removed and this copies the text from the CustomXmlPart node using XPath.

CustomXmlPart

Adding a CustomXmlPart to a document
Removing CustomXmlPart from a document
Getting CustomXmlPart from a document
Add/Update a Xml element node inside CustomXmlPart. This is required
1. To keep Document related metadata e.g. Document type, version etc.
2. To make the Document self-refreshable. In this case the container content control is persisted inside a Placeholder node, the first time document is generated from template. Onwards when refreshing document we fetch the container content control from CustomXmlPart
3. Saving the Xml e.g. serialized object which will be the data store for data bound content controls

Sample Generators:

SampleDocumentGenerator: This sample shows how to generate a non-refreshable document from a template. The content controls are populated using C# code i.e. not using data bound content controls. It covers both direct assignment as well as recursive controls. The screenshot is
SampleRefreshableDocumentGenerator: This sample shows how to generate a refreshable document from a template. The content controls are populated using C# code i.e. not using data bound content controls. This is similar to SampleDocumentGenerator in implementation. Only difference is the generated document can be refreshed in this case. The screenshot is
SampleDocumentWithTableGenerator: This sample shows how to generate a refreshable document from a template having Table. The content controls are populated using C# code i.e. not using data bound controls. This is similar to to SampleRefreshableDocumentGenerator in implementation. Only difference is the template has an additional table. The screenshot is
SampleDocumentGeneratorUsingDatabinding: This sample shows how to generate a refreshable document from a template using data bound content controls. This requires that each of the placeholder(template’s content control) has a predefined XPath. The generated document is similar to SampleRefreshableDocumentGenerator.
SampleDocumentWithTableGeneratorUsingDatabinding: This sample shows how to generate a refreshable document from a template having table using data bound content controls. This requires that each of the placeholder(template’s content control) has a predefined XPath. The generated document is similar to SampleDocumentWithTableGenerator.
SampleDocumentGeneratorUsingXml (New): This sample shows how to generate a document from a template using XmlNode as data. This approach shows that a generic generator can be created which requires Xml as data. This requires that XPath for Tag as well as content needs to be provided. The content controls are populated using C# code i.e. not using data bound controls. It covers both direct assignment as well as recursive controls. The generated document is similar to SampleDocumentGenerator.
SampleDocumentGeneratorUsingXmlAndDatabinding (New): This sample shows how to generate a document from a template using XmlNode as data and data bound content controls. This approach shows that a generic generator can be created which requires Xml as data. This requires that XPath for Tag as well as content needs to be provided. It covers both direct assignment as well as recursive controls. The generated document is similar to SampleDocumentGenerator.

Utility to generate Word documents from templates using Visual Studio 2010 and Open Xml 2.0 SDK

Atul.Verma — Sat, 31 Dec 2011 18:10:00 GMT

This utility generates Word documents from templates using Content controls. The utility source code is available for download at http://worddocgenerator.codeplex.com/. It has been created in Visual Studio 2010 and uses Open Xml 2.0 SDK which can be downloaded from http://www.microsoft.com/download/en/details.aspx?id=5124.

The next parts in this series are

In Part 2 I have discussed about
- List of functionalities that can be achieved using the utility/source code
- Description regarding Samples provided with utility
- New samples added in this update
In Part 3 I have explained one of the way to “Refresh the document from within the document(e.g. right click on document and click Refresh) using document-level projects for Word 2007 and Word 2010“

The purpose of creating this utility was to use the Open Xml 2.0 SDK to generate Word documents based on predefined templates using minimum code changes. These documents can either be refreshable or non- refreshable. I’ll explain this difference later. Also there is no dependency that Word should be installed.

A few samples for generating Word 2010 documents have been provided. The screenshots below display the sample template and the document generated out of this template using this utility.

Word 2010 Template –> Generated Document:

Word 2010 Template –> Generated Document –> Refreshed Document:

Document Refresh from within Word –> Refreshed Document:

Template Design:

The sample templates are inside “WordDocumentGenerator.Client\Sample Templates” folder. A content control as displayed below can have Title and Tag properties.

The logic is to have a tag property of a content control and then populate data using that placeholder. This means every content control inside a Word template will have a different Tag.

As per image above the tag of the content control is “PlaceholderNonRecursiveB”. During document generation we can assign (not mandatory) a unique Id e.g. Guid of a record to make the tag unique e.g. “PlaceholderNonRecursiveB:Guid”. Let’s say that we have an Id and Name field. Thus the Name will be the content of the control and tag will be “PlaceholderNonRecursiveB:Id”. As per Word 2010 the Tag maximum length is 64.

In code we map the tag to the PlaceHolderType enum.

     public enum PlaceHolderType

    {

            None = 0,

            Recursive = 1,

            NonRecursive = 2,

            Ignore = 3,

            Container = 4    

    }

There can be multiple types of PlaceHolders

Recursive: This type corresponds to controls where there is 1:N relation between template and data i.e. one example will be repeating a list of Items.
Non-Recursive: This type corresponds to controls where there is 1:1 relation between template and data i.e. one example will be showing a User name.
Ignore: No action is required for these controls.
Container: This type is required only for refreshable documents. We save the container region in CustomXmlPart the first time document is generated from template. Next time onwards we retrieve the container region that was saved and refresh the document. This makes the document self-refreshable.

I’ve named the tags in “Test_Template – 1.docx” as per their PlaceHolder type to make it more clear.

Implementation:

As explained above the Tag property will used to bind data to this content control. The project “WordDocumentGenerator.Library” is the utility library. The project “WordDocumentGenerator.Client” shows how this library can be used to generate documents.

“DocumentGenerator” is the base class that needs to be inherited by the document generators. The sample generators location is “WordDocumentGenerator.Client\Sample Document Generators”.

In order to protect the document I’ve used already created salt and hash. For an implementation where one needs to have document protection enabled for custom passwords one can view http://blogs.msdn.com/b/vsod/archive/2010/04/05/how-to-set-the-editing-restrictions-in-word-using-open-xml-sdk-2-0.aspx

Summary:

How to upload/download a document in SharePoint 2010 using Client Context Object Model

Atul.Verma — Wed, 27 Oct 2010 08:47:20 GMT

In order to use ClientContext I need to add reference to two dll’s to my project i.e. Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll.
In this blog I’ll display how to

Get ListItemCollection from SharePoint document list using CAML
Upload a document to SharePoint Document list
Download a document from SharePoint Document list

Get ListItemCollection from SharePoint Document list using CAML:

I can get the ListItemCollection as displayed in code snippets below

ListItemCollection listItems = GetListItemCollectionFromSP("FileLeafRef",
                                                            documentName, 
                                                            "Text",
                                                            1);

The GetListItemCollectionFromSP returns the List item and the parameters to method are
name –> Name of the FieldRef,

value –> value to match for that FieldRef,

type –> Type of the value

rowLimit –> Maximum number of rows to fetch

private static ListItemCollection GetListItemCollectionFromSP(string name, 
                                                              string value,
                                                              string type,
                                                              int rowLimit)
{
//Replace siteURL and documentListName with 
//your SharePoint site URL and DocumentList
ListItemCollection listItems = null;
string siteURL = "site URL";
string documentListName = "document list";

using (ClientContext clientContext = new ClientContext(siteURL))
{
List documentsList = clientContext.Web.Lists.GetByTitle(documentListName);
 
CamlQuery camlQuery = new CamlQuery(); ;
camlQuery.ViewXml =
@"<View>
<Query>
<Where>
<Eq>
<FieldRef Name='" + name + @"'/>
<Value Type='" + type + "'>" + value + @"</Value>
</Eq>
</Where>                    
<RowLimit>" + rowLimit.ToString() + @"</RowLimit>
</Query>
</View>";
 
listItems = documentsList.GetItems(camlQuery);
clientContext.Load(documentsList);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
}
 
return listItems;
}

Upload a document to SharePoint Document List:

In this case I want to upload a document to SharePoint document list and also update the field metadata i.e. for field “DocType” to “Favourites” as in this example using ClientContext. The code snippet is displayed below

public void UploadDocument(string siteURL, string documentListName,
string documentListURL, string documentName,
byte[] documentStream)
{
using (ClientContext clientContext = new ClientContext(siteURL))
{        
//Get Document List
List documentsList = clientContext.Web.Lists.GetByTitle(documentListName);
var fileCreationInformation = new FileCreationInformation();
//Assign to content byte[] i.e. documentStream
fileCreationInformation.Content = documentStream;
//Allow owerwrite of document
fileCreationInformation.Overwrite = true;
//Upload URL
fileCreationInformation.Url = siteURL + documentListURL + documentName;
Microsoft.SharePoint.Client.File uploadFile = null;
uploadFile = documentsList.RootFolder.Files.Add(fileCreationInformation);
//Update the metadata for a field having name "DocType"
uploadFile.ListItemAllFields["DocType"] = "Favourites";
uploadFile.ListItemAllFields.Update();
clientContext.ExecuteQuery();
}
}

Download a document from SharePoint Document List:

I can download the document using the code snippets displayed below

public Stream DownloadDocument(string siteURL, string documentName)
{
ListItem item = GetDocumentFromSP(documentName);
if (item != null)
{
using (ClientContext clientContext = new ClientContext(siteURL))
{
FileInformation fInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(
clientContext, item ["FileRef"].ToString()); 
return fInfo.Stream;
}
}
return null;
}
private static ListItem GetDocumentFromSP(string documentName)
{
// This method is discussed above i.e. 
// Get List Item Collection from SharePoint Document List
ListItemCollection listItems = null;
listItems = GetListItemCollectionFromSP("FileLeafRef", 
                                        documentName, 
                                        "Text", 1);

return (listItems != null && listItems.Count == 1) ? listItems[0] : null;
}

How to test a class member that is not public using Visual Studio 2010

Atul.Verma — Thu, 16 Sep 2010 23:31:38 GMT

There are various ways to test a Class member which is not public

By making the private members public of a class under test
- Breaks the encapsulation principle
- API is modified just for the sake of testing
By making the private members internal of a class under test and annotate the assembly with internalsVisibleTo attribute and you can read more about it at http://msdn.microsoft.com/en-us/library/0tke9fxk.aspx
- Breaks the encapsulation principle
- API is modified just for the sake of testing
Make the private members reachable through a public member of a class under test and test these public members
- Breaks the encapsulation principle
- API is modified just for the sake of testing
Use reflection to invoke the non public members of a class under test

Using reflection is abstracted in Visual Studio so we can use

PrivateObject to access non public Instance members of a class under test
PrivateType to access static members of a class under test

These classes are inside Microsoft.VisualStudio.TestTools.UnitTesting namespace. I have created code snippets in Visual Studio 2010.

PrivateObject:

PrivateObject class is used to access instance based members of a class under test. The methods and properties of this class are displayed below

e.g. We have a BankAccount class that contains a private method i.e. VerifyAmount as displayed below

     public class BankAccount

    {

        //Private method to test

        private bool VerifyAmount(double amount)

        {

            return (amount <= 1000);

        }

    }

In order to unit test VerifyAmount method using PrivateObject class the code is displayed below

    [TestMethod()]        

    public void VerifyAmountTest()

    {

        //Using PrivateObject class

        PrivateObject privateHelperObject = new PrivateObject(typeof(BankAccount));                             

        double amount = 500F;

        bool expected = true;

        bool actual;

        actual = (bool)privateHelperObject.Invoke("VerifyAmount", amount);            

        Assert.AreEqual(expected, actual);            

    }

PrivateType:

PrivateType class is used to access the static non public members of a class under test. The methods and properties of this class are displayed below

e.g. We have a BankAccount class that contains a private static method i.e. VerifyAmount as displayed below

    public class BankAccount

    {

        //Private static method under test

        private static bool VerifyAmount(double amount)

        {

            return (amount <= 2000);

        }

    }

In order to unit test VerifyAmount method using PrivateType class the code is displayed below

    [TestMethod()]        

    public void VerifyAmountTest()

    {

        PrivateType privateHelperType = new PrivateType(typeof(BankAccount));                             

        double amount = 1000F;

        bool expected = true;

        bool actual;

        actual = (bool)privateHelperType.InvokeStatic("VerifyAmount", amount);            

        Assert.AreEqual(expected, actual);            

    }

In both the cases displayed above i.e. PrivateObject and PrivateType the method names are hardcoded. In order to avoid hard coding Visual Studio provides an option to create private accessor as displayed below where PrivateBanking is the name of the project.

Private Accessor automatically gets created when creating unit tests as displayed below

In the test project under Test References you can now see PrivateBanking.accessor as displayed below

The unit tests using the private accesser for class BankAccount as displayed below where BankAccount_Accessor is the class publicizes the BankAccount class and it derives from BaseShadow class.

    //To test non public Instance member

    [TestMethod()]

    [DeploymentItem("PrivateBanking.dll")]

    public void VerifyAmountTest1()

    {

        BankAccount_Accessor target = new BankAccount_Accessor(); // TODO: Initialize to an appropriate value

        int amount = 10; // TODO: Initialize to an appropriate value

        bool expected = true; // TODO: Initialize to an appropriate value

        bool actual;

        actual = target.VerifyAmount(amount);

        Assert.AreEqual(expected, actual);            

    }

    //To test non public static member

    [TestMethod()]

    [DeploymentItem("PrivateBanking.dll")]

    public void VerifyAmountTest()

    {

        double amount = 10F; // TODO: Initialize to an appropriate value

        bool expected = true; // TODO: Initialize to an appropriate value

        bool actual;

        actual = BankAccount_Accessor.VerifyAmount(amount);

        Assert.AreEqual(expected, actual);           

    }

As we are using the accessor now no hard coding is required for method names as displayed in code snippet above. The BankAccount_Accessor class is displayed below

The BaseShadow class is used by the publicize method to help test private types and is displayed below

This concludes the article on how to unit test non public members using Visual Studio 2010.

del.icio.us Tags: visual Studio 2010,unit Test,Non public member

How to unit test SQL Server 2008 database using Visual Studio 2010 – Part 3

Atul.Verma — Sun, 22 Aug 2010 22:37:28 GMT

This is the last article of the series “How to unit test SQL Server 2008 database using Visual Studio 2010”. The previous articles are

Unit test SQL Server 2008 - Part 1 – Focused on setting up environment, creating database project and executing simple unit tests

Unit test SQL Server 2008 - Part 2 – Focused on internals of database unit testing i.e. assemblies

I’ll divide this article into two logical parts

Tools that are useful for creation of test data as well as ensuring that database schemas are synchronized
- Data generation
- Schema comparison
- Data comparison
More about unit testing a database which include
- Data driven unit tests
- Pre, test and post scripts
- Test conditions
- Transactions

Data Generation Plan:

Test data is created for verifying the behaviour of database objects in a database project. Generating data that is appropriate for the schema of the database but unrelated to the production data helps protect the privacy or security of the production data. Data generation plan can be added to the database project as displayed below

When data generation plan is created for a database project, the plan is based on the schema of the project. If after creation of the plan schema changes, Visual Studio prompts to update the plan.

There are two types of templates available for creating data generation plan

Data Generation Plan: This template creates an empty data generation plan
Data Transform Plan: This template creates an data generation plan reusing data from another data source

Data generation plan Template:

Select the template i.e. Data Generation Plan and specify name and Add as displayed below

After you click Add you can customize various aspects of data generation plan in the Data generation plan designer as displayed below

Here three tables are displayed as I had three tables Event, EventPass and Student in my database project. The Related Table shows a dropdown only for EventPass table because EventPass has foreign key relation to Event and Student table. Please refer to first article for details.

You can choose Table and Rows to Insert in each table for all the tables provided for table that has related tables e.g. EventPass Related Table is set to None. If Related Table is chosen then you can specify the Ratio to Related Table and depending upon this ratio the Rows to Insert column will be populated.

You can preview as well as change the generator as displayed below

Once you are done configuring this plan go to Data –> Data Generator –> Generate Data as displayed below

Specify a new or an existing connection in the Connect to Database dialog that will appear next. Please note that you need to have the same schema in the database that you have specified for this else Data generation will fail. In this example my database is SampleDBTestData and it contains tables only. Once data generation is completed you can see the generate data in the database that you specified.

Data transform plan Template:

Select the Data transform plan template, specify name and Add as displayed below

I have highlighted the sections that can be configured in this dialog. Rest of the steps are same as discussed above i.e. once you are done configuring this plan go to Data –> Data Genarator –> Generate Data.

Schema Comparison:

Schema comparison is a powerful tool that is used to compare database schemas as displayed below

The result when source and target schema are same is displayed below

The result when source and target schema were somewhat different is displayed below

Data Comparison:

The data from two databases can be compared as display below

This concludes the first logical part.

Data driven unit tests

In order to write a data driven unit test two important characteristics that we use are

DataSourceAttribute: This attribute provides data source specific information for data driven testing and is available in assembly Microsoft.VisualStudio.QualityTools.UnitTestFramework
TestContext: This class is used to store information that is provided to unit tests and is available in assembly Microsoft.VisualStudio.QualityTools.UnitTestFramework

As displayed in code snippet I have marked the dbo_AddStudentTest with DataSource and TestMethod attributes and specified the provider type, connection string, table name and data access method as displayed below

[DataSource("System.Data.SqlClient", @"Data Source=SQLSERVER2008;Initial Catalog=TestData;Persist Security Info=True;User ID=sa;Password=*****;Pooling=False", "EmployeeData", DataAccessMethod.Sequential), TestMethod()]

Inside the test method I can retrieve that parameter value that I am expecting from Test Data from the TestContext as displayed below

object parameterValue = TestContext.DataRow.ItemArray.GetValue(0);

The unit test script is expects the parameter “StudentName” which I’ll populate from the test data. The same example I provided in first article of this series I had declared the parameter in the script and hard coded value for that parameter inside the script. Now you can drive unit tests from the test data. The unit test script for this test is displayed below.

-- unit test for dbo.uspAddStudent

DECLARE @RC AS INT

SELECT @RC = 0

EXECUTE @RC = [dbo].[uspAddStudent] @StudentName;

SELECT * FROM [dbo].[Student];

The code snippet is displayed below

[DataSource("System.Data.SqlClient", @"Data Source=SQLSERVER2008;Initial Catalog=TestData;Persist Security Info=True;User ID=sa;Password=*******;Pooling=False", "EmployeeData", DataAccessMethod.Sequential), TestMethod()]public void dbo_uspAddStudentTest(){DatabaseTestActions testActions = this.dbo_uspAddStudentTestData;// Execute the pre-test script// System.Diagnostics.Trace.WriteLineIf((testActions.PretestAction != null), "Executing pre-test script...");ExecutionResult[] pretestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PretestAction);//Specifying the value of parameter from TestContextobject parameterValue = TestContext.DataRow.ItemArray.GetValue(0);DbParameter parameter= CreateDBParameter("StudentName", ParameterDirection.Input, DbType.String, 100, parameterValue);   // Execute the test script// System.Diagnostics.Trace.WriteLineIf((testActions.TestAction != null), "Executing test script...");ExecutionResult[] testResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.TestAction, parameter);// Execute the post-test script// System.Diagnostics.Trace.WriteLineIf((testActions.PosttestAction != null), "Executing post-test script...");ExecutionResult[] posttestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PosttestAction);} private DbParameter CreateDBParameter(string name, ParameterDirection direction, DbType dbType, int size, object value){DbParameter parameter = base.ExecutionContext.Provider.CreateParameter();parameter.Direction = direction;parameter.DbType = DbType.String;parameter.ParameterName = name;parameter.Size = size;parameter.Value = value;return parameter;}

One improvement that I can make is instead of hard coding the provider, connection string etc. by adding them to the App.Config as displayed below

<configSections>    <section name="microsoft.visualstudio.testtools" type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/></configSections><connectionStrings>    <add name="sqlDataCon" connectionString="Data Source=SQLSERVER2008;Initial Catalog=TestData;Persist Security Info=True;User ID=sa;Password=*********;Pooling=False" providerName ="System.Data.SqlClient"/></connectionStrings><microsoft.visualstudio.testtools>    <dataSources>        <add name ="sqlDataSource" connectionString="sqlDataCon" dataTableName="EmployeeData" dataAccessMethod="Sequential"/>    </dataSources></microsoft.visualstudio.testtools>

and mark the Test method as displayed below

[DataSource("sqlDataSource")]

[TestMethod]

public void dbo_uspAddStudentTest()

Common, Pre, Test and Post scripts:

You can specify Common scripts(common to all tests), Pre and Post scripts to a test in test editor. By default these are optional. The order of execution of these scripts is

The context of these are also different i.e. TestInitializion, Pre Test script, Post Test script, TestCleanup execute having PrivelegedContext whereas Test script executes having ExecutionContext.

Test Conditions:

I have discussed the test conditions in previous articles. In this article I’ll show two properties i.e. Configuration and Enabled. Enabled exist across all test conditions. You can enable/disable a test condition using this. Configuration property exists for Expected Schema and Data Checksum test conditions. For these conditions you need to configure the result you are expecting by clicking the button as highlighted below

You can define a custom test condition to verify the behaviour of a database object in ways that the built-in conditions do not support. Please read this msdn article for more details Define Custom Conditions for Database Unit Tests

Transactions:

There are three ways through which we can specify transactions as listed below

Inside Transact-SQL
ADO.NET Transactions: Transactions can be implemented by adding code snippet to the test method as displayed below

base.ExecutionContext.Connection.BeginTransaction()

This only works if there are no pre and post test scripts for an unit test. This is because Pre and Post scripts are executed having PrivelegedContext where as Test script is executed having ExecutionContext

System.Transactions: Transactions can be implemented by adding code snippet to the test method as displayed below

using (TransactionScope scope = new System.Transactions.TransactionScope(TransactionScopeOption.Required))

base.PrivilegedContext.Connection.EnlistTransaction(Transaction.Current);

base.ExecutionContext.Connection.EnlistTransaction(Transaction.Current);

//Rest of the test method code

In this case we have to enlist the transaction as we have two different contexts i.e. PrivelegedContext and ExecutionContext. This means if these we need to have distributed transaction coordinator service running as we are having transaction across two different connections.

This completes the series “How to unit test SQL Server database using Visual Studio 2010”. Please share the feedback so that I can improve/rectify the series.

del.icio.us Tags: Visual Studio 2010,SQL Server 2008,Unit Test,Schema Comparer,Data Comparer

How to unit test SQL Server 2008 database using Visual Studio 2010 – Part 2

Atul.Verma — Sun, 22 Aug 2010 22:33:05 GMT

This is the second article I am writing on unit testing SQL Server 2008 database using Visual Studio 2010. The other articles are

Unit test SQL Server 2008 - Part 1 – Focused on setting up environment, creating database project and executing simple unit tests

Unit test SQL Server 2008 - Part 3 – Focused on data generation, schema comparison, data comparison and data driven unit tests

In this article I will explore the important features that can be configured to build comprehensive database unit tests. The two assemblies that are the backbone for database unit testing are

Microsoft.VisualStudio.QualityTools.UnitTestFramework: This assembly is the testing framework assembly the test engine depends on when executing the unit tests. I won’t get into details of the parts of this assembly and focus on some the the important attributes that are required to execute an database unit test.
Microsoft.Data.Schema.UnitTesting: This assembly provides the classes required to create and execute database unit test. In this blog I’ll focus on this assembly.

In this article I’ll dig deep into these two assemblies and explore the various components of these assemblies that are used to execute unit test for a database.

I’ll start with the Database Unit Test Designer and then move on to explaining various components.

Database Unit Test Designer:

I have highlighted the various options available in Database Unit Test Designer and added short description as displayed below

The scripts that are created are stored in the resource file.

I’ll now discuss the important components of the Unit Testing Ecosystem which reside inside the assemblies

Microsoft.VisualStudio.QualityTools.UnitTestFramework:

This assembly is the testing framework assembly that the unit test engine depends upon while executing unit tests.

Microsoft.VisualStudio.QualityTools.UnitTestFramework:

This namespace supplies classes that provide unit testing support. This namespace contains many attributes that identify test information to the test engine regarding data sources, order of method execution, program management, agent/host information, and deployment data.

I will touch upon the important attributes and classes that are required by the test engine to execute unit tests.

TestClassAttribute: The class containing test methods should be marked with this attribute else the tests won’t execute. This attribute can only be applied to a class.
AssemblyInitializeAttribute: Method marked with this attribute will be run prior to methods marked with ClassInitializeAttribute, TestInitializeAttribute, and TestMethodAttribute attributes. This attribute can only be applied to a method. Only one method in an assembly may be decorated with this attribute. An example of usage of this attribute is setting up database based upon setting from a configuration file i.e. deploying database, generating data.
AssemblyCleanupAttribute: Method marked with this attribute will be run after all the test methods in that assembly will execute i.e. after methods marked with TestMethodAttribute, TestCleanupAttribute and ClassCleanupAttribute attributes. This attribute can only be applied to a method. Only one method in an assembly may be decorated with this attribute. This method won’t execute if there is any exception in the method marked with AssemblyInitialize attribute.
ClassInitializeAttribute: Method marked with this attribute will run only once in the entire test. This attribute can only be applied to a method. This method runs before the method marked with TestInitialize attribute and after the method marked with AssemblyInitialze attribute. A class can have only one method marked with this attribute.
ClassCleanupAttribute: Method marked with this attribute will run only once in the entire test. This attribute can only be applied to a method. This method runs before the method marked with AssemblyCleanup attribute and after the method marked with TestCleanup attribute. A class can have only one method marked with this attribute. This method won’t execute if there is any exception in the method marked with ClassInitialize attribute.
TestMethodAttribute: Test method should be marked with this attribute else method won’t execute. This attribute can only be applied to a method.
TestInitializeAttribute: The method that is marked with this attribute runs before each test is executed. This method runs before methods marked with TestMethod attribute and after method marked with ClassInitialize attribute. This attribute can only be applied to a method. A class can have only one method marked with this attribute.
TestCleanupAttribute: The method that is marked with this attribute runs after each test is executed. This method runs before method marked with ClassCleanup attribute and after methods marked with TestMethod attribute. This attribute can only be applied to a method. A class can have only one method marked with this attribute. This method won’t execute if there is any exception in the method marked with TestInitialize attribute.
DataSourceAttribute: This attribute provides data-source specific information for data-driven testing.
PriortyAttribute: This attribute is used to specify the priorty of an unit test.
IgnoreAttribute: This attribute is used to ignore the unit test i.e. specific test should not run.
ExpectedExceptionAttribute: This is used in case exception is expected during execution of unit test.
Assert: This class verifies conditions in unit test.
TestContext: This class is used to store information that is provided to unit tests.
TestConfigurationSection: This class provides access to data source configuration data.
TestConfiguration: This class provides access to a TestConfigurationSection that represents the microsoft.visualstudio.testtools section in an app.config file.

The order in which methods marked with these attributes will run by the test engine is displayed below assuming we have one assembly, one class marked with TestClassAttribute, one method marked with TestMethodAttribute. I have provided this example as

Methods marked with AssemblyInitialize and AssemblyCleanUp attributes will execute only once for an assembly.
Methods marked with ClassInitialize and ClassCleanUp attributes will execute only once for each class in an assembly.
Methods marked with TestInitialize and TestCleanUp attributes will execute before and after respectively for each test method(i.e. method marked with TestMethod attribute).

I have discussed the common attributes required by the test engine to execute unit test. For more information about this assembly please read this link microsoft.visualstudio.testtools.unittesting. I’ll now move on to the next assembly i.e. Microsoft.Data.Schema.UnitTesting.

Microsoft.Data.Schema.UnitTesting:

This assembly provides the classes to run and execute unit tests. The classes grouped by namespace for this assembly are displayed below

Microsoft.Data.Schema.UnitTesting:

This namespace provides classes that you can use to create and execute database unit tests. The classes are listed below

DatabaseTestClass: This is the base calss for a database unit test. This class derives from Component. For more information about choosing between IComponent, Component, IDisposable, MarshalByValueComponent and Control while implementing a class please read my blog How to Choose Component, IComponent ....
ConnectionContext: This class encapsulated the database connection associated with the database unit test. In the DatabaseTestClass we have two properties of this type i.e. ExecutionContext and PrivilegedContext. The ExecutionContext and PrivelegedContext differ only if SQL Server authentication is used. If Windows authentication is used same credentials will be used for both. There is a reason behind having two separate contexts inside a DatabaseTestClass
- ExecutionContext: This defines the context used to execute the Transact-Sql script associated with an unit test. Ideally connection string should have same credentials as that of end user. This is the ExecutionContext element in the App.config.
- PrivilegedContext: This is optional and defines the context used for running the pre-test action, post-test action, TestInitialize, and TestCleanup scripts. This connection string is also used to deploy database changes and generate data. This is the PrivilegedContext element in the App.config. If unit tests run the test script only, then there is no need to specify a privileged context.
DatabaseTestAction: This class contains Transact-Sql test script and test actions.
DatabaseTestActions: This class is the design time equivalent of test and contains PostTestAction, PreTestAction and TestAction that are of type DatabaseTestAction.
DatabaseTestService: This class provides methods to Deploy database project, Execute test, Generate data and opening of Execution context and privileged execution contexts.
ExecutionResult: This is the result for and unit test and contains Dataset, execution time and rows affected.
ExpectedSqlExceptionAttribute: This attribute specifies that unit test is expecting a SQL exception and optionally specify the error number, severity, and state of the expected error.
TestScript: This class represents a generated SQL test
UnitTestingDesignService: Provides the base class for database schema providers to generate scripts for objects in their project system.

Microsoft.Data.Schema.UnitTesting.Configuration:

This namespace provides classes used to read or write configuration information to the DatabaseUnitTesting section of the app.config. The classes are listed below

ConnectionContextElement: This class represents the ExecutionContext or PrivilegedContext configuration element in the DatabaseUnitTesting section of the app.config file
DatabaseDeploymentElement: This class represents the DatabaseDeployment configuration element in the DatabaseUnitTesting section of the app.config file.
DatabaseUnitTestingSection: This class represents the DatabaseUnitTesting configuration section in the app.config file.
DatabaseGenerationElement: This class represents the DataGeneration configuration element in the DatabaseUnitTesting section of the app.config file.

Microsoft.Data.Schema.UnitTesting.Conditions:

This namespace provides classes that you can use to create and manage test conditions. The classes are listed below

TestCondition: Base class of all test conditions.
ChecksumCondition: This condition verifies the checksum of the result set in a database unit test.
DataSetTestCondition: This is the base class for test conditions that operate on a DataSet and are configured by the user during test "design time".
EmptyResultSetCondition: For this test conditon to succeed Execution.DataSet property must be empty.
ExecutionTimeCondition: If test takes more time to execute than specified by this condition test fails.
ExpectedSchemaCondition: Specifies that result set of a database unit test must match a specific schema.
InconclusiveCondition: Represents a default condition where nothing is required or expected of a test result.
NotEmptyResultSetCondition: For this test conditon to succeed Execution.DataSet property should not be empty.
ResultSetCondition: Represents the base class for all test conditions that operate on one ResultSet.
RowCountCondition: Defines a test condition that verifies the number of rows in the ResultSet that is returned by a database unit test.
ScalarValueCondition: Verifies that a particular value in a specific row and column was expected.

This concludes the part-2 of the series “How to unit test SQL Server 2008 using Visual Studio 2010”.

In the next article I’ll show how to unit test a database using the components discussed in this article.

The link to next article of this series is Unit test database - Part 3

del.icio.us Tags: Unit Test,SqL Server 2008,Visual Studio 2010

How to choose between IDisposable, IComponent, Component, MarshalByValueComponent and Control while implementing a Class

Atul.Verma — Sat, 31 Jul 2010 23:15:00 GMT

In this article I’ll discuss about the criteria behind choosing IDisposable, IComponent, Component, MarshalByValueComponent and base Control classes(System.Windows.Form.Control and System.Web.UI.Control) while implementing a Class.

Prior to discussing further I’d like to share the reason behind this article. I ran code analysis in a project code and got a warning that Dispose was not called for a DataSet in the project’s code. I than called the Dispose method for that particular DataSet and this time code analysis executed with zero errors/warnings. Out of curiosity I thought of verifying the use of calling Dispose on DataSet as even though when Dispose was not being called everything was fine. I found that calling Dispose method did nothing as DataSet is a managed object and does not contain unmanaged resources(if I’m wrong please correct me on this). I could verify this as I was able to access/update the DataSet even after Dispose had been called without any “Object Disposed Exception”. FeatureSchema is a typed DataSet as displayed in code snippet below

FeatureSchema fs = Proxy.GetFeatureSchema();

fs.Dispose();

foreach (FeatureSchema.FeatureRow featureRow in fs.Feature.Rows)

//Do procesing on dataset

I went one step further to find the reason behind having Dispose method in DataSet/DataTable and the reason was inheritance as DataSet/DataTable inherits from System.ComponentModel.MarshalByValueComponent class. The reason DataSet/DataTable inherits MarshalByValueComponent class so that it can be designable i.e. used on a design surface. Thus DataSet/DataTable has dispose method even though it doesn’t clean up the resources. DataSet/DataTable call GC.SuppressFinalize in the constructor to prevent the Garbage Collector from calling Object.Finalize on an object that does not require it.

This can be proved by the code snippet displayed below

I have a typed dataset FeatureSchema and I have added a destructor to it. I have excluded the code that was auto generated as I am focusing on finalizers only

public partial class FeatureSchema : global::System.Data.DataSet

//Added only a destructor to this types dataset. I have excluded all other code

// as I am focusing on finalizers

~FeatureSchema ()

As displayed below I am loading data to this typed dataset from an .xml file

//I am just loading the data into this typed dataset

FeatureSchema schema = new FeatureSchema();

schema.ReadXml(featureSchemaFile, XmlReadMode.Auto);

As DataSet calls GC.SuppressFinalize in the constructor Object.Finalize will not be called by the Garbage Collector. This can be verified by placing a break point on the destructor and you will find that it is not getting called.

Now I will call GC.ReRegisterForFinalize as displayed in the code snippet below

FeatureSchema schema = new FeatureSchema();

GC.ReRegisterForFinalize(schema);

schema.ReadXml(featureSchemaFile, XmlReadMode.Auto);

Calling GC.ReRegisterForFinalize will register the object for finalization. I am doing this as I know that DataSet calls GC.SuppressFinalize in the constructor. Now the code will break on the break point which I placed on the destructor at some random time i.e. when GC will execute the “GC Finalizer Thread”.

This verifies the need of calling GC.SuppressFinalize from the constructor of DataSet/DataTable to prevent the garbage collector from requesting finalization.

Even though Dispose in DataSet/DataTable does nothing we should not ignore it and stick to the .Net coding practices otherwise in future versions of Framework things may break. Please do correct me if I am wrong. This concludes the reason behind this article.

I’ll continue with the main theme of article. The guidelines for implementers are

IDisposable:

If a class uses external resources and will not be used on a design surface IDisposable interface has to be implemented directly or indirectly e.g. System.Drawing.Font class etc.
IComponent:

If a class will be used on a design surface IComponent interface has be implemented directly or indirectly. IComponent implements IDisposable e.g. System.Web.UI.Control class etc.
Component:

If a class will be used on a design surface and is Marshalled by reference then it has to derive from Component class e.g. System.Timers.Timer class etc.
MarshalByValueComponent:

If a class will be used on a design surface and is Marshalled by value then it has to derive from MarshalByValueComponent class e.g. DataSet, DataTable etc.
Control:

If a class will be used on a design surface and provides a user interface then this class is a control and has to derive from System.Windows.Form.Control or System.Web.UI.Control e.g. System.Windows.Forms.Button etc.

The above discussion does not apply to WPF. The WPF Designer architecture is significantly different from the Windows Forms Designer architecture, which is characterized by the IComponent interface and the System.ComponentModel namespace. The WPF Designer architecture retains the TypeConverter and TypeDescriptor classes from the Windows Forms Designer object model. Most other aspects of the WPF Designer architecture are different. For more information please read Comparing the Windows Forms Designer Framework to the WPF Designer Framework

Refrences: http://msdn.microsoft.com/en-us/library/0b1dk63b.aspx

del.icio.us Tags: .NET,.NET Framework,IDisposable,IComponent,Component,MarshalByValueComponent,Control,Windows Forms,ASP.NET,DataSet,DataTable,Garbage Collector,GC

How to host a service in Azure and expose using WCF and Visual Studio 2010 : Issues Faced and Fixes

Atul.Verma — Wed, 28 Jul 2010 03:30:00 GMT

This blog is regarding my first attempt to explore “Hosting a service in Azure using WCF and Visual Studio 2010”, the issue I faced and the quick fixes required to make it running. You can explore the Windows Azure Platform at http://www.microsoft.com/windowsazure/

I faced a few issues and so sharing the workarounds. I won’t get into details in this post so keeping things simple and targeting the workarounds only.

1. Run the Visual Studio 2010 with elevated permission e.g. “Run as Administrator” option otherwise you will get a prompt as displayed below when creating/opening the Windows Azure Cloud Service project.

You may need to install Windows Azure Tools for Microsoft Visual Studio to enable the creation, configuration, building, debugging, running and packaging of scalable web applications and services on Windows Azure. The download link for Windows Azure Tools for MS Visual Studio 1.1 is http://www.microsoft.com/downloads/details.aspx?FamilyID=5664019e-6860-4c33-9843-4eb40b297ab6&displaylang=en

2. Create a New Project. Select Cloud from Installed Templates and then select Windows Azure Cloud Service and click Ok.

3. Select the WCF Service Web Role as we are exposing service using WCF. I’ll go with the default code that gets generated and without making any change according to the context of this Blog. Build and Run the application

4. In order to test the WCF services we will use Microsoft WCF Test Client. This can be opened as displayed below

5. We need to Add the Service to test by right clicking on the My Service Projects and then clicking Add Service. Here we need to specify the end point address e.g. in my case it was “//127.0.0.1:81/Service1.svc?wsdl”

6. In case you see the error “Service metadata may not be accessible” as displayed below we need to add a Service Behaviour in the Web.Config file as displayed in next step

7. The section displayed in green font is required to resolve the error in above step. This is a known issue and there is a patch available for it at http://code.msdn.microsoft.com/wcfazure/Wiki/View.aspx?title=KnownIssues. You may see that IDE doesn’t recognise it shows a Warning so ignore the warning

8. Now when you run you may see the error displayed below in browser. This is a known issue and there is a patch available for it at http://code.msdn.microsoft.com/wcfazure/Wiki/View.aspx?title=KnownIssues. Install this patch and after installation restart your machine

9. Now when you run the application it will run fine so it’s time to test it using Microsoft Windows Test Client. In case WCF HTTP Activation is turned off you may now see this error while trying to Add the service to test as in Step 5

10. In Order to resolve the error above just turn on the WCF HTTP activation in “Turn Windows Features on and off” which is turned off as in image displayed below

11. Now add the service to the WCF test client as in Step 5. Voila finally everything is working fine. In order to test your WCF service choose any of the Operation e.g. GetData() in this case, double click it, specify the Value in Request section e.g. I have specified 23 and click Invoke. In the Response you will see the results

12. At runtime, WCF services may return the following error: The message with To 'http://127.0.0.1:81/Service.svc' cannot be processed at the receiver, due to an AddressFilter mismatch at the EndpointDispatcher. Check that the sender and receiver's EndpointAddresses agree. The error can be corrected by applying the following attribute to the service class.
[ServiceBehavior(AddressFilterMode = AddressFilterMode.Any)]

This concludes the article as we have Service hosted in Microsoft Azure platform and exposed using WCF and Visual Studio 2010. Time to explore the advanced features of Azure platform.

INotifyPropertyChanging and INotifyPropertyChanged in Real Life using Expression Trees

Atul.Verma — Wed, 28 Jul 2010 03:08:57 GMT

Functional Requirements:

Requirements when we need to achieve any or all of the functionalities listed below

Undo/Redo functionality in an Application
Any of user interface patterns e.g. MVC, MVP, MVVM etc
Other patterns State Pattern, Events, Event handlers etc.

In this blog I’ll discuss the points mentioned below

Sample Scenario
High level Solution
Solution using .Net Framework 2 (and Drawbacks)
Solution using .Net Framework 3.5 using Lambda Expressions
Summary

public class Barometer 
{ 
private double pressure; 

public double Pressure 
{ 
get 
{ 
return pressure; 
} 
set 
{ 
pressure = value; 
} 
} 
}

e.g. Pressure has to be updated from “0” to “60”. In this case we can save “0” as it is the previous state in the Undo stack. Redo stack will be empty as there is no action to Redo. The sequence of events for “Pressure” property will be [Before Update<”0”>] –> Write value to Undo Stack –> [Perform Update<”60”>]

Now if I have to to Undo the action that I performed I’ll copy the state from the Undo stack. Then write the state to the Redo stack so that I can Redo the action and then apply the state on the “Pressure” property.

I can use an Command pattern here and if an command implements IUndoable and IRedoable behavior we can have this framework in place.

In this blog I won’t be going into the details of Command pattern and Undo Redo implementation. I can extend this blog if the need arises as the main focus here is to discuss the high level implementation. I’ll focus on the interfaces INotifyPropertyChanging and INotifyPropertyChanged and their usage in this scenario.

public class Barometer : INotifyPropertyChanged, INotifyPropertyChanging 
{ 
private double pressure; 

public double Pressure 
{ 
get 
{ 
return pressure; 
} 
set 
{ 
if (value != pressure) 
{ 
//Maintaining the temporary copy of event to avoid race condition 
PropertyChangingEventHandler propertyChanging = PropertyChanging; 
if (propertyChanging != null) 
{ 
propertyChanging(this, new PropertyChangingEventArgs("Pressure")); 
} 

pressure = value;

//Maintaining the temporary copy of event to avoid race condition 
PropertyChangedEventHandler propertyChanged = PropertyChanged; 
if (propertyChanged != null) 
{ 
propertyChanged(this, new PropertyChangedEventArgs("Pressure")); 
} 
} 
} 
}

#region INotifyPropertyChanged Members 
public event PropertyChangedEventHandler PropertyChanged; 
#endregion 

#region INotifyPropertyChanging Members 
public event PropertyChangingEventHandler PropertyChanging; 
#endregion 
}

In the code snippet above I am maintaining the temporary copy to avoid possibility of a race condition in case the last subscriber unsubscribe after the null check and before the event is raised.

As displayed in Red text we are hard coding the name of the property. Here we have only one property but in real world we had to do the same across all the properties for which we needed to have the same behavior.

The issues with this approach are

Prone to typing mistakes
Any refactoring will break the code

We can achieve the same without these drawbacks in .Net framework 3.5 and above using Lambda expressions.

Solution using .Net framework 3.5:

The Barometer class updated to use Lambda Expression is displayed in code snippet below

public class Barometer : INotifyPropertyChanged, INotifyPropertyChanging 
{ 
private double pressure; 

public double Pressure 
{ 
get 
{ 
return pressure; 
} 
set 
{ 
//Using Lambda Expressions, Anonymous methods and Extension methods
PropertyChanged.SetPropertyValue(PropertyChanging, value, () => this.Pressure, new  Action<double>(delegate(double newValue) { pressure = newValue; })); 
} 
}

#region INotifyPropertyChanged Members 
public event PropertyChangedEventHandler PropertyChanged; 
#endregion

#region INotifyPropertyChanging Members 
public event PropertyChangingEventHandler PropertyChanging; 
#endregion 
}

The difference with the code snippet in .Net 2.0 version is in the set block in code snippet displayed above.

Using the Lambda expressions i.e. (()=>this.Pressure) along with anonymous methods and Extension methods we can achieve the same in much better way.

I have created one more class in which the Extension method SetPropertyValue is defined. This is a generic class and the functionality of method is explained in the inline comments. This is displayed in code snippet below

public static class PropertyChangeExtensions 
{ 
public static T SetPropertyValue<T>(this PropertyChangedEventHandler postHandler, 
PropertyChangingEventHandler preHandler, T newValue, Expression<Func<T>> oldValueExpression, 
Action<T> setter)

{ 
//Retrieve the old value 
Func<T> getter = oldValueExpression.Compile(); 
T oldValue = getter(); 

//In case new and old value both are equal to default 
//values for that type or are same return 
if ((Equals(oldValue, default(T)) && Equals(newValue, default(T))) 
|| Equals(oldValue, newValue)) 
{ 
return newValue; 
} 

//Retrieve the property that has changed 
var body = oldValueExpression.Body as System.Linq.Expressions.MemberExpression; 
var propInfo = body.Member as PropertyInfo; 
string propName = body.Member.Name; 
var targetExpression = body.Expression as ConstantExpression; 
object target = targetExpression.Value; 

//Maintaining the temporary copy of event to avoid race condition 
var preHandlerTemp = preHandler; 
//Raise the event before property is changed 
if (preHandlerTemp != null) 
{ 
preHandlerTemp(target, new PropertyChangingEventArgs(propName)); 
} 

//Update the property value 
setter(newValue); 

//Maintaining the temporary copy of event to avoid race condition 
var postHandlerTemp = postHandler; 
//Raise the event after property is changed 
if (postHandlerTemp != null) 
{ 
postHandlerTemp(target, new PropertyChangedEventArgs(propName)); 
}

return newValue;
} 
}

Summary:

Thanks for your patience to read up to this. This can be optimized by avoiding the repeated resolution of the expression tree(happening for every property change event). Implementing some sort of caching strategy will do the trick. Well is this the best solution. I’ll say “Depends”.

Pros:

Code should be easily refactored
Performance issues if any in this approach are considered to be insignificant
Solution can be extendable by using interfaces like ISerializable and saving state in Undo/Redo stacks for only the event i.e. chunk and not the complete state
You can control the properties for which you want to implement this kind of behavior by applying custom attributes

Cons:

Incase of a object tree in which you are having circular references this approach may or may not work. All depends how the application is designed

For a complex object tree I think one approach is to start with the parent object and then iterate over the child nodes until you reach the leafs. For every command that can be Undoable/Redoable you need to have and unique ID (GUID) and then you can save the state of the application against that when performing push/pop operation to Stack.

References:

More Effective C#: 50 Specific Ways to Improve Your C#

Technorati Tags: .Net Framework 3.5,Lambda Expressions,Expression Trees,INotifyPropertyChanging,INotifyPropertyChanged,Anonymous Methods,Extension Methods

How to unit test SQL Server 2008 database using Visual Studio 2010

Atul.Verma — Wed, 28 Jul 2010 03:05:00 GMT

In this article I’ll discuss about unit testing SQL Server 2008 Database project using Visual Studio 2010. As Unit tests test the part of the program integration testing becomes easier, moreover unit tests help enhancing, maintaining or extending a solution provided they are well written. This is first part of this series “How to unit test SQL Server 2008 database using Visual Studio 2010”. The next parts of this series are

Unit test SQL Server 2008 - Part 2 – Focused on internals of database unit testing i.e. assemblies

Unit test SQL Server 2008 - Part 3 – Focused on data generation, schema comparison, data comparison and data driven unit tests

There is no use to write an unit test badly as later on maintainability of the solution suffers. It either has to be a good unit test or no test at all. It’s better to eliminate bugs early rather than late and that is where unit tests wins. Secondly they can be fully automated and ideally should be.

Assuming a team is developing a solution and no unit testing is introduced. After 2 weeks they realize that some bugs have been introduced into the system. Now in order to find and fix these the team

needs to track all the changes to find when it got introduced
check for the features that are dependent and fix them
it may happen and to be true happens most of the time that the other features implemented though working fine may break with the fix that will be applied to close these bugs e.g. initial state of the code is S1. Developer A checks in that code and now state is S2. Developer B fixes some bug that was there in S1. As Developer B is working on codebase S2 so after he fixes the bug the state of code is S3. It was reported that there was an bug in changes Developer A made. Now as Developer B has checked in on state S2 and if those depend on Developer A changes that means after Developer A will fix his part, Developer B changes will break. See how much havoc it can create as here I considered only 2 check ins and 2 Developers.

This is where the power of writing good unit tests kick in. This ripple effect of bugs had not happened if unit testing framework was incorporated into the system. This adds to the confidence of the team and confidence can have a positive effect beyond the boundaries of the team. The next point is “Is unit testing a overload” and the answer is “No” unless you write bad unit tests.

“With power comes great responsibility” quote from Spiderman movie seems to be the right answer. I can go deep into unit tests but in this article I’ll discuss how we can unit test SQL Server 2008 database using Visual Studio 2010.

There are templates for SQL Server 2005 database project in Visual Studio 2008/2010 but those are out of scope of this article as I have not tried them. They may or may not have the same workflow as discussed later so I will restrict the scope of this article to only VS 2010 and SQL Server 2008.

Software:

VS 2010 Ultimate

SQL Server 2008

Test Data:

In this example as displayed below I will implement unit test for adding a Student i.e. uspAddStudent store procedure. If unit test has to be written for uspAddEventPass stored procedure then we need to set pre-test conditions as it has foreign key relation to Student and Event tables along with the test condition.

Please find below the SQL script for creating the test database for unit testing. This script will be imported so please save this to file.

USE [SampleDB]GO/****** Object:  Table [dbo].[Event]    Script Date: 07/27/2010 06:20:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Event]([EventId] [bigint] IDENTITY(1,1) NOT NULL,[EventName] [nchar](100) NOT NULL,CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED ([EventId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  Table [dbo].[Student]    Script Date: 07/27/2010 06:20:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Student]([StudentId] [bigint] IDENTITY(1,1) NOT NULL,[StudentName] [nchar](100) NOT NULL,CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([StudentId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  Table [dbo].[EventPass]    Script Date: 07/27/2010 06:20:37 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[EventPass]([EventPassId] [bigint] IDENTITY(1,1) NOT NULL,[EventId] [bigint] NOT NULL,[StudentId] [bigint] NOT NULL,CONSTRAINT [PK_EventPass] PRIMARY KEY CLUSTERED ([EventPassId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  StoredProcedure [dbo].[uspAddStudent]    Script Date: 07/27/2010 06:20:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        <Author,,Name>-- Create date: <Create Date,,>-- Description:    <Description,,>-- =============================================CREATE PROCEDURE [dbo].[uspAddStudent]-- Add the parameters for the stored procedure here@StudentName nchar(100)    ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON; -- Insert statements for procedure hereINSERT INTO [dbo].[Student]([StudentName])VALUES(@StudentName)ENDGO/****** Object:  StoredProcedure [dbo].[uspAddEvent]    Script Date: 07/27/2010 06:20:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        <Author,,Name>-- Create date: <Create Date,,>-- Description:    <Description,,>-- =============================================CREATE PROCEDURE [dbo].[uspAddEvent]-- Add the parameters for the stored procedure here@EventName nchar(100)    ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON; -- Insert statements for procedure hereINSERT INTO [dbo].[Event]([EventName])VALUES(@EventName)ENDGO/****** Object:  StoredProcedure [dbo].[uspGetEventDetails]    Script Date: 07/27/2010 06:20:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        <Author,,Name>-- Create date: <Create Date,,>-- Description:    <Description,,>-- =============================================CREATE PROCEDURE [dbo].[uspGetEventDetails]ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON; -- Insert statements for procedure hereSELECT EP.[EventPassId],EP.[EventId], E.EventName, EP.[StudentId], S.StudentName      FROM [dbo].[EventPass] As EPINNER JOIN .dbo.Student As SON EP.StudentId = S.StudentIdINNER JOIN .dbo.[Event] As EON EP.[EventId] = E.[EventId]ENDGO/****** Object:  StoredProcedure [dbo].[uspAddEventPass]    Script Date: 07/27/2010 06:20:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        <Author,,Name>-- Create date: <Create Date,,>-- Description:    <Description,,>-- =============================================CREATE PROCEDURE [dbo].[uspAddEventPass]-- Add the parameters for the stored procedure here@EventId bigint,@StudentId bigintASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON; -- Insert statements for procedure hereINSERT INTO [dbo].[EventPass]([EventId],[StudentId])VALUES(@EventId,@StudentId)ENDGO/****** Object:  ForeignKey [FK_EventPass_Event]    Script Date: 07/27/2010 06:20:37 ******/ALTER TABLE [dbo].[EventPass]  WITH CHECK ADD  CONSTRAINT [FK_EventPass_Event] FOREIGN KEY([EventId])REFERENCES [dbo].[Event] ([EventId])GOALTER TABLE [dbo].[EventPass] CHECK CONSTRAINT [FK_EventPass_Event]GO/****** Object:  ForeignKey [FK_EventPass_Student]    Script Date: 07/27/2010 06:20:37 ******/ALTER TABLE [dbo].[EventPass]  WITH CHECK ADD  CONSTRAINT [FK_EventPass_Student] FOREIGN KEY([StudentId])REFERENCES [dbo].[Student] ([StudentId])GOALTER TABLE [dbo].[EventPass] CHECK CONSTRAINT [FK_EventPass_Student]GO

Steps:

Step 1:

Open Visual Studio and create a new SQL Server 2008 Database Project as displayed below

Step 2:

Save the test SQL script for on you machine. Right click the project and click import script and import the saved script as displayed below

After import is done you will find the schema objects as highlighted below

Step 3:

In the Properties –> Deploy tab of the project set

Configure deployment settings for –> My Isolated development environment

Deploy Action –> Create a deployment script(.sql) and deploy to database

Target Connection –> Edit/New connection

Deployment configuration file –> Edit –> Uncheck Block incremental deployment if data loss might occur checkbox

as displayed below

Here an isolated development environment is set so that each database developer can make and test changes without affecting other members of the team. The isolated database development environment is based on the database project that contains the definitions for all the objects that are in the production database.

Create a deployment script(.sql) and deploy to database is selected as we want the database project to be deployed for each build configuration.

When you make changes to a database project and deploy them to a database server, some changes can result in data loss from database project being dropped and recreated. Here data loss is allowed as we are working in an isolated development environment on a database that is populated with test data.

Save changes and Build the solution.

Step 4:

Deploy this project as displayed below

You can configure the properties as displayed below. If you click Show All files then you can view the files generated e.g. in Debug mode. The connection string that you specified is saved in sql –> debug –> UnitTestDB.deploymanifest

Step 5:

I’ll now create database unit tests for the stored procedures. As displayed below go to View –> Database Schema View and then right click the stored procedure for which you want to write unit test and click Create Unit Tests as displayed below.

In case this option is disabled then there must be some issue with the sql file that has been imported and check the Error List for Warnings/Errors e.g. in my case I had specified [Database].[Schema].[Object] in one of the store procedure and though there was warning but I ignored as there were no errors :) and when I tried to create the unit test the option was disabled. The fix was to use [Schema].[Object] as displayed below

Now select the procedures as displayed below

Step 6:

In the configuration dialog for project ‘SampleDBTestProject’ configure options as displayed below

The connection string that you specified is saved in the app.config of this project.

Step 7:

Open the SampleDBStoredProcedureUnitTests as displayed below

Select dbo_uspAddStudentTest and in Test Conditions remove Inconclusive test condition. If an Inconclusive Test Condition is added to a database unit test, the test will always fail with an Inconclusive result. It’s not a passed result, nor a failed result. It indicates that the results of the test could not be verified. Add Row Count test condition and set Row Count = 1 as only one row gets added when this store procedure runs as displayed below

The snippet is displayed below

-- unit test for dbo.uspAddStudentDECLARE @RC AS INT, @StudentName AS NVARCHAR (100); SELECT @RC = 0,@StudentName = 'Unit Test'; EXECUTE @RC = [dbo].[uspAddStudent] @StudentName; SELECT * FROM [dbo].[Student];

Step 8:

Go to Test –> Windows –> Test View and select dbo_uspAddEventTest and Run Selection as displayed below.

In the Test Results the results of the tests run will be displayed. In case a particular test fails it will display as failed and the reason for failure.

In the same way the unit tests for other stored procedures can be written by specifying the Pre-Test/Test/Post-Test Conditions and Test Conditions.

The link to the next part of this series is Unit test SQL Server 2008 - Part 2

del.icio.us Tags: Visual Studio 2010,SQL Server 2008,Unit Test,Test View,Database Schema View

References: http://msdn.microsoft.com/en-us/library/aa833283.aspx

Application design and programming in .NET

Unit Test a project having external dependency(WCF Proxy) using Fakes & Visual Studio 11 Beta

Visual Studio 11 database projects and Unit Testing SQL Server database

Visual Studio 11 Developer Day – Hyderabad(18th Feb 2012)

Utility to generate Word documents from templates using Visual Studio 2010 and Open Xml 2.0 SDK - Part 4

Word 2010 Template having Charts –> Generated documents with Charts refreshed:

Code Changes:

Summary:

How to use Pex and Moles to generate unit tests for a project having external dependency(WCF Proxy) using Visual Studio 2010 SP1

Summary:

Utility to generate Word documents from templates using Visual Studio 2010 and Open Xml 2.0 SDK - Part 3

Utility to generate Word documents from templates using Visual Studio 2010 and Open Xml 2.0 SDK – Part 2 (Samples Updated)

Utility to generate Word documents from templates using Visual Studio 2010 and Open Xml 2.0 SDK

Word 2010 Template –> Generated Document:

Word 2010 Template –> Generated Document –> Refreshed Document:

Document Refresh from within Word –> Refreshed Document:

Template Design:

Implementation:

Summary:

How to upload/download a document in SharePoint 2010 using Client Context Object Model

Get ListItemCollection from SharePoint Document list using CAML:

Upload a document to SharePoint Document List:

Download a document from SharePoint Document List:

How to test a class member that is not public using Visual Studio 2010

PrivateObject:

PrivateType:

How to unit test SQL Server 2008 database using Visual Studio 2010 – Part 3

Data Generation Plan:

Data generation plan Template:

Data transform plan Template:

Schema Comparison:

Data Comparison:

Data driven unit tests

Common, Pre, Test and Post scripts:

Test Conditions:

Transactions:

How to unit test SQL Server 2008 database using Visual Studio 2010 – Part 2

Database Unit Test Designer:

Microsoft.VisualStudio.QualityTools.UnitTestFramework:

Microsoft.VisualStudio.QualityTools.UnitTestFramework:

Microsoft.Data.Schema.UnitTesting:

Microsoft.Data.Schema.UnitTesting:

Microsoft.Data.Schema.UnitTesting.Configuration:

Microsoft.Data.Schema.UnitTesting.Conditions:

How to choose between IDisposable, IComponent, Component, MarshalByValueComponent and Control while implementing a Class

IDisposable:

IComponent:

Component:

MarshalByValueComponent:

Control:

How to host a service in Azure and expose using WCF and Visual Studio 2010 : Issues Faced and Fixes

INotifyPropertyChanging and INotifyPropertyChanged in Real Life using Expression Trees

Functional Requirements:

Sample Scenario:

High Level Solution:

Solution using .Net framework 2.0:

Solution using .Net framework 3.5:

Summary:

References:

How to unit test SQL Server 2008 database using Visual Studio 2010

Software:

Test Data:

Steps:

Step 1:

Step 2:

Step 3:

Step 4:

Step 5:

Step 6:

Step 7:

Step 8: