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

I have written one article about how to create dynamic word file from C# previously at http://www.sqlhub.com/2009/04/dynamic-word-document-with-sql-server.html

After reading it, one of my reader asked me how to insert image along with text in word document so here is modified code of the above article with Image code inside.

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

//inserting image (I am fetching image from D drive, you can have your own image from your own path)

wPara.Range.InlineShapes.AddPicture("D:\\color.jpg", ref missing, ref missing, ref missing);

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