Retrieve pdf files from sql c#

  • 186 Views
  • Last Post 07 August 2018
  • Topic Is Solved
Stylus STYLUS posted this 06 August 2018

Insert in db working fine...

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;
                        querySaveStaff.Parameters.Add("@date", SqlDbType.Date).Value = dateDateTimePicker.Text;
                        querySaveStaff.Parameters.Add("@file_location", SqlDbType.VarChar, 255).Value = file_locationTextBox.Text;
                        //querySaveStaff.Parameters.Add("@pdf_file", SqlDbType.VarChar, 255).Value = partner_typeComboBox.Text;
                        querySaveStaff.Parameters.AddWithValue("@pdf_file", SqlDbType.VarBinary).Value = ms.ToArray();

                        openCon.Open();
                        querySaveStaff.ExecuteNonQuery();
                        MessageBox.Show("Uspješno ste izmenili stavku!", "Informacija", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        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
        {
            MessageBox.Show("Dokument je već potvrđen! Unesite novi ili izmjenite postojeći!", "Obavještenje", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        using (SqlConnection openCon = new SqlConnection(cs))
        {
            string saveStaff = "SELECT * FROM dbo.documents ";
            openCon.Open();
            using (SqlDataAdapter querySaveStaff = new SqlDataAdapter(saveStaff, cs))
            {
                querySaveStaff.Fill(dMS_2018DataSet.documents);
                documentsDataGridView.DataSource = dMS_2018DataSet.documents;
                documentsDataGridView.Update();
                documentsDataGridView.Refresh();
            }
        }

        countRowsLabel.Text = "Ukupno redova: " + documentsDataGridView.RowCount.ToString();
    }
}

How to retrieve files from db

I try this

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)
            {

                ////////////////////////////here I need help
            }
            else
            {
                axAcroPDF1.src = null;
            }
        }
        catch
        {
            axAcroPDF1.src = null;
        }
    }
}

 

 

lucy posted this 07 August 2018

First, You need to add a binding source to your DataGridView

Add an event handler to Form_Load. You should fetch your data from the Form_Load event

private void Form4_Load(object sender, EventArgs e)
{
    DbEntities db = new DbEntities();
    pdfFileBindingSource.DataSource = db.PdfFiles.ToList();
}

I'm using EF to fetch data from database.

Add an event handler to the CurrentChanged of your databinding

private void pdfFileBindingSource_CurrentChanged(object sender, EventArgs e)
{
    PdfFile pdf = pdfFileBindingSource.Current as PdfFile;
    if (pdf != null)
    {
        MemoryStream ms = new MemoryStream(pdf.Pdf);
        ms.Position = 0;
        File.WriteAllBytes(@"D:\testpdf.pdf", ms.ToArray());
        Process.Start(@"D:\testpdf.pdf");
    }
}
  • Liked by
  • Stylus STYLUS
Close