How to add and retrieve PDF files to MS SQL server database?

Hi…


Anybody PLZ guide me how to export PDF files to SQL database Table using [varbinary (MAX)] field, and retrieve it using Adobe PDF Reader control through VB.net Project.



Regards…

Try sample linked in my signature - although it's done for MS Access it can easily be adapted for any other database.

Quote:

---

Originally Posted by RhinoBull

Try sample linked in my signature - although it's done for MS Access it can easily be adapted for any other database.

---

Hi RhinoBull...
Thanks for reply..

Could you PLZ specify which Sample Link do you mean!
If it’s about storing & retrieving Images ! I've already knows the codes for images but I’m looking for PDF....

Best Regards...

HOTFIX,
My guess would be "Extract File from Datbase"

The basic concept is to save the bytes from that file to the column in the database.
If you are using Sql Server 2008 and .NET framework 4.0, you can use this info in msdn.

Quote:

---

Originally Posted by abhijit

HOTFIX,
My guess would be "Extract File from Datbase"

The basic concept is to save the bytes from that file to the column in the database.
If you are using Sql Server 2008 and .NET framework 4.0, you can use this info in msdn.

---

Hi abhijit...

The 1st link about images...

The 2nd link from msdn :

Quote:

---

No code example is currently available or this language may not be supported.

---

:confused:

Regards...

Hotfix,
you can use the exact same code that you would use for an image. just change the name of the file.
the concept is the same. you stream your file into an array of bytes and save those bytes to the varbinary column.

bytes are bytes... so storing an image in the database is exactly the same as it would be for a PDF file.

-tg

Quote:

---

Originally Posted by abhijit

Hotfix,
you can use the exact same code that you would use for an image. just change the name of the file.
the concept is the same. you stream your file into an array of bytes and save those bytes to the varbinary column.

---

Quote:

---

Originally Posted by techgnome

bytes are bytes... so storing an image in the database is exactly the same as it would be for a PDF file.

-tg

---

abhijit , tachgnome

I know it will be same principle for PDF and other cases (.docx , ,xlsx,...etc) that is saving it as a binary...

I've tried to alter the codes in my project to fit PDF files; unfortunately I couldn’t figure it how to make it work!

Anyway this is codes in my project for saving images in dataset table of SQL Server database:

Code:

---

Private Sub BtnAddInvoiceScannedCopy_Click(sender As System.Object, e As System.EventArgs) Handles BtnAddInvoiceScannedCopy.Click

        If TradeIDTextBox.Text.Trim = "" Then Beep() : Exit Sub

        Try

            If InvoiceScannedCopyPictureBox.Image IsNot Nothing Then

                If MsgBox("Do you want to replace the current image  " & " ? ", MsgBoxStyle.Exclamation + MsgBoxStyle.OkCancel, "Replace an image") = MsgBoxResult.Cancel Then Exit Sub
            Else

                If MsgBox(String.Format("Continuation add a new invoice image for the record ID number  {0} ? ", TradeIDTextBox.Text), MsgBoxStyle.Exclamation + MsgBoxStyle.OkCancel, "Insert Image") = MsgBoxResult.Cancel Then Exit Sub

            End If


            Application.DoEvents()



            Dim OFG As New OpenFileDialog

            OFG.Filter = "Files(*.jpg)|*.jpg|Files(*.gif)|*.gif|Files(*.bmp)|*.bmp|Files(*.png)|*.png"
            OFG.Title = "Insert image"
            OFG.FileName = ""


            If OFG.ShowDialog() = Windows.Forms.DialogResult.OK Then


                Dim fs As IO.FileStream = New IO.FileStream(OFG.FileName, IO.FileMode.Open, IO.FileAccess.Read)
                Dim LoadImage As Image = Image.FromStream(fs)
                fs.Close()



                Dim Stream As New IO.MemoryStream()

                Dim NewBitmap As New Bitmap(LoadImage, 275, 309)



                NewBitmap.Save(Stream, System.Drawing.Imaging.ImageFormat.Jpeg)


                Application.DoEvents()



                BusinessDataSet.Tables("Trade").Rows(TradeBindingSource.Position).Item("InvoiceScannedCopy") = Stream.ToArray


                Stream.Close()



                TradeBindingSource.EndEdit()
                TradeTableAdapter.Update(BusinessDataSet.Tables("Trade"))

                Application.DoEvents()


                MsgBox("The image was successfully saved", MsgBoxStyle.Information, "Trade")


            End If




        Catch ex As Exception

            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try
    End Sub

---

I hope anybody adjust the codes to save PDF files & binding it using Adobe PDF Reader Control...



Best Regards….

"SQL Code:

/*SQL SERVER TABLE*/
CREATE TABLE employees
(
  EmployeeId INT  NOT NULL  PRIMARY KEY,
  Photo VARBINARY(MAX) FILESTREAM  NULL,
  RowGuid UNIQUEIDENTIFIER  NOT NULL  ROWGUIDCOL
  UNIQUE DEFAULT NEWID()
)
GO
Insert into employees
Values(1, 0x00, default)
GO

C# Code:

/*READING EXAMPLE*/
using (SqlConnection connection = new SqlConnection(
    connStringBuilder.ToString()))
{
    connection.Open();
    SqlCommand command = new SqlCommand("", connection);
 
    SqlTransaction tran = connection.BeginTransaction(
       System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;
 
    command.CommandText = 
        "select Top(1) Photo.PathName(), " 
        + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for the file
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
 
            // Create the SqlFileStream
            FileStream fileStream = new SqlFileStream(path,
            (byte[])reader.GetValue(1),
                FileAccess.Read,
                FileOptions.SequentialScan, 0);
 
            // Read the contents as bytes and write them to the console
            for (long index = 0; index < fileStream.Length; index++)
            {
                Console.Write(fileStream.ReadByte());
            }
            fileStream.Close();
        }
    }
    tran.Commit();
}
 
/*WRITING EXAMPLE*/
using (SqlConnection connection = new SqlConnection(
    connStringBuilder.ToString()))
{
    connection.Open();
 
    SqlCommand command = new SqlCommand("", connection);
    command.CommandText = "select Top(1) Photo.PathName(), "
    + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";
 
    SqlTransaction tran = connection.BeginTransaction(
        System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;
 
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for file 
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
 
            // Create the SqlFileStream
            FileStream fileStream = new SqlFileStream(path,
                (byte[])reader.GetValue(1),
                FileAccess.Write,
                FileOptions.SequentialScan, 0);
 
            // Write a single byte to the file. This will
            // replace any data in the file.
            fileStream.WriteByte(0x01);
 
            fileStream.Close();
        }
    }
    tran.Commit();
}

[/HIGHLIGHT]

Quote:

---

Originally Posted by abhijit

"SQL Code:

/*SQL SERVER TABLE*/
CREATE TABLE employees
(
  EmployeeId INT  NOT NULL  PRIMARY KEY,
  Photo VARBINARY(MAX) FILESTREAM  NULL,
  RowGuid UNIQUEIDENTIFIER  NOT NULL  ROWGUIDCOL
  UNIQUE DEFAULT NEWID()
)
GO
Insert into employees
Values(1, 0x00, default)
GO

C# Code:

/*READING EXAMPLE*/
using (SqlConnection connection = new SqlConnection(
    connStringBuilder.ToString()))
{
    connection.Open();
    SqlCommand command = new SqlCommand("", connection);
 
    SqlTransaction tran = connection.BeginTransaction(
       System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;
 
    command.CommandText = 
        "select Top(1) Photo.PathName(), " 
        + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for the file
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
 
            // Create the SqlFileStream
            FileStream fileStream = new SqlFileStream(path,
            (byte[])reader.GetValue(1),
                FileAccess.Read,
                FileOptions.SequentialScan, 0);
 
            // Read the contents as bytes and write them to the console
            for (long index = 0; index < fileStream.Length; index++)
            {
                Console.Write(fileStream.ReadByte());
            }
            fileStream.Close();
        }
    }
    tran.Commit();
}
 
/*WRITING EXAMPLE*/
using (SqlConnection connection = new SqlConnection(
    connStringBuilder.ToString()))
{
    connection.Open();
 
    SqlCommand command = new SqlCommand("", connection);
    command.CommandText = "select Top(1) Photo.PathName(), "
    + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";
 
    SqlTransaction tran = connection.BeginTransaction(
        System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;
 
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for file 
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
 
            // Create the SqlFileStream
            FileStream fileStream = new SqlFileStream(path,
                (byte[])reader.GetValue(1),
                FileAccess.Write,
                FileOptions.SequentialScan, 0);
 
            // Write a single byte to the file. This will
            // replace any data in the file.
            fileStream.WriteByte(0x01);
 
            fileStream.Close();
        }
    }
    tran.Commit();
}

[/HIGHLIGHT]

---

Hi abhijit :wave:



Could you PLZ show me which part of your codes will fit the replacement of mine! :confused: cuz I’m a bit novice with handling vb codes :(

Can I ask you a favor! :blush: Could you PLZ post an attached project about this issue? That will be helpful…

Many Thanks…

I am going to create a tutorial for the filestream object. You will find it in codebank.

Quote:

---

Originally Posted by abhijit

I am going to create a tutorial for the filestream object. You will find it in codebank.

---

Excellent …:thumb:


I appreciate your time and efforts for helping…:)


Best Regards… :wave:

http://www.vbforums.com/showthread.php?t=657218