Click here to Skip to main content
Click here to Skip to main content

A Dynamic Word Template that Generates information from SQL

By , 27 Jul 2010
Rate this:
Please Sign up or sign in to vote.

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:


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 3 Pinmembermanjeeet26-Jul-10 23:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 27 Jul 2010
Article Copyright 2010 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid