Dynamic word document with SQL Server data from C Sharp .NET

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

USE [adventureworks]

GO

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

--insert records

INSERT INTO emps

SELECT 'RITESH','MIS','ECHEM' UNION ALL

SELECT 'Rajan','MIS','mar'

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";

conn.Open();

//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();

recordAdp.Fill(recordSet);

command.Dispose();

conn.Close();

object missing = System.Reflection.Missing.Value;

object visible = true;

object start1 = 0;

object end1 = 0;

try

{

//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[0].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[0].Rows[i]["name"].ToString() + "\n you are appointed in " + recordSet.Tables[0].Rows[i]["dept"].ToString()+ " department";

wPara.Range.InsertParagraphAfter();

//making page break

wPara.Range.InsertBreak(ref missing);

}

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)

{

Response.Write(ex.ToString());

}

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com