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:
[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 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
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
The sample document generators are discussed later. The functionalities that can be achieved using the utility/source code are:
Document Generation
Content Controls
CustomXmlPart
Sample Generators:
[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 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
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.
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
I’ve named the tags in “Test_Template – 1.docx” as per their PlaceHolder type to make it more clear.
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
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. New samples will be added as per feedback.
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
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
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.
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 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
The data from two databases can be compared as display below
This concludes the first logical part.
In order to write a data driven unit test two important characteristics that we use are
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 TestContext
object 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()
{
}
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.
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
There are three ways through which we can specify transactions as listed 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
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.