Insert PDF in database C#

Question

1.00/5 (1 vote)

See more:

Insert PDF in database

Is this good way?

What I have tried:

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();                            


                        }

                    }

}

Posted 5-Aug-18 20:40pm

Goran Bibic

Updated 6-Aug-18 2:46am

Add a Solution

Comments

Richard MacCutchan 6-Aug-18 3:04am

Do you have a question?

Goran Bibic 6-Aug-18 3:15am

Yes. That is question. Why in db write just 0x

When put picture have 0xFFD8FFE000104A46494600010101000000000000FFE2021C4943435F50524F........

And I can read from db file

Ok?

Patrice T 6-Aug-18 3:11am

'Is this good way?'
Does it work ?

Goran Bibic 6-Aug-18 3:15am

Yes. That is question. Why in db write just 0x

When put picture have 0xFFD8FFE000104A46494600010101000000000000FFE2021C4943435F50524F........

And I can read from db file

Ok?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2018-08-05T21:44:00

While the actual storing of the PDF byte array is roughly correct, you have a fundamental problem in your ID generation. Rather than manually adding an ID yourself, consider changing this field to an autoincrement and let that take care of itself. As it stands, you run the risk of having two fields attempt to add the same ID in a multi-user system because there's no guarantee that you will complete the insert sequence before the next one commences.

Richard MacCutchan · Answer 2 · 2018-08-06T02:46:00

Solution 2

What is this code supposed to do?

C#

            byte[] filedata = null;   // filedata contains nothing
            MemoryStream ms = new MemoryStream(); // a new empty memory stream
            filedata = ms.GetBuffer();  // filedata still contains nothing
            axAcroPDF1.src = LocalEncoding.GetString(ms.ToArray()); // no idea what this is supposed to do

// ...

querySaveStaff.Parameters.AddWithValue("@pdf_file", SqlDbType.VarBinary).Value = filedata;
// but you never add any content to filedata, so it adds 0x0 to the database.

Posted 6-Aug-18 2:46am

Richard MacCutchan

Comments

Goran Bibic 6-Aug-18 13:15pm

Some help?

Richard MacCutchan 6-Aug-18 13:29pm

Some help: you just need to read the contents of the file into the filedata array. A simple operation using a streamreader.

Goran Bibic 6-Aug-18 13:35pm

I solve that and work...just need help to retrieve from db.

Save like this

private void Button2_Click(object sender, EventArgs e) //Save //snimi
{
OpenFileDialog ofd = new OpenFileDialog() { Filter = "PDF|*.pdf" };//Open pdf file
if (ofd.ShowDialog() == DialogResult.OK)
{

FileStream fs = File.OpenRead(ofd.FileName);
MemoryStream ms = new MemoryStream();
axAcroPDF1.src = LocalEncoding.GetString(ms.ToArray());
fs.CopyTo(ms);

if (count_numberTextBox.Enabled == true)
{
if (string.IsNullOrEmpty(idTextBox.Text))
{

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, count_number, label, partner, tax_number, date, file_location, pdf_file) VALUES (@maxNo,@count_number,@label,@partner,@tax_number,@date,@file_location,@pdf_file)";

using (SqlCommand querySaveStaff = new SqlCommand(saveStaff))
{
querySaveStaff.Connection = openCon;
querySaveStaff.Parameters.Add("@count_number", SqlDbType.VarChar, 255).Value = count_numberTextBox.Text;
querySaveStaff.Parameters.Add("@label", SqlDbType.VarChar, 255).Value = labelTextBox.Text;
querySaveStaff.Parameters.Add("@partner", SqlDbType.VarChar, 255).Value = partnerComboBox.Text;
querySaveStaff.Parameters.Add("@tax_number", SqlDbType.VarChar, 255).Value = tax_numberTextBox.Text;
querySaveStaff.Parameters.Add("@date", SqlDbType.Date).Value = dateDateTimePicker.Text;
querySaveStaff.Parameters.Add("@file_location", SqlDbType.VarChar, 255).Value = file_locationTextBox.Text;
querySaveStaff.Parameters.AddWithValue("@pdf_file", SqlDbType.VarBinary).Value = ms.ToArray();

openCon.Open();
querySaveStaff.ExecuteNonQuery();
openCon.Close();

dateDateTimePicker.Enabled = false;
count_numberTextBox.Enabled = false;
file_locationTextBox.Enabled = false;
labelTextBox.Enabled = false;
partnerComboBox.Enabled = false;
tax_numberTextBox.Enabled = false;
button3.Enabled = false;
button4.Enabled = false;

}

}
}
else
{

using (SqlConnection openCon = new SqlConnection(cs))
{
string saveStaff = "UPDATE dbo.documents SET count_number=@count_number, label=@label, partner=@partner, tax_number=@tax_number, date=@date, file_location=@file_location, pdf_file=@pdf_file WHERE id= " + idTextBox.Text;

using (SqlCommand querySaveStaff = new SqlCommand(saveStaff))
{

querySaveStaff.Connection = openCon;
querySaveStaff.Parameters.Add("@count_number", SqlDbType.VarChar, 255).Value = count_numberTextBox.Text;
querySaveStaff.Parameters.Add("@label", SqlDbType.VarChar, 255).Value = labelTextBox.Text;
querySaveStaff.Parameters.Add("@partner", SqlDbType.VarChar, 255).Value = partnerComboBox.Text;
querySaveStaff.Parameters.Add("@tax_number", SqlDbType.VarChar, 255).Value = tax_numberTextBox.Text

Richard MacCutchan 7-Aug-18 2:48am


FileStream fs = File.OpenRead(ofd.FileName);
MemoryStream ms = new MemoryStream();
axAcroPDF1.src = LocalEncoding.GetString(ms.ToArray());
fs.CopyTo(ms);

I still do not understand what all the above code is about. You only need to read the file into a byte array in order to get it into the database. Then to retrieve it you read the database record into another byte array and write that to a new file. Converting to strings and memory streams is just a waste of time and effort.

Richard MacCutchan 6-Aug-18 13:34pm

I notice also you have a field called file_location. What is that supposed to be for? If you are saving the location of the file on disk and the content, then one of those fields is redundant.

Goran Bibic 6-Aug-18 13:37pm

How to retrieve from db?

I try this...but somesthing wrong...file pdf

Other textbox and combo box values are ok

private void DocumentsDataGridView_SelectionChanged(object sender, EventArgs e)
{
if (documentsDataGridView.SelectedRows.Count > 0)
{

string id = documentsDataGridView.SelectedRows[0].Cells["dataGridViewTextBoxColumn1"].Value.ToString();
string number = documentsDataGridView.SelectedRows[0].Cells["dataGridViewTextBoxColumn2"].Value.ToString();
string count_number = documentsDataGridView.SelectedRows[0].Cells["dataGridViewTextBoxColumn3"].Value.ToString();
string label = documentsDataGridView.SelectedRows[0].Cells["dataGridViewTextBoxColumn4"].Value.ToString();
string partner = documentsDataGridView.SelectedRows[0].Cells["dataGridViewTextBoxColumn5"].Value.ToString();
string tax_number = documentsDataGridView.SelectedRows[0].Cells["dataGridViewTextBoxColumn6"].Value.ToString();
string file_location = documentsDataGridView.SelectedRows[0].Cells["file_location"].Value.ToString();

idTextBox.Text = id;
numberTextBox.Text = number;
count_numberTextBox.Text = count_number;
labelTextBox.Text = label;
partnerComboBox.Text = partner;
tax_numberTextBox.Text = tax_number;
dateDateTimePicker.Value = Convert.ToDateTime(documentsDataGridView.SelectedRows[0].Cells["dataGridViewTextBoxColumn7"].Value);
file_locationTextBox.Text = file_location;

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

{

//FileStream fs = File.OpenRead(ofd.FileName);
//MemoryStream ms = new MemoryStream();
//fs.CopyTo(ms);

//byte[] ap = (byte[])(documentsDataGridView.SelectedRows[0].Cells["pdf_file"].Value);
//MemoryStream ms = new MemoryStream(ap);
//axAcroPDF1.src = LocalEncoding.GetString(ms.ToArray());
//axAcroPDF1.LoadFile(ms);

// //// pictureBox.Image = ConvertBinaryToImage(list[listView.FocusedItem.Index].Data);

// 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;
}

//SqlConnect