Insert PDF in database c#

Goran Bibic

8y2.6kLearn iOS Programming

Is this good way?

private void Button2_Click(object sender, EventArgs e) //Save pdf in database
{
byte[] filedata = null;
MemoryStream ms = new MemoryStream();
filedata = ms.GetBuffer();
axAcroPDF1.src = LocalEncoding.GetString(ms.ToArray());
using (SqlConnection openCon = new SqlConnection(cs))
{
string saveStaff = "declare @maxNo integer = 0 select @maxNo = isnull(max(number), 0) from [dbo].[documents]; Set @maxNo=@maxNo+1; INSERT into dbo.documents (number,file_location, pdf_file) VALUES (@maxNo,@file_location,@pdf_file)";
using (SqlCommand querySaveStaff = new SqlCommand(saveStaff))
{
querySaveStaff.Connection = openCon;
querySaveStaff.Parameters.Add("@file_location", SqlDbType.VarChar, 255).Value = file_locationTextBox.Text;
querySaveStaff.Parameters.AddWithValue("@pdf_file", SqlDbType.VarBinary).Value = filedata;
openCon.Open();
querySaveStaff.ExecuteNonQuery();
openCon.Close();
}
}
}

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shweta Lodha8y ago
Hey Goran,
Here are the few articles which clearly mentioned that avoid saving contents of the file in DB as it may lead to caching and scalability issues.

https://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay#3756

https://habiletechnologies.com/blog/better-saving-files-database-file-system/
Raghavendar Swaminathan8y ago
Hi Goran,

You can go with the classic approach itself, it will works without any discrepancy. let me know if you facing any challenges.
Goran Bibic8y ago
Raghavendar Swaminathan

How to recieve file from db to adobe acrobat?

Clasic file open and show in acrobat is ok
Raghavendar Swaminathan8y ago
Hi Goran,

Query 1 : It will works, No Issues.

Query 2 : Refer this blog

https://www.c-sharpcorner.com/UploadFile/hirendra_singh/how-to-show-pdf-file-in-C-Sharp/

https://weblogs.asp.net/cazzu/25568
Goran Bibic8y ago
I use google drive 100gb
Raghavendar Swaminathan
But need some help
Goran Bibic8y ago
Raghavendar Swaminathan

Regards

1) When do this in db have just 0x, but in another app whe save picture have 0xFFD8FFE000104A46494600010101000000000000FFE2021C4943435F50524F........

Is that ok?

2) How to retrieve file from db and show in adobe raeder, I try in datagrid selection changed this 3 variant but not succesfull...some help?
try

               {

                 if (documentsDataGridView.SelectedRows[0].Cells["pdf_file"].Value != null)



                   {

                       //        // //byte[] ap = (byte[])(documentsDataGridView.SelectedRows[0].Cells["pdf_file"].Value);

                       //        // //MemoryStream ms = new MemoryStream(ap);

                       //        // //axAcroPDF1.src = LocalEncoding.GetString(ms.ToArray());

                       //        // ////axAcroPDF1.LoadFile(ms);









                       //        byte[] ap = (byte[])(documentsDataGridView.SelectedRows[0].Cells["pdf_file"].Value);

                       //        MemoryStream ms = new MemoryStream(ap);

                       //        ms.Position = 0;

                       //        File.WriteAllBytes(@"C:\PDFs.pdf", ms.ToArray());

                       //        //Process.Start(@"C:\PDFs.pdf");

                       //        axAcroPDF1.LoadFile(@"C:\PDFs.pdf");

                       //        axAcroPDF1.Enabled = true;





                     //  SqlConnection connection = new SqlConnection(cs);

                     //  connection.Open();

                     //  SqlCommand command = new

                     //    SqlCommand("select pdf_file from documents where id=" + idTextBox.Text, connection);

                     //  byte[] buffer = (byte[])command.ExecuteScalar();

                     //  connection.Close();

                     //  FileStream fs = new FileStream(@"C:\PDFs\test.pdf", FileMode.Create);

                     //  //fs.Write(buffer, 0, buffer.Length);

                     //  //fs.Close();



                     //axAcroPDF1.LoadFile(@"C:\PDFs\test.pdf");

                     //axAcroPDF1.Enabled = true;





                   }

                   else

                  {

                       axAcroPDF1.src = null;

                  }

               }

               catch

               {

                  axAcroPDF1.src = null;

               }
Raghavendar Swaminathan8y ago
Hi Goran,

You can use GDrive or One Drive. If you have any access to any content portal like Sharepoint use the library as source dB & you can perform the read/write operations.

Thanks
Raghav
Goran Bibic8y ago
My free choise is to save pdf in database. Using azure sql db services, becose need acces from 17 location, 145 employee. Some sugestion? Maybe is better solution google drive location and read from that? Or some deskop variant?
Raghavendar Swaminathan8y ago
Hi Goran,

You have use the varbinary [byte array] which is recommended to process the file oriented streaming in dB. Its good practise as well.

PFB reference Links,

https://www.morgantechspace.com/2014/05/How-to-Store-and-Retrieve-File-in-SQL-Server-using-CSharp.html

https://social.msdn.microsoft.com/Forums/windows/en-US/db8790ac-46c6-41e5-87b0-d714dc4a2fb1/retrieving-pdfword-document-from-database-and-display-it?forum=winforms
Shweta Lodha8y ago
It is always preferred to save the URL of the file in DB rather than contents. Do you have any specific requirement to save PDF contents in the memory, rather than location of a PDF?

Insert PDF in database c#

Raghavendar Swaminathan

Raghavendar Swaminathan