Changed my blog

Hello, I changed my blog to http://desktopweb.wordpress.com/.

Design and Run a Windows Workflow 4.0 within Excel or Word 2010

I have a document at (http://desktopweb.codeplex.com/releases/view/70858#DownloadId=269599) that discusses more about integrating Windows Workflow into Office Add-ins.

The source code is located at http://desktopweb.codeplex.com/SourceControl/changeset/changes/9363.

Please note that an Access version is currently not included in the source code. Please let me know if an Access version would help you.

I prefer to put the documents into my codeplex.com project since it is much faster to authoring in Word and simply upload the document.

Here are a few screenshots from the source code.

Host a Windows Workflow 4.0 Designer in an Office add-in

desktopWeb.codeplex.com has a new check-in containing source code to host a Windows Workflow 4.0 designer in an Office add-in and a couple of SharePoint 2010 workflow activities. The source code is at http://desktopweb.codeplex.com/SourceControl/changeset/changes/9308 and the setup is at http://desktopweb.codeplex.com/releases/view/70858.

Update: A new check-in has a separate MyData.Workflow.Designer control.

The check-in covers the following Windows Workflow 4.0 topics:

·         Hosting a Windows Workflow 4.0 Designer surface in an Access add-in

o   Note that the WF4 designer can also be hosted in any other Office add-in such as an Excel VSTO project.

·         Custom Activity Designers  

·         Context sensitive WF4 designer expression editor that works with SharePoint 2010 Client Side Object Model (CSOM) list schema

·         Two SharePoint CSOM code activities

o   BasePermissions

o   UpdateListItem

·         Sample workflow that uses the SharePoint CSOM UpdateListItem activity to add a list item to a SharePoint task list

SharePoint CSOM code activities enable some interesting scenarios in that Office Add-in solutions that interact with SharePoint can be created in a declarative fashion. One can simply drag SharePoint 2010 activities into workflow logic and set some arguments to gain SP functionality. The check-in shows two activities as a starting point to develop other SP 2010 workflow activities. Perhaps others would like to contribute to the project by creating other SP 2010 activities. In a few weeks, I will follow up with a descriptive article about hosting a WF4 designer.

Note The designer is part of MyDataAddin. A future check-in will pull the designer out into a stand-alone UserControl.

Setup Note

In addition to running MyDataAddinSetup.msi, the custom activity designers need to be added to the GAC using the gacutil utility.

From the Visual Studio Command Prompt:

gacutil /i {FullPath}\bin\Debug\MyData.ActivityDesigners.dll

Workflow Designer

A workflow designer with custom activities can be hosted in any .NET 4.0 application (in this case an Access add-in). The vision is to create a suite of SP based activities to provide a much faster means to create Office / SharePoint applications.

SharePoint Activity Designer with Context-sensitive expression editor

Microsoft Patterns and Practices and Add-in Exception Logging

This post shows how to use the Microsoft Patterns and Practices (MSPP) Logging Block, and related Security Block, within an Access add-in to log exceptions to the Windows Event Viewer. MSPP is a collection of application blocks to assist developers with implementing common enterprise development scenarios.  Since sensitive logging data could be viewed by an unauthorized user, the post also introduces how to use the MSPP Security Block to prevent unauthorized access to the event log. Along with an introduction to using the security block, SharePoint Foundation 2010 base permission’s are demonstrated as an example authorization provider.

Topics

• Microsoft Patterns and Practices Logging Block
• Microsoft Patterns and Practices Security Block
• SharePoint 2010 Base Permissions

Sample Code

Sample code accompanying this post can be viewed and downloaded from http://desktopweb.codeplex.com/SourceControl/changeset/changes/8578. The Microsoft Enterprise Library 5.0 is required to compile and use the CodePlex sample.
Logging Block

The MSPP Logging Block is designed to address a wide range of enterprise application logging scenarios including logging exceptions, workflow logs and performance monitoring. This post discusses how to use the Logging Block, along with the Security Block, to enable Access add-in exception logging. Exception logging can be particularly handy to diagnose add-in issues.

 You can read the full document for this post at:

• PDF http://desktopweb.codeplex.com/releases/view/68662#DownloadId=251277 
• DOCX http://desktopweb.codeplex.com/releases/view/68662#DownloadId=251276

A user with proper permissions can view add-in event entries from within Access.

Logging Dialog Form

Steps to authorize a user:

Step 1 An AuthorizationRuleProvider configuration type configured in msaccess.exe.config defines a RuleProvider

<add type="Microsoft.Practices.EnterpriseLibrary.Security

    .AuthorizationRuleProvider, Microsoft.Practices.EnterpriseLibrary.Security"

  name="RuleProvider">

Step 2 An AuthorizationProvider also defines authorization Rules

<rules>

<add expression="R:ManageLog" name="ReadEventViewerLog" />

</rules>

Step 3 Get the RuleProvider defined in msaccess.exe.config

IAuthorizationProvider ruleProvider =

  EnterpriseLibraryContainer.Current

  .GetInstance<IAuthorizationProvider>("RuleProvider");

Step 4 Get the current user identity

WindowsIdentity identity = WindowsIdentity.GetCurrent();

Step 5 Get an IPrincipal object that maps an identity to a role

IPrincipal principal = new GenericPrincipal

  (WindowsIdentity.GetCurrent(), new string[] { "ManageLog" });

Note In a production application, the user’s rule expression would be stored in a secure data store such as a SQL Server Database or the SharePoint Permissions service.

Step 6 A RuleProvider.Authorize() method  determines whether an IPrincipal has ReadEventViewerLog authorization.

canReadEventViewerLog = ruleProvider.Authorize(principal, "ReadEventViewerLog");

SQL XML with Access

The previous post discussed a SurfaceView object model concept for managed code add-ins. This post shares how to use SQL Server 2005, and above, XML features to implement stored procedure array parameters. The SQL XML code uses the sample Northwind SQL Server database as well as the SurfaceView model. The example scenario sends a <Products><ProductID> xml list that returns a non-contiguous list of products.

Note      The sample code, located at http://desktopweb.codeplex.com, contains a sql script that adds the SQL CML code to the Northwind sample database. The Northwind sample database is also available at http://desktopweb.codeplex.com.

SelectByIdList Stored Procedure

CREATE PROCEDURE SelectByIdList (       @productIds xml ) AS SET ARITHABORT ON -- @Products table for Products Non-contiguous XML query DECLARE @Products TABLE (ProductID int) INSERT INTO @Products (ProductID) SELECT ParamValues.ProductID.value('.','INT') FROM @productIds.nodes('/Products/ProductID') as ParamValues(ProductID) SELECT * FROM     Products INNER JOIN     @Products p ON    Products.ProductID = p.ProductID -- Test the procedure EXEC SelectByIdList @productIds= '<Products><ProductID>37</ProductID><ProductID>6</ProductID> <ProductID>15</ProductID><ProductID>3</ProductID></Products>'

SelectByIdList() Method

Once the SelectByIdList stored procedure is added to the Northwind database, you need to write some ADO.NET code to call SelectByIdList @productIds sending the stored procedure a XElement containing a non-contiguous list of products. Using SQL XML parameters provides a flexible means to query SQL Server.

ADO.NET Code

private DataTable sp_SelectByIdList(XElement idElements) {   using (StringWriter swStringWriter = new StringWriter())   {    using (SqlConnection dbConnection =   new SqlConnection   (MyDataAddin.Properties.Settings.Default.NorthwindConnectionString))      {        using (SqlCommand dbCommand = new SqlCommand("SelectByIdList", dbConnection))        {          dbCommand.CommandType = CommandType.StoredProcedure;          SqlParameter parameter = new SqlParameter();          parameter.ParameterName = "@productIds";         parameter.DbType = DbType.Xml;          parameter.Direction = ParameterDirection.Input; // Input Parameter           parameter.Value = idElements.ToString();          dbCommand.Parameters.Add(parameter);          dbConnection.Open();          SqlDataAdapter da = new SqlDataAdapter(dbCommand);          northwindDataSet.sp_SelectByIdList.Clear();          da.Fill(this.northwindDataSet.sp_SelectByIdList);          return this.northwindDataSet.sp_SelectByIdList;        }      }    }   }

Create Products XElement

private void selectButton_Click(object sender, EventArgs e) {   XElement xmlIdElements = new XElement("Products");   foreach (DataGridViewRow row in vw_ProductListDataGridView.Rows)   {   if (true == Convert.ToBoolean(row.Cells["SelectCheckbox"].Value))    { xmlIdElements.Add(new XElement("ProductID", Convert.ToInt32(row.Cells["ProductIDColumn"].Value)));    }   }   //Bind the DataGridView   sp_SelectByIdListDataGridView.DataSource = sp_SelectByIdList(xmlIdElements); }

To run the sample, download and setup the source from http://desktopweb.codeplex.com/SourceControl/changeset/changes/7973.  You might need to change the NorthwindConnectionString within Settings.Settings to point to your SQL Server instance.

To run the sample

1)      Open the Dashboard form by clicking the Dashboard ribbon button

2)      Click the SQL Xml Table ribbon button to view the data grid task pane

When you click a product row, the data in the form is bound to the DataGridRow. As you can see from the example, the form is now bound to Access data and SQL Server data. The sample code partially implements ADO.NET data binding. Hopefully, this might get you started bringing SQL Server data and ADO.NET into your Access solutions.

BindForm

public void BindForm(DataRow currentRow) {   access.TextBox textBox;    //Fill Form from BindingSource    //Set Form Textbox values based on ColumnName match    foreach (DataColumn c in currentRow.Table.Columns)    {      if (ThisDatabase.AllForms["Dashboard"].FindControl(c.ColumnName))        { textBox = ThisDatabase.AllForms["Dashboard"].Controls(c.ColumnName) as access.TextBox;          textBox.Value = currentRow[c.ColumnName].ToString();        }    }    textBox = null;    }   } }

Navigate a DataTable

int rowPosition = 0; void prevCommand_Click() {   if (this.SqlXmlDataGrid != null && this.SqlXmlDataGrid.Visible)   {    if (rowPosition > 0)    {      rowPosition--; this.SqlXmlDataGrid.BindForm (this.SqlXmlDataGrid.CurrentDataRow.Table.Rows[rowPosition]);    }   } } void nextCommand_Click() {   if (this.SqlXmlDataGrid != null && this.SqlXmlDataGrid.Visible)   {    if (rowPosition + 1 < this.SqlXmlDataGrid.CurrentDataRow.Table.Rows.Count)    {      rowPosition++;           this.SqlXmlDataGrid.BindForm      (this.SqlXmlDataGrid.CurrentDataRow.Table.Rows[rowPosition]);    }   } }

Managing Access Add-ins with a SurfaceView

Extending Microsoft Access solutions with managed code has traditionally been a challenge. Visual Studio Shared Add-ins have been available for some time; however, managing the details of releasing Access.exe after closing an Access application has been a challenge. This article presents a SurfaceView object model that manages the release of Access form and report controls. After discussing the SurfaceView, the article suggests effective ways to extend Access applications using SurfaceView, Ribbon controls, custom task panes, a custom Access form and Windows Workflow 4.0. The code snippets used within the article are contained within the source code located at http://desktopweb.codeplex.com/SourceControl/changeset/changes/7526.

Topics

Using the SurfaceView object model to manage an Access .NET add-in enables extensibility opportunities beyond traditional VBA. This article presents several how to topics to get you started extending Access with managed code.

• Setup the sample including troubleshooting Add-in setup
• Use the sample
• Use a SurfaceView to manage forms and reports
• Use a ribbon button
• Create custom task panes
• Create a custom Access popup form
• Use Windows Workflow 4.0 to automate Access

Please note that the SurfaceView model is a sample of a potential object model to manage Access objects. As with any sample, the concepts and source code have not been thoroughly designed or tested and are not intended for production use.

The remainder of this blog is in pdf format …

PDF Article

Source code update

This week we will post new code to http://desktopweb.codeplex.com. The following screenshot shows the Access HTML editor improvements.

HTML Editor enhancements:

• Open content stored in an Access table
• Save content
• Common formatting including bold, italic, underline, fonts and colors
• Undo, cut, copy and paste
• Numbered list, bulleted list, outdent and indent
• DataLinks
• Find text
• Insert content via a HyperBar control (not fully implemented)

Access Addin stability is another significant improvement. An abstract SurfaceController class and corresponding FormController and ReportController class was added to provide a clean and stable model to manage Access form and report controls. Access controls can be referenced as ThisDatabase.AllForms["Dashboard"].Controls(“Budget”). The SurfaceController handles the details including ensuring that Access is always released when closed.

Silverlight WebWidgets can be included in a database as an attachment and deployed to the client.

The source code also shows more Windows Workflow 4.0 to add .NET features into Access. The idea is to create common CodeActivities that others can drop into a Workflow to automate Access.

I will upload the new source code and provide more documentation later this week …