Retrieve binary file path from Database and Save in Directory in Windows Application using C# and VB.Net

Last Reply 3 months ago By pandeyism

Posted 4 months ago

Hi! I used Save and retrieve word file into database using c# windows application it's helpful. Here save name and path file into database, but I want save file word into database. After click retrieve button get file from database and save in define path.

I am saving binary data in database.

You are viewing reply posted by: pandeyism 3 months ago.
Posted 3 months ago Modified on 3 months ago

Hey PRA,

Please refer below sample.

Namespaces

C#

using System.IO;
using System.Data.SqlClient;

VB.Net

Imports System.IO
Imports System.Data.SqlClient

Code

C#

private void button1_Click(object sender, EventArgs e)
{
    string saveDirectory = @"D:\File\";
    using (OpenFileDialog openFileDialog1 = new OpenFileDialog())
    {
        if (openFileDialog1.ShowDialog() == DialogResult.OK)
        {
            if (!Directory.Exists(saveDirectory))
            {
                Directory.CreateDirectory(saveDirectory);
            }
            filepath.filename = Path.GetFileName(openFileDialog1.FileName);
            filepath.fileSavePath = Path.Combine(saveDirectory, filepath.filename);
            string fileName = openFileDialog1.FileName;
            byte[] bytes = File.ReadAllBytes(fileName);
            string contentType = "";
            switch (Path.GetExtension(fileName))
            {
                case ".docx":
                    contentType = "application/vnd.ms-word";
                    break;
                case ".pdf":
                    contentType = "application/pdf";
                    break;
                case ".gif":
                    contentType = "image/gif";
                    break;
                case ".bmp":
                    contentType = "image/bmp";
                    break;
            }
            File.Copy(openFileDialog1.FileName, filepath.fileSavePath, true);
            //insert the file into database
            string constr = @"Data Source=.\SQL2005;Database=dbFiles; uid=sa; pwd=pass";
            SqlConnection conn = new SqlConnection(constr);
            string strQuery = "insert into tblfiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)";
            SqlCommand cmd = new SqlCommand(strQuery, conn);
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filepath.filename;
            cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar).Value = contentType;
            cmd.Parameters.AddWithValue("@Data", bytes);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}

private void button2_Click(object sender, EventArgs e)
{
    byte[] bytes;
    string fileName;
    string constr = @"Data Source=.\SQL2005;Database=dbFiles; uid=sa; pwd=pass";
    SqlConnection conn = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("SELECT TOP 1 Id, Name, ContentType, Data FROM tblfiles ORDER BY Id DESC", conn);
    conn.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    foreach (DataRow dr in dt.Rows)
    {
        fileName = dr["Name"].ToString();
        bytes = (byte[])dr["Data"];

        File.WriteAllBytes(@"C:\Users\anand\Desktop\" + fileName, bytes);
        conn.Close();
    }
}

public static class filepath
{
    public static string fileSavePath;
    public static string filename;
}

VB.Net

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim saveDirectory As String = "D:\File\"
    Using openFileDialog1 As OpenFileDialog = New OpenFileDialog()
        If openFileDialog1.ShowDialog() = DialogResult.OK Then
            If Not Directory.Exists(saveDirectory) Then
                Directory.CreateDirectory(saveDirectory)
            End If
            filepath.filename = Path.GetFileName(openFileDialog1.FileName)
            filepath.fileSavePath = Path.Combine(saveDirectory, filepath.filename)
            Dim fileName As String = openFileDialog1.FileName
            Dim bytes As Byte() = File.ReadAllBytes(fileName)
            Dim contentType As String = ""

            Select Case Path.GetExtension(fileName)
                Case ".docx"
                    contentType = "application/vnd.ms-word"
                Case ".pdf"
                    contentType = "application/pdf"
                Case ".gif"
                    contentType = "image/gif"
                Case ".bmp"
                    contentType = "image/bmp"
            End Select

            File.Copy(openFileDialog1.FileName, filepath.fileSavePath, True)
            Dim constr As String = "Data Source=.\SQL2005;Database=dbFiles; uid=sa; pwd=pass"
            Dim conn As SqlConnection = New SqlConnection(constr)
            Dim strQuery As String = "insert into tblfiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)"
            Dim cmd As SqlCommand = New SqlCommand(strQuery, conn)
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filepath.filename
            cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar).Value = contentType
            cmd.Parameters.AddWithValue("@Data", bytes)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
        End If
    End Using
End Sub

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    Dim bytes As Byte()
    Dim fileName As String
    Dim constr As String = "Data Source=.\SQL2005;Database=dbFiles; uid=sa; pwd=pass"
    Dim conn As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand("SELECT TOP 1 Id, Name, ContentType, Data FROM tblfiles ORDER BY Id DESC", conn)
    conn.Open()
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        fileName = dr("Name").ToString()
        bytes = CType(dr("Data"), Byte())
        File.WriteAllBytes("C:\Users\anand\Desktop\" & fileName, bytes)
        conn.Close()
    Next
End Sub

Class filepath
    Public Shared fileSavePath As String
    Public Shared filename As String
End Class