Retrieve Save file from Database in folder path using FolderBrowser Dialog in Windows Application using C# and VB.Net

Last Reply 2 months ago By pandeyism

Posted 2 months ago

Hi! I used below code in local computer its worked. When I copy exe to another computer its not extract file in local disk. How I can solve this? 

namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public static void InsertFileintoSqlDatabase()
        {
            string filePath = @"D:\Description.docx";

            using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=10.101.8.32;Database=dbFiles; uid=sa; pwd=d-123"))
            {
                sqlconnection.Open();

                // create table if not exists 
              /*  string createTableQuery = @"Create Table [MyTable](ID int, [FileData] varbinary(max))";
                SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
                command.ExecuteNonQuery();*/

                // Converts text file(.txt) into byte[]
                byte[] fileData = File.ReadAllBytes(filePath);

                string insertQuery = @"Insert Into [MyTable] (ID,[FileData]) Values(1,@FileData)";

                // Insert text file Value into Sql Table by SqlParameter
                SqlCommand insertCommand = new SqlCommand(insertQuery, sqlconnection);
                SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@FileData", fileData);
                sqlParam.DbType = DbType.Binary;
                insertCommand.ExecuteNonQuery();
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            InsertFileintoSqlDatabase();
        }        

        public static void ExportFileFromSqlDatabase(int ID, string FilePath)
        {
            using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=localhost;Database=dbFiles; uid=sa; pwd=d-123"))
            {
                sqlconnection.Open();

                string selectQuery = string.Format(@"Select [FileData] From [MyTable] Where ID={0}"
                                    , ID);

                // Read File content from Sql Table 
                SqlCommand selectCommand = new SqlCommand(selectQuery, sqlconnection);
                SqlDataReader reader = selectCommand.ExecuteReader();
                if (reader.Read())
                {
                    byte[] fileData = (byte[])reader[0];
                    // Write/Export File content into new text file
                    File.WriteAllBytes(FilePath, fileData);
                }
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            string FilePath = @"E:\documment.docx";
            ExportFileFromSqlDatabase(1, FilePath);
        }
    }
}

 

Posted 2 months ago

Hey RPA,

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"];
        FolderBrowserDialog folderDlg = new FolderBrowserDialog();
        folderDlg.ShowNewFolderButton = true;
        DialogResult result = folderDlg.ShowDialog();
        if (result == DialogResult.OK)
        {
            File.WriteAllBytes(folderDlg.SelectedPath + "\\" + 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())
        Dim folderDlg = New FolderBrowserDialog()
        folderDlg.ShowNewFolderButton = True
        Dim result = folderDlg.ShowDialog()
        If result = DialogResult.OK Then
            File.WriteAllBytes(folderDlg.SelectedPath + "\\" + fileName, bytes)
            conn.Close()
        End If
    Next
End Sub

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