I have seen many time when question comes to generate dynamic word document with SQL Server’s data, developer feel confusion, not because it is difficult but because unawareness. .NET framework has made life so much easier than ever in programming world. Generating dynamic word document is really very easy. Let us see one example which will get employee name and department in which they have appointed and write separate page for each employee.
We will have two task, 1.) Create one table in SQL Server 2.) Use that SQL table in ASP.NET C# application and generate dynamic DOCX file.
1.) Creating table in SQL Server and insert some data in it.
CREATE TABLE [dbo].[emps](
) ON [PRIMARY]
INSERT INTO emps
SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL
2.) Now let us create on ASP.NET C# web application in VISUAL STUDIO 2008. Once creating website, first task we will do is, add reference of WORD library in our application from Website Menu of VS 2008 and “Add Reference” option. It will open one dialog box. You will have to move to “COM” table in that dialog box and find “Microsoft Word 12.0 Object Library”, select it and click on “OK”.
Note: You may have different WORD library other than 12.0, as per your system’s configuration.
After adding reference, create on button in your web form and write following code in that button’s click event.
//create connection to database
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = “Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True”;
//setup SqlCommand and assign SQL query in command
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.CommandType = System.Data.CommandType.Text;
command.Connection = conn;
command.CommandText = “Select name,dept From emps”;
//create one data adapter which will execute the command and fill the data into data set
System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();
recordAdp.SelectCommand = command;
System.Data.DataSet recordSet = new System.Data.DataSet();
object missing = System.Reflection.Missing.Value;
object visible = true;
object start1 = 0;
object end1 = 0;
//create one word application
Microsoft.Office.Interop.Word.ApplicationClass wordApp = new Microsoft.Office.Interop.Word.ApplicationClass();
//create one document for above word application
Microsoft.Office.Interop.Word.Document wordDoc = wordApp.Documents.Add(ref missing, ref missing, ref missing, ref missing);
for (int i = 0; i < recordSet.Tables.Rows.Count; i++)
//creating paragraph to add in word document
Microsoft.Office.Interop.Word.Paragraph wPara = wordDoc.Content.Paragraphs.Add(ref missing);
wPara.Range.Text = “Hello “ + recordSet.Tables.Rows[i][“name”].ToString() + “\n you are appointed in “ + recordSet.Tables.Rows[i][“dept”].ToString()+ ” department”;
//making page break
object fileName = “D:\\SQLHub.DOCX”;
wordDoc.SaveAs(ref fileName, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);
catch (Exception ex)
Reference: Ritesh Shah