Yesterday I've been confirmed by Microsoft Indonesia to deliver a training about Visual Studio Tools for Office (VSTO) in Jakarta on Feb 28, 2007. So, I think it would be good to prepare it from now, start from building a simple Office Business Application (OBA) but yet useful. Here we go.
What I'm about to explain is a walkthrough to develop an application called Letter Numbering Word Add-In. As the name implied, the software is a Microsoft Word 2007 Add-In used to get business letter number from server to ensure global business letter numbering across a company or organization. This Add-In is displayed in Add-Ins tab of Word ribbon and has a button to get letter number for specified letter type from server and put the received number to active document. The server will track last number of specified letter type and give the next number to ensure there's no duplicate number in database. Database operation in server-side will be done using LINQ to SQL. Communication between Word Add-In and server will be done via Web Service. Pretty straightforward huh? In this walkthrough, I won't write much about the overview of used technologies. I suggest you to read some intro on other place.
As a recaps, the used technologies are:
- VSTO v3 delivered in Visual Studio 2008 Team System or as separated installation
- LINQ to SQL
- Web Service
As we know, most companies put some number to formal letters before they are sent to other parties. Letter number is used to easily track sent or received letters. The number need to be unique, so it must be managed centrally. The Word Add-In will look like:
After I select the letter type and click Get Letter No. button, a number will be written at current cursor in active document:
Server-Side
At server-side, two web service methods are deployed to get array of letter type and get the next number for selected letter type. These are steps to build the web service.
1. Open your holy Visual Studio 2008. Create a new web site or web application project. Name it as let say AdmWebService
2. Create a new database. Right click to web site name and Add New Item. Then in template list, click SQL Server Database, name it Adm.mdf.
3. Create two tables: LetterType an LetterNumbers. The definition of those tables are:
LetterType table:
LetterNumbers table:
LetterType column of LetterNumbers table is a foreign key to LetterTypeCode column of LetterType table. Input some records to those tables.
4. Add New LINQ to SQL Classes. Right click to web site name, Add New Item, and select LINQ to SQL Classes, name it as Adm.dbml. The designer will be displayed. Then select those two tables from Server Explorer window and drag to designer. You will have this:
5. Create the web service.
Still at web site project, add a new web service an name it AdmWebService.asmx. At code behind of that web service (AdmWebService.asmx.cs) prepare two web methods: GetLetterTypes() to return list of LetterType objects and GetLetterNo() to return letter number string. The codes are:
[WebMethod]
public List<LetterType> GetLetterTypes()
{
AdmDataContext db = new AdmDataContext();
var letterTypes = from lt in db.LetterTypes select lt;
return letterTypes.ToList<LetterType>();
}
As you can see, that method is basically used to query all letter types to LetterType table in Adm database. The query is specified using LINQ sintax. I won't explain about the query, you need to understand LINQ itself.
The codes of GetLetterNo() method are:
[WebMethod]
public string GetLetterNo(String letterTypeCode)
{
AdmDataContext db = new AdmDataContext();
var lastNo = from l in db.LetterNumbers
where l.LetterType == letterTypeCode
select l.LetterNo;
long lastLetterNo = 0;
if (lastNo.Max().HasValue) {
lastLetterNo = lastNo.Max().Value;
}
String letterNoFormat = ConfigurationManager.AppSettings["letterNoFormat"];
String generatedLetterNo = letterNoFormat;
generatedLetterNo = generatedLetterNo.Replace("{letterNoSeq}", (lastLetterNo + 1).ToString());
generatedLetterNo = generatedLetterNo.Replace("{letterType}", letterTypeCode);
generatedLetterNo = generatedLetterNo.Replace("{month}", DateTime.Now.Month.ToString());
generatedLetterNo = generatedLetterNo.Replace("{year}", DateTime.Now.Year.ToString());
LetterNumber ln = new LetterNumber();
ln.LetterNo = lastLetterNo + 1;
ln.LetterType = letterTypeCode;
ln.GeneratedLetterNo = generatedLetterNo;
db.LetterNumbers.InsertOnSubmit(ln);
db.SubmitChanges();
return generatedLetterNo;
}
The logic of those code are:
a. Get last number of specified letter type code
b. Get letter number format from application setting of web.config. You need to add a AppSetting in web.config like this:
Change the format to suit your need
c. Construct the new number
d. Save resulted new number to database
e. Return the new number
So, the complete codes of the web service are:
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Configuration;
using System.Text;
using System.Text.RegularExpressions;
namespace DyCode.WebService
{
/// <summary>
/// Summary description for Service1
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class AdmWebService : System.Web.Services.WebService
{
[WebMethod]
public string GetLetterNo(String letterTypeCode)
{
AdmDataContext db = new AdmDataContext();
var lastNo = from l in db.LetterNumbers
where l.LetterType == letterTypeCode
select l.LetterNo;
long lastLetterNo = 0;
if (lastNo.Max().HasValue) {
lastLetterNo = lastNo.Max().Value;
}
String letterNoFormat = ConfigurationManager.AppSettings["letterNoFormat"];
String generatedLetterNo = letterNoFormat;
generatedLetterNo = generatedLetterNo.Replace("{letterNoSeq}", (lastLetterNo + 1).ToString());
generatedLetterNo = generatedLetterNo.Replace("{letterType}", letterTypeCode);
generatedLetterNo = generatedLetterNo.Replace("{month}", DateTime.Now.Month.ToString());
generatedLetterNo = generatedLetterNo.Replace("{year}", DateTime.Now.Year.ToString());
LetterNumber ln = new LetterNumber();
ln.LetterNo = lastLetterNo + 1;
ln.LetterType = letterTypeCode;
ln.GeneratedLetterNo = generatedLetterNo;
db.LetterNumbers.InsertOnSubmit(ln);
db.SubmitChanges();
return generatedLetterNo;
}
[WebMethod]
public List<LetterType> GetLetterTypes()
{
AdmDataContext db = new AdmDataContext();
var letterTypes = from lt in db.LetterTypes
select lt;
return letterTypes.ToList<LetterType>();
}
}
}
6. Test your web service. Right click to web service and click View in Browser, you should have this screen:
Click to a method, invoke, and see the returned value.
Now, your web service should be prepared.
Word 2007 Add-In
Now it's time to develop Word 2007 Add-In to consume above web service.
1. Still in same solution, Add New Project, select Word 2007 Add-in in the template list, and name it WordLetterNumbering. If the project template is not available, make sure you have VSTS 2008 or install VSTO template.
2. Add Service Reference. To be able to consume AdmWebService, you need to add service reference. Right click to WordLetterNumbering project, then click Add Service Reference and a dialog will be displayed. At dialog, click Discover button to search available web services in current solution. You should have this screen. Type the namespace, let say DyCodeAdmServiceReference or whatever you want.
3. Create Ribbon. What I like the most about VSTO v3 & VS2008 is you are allowed to design ribbon visually.
Right click to project name, Add New Item, select Ribbon (Visual Designer) from project templates, and name it LetterNumberingRibbon.cs. Then design the ribbon so it will look like this.
As you see, there's two control in the ribbon: DropDown and Button ribbon control. You can drag ribbon controls from toolbox, at Office Ribbon Controls tab. Name those controls to: dropDownLetterType for DropDown and buttonGetLetterNo for Button.
4. Write ribbon code. At Ribbon Designer, right click and click View Code. Write these codes:
public partial class LetterNumberingRibbon : OfficeRibbon
{
public LetterNumberingRibbon()
{
InitializeComponent();
dropDownLetterType.Label = String.Empty;
}
private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
{
bindLetterTypeDropDown();
}
private void bindLetterTypeDropDown()
{
DyCodeAdmServiceReference.AdmWebServiceSoapClient serv = new DyCodeAdmServiceReference.AdmWebServiceSoapClient();
DyCodeAdmServiceReference.LetterType[] types = serv.GetLetterTypes();
RibbonDropDownItem item = new RibbonDropDownItem();
item.Label = "- Letter Type -";
item.Tag = String.Empty;
dropDownLetterType.Items.Add(item);
foreach(DyCodeAdmServiceReference.LetterType t in types) {
item = new RibbonDropDownItem();
item.Label = t.Name;
item.Tag = t.LetterTypeCode;
dropDownLetterType.Items.Add(item);
}
}
private void buttonGetLetterNo_Click(object sender, RibbonControlEventArgs e)
{
DyCodeAdmServiceReference.AdmWebServiceSoapClient serv = new DyCodeAdmServiceReference.AdmWebServiceSoapClient();
if (!String.IsNullOrEmpty(dropDownLetterType.SelectedItem.Tag.ToString()))
{
String letterNo = serv.GetLetterNo(dropDownLetterType.SelectedItem.Tag.ToString());
Globals.ThisAddIn.Application.ActiveDocument.Content.InsertAfter(letterNo);
}
}
}
Off course you need to add click event handler to Get Letter No. button, name it: buttonGetLetterNo_Click
5. I think that's it. Now you can try to debug your project.
Publish Word Add-in
Beside ribbon visual designer, one of cool features about VSTO v3 and VS2008 is simplification of deployment. Back to old days with VSTO 2005, it's so hard to deploy VSTO project. You need to create a setup project, manually set CAS policy, etc, etc. Now, it's as simple as click Publish button. Here are the steps.
1. Right click to Word Add-in project, and click Publish. You'll see this dialog. Specify the location to publish the application. Then click next.
2. Next screen allow you to select default installation path.
As you can see, it allow to install add-in from web site, UNC, or CD-ROM/Flash Disk/DVD. Choose last option. Click Next and Finish.
3. In publish folder, you'll have these files. Double click to setup.exe, follow the instructions, and you are ready to use the add-in.
That's it. Hopefully this post will give you a quick intro about VSTO v3, especially to build and deploy a Word 2007 Add-in, also other technology like LINQ to SQL.
Have a nice try. You can download this walkthrough solution files at: http://dycode.com/files/folders/codesamples/entry74.aspx