-
Aug 4th, 2011, 05:12 AM
#1
Thread Starter
Lively Member
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…
-
Aug 4th, 2011, 08:48 AM
#2
Re: How to add and retrieve PDF files to MS SQL server database?
Try sample linked in my signature - although it's done for MS Access it can easily be adapted for any other database.
-
Aug 4th, 2011, 10:55 AM
#3
Thread Starter
Lively Member
Re: How to add and retrieve PDF files to MS SQL server database?
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...
-
Aug 4th, 2011, 11:04 AM
#4
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
-
Aug 4th, 2011, 11:25 AM
#5
Thread Starter
Lively Member
Re: How to add and retrieve PDF files to MS SQL server database?
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 :
No code example is currently available or this language may not be supported.
Regards...
-
Aug 4th, 2011, 11:50 AM
#6
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
-
Aug 4th, 2011, 12:08 PM
#7
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
-
Aug 4th, 2011, 12:40 PM
#8
Thread Starter
Lively Member
Re: How to add and retrieve PDF files to MS SQL server database?
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.
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….
-
Aug 5th, 2011, 07:55 AM
#9
Re: How to add and retrieve PDF files to MS SQL server database?
"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]
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
-
Aug 5th, 2011, 08:48 AM
#10
Thread Starter
Lively Member
Re: How to add and retrieve PDF files to MS SQL server database?
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
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…
-
Aug 8th, 2011, 11:19 AM
#11
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
-
Aug 8th, 2011, 12:13 PM
#12
Thread Starter
Lively Member
Re: How to add and retrieve PDF files to MS SQL server database?
-
Aug 8th, 2011, 01:10 PM
#13
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|