Results 1 to 13 of 13

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

  1. #1

    Thread Starter
    Lively Member HOTFIX's Avatar
    Join Date
    Sep 2008
    Posts
    91

    Question 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…

  2. #2

  3. #3

    Thread Starter
    Lively Member HOTFIX's Avatar
    Join Date
    Sep 2008
    Posts
    91

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

    Quote Originally Posted by RhinoBull View Post
    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...

  4. #4
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

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

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  5. #5

    Thread Starter
    Lively Member HOTFIX's Avatar
    Join Date
    Sep 2008
    Posts
    91

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

    Quote Originally Posted by abhijit View Post
    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 :
    No code example is currently available or this language may not be supported.


    Regards...

  6. #6
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

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

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

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

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Lively Member HOTFIX's Avatar
    Join Date
    Sep 2008
    Posts
    91

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

    Quote Originally Posted by abhijit View Post
    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 View Post
    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….

  9. #9
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

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

    "SQL Code:
    1. /*SQL SERVER TABLE*/
    2. CREATE TABLE employees
    3. (
    4.   EmployeeId INT  NOT NULL  PRIMARY KEY,
    5.   Photo VARBINARY(MAX) FILESTREAM  NULL,
    6.   RowGuid UNIQUEIDENTIFIER  NOT NULL  ROWGUIDCOL
    7.   UNIQUE DEFAULT NEWID()
    8. )
    9. GO
    10. Insert into employees
    11. Values(1, 0x00, default)
    12. GO
    C# Code:
    1. /*READING EXAMPLE*/
    2. using (SqlConnection connection = new SqlConnection(
    3.     connStringBuilder.ToString()))
    4. {
    5.     connection.Open();
    6.     SqlCommand command = new SqlCommand("", connection);
    7.  
    8.     SqlTransaction tran = connection.BeginTransaction(
    9.        System.Data.IsolationLevel.ReadCommitted);
    10.     command.Transaction = tran;
    11.  
    12.     command.CommandText =
    13.         "select Top(1) Photo.PathName(), "
    14.         + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";
    15.     using (SqlDataReader reader = command.ExecuteReader())
    16.     {
    17.         while (reader.Read())
    18.         {
    19.             // Get the pointer for the file
    20.             string path = reader.GetString(0);
    21.             byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
    22.  
    23.             // Create the SqlFileStream
    24.             FileStream fileStream = new SqlFileStream(path,
    25.             (byte[])reader.GetValue(1),
    26.                 FileAccess.Read,
    27.                 FileOptions.SequentialScan, 0);
    28.  
    29.             // Read the contents as bytes and write them to the console
    30.             for (long index = 0; index < fileStream.Length; index++)
    31.             {
    32.                 Console.Write(fileStream.ReadByte());
    33.             }
    34.             fileStream.Close();
    35.         }
    36.     }
    37.     tran.Commit();
    38. }
    39.  
    40. /*WRITING EXAMPLE*/
    41. using (SqlConnection connection = new SqlConnection(
    42.     connStringBuilder.ToString()))
    43. {
    44.     connection.Open();
    45.  
    46.     SqlCommand command = new SqlCommand("", connection);
    47.     command.CommandText = "select Top(1) Photo.PathName(), "
    48.     + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";
    49.  
    50.     SqlTransaction tran = connection.BeginTransaction(
    51.         System.Data.IsolationLevel.ReadCommitted);
    52.     command.Transaction = tran;
    53.  
    54.     using (SqlDataReader reader = command.ExecuteReader())
    55.     {
    56.         while (reader.Read())
    57.         {
    58.             // Get the pointer for file
    59.             string path = reader.GetString(0);
    60.             byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
    61.  
    62.             // Create the SqlFileStream
    63.             FileStream fileStream = new SqlFileStream(path,
    64.                 (byte[])reader.GetValue(1),
    65.                 FileAccess.Write,
    66.                 FileOptions.SequentialScan, 0);
    67.  
    68.             // Write a single byte to the file. This will
    69.             // replace any data in the file.
    70.             fileStream.WriteByte(0x01);
    71.  
    72.             fileStream.Close();
    73.         }
    74.     }
    75.     tran.Commit();
    76. }
    [/HIGHLIGHT]
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  10. #10

    Thread Starter
    Lively Member HOTFIX's Avatar
    Join Date
    Sep 2008
    Posts
    91

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

    Quote Originally Posted by abhijit View Post
    "SQL Code:
    1. /*SQL SERVER TABLE*/
    2. CREATE TABLE employees
    3. (
    4.   EmployeeId INT  NOT NULL  PRIMARY KEY,
    5.   Photo VARBINARY(MAX) FILESTREAM  NULL,
    6.   RowGuid UNIQUEIDENTIFIER  NOT NULL  ROWGUIDCOL
    7.   UNIQUE DEFAULT NEWID()
    8. )
    9. GO
    10. Insert into employees
    11. Values(1, 0x00, default)
    12. GO
    C# Code:
    1. /*READING EXAMPLE*/
    2. using (SqlConnection connection = new SqlConnection(
    3.     connStringBuilder.ToString()))
    4. {
    5.     connection.Open();
    6.     SqlCommand command = new SqlCommand("", connection);
    7.  
    8.     SqlTransaction tran = connection.BeginTransaction(
    9.        System.Data.IsolationLevel.ReadCommitted);
    10.     command.Transaction = tran;
    11.  
    12.     command.CommandText =
    13.         "select Top(1) Photo.PathName(), "
    14.         + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";
    15.     using (SqlDataReader reader = command.ExecuteReader())
    16.     {
    17.         while (reader.Read())
    18.         {
    19.             // Get the pointer for the file
    20.             string path = reader.GetString(0);
    21.             byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
    22.  
    23.             // Create the SqlFileStream
    24.             FileStream fileStream = new SqlFileStream(path,
    25.             (byte[])reader.GetValue(1),
    26.                 FileAccess.Read,
    27.                 FileOptions.SequentialScan, 0);
    28.  
    29.             // Read the contents as bytes and write them to the console
    30.             for (long index = 0; index < fileStream.Length; index++)
    31.             {
    32.                 Console.Write(fileStream.ReadByte());
    33.             }
    34.             fileStream.Close();
    35.         }
    36.     }
    37.     tran.Commit();
    38. }
    39.  
    40. /*WRITING EXAMPLE*/
    41. using (SqlConnection connection = new SqlConnection(
    42.     connStringBuilder.ToString()))
    43. {
    44.     connection.Open();
    45.  
    46.     SqlCommand command = new SqlCommand("", connection);
    47.     command.CommandText = "select Top(1) Photo.PathName(), "
    48.     + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";
    49.  
    50.     SqlTransaction tran = connection.BeginTransaction(
    51.         System.Data.IsolationLevel.ReadCommitted);
    52.     command.Transaction = tran;
    53.  
    54.     using (SqlDataReader reader = command.ExecuteReader())
    55.     {
    56.         while (reader.Read())
    57.         {
    58.             // Get the pointer for file
    59.             string path = reader.GetString(0);
    60.             byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
    61.  
    62.             // Create the SqlFileStream
    63.             FileStream fileStream = new SqlFileStream(path,
    64.                 (byte[])reader.GetValue(1),
    65.                 FileAccess.Write,
    66.                 FileOptions.SequentialScan, 0);
    67.  
    68.             // Write a single byte to the file. This will
    69.             // replace any data in the file.
    70.             fileStream.WriteByte(0x01);
    71.  
    72.             fileStream.Close();
    73.         }
    74.     }
    75.     tran.Commit();
    76. }
    [/HIGHLIGHT]

    Hi abhijit



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

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

    Many Thanks…

  11. #11
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

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

    I am going to create a tutorial for the filestream object. You will find it in codebank.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  12. #12

    Thread Starter
    Lively Member HOTFIX's Avatar
    Join Date
    Sep 2008
    Posts
    91

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

    Quote Originally Posted by abhijit View Post
    I am going to create a tutorial for the filestream object. You will find it in codebank.
    Excellent …


    I appreciate your time and efforts for helping…


    Best Regards…

  13. #13
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

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

    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width