Application development musings
on .NET, Azure, Docker, Kubernetes & OSS
Please find the slides and demos for the session I presented at Microsoft User Group Hyderabad(MUGH). The agenda of the session was
Download from
[Update: The source code has been updated to Visual Studio 2017 and DocumentFormat.OpenXml 2.8.1. You can download the code from GitHub.]
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
In Part 2 I discussed about
In Part 3 I discussed about
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.
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
/// <summary>
/// Refreshes the charts.
/// </summary>
/// <param name="mainDocumentPart">The main document part.</param>
protected virtual void RefreshCharts(MainDocumentPart mainDocumentPart)
{
}
/// <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();
}
}
}
/// <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.
Please provide feedback/comments and I’ll try to incorporate most of them in new releases.
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 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
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
/// <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)
}
}
![image_thumb2[1]](https://msdnshared.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/37/36/metablogapi/3718.image_thumb21_5DCF076C.png "image_thumb2[1]")
/// <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) =>
{ };
}
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/.
[Update: The source code has been updated to Visual Studio 2017 and DocumentFormat.OpenXml 2.8.1. You can download the code from GitHub.]
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
In Part 2 I discussed about
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
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 bytesbyte[] 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 callbyte[] 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:
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 – Focuses on internals of database unit testing i.e. assemblies
Unit test SQL Server 2008 – Part 3 – Focuses 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
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.
VS 2010 Ultimate
SQL Server 2008
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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[uspAddStudent]
-- Add the parameters for the stored procedure here
@StudentName nchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [dbo].[Student]
([StudentName])
VALUES(@StudentName)
END
GO
/****** Object: StoredProcedure [dbo].[uspAddEvent] Script Date: 07/27/2010 06:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[uspAddEvent]
-- Add the parameters for the stored procedure here
@EventName nchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [dbo].[Event]
([EventName])
VALUES(@EventName)
END
GO
/****** Object: StoredProcedure [dbo].[uspGetEventDetails] Script Date: 07/27/2010 06:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[uspGetEventDetails]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT EP.[EventPassId],EP.[EventId], E.EventName, EP.[StudentId], S.StudentName
FROM [dbo].[EventPass] As EP
INNER JOIN .dbo.Student As S
ON EP.StudentId = S.StudentId
INNER JOIN .dbo.[Event] As E
ON EP.[EventId] = E.[EventId]
END
GO
/****** Object: StoredProcedure [dbo].[uspAddEventPass] Script Date: 07/27/2010 06:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[uspAddEventPass]
-- Add the parameters for the stored procedure here
@EventId bigint,
@StudentId bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [dbo].[EventPass]
([EventId]
,[StudentId])
VALUES
(@EventId
,@StudentId)
END
GO
/****** 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])
GO
ALTER 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])
GO
ALTER TABLE [dbo].[EventPass] CHECK CONSTRAINT [FK_EventPass_Student]
GO
Open Visual Studio and create a new SQL Server 2008 Database Project as displayed below
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
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.
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
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
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.
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.uspAddStudent
DECLARE @RC AS INT, @StudentName AS NVARCHAR (100);
SELECT @RC = 0,
@StudentName = 'Unit Test';
EXECUTE @RC = [dbo].[uspAddStudent] @StudentName;
SELECT * FROM [dbo].[Student];
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
References: http://msdn.microsoft.com/en-us/library/aa833283.aspx