How to save 1 file PDF to SQL Server with WinForm

Question

Code query database:

I can't save the PDF files to my database, I have a serious problem in the database storage area.

public void ADDWORK(String MAW, string NAMEW, string IDUSER, bool Image,string room, byte[] document,string content,bool donework)
{
    String strSql = string.Format("INSERT INTO WORK(IDWORD,NAMEWORk,IDUSER,IMAGES,IDROOM,DOCUMENTS,CONTENT,DONEWORK)VALUES('{0}',N'{1}',N'{2}','{3}',N'{4}',N'{5}',N'{6}',N'{7}')"
        , MAW, NAMEW, IDUSER, Image,room,document,content,donework);
    db.ExecuteNonQuery(strSql);
}

Code call function:

byte[] pdf;
public void UploadFlie(string file)
{
    FileStream fileStream = File.OpenRead(file);
    byte[] contents = new byte[fileStream.Length];
    fileStream.Read(contents, 0, (int)fileStream.Length);
    fileStream.Close();
    pdf = contents;
}

private void button1_Click(object sender, EventArgs e)
{
    UploadFlie(filename);
     dg.ADDWORK(idword, textBox1.Text,"1",  false,"ROOM1", pdf, richTextBox1.Text, false);
    MessageBox.Show("Done!");
}

Answer 1

here is my example how to get any file and save it on database.

I use two diffrent methods

1. Saving byte array.
2. Saving Stream

First you need to create column varbinary(MAX) in my case I called it - PdfFile and PdfExtn column to save the type of the file As varchar(10) (You can use 4).

1. Saving byte array:

        private void btnGetFile_Click(object sender, EventArgs e)
    {
        OpenFileDialog openFileDialog1 = new OpenFileDialog();
        DialogResult result = openFileDialog1.ShowDialog(); // Show the dialog.
        if (result == DialogResult.OK) // Test result.
        {
            string file = openFileDialog1.FileName;
            string extn = new FileInfo(file).Extension;
   
            try
            {
                byte[] fileBytes = File.ReadAllBytes(file);
                string cmd = "INSERT INTO Employee(Id,FirstName,LastName,Email,Password,PdfFile,FileExtn) VALUES (@Id,@FirstName,@LastName,@Email,@Password,@PdfFile,@FileExtn)";
   
                List<SqlParameter> l = new List<SqlParameter>();
                l.Add(new SqlParameter("@Id", "101"));
                l.Add(new SqlParameter("@FirstName", "Aviv"));
                l.Add(new SqlParameter("@LastName", "Halevy"));
                l.Add(new SqlParameter("@Email", "Assadsa@gmail.com"));
                l.Add(new SqlParameter("@Password", "123456"));
                SqlParameter sp = new SqlParameter("@PdfFile", SqlDbType.VarBinary, -1);
                sp.Value = fileBytes;
                l.Add(sp);
                l.Add(new SqlParameter("@FileExtn", extn));
                if (DAL.Database.ParametersCommand(cmd, l) > 0)
                    textBox1.Text = "SUCCESS! Save with byte array";
            }
            catch (IOException ex)
            {
   
            }
        }
    }
   

Write the file from bytes:

  private void btnReadFromDb_Click(object sender, EventArgs e)
    {
        string cmd = "SELECT * FROM Employee WHERE Id = '101'";
        string path = "YOUR_PATH\\Test";
        DataTable dt = DAL.Database.GetDataTable(cmd);
        if (dt != null && dt.Rows.Count > 0)
        {
            Byte[] file = (Byte[])dt.Rows[0]["PdfFile"];
            string extn = dt.Rows[0]["FileExtn"].ToString();
            path = Path.Combine(path, "Test321"+extn);

            File.WriteAllBytes(path, file);
            Process.Start(path);
        }
    }

And the result:

2. Using Stream

   private void button1_Click(object sender, EventArgs e)
    {
   
        OpenFileDialog openFileDialog1 = new OpenFileDialog();
        DialogResult result = openFileDialog1.ShowDialog(); // Show the dialog.          
        if (result == DialogResult.OK) // Test result.
        {
            string file = openFileDialog1.FileName;
            using (Stream stream = File.OpenRead(file))
            {
                byte[] buffer = new byte[stream.Length];
                stream.Read(buffer, 0, buffer.Length);
                string extn = new FileInfo(file).Extension;
                string cmd = "INSERT INTO Employee(Id,FirstName,LastName,Email,Password,PdfFile,FileExtn) VALUES (@Id,@FirstName,@LastName,@Email,@Password,@PdfFile,@FileExtn)";
   
                List<SqlParameter> l = new List<SqlParameter>();
   
                l.Add(new SqlParameter("@Id", "102"));
                l.Add(new SqlParameter("@FirstName", "Aviv"));
                l.Add(new SqlParameter("@LastName", "Halevy"));
                l.Add(new SqlParameter("@Email", "Assadsa@gmail.com"));
                l.Add(new SqlParameter("@Password", "123456"));
                l.Add(new SqlParameter("@PdfFile", buffer));
                l.Add(new SqlParameter("@FileExtn", extn));
                if (DAL.Database.ParametersCommand(cmd, l) > 0)
                    textBox1.Text = "SUCCESS! Save with Stream";
            }
        }
    }
   

Write the file from stream:

   private void button2_Click(object sender, EventArgs e)
    {
        string cmd = "SELECT * FROM Employee WHERE ID = '102'";
        string path = "YOUR_PATH\\Test";
        DataTable dt = DAL.Database.GetDataTable(cmd);
        if (dt != null && dt.Rows.Count > 0)
        {
            Byte[] file = (Byte[])dt.Rows[0]["PdfFile"];
            string extn = dt.Rows[0]["FileExtn"].ToString();
            path = Path.Combine(path, "Test123"+extn);

            File.WriteAllBytes(path, file);
            Process.Start(path);
        }
    }

And the result:

Note: In my examples the DAL project has a static methods that runs my sql commands.