SQL Server Database data into PDF

[Mirza Javed]

Greetings

I was wondering if there was a sample out there that can show me how to pretty much read the data from the SQL Server database table and insert it into a PDF document. I am using an asp.net enviroment to do this.

Thanks

[Ajish]

Hi Mirza, Thank you for your interest in Essential PDF. Essential PDF has support for inserting tables into the PDF page. This is useful for drawing tabular data into the PDF page. There is also built in support for importing a ADO.NET Data Table into a table in the PDF page. Steps in inserting ADO.Net DataTable · Create a new table using ITable interface.This table is inserted in to the PDF document. · Get the Datatable to be inserted from the database and assign to the created table DataMember using Dataset. · Setting styles and colors to the table. · Set the PageBreakBehavior property of the created table to span in multiple pages.Set the Header to repeat in all pages of the pdf document. · Draw the table into the created PDF document. Given below is code snippet to establish connection to sql sever

string ConnectionString = "server=localhost; Trusted_Connection=yes; database=northwind"; 

string CommandString ="Select * from Customers";

SqlDataAdapter  DataAdapter= new SqlDataAdapter(CommandString,ConnectionString);

DataSet dset = new DataSet();

DataAdapter.Fill(dset,"Customers");

DataTable dTable=dset.Tables[0];

Here is a sample for your reference:NorthWindReport.zip Please let me know if you need any further assistance. Regards, Ajish.

[Mirza Javed]

Thanks for your reply Ajish. That worked perfect. But, now I have another question for you. When I try to save the files to my c:\ drive it works just fine but when I try to save it to a directory with a UNC path ("\\pdfFiles\client") it throws an error. Any suggestions.

Thanks
Mirza

[Mirza Javed]

The correct file path looks something like this:

pdfDoc.Save("\\server\share\file_path")

Thanks

[Ajish]

Hi Mirza, I think the problem is due to the path not being well formatted. Some of the valid paths that are acceptable are: · "c:\\MyDir\\MyFile.txt" in C#, or "c:\MyDir\MyFile.txt" in Visual Basic. · "c:\\MyDir" in C#, or "c:\MyDir" in Visual Basic. · "MyDir\\MySubdir" in C#, or "MyDir\MySubDir" in Visual Basic. · "\\\\MyServer\\MyShare" in C#, or "\\MyServer\MyShare" in Visual Basic. Here is a sample using the path given by you: UNCpathSample.zip Some important points to remember: 1. Path names are limited to 248 characters 2. The specified path can also refer to a relative path or a Universal Naming Convention (UNC) path for a server and share name. 3. Ensure that your paths are well-formed when using methods that accept a path string .In C# path names should have \\ instead of singe \ 4. If you don''t have permission it will generate FileIOPermission exception. I think this would help you. Please take a look at the sample and let me if you have any other questions. Thanks, Ajish. >The correct file path looks something like this: > >pdfDoc.Save("\\server\share\file_path") > >Thanks

[Mirza Javed]

Thanks for your reply. I tried some of the code that you posted in this thread, but I still could not resolve the problem. I am coding this application in VB.Net. According to my IT Director I have all the rights to the folder that I am trying to save the files to. Anyways, I have attached a document which shows the error screen that I get everytime I try to transfer the PDF files to the directory using the UNC path. Hopefully this will give more details. Thanks for your help again, Mirza.

PDF_save_error.zip

[Ajish]

Hi Mirza,

From your description, based on my local tests and the code snippet, the problem is likely caused by something with your environment. This may be due to the account that ASP.NET is running under. By default it''s a local account called ASPNET, and unless you''re using impersonation of some sort, you won''t be able to see network shares. There
are several options when it comes to accessing network shares (and SQL Server) from ASP.NET.

Would you help me confirm the following
things:

1. Does the problem occur if you directly pass the full physical Path of
your application''s virtual dir to the CreateDirectory function?

2. If still occurs, we may do some check on permission. How about turn off
impersonate and use the default process identity(should be machine\aspnet
for win2k server) and grant "modify/write" permission to that account. Test
again to see whether that can work. ( You can also test creating directory
through passing full qualified path when you switch the process identity
and turn off impersonate).

BTW, to get the physical path of the root dir of our ASP.NET application,
we can use the Server.MapPath("~/"), this will be better than using the empty string which only retrieve the relative path the current directory.

Please feel free to let me know if you need any further help.

Thanks,
Ajish.

[Vadim Abramov]

Hi,
I can''t make NorthWindReport to work :( I am using VS 2003. Can you show to me the code how to insert datatable to PDF file?
There is my code :

Dim table As ITable = pdfDoc.CreateTable()
table.Style.BorderColors.All = Color.Black
table.Style.BordersWidth.All = 1
table.ImportDataTable(oDs.Tables("w-1"), True, 0, 0, 100, 30)
Nothing is showing up ..

[Vadim Abramov]

ops..i guess i was missing follow line :
table.Publish(New PointF(0, 0), pdfDoc.Pages(0))