Sahir Shah
23-January-2006
Most database formats support the storage binary large objects. These objects can be pictures, word documents , excel spreadsheets, PDF documents etc. This article describes how you can retrieve and display PDF documents stored as OLE Objects in an MSAccess database. Because we use ADO to retrieve data from the database this example will work equally well with MSSQL Server and Oracle, the only thing you need to do is modify the connection string.
Start MSAccess and create a database as C:\PDFCS\Test.mdb. Add a table to this database with one field. Name the field as Field1 and the table as Table1. Choose OLE Object as the data type for Field1. Now exit design mode and open the table in datasheet view. Right click a row and select "Insert Object". When the insert object dialog opens select insert from file, click browse and select some PDF file in your harddisk. Ensure that the link check box is not checked. Though storing a link to the object is the ideal way it is not suitable for this example.
Start Visual Studio and create a new C# windows application. Add a button called Button1 to the default form. Now we need to insert an ActiveX control to display PDF files. You must have ADOBE Acrobat Reader 5.0 or greater on your machine to proceed any further. Right click the toolbox and select customise toolbox. When the customise toolbox dialog opens select "Adobe Acrobat control for ActiveX" from the list of COM components. The component's location in a typical Acrobat 6 installation is C:\Program Files\Adobe\Acrobat 6.0\Reader\ActiveX\pdf.ocx.
After adding this control to the toolbar. Drag the control onto the form form the toolbar. Visual studio gives the control a default name of axPdf1. We will retain this. Now on the button click add the code
axPdf1.LoadFile("c:\\folder\\file.pdf");
On running the form you can see that it opens the specified file. One point to note here is that you cannot explicitly close a file. When a new file is loaded the current file is implicitly unloaded.
Next thing we need to do is add a reference to adodb, declare a connection object as an instance variable of class Form1, and add the function to connect to the database.
private ADODB.Connection connObj = null;
private bool ConnectToDB(){
connObj = new ADODB.ConnectionClass();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\PDFCS\\test.mdb;"
+ "User ID=Admin;Password=;";
connObj.ConnectionString = strConn;
connObj.Open(null, null , null , 0);
if(connObj.State == 1)
return true;
else
return false;
}
Now on the form load event handler add the function call to connect to the database.
private void Form1_Load(object sender, System.EventArgs e) {
if(ConnectToDB())
MessageBox.Show("connected");
else
MessageBox.Show("Database not found!");
}
Run the form and test if you are able to connect to the database.
The method we use here is to write the value returned by the recordset to disk using the ADODB Stream object and then we open the saved PDF document using Adobe control's LoadFile method. This method can be a little slow, but reading from disk is the only method supported by the Adobe Acrobat ActiveX control. When you are working with files that aren't unusually large there won't be a noticeable delay.
private void button1_Click(object sender, System.EventArgs e) {
string strSQL = "select Field1 from Table1";
object r = 0;
string strPDFSrc = "C:\\PDFCS\\temp.pdf";
ADODB.Recordset rs;
ADODB.StreamClass adoStream = new ADODB.StreamClass();
adoStream.Type = ADODB.StreamTypeEnum.adTypeBinary;
connObj.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
rs = connObj.Execute(strSQL,out r , 0);
adoStream.Open(Missing.Value , ADODB.ConnectModeEnum.adModeUnknown,
ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified , "" , "");
adoStream.Write(rs.Fields[0].Value);
adoStream.SaveToFile(strPDFSrc , ADODB.SaveOptionsEnum.adSaveCreateOverWrite);
if(!(axPdf1.LoadFile(strPDFSrc)))
MessageBox.Show("Error");
}
Typically we would call the Stream object's Open method with the source recordset as the first parameter, but if the record source contains binary data we need to call the Write method to create a stream from the recordset. But the Write method cannot be called unless the stream has been opened. To get around this we need to open the stream object without associating it with a record source. Instead of passing a record source as the first parameter you pass a Missing.Value to the method ( note : class Missing requires the inclusion of namespace System.Reflection), and then we write the stream to disk. Once the stream has been written to disk we are able to open it using the Acrobat control's LoadFile method which takes a file name as it's parameter.
The Visual Basic equivalent is as follows
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim adoStream As New ADODB.Stream()
adoStream.Type = ADODB.StreamTypeEnum.adTypeBinary
strSQL = "select Field1 from Table1"
rs = connObj.Execute(strSQL)
adoStream.Open()
adoStream.Write(rs.Fields(0).Value)
adoStream.SaveToFile("c:\PDFVB\temp.pdf", ADODB.SaveOptionsEnum.adSaveCreateOverWrite)
AxPdf1.LoadFile("c:\PDFVB\temp.pdf")
End Sub
When you fetch a new record from the database you don't need to call adoStream.Open() again. You can keep the stream open and call just the Write and SaveToFile methods to write the contents of the new record to disk. The file temp.pdf is overwritten and reused when a new record is fetched from the database.