A Dynamic Word Template that Generates information from SQL

Have you ever wondered how to automatically generate word templates that grab information from a database? Now why would you do that? In case you have an organization that has a lot of departments or even branches and you want to distribute templates once and manage the data within from a SQL server database like the logo information, address information, or even contact details. This means any change in the information of any branch or department would not entail updating all of the templates that are in each workstation but rather change it in the database and let the template grab that information on a real time basis.

Having said that, you can have one template called TestTemplate.dotx and let's say depends on your AD Organizational Unit the contents of that template would change. For example your AD OU Membership is in “Branch A” then it would get information on a SQL Server Database regarding that Branch.

So let's start on how this is achieved.

First you need to create an Office 2007 Word Template Visual Studio Project.

It will then immediately ask for you to create a new document.

Then once it's created, open in Design Mode ThisDocument.cs and start designing your document like you are designing a form or a webpage.

Add the following objects in the header of the document, a Picture Content Control and Rich Text Content Control.

Name your objects according to your liking, that will be the name property that you will access on the code behind.

Now let's start coding. Here is the code:

public static string sSQLConnectionString = "Data Source={SERVERNAME};
	Initial Catalog={DatabaseName};User Id={UserName};Password={Password};";

private void ThisDocument_Startup(object sender, System.EventArgs e)
{
SqlConnection myConnection;
SqlDataReader myReader;
SqlCommand myCommand;

myConnection = new SqlConnection(sSQLConnectionString);

myConnection.Open();

SqlParameter myParam = new SqlParameter("@OU", SqlDbType.NVarChar, 50);
myParam.Value = GetUserOU(sUserName); //This is my own AD Method to get Users OU

myCommand = new SqlCommand("SELECT TOP 1 LogoPath, 
	BranchName FROM Branches where OU = @OU", myConnection);
myCommand.Parameters.Add(myParam);

myReader = null;

myReader = myCommand.ExecuteReader();

if (myReader.HasRows)
{
 while (myReader.Read())
 {
 imgHeaderLogo.Image = System.Drawing.Image.FromFile(myReader["LogoPath"].ToString());
 txtHeaderText.Text = myReader["BranchName"].ToString();
 }
}
}

Now if you are using SQL Server like me, don't forget to import these references:

using System.Data.Sql;
using System.Data.SqlClient;

Now publish your application:

Then remember to set up your application on your target machine. Then you can start using your template. Now depending on where you published the application, the setup.exe would show.

Note: The template will not run on a machine where the package was not installed as the references that are needed by the template are in that setup package. You can also set updates automatically on the Properties –> Publish section of the project.

Here's my final output: