Windows Form กับ Upload File ลงใน Database และแสดงผลรายการไฟล์บน Table (VB.Net,C#)

CREATE TABLE [dbo].[mytable](
	[FileID] [int] IDENTITY(1,1) NOT NULL,
	[FileName] [varchar](255) NULL,
	[FilePath] [varchar](255) NULL,
	[FileSize] [int] NULL,
 CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED 
(
	[FileID] ASC
) ) ON [PRIMARY]

 Private strConnString As String = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase"
    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ShowData()
    End Sub

    Private Sub ShowData()
        '*** Connect Database
        Dim objConn = New SqlConnection(strConnString)
        objConn.Open()

        Dim strSQL As String = "SELECT * FROM mytable"
        Dim objCmd = New SqlCommand(strSQL, objConn)
        Dim dtReader = objCmd.ExecuteReader()

        '*** ListView Header
        Me.myListView.Clear()
        myListView.Columns.Clear()
        myListView.HideSelection = False
        myListView.Columns.Add("File ID", 50, HorizontalAlignment.Center)
        myListView.Columns.Add("File Name", 140, HorizontalAlignment.Left)
        myListView.Columns.Add("File Path", 140, HorizontalAlignment.Left)
        myListView.Columns.Add("File Size (KB)", 90, HorizontalAlignment.Right)
        myListView.FullRowSelect = True
        myListView.View = View.Details

        '*** ListView Row
        While dtReader.Read()
            Dim lvi = New ListViewItem(dtReader("FileID").ToString())
            lvi.SubItems.Add(dtReader("FileName").ToString())
            lvi.SubItems.Add(dtReader("FilePath").ToString())
            lvi.SubItems.Add(dtReader("FileSize").ToString())
            Me.myListView.Items.Add(lvi)
        End While

        '*** Close Database
        dtReader.Close()
        dtReader = Nothing
        objConn.Close()

        '*** Disabled Open/Delete
        Me.btnOpen.Enabled = False
        Me.btnDelete.Enabled = False
    End Sub

   Private Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
        Dim strFolder As String = "/myFile/"
        Dim strFullPath As String = Application.StartupPath + strFolder

        Dim dlg As New OpenFileDialog()
        If dlg.ShowDialog() = DialogResult.OK Then
            '*** Create Folder
            If Not Directory.Exists(strFullPath) Then
                Directory.CreateDirectory(strFullPath)
            End If

            '*** Save File
            Dim filePath As String = dlg.FileName
            Dim fileName As String = Path.GetFileName(filePath)
            File.Copy(filePath, strFullPath & fileName, True)

            '*** Save to Database
            Dim objConn = New SqlConnection(strConnString)
            objConn.Open()

            Dim strSQL As String = "INSERT INTO mytable (FileName,FilePath,FileSize) " &
                " VALUES (@FileName,@FilePath,@FileSize)"

            Dim objCmd = New SqlCommand(strSQL, objConn)
            objCmd.Parameters.AddWithValue("@FileName", fileName)
            objCmd.Parameters.AddWithValue("@FilePath", strFolder & fileName)
            objCmd.Parameters.AddWithValue("@FileSize", (New FileInfo(filePath).Length) / 1024)
            objCmd.ExecuteNonQuery()

            objConn.Close()
            ShowData()
        End If
    End Sub

    Private Sub myListView_SelectedIndexChanged(sender As Object, e As EventArgs) Handles myListView.SelectedIndexChanged
        If Me.myListView.SelectedItems.Count > 0 Then
            '*** Enabled Open/Delete
            Me.btnOpen.Enabled = True
            Me.btnDelete.Enabled = True
        End If
    End Sub

        private string strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase";
        private void frmMain_Load(object sender, EventArgs e)
        {
            ShowData();
        }

        private void ShowData()
        {
            //*** Connect Database
            var objConn = new SqlConnection(strConnString);
            objConn.Open();

            string strSQL = "SELECT * FROM mytable";
            var objCmd = new SqlCommand(strSQL, objConn);
            var dtReader = objCmd.ExecuteReader();

            //*** ListView Header
            this.myListView.Clear();
            myListView.Columns.Clear();
            myListView.HideSelection = false;
            myListView.Columns.Add("File ID", 50, HorizontalAlignment.Center);
            myListView.Columns.Add("File Name", 140, HorizontalAlignment.Left);
            myListView.Columns.Add("File Path", 140, HorizontalAlignment.Left);
            myListView.Columns.Add("File Size (KB)", 90, HorizontalAlignment.Right);
            myListView.FullRowSelect = true;
            myListView.View = View.Details;

            //*** ListView Row
            while (dtReader.Read())
            {
                var lvi = new ListViewItem(dtReader["FileID"].ToString());
                lvi.SubItems.Add(dtReader["FileName"].ToString());
                lvi.SubItems.Add(dtReader["FilePath"].ToString());
                lvi.SubItems.Add(dtReader["FileSize"].ToString());
                this.myListView.Items.Add(lvi);
            }

            //*** Close Database
            dtReader.Close();
            dtReader = null;
            objConn.Close();

            //*** Disabled Open/Delete
            this.btnOpen.Enabled = false;
            this.btnDelete.Enabled = false;
        }

        private void btnUpload_Click(object sender, EventArgs e)
        {
            string strFolder = "/myFile/";
            string strFullPath = Application.StartupPath + strFolder;

            OpenFileDialog dlg = new OpenFileDialog();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                //*** Create Folder
                if (!Directory.Exists(strFullPath))
                {
                    Directory.CreateDirectory(strFullPath);
                }

                //*** Save File
                string filePath = dlg.FileName;
                string fileName = Path.GetFileName(filePath);
                File.Copy(filePath, strFullPath + fileName, true);

                //*** Save to Database
                var objConn = new SqlConnection(strConnString);
                objConn.Open();

                string strSQL = "INSERT INTO mytable (FileName,FilePath,FileSize) "
                    + " VALUES  (@FileName,@FilePath,@FileSize)";

                var objCmd = new SqlCommand(strSQL, objConn);
                objCmd.Parameters.AddWithValue("@FileName", fileName);
                objCmd.Parameters.AddWithValue("@FilePath", strFolder + fileName);
                objCmd.Parameters.AddWithValue("@FileSize", (new FileInfo(filePath).Length) / 1024);
                objCmd.ExecuteNonQuery();

                objConn.Close();
                ShowData();
            }
        }

        private void myListView_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.myListView.SelectedItems.Count > 0)
            {
                //*** Enabled Open/Delete
                this.btnOpen.Enabled = true;
                this.btnDelete.Enabled = true;
            }
        }

    Private Sub btnOpen_Click(sender As Object, e As EventArgs) Handles btnOpen.Click
        If Me.myListView.SelectedItems.Count > 0 Then
            Dim selPath As String = Me.myListView.SelectedItems(0).SubItems(2).Text
            ' File Path
            System.Diagnostics.Process.Start(Application.StartupPath + selPath)
        End If
    End Sub

        private void btnOpen_Click(object sender, EventArgs e)
        {
            if (this.myListView.SelectedItems.Count > 0)
            {
                string selPath = this.myListView.SelectedItems[0].SubItems[2].Text; // File Path
                System.Diagnostics.Process.Start(Application.StartupPath + selPath);
            }
        }

Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        If Me.myListView.SelectedItems.Count > 0 Then
            Dim selID As String = Me.myListView.SelectedItems(0).Text
            ' File ID
            Dim selPath As String = Me.myListView.SelectedItems(0).SubItems(2).Text
            ' File Path
            If MessageBox.Show("Delete ?", "Confirm", MessageBoxButtons.YesNoCancel, 
                MessageBoxIcon.Information) = DialogResult.Yes Then
                '*** Delete from Database
                Dim objConn = New SqlConnection(strConnString)
                objConn.Open()

                ' Delete in table
                Dim strSQL As String = "DELETE mytable WHERE FileID = @FileID"
                Dim objCmd = New SqlCommand(strSQL, objConn)
                objCmd.Parameters.AddWithValue("@FileID", selID)
                objCmd.ExecuteNonQuery()

                ' Delete in folder
                If File.Exists(Application.StartupPath + selPath) Then
                    File.Delete(Application.StartupPath + selPath)
                End If

                objConn.Close()
                ShowData()
            End If
        End If
End Sub

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (this.myListView.SelectedItems.Count > 0)
            {
                string selID = this.myListView.SelectedItems[0].Text; // File ID
                string selPath = this.myListView.SelectedItems[0].SubItems[2].Text; // File Path
                if (MessageBox.Show("Delete ?", "Confirm", MessageBoxButtons.YesNoCancel,
                    MessageBoxIcon.Information) == DialogResult.Yes)
                {
                    //*** Delete from Database
                    var objConn = new SqlConnection(strConnString);
                    objConn.Open();

                    // Delete in table
                    string strSQL = "DELETE mytable WHERE FileID = @FileID";
                    var objCmd = new SqlCommand(strSQL, objConn);
                    objCmd.Parameters.AddWithValue("@FileID", @selID);
                    objCmd.ExecuteNonQuery();

                    // Delete in folder
                    if (File.Exists(Application.StartupPath + selPath))
                    {
                        File.Delete(Application.StartupPath + selPath);
                    }

                    objConn.Close();
                    ShowData();
                }
            }
        }

    Private Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
        Dim strFolder As String = "/myFile/"
        Dim strFullPath As String = Application.StartupPath + strFolder

        Dim dlg As New OpenFileDialog()
        dlg.Multiselect = True
        If dlg.ShowDialog() = DialogResult.OK Then
            '*** Create Folder
            If Not Directory.Exists(strFullPath) Then
                Directory.CreateDirectory(strFullPath)
            End If

            '*** Save to Database
            Dim objConn = New SqlConnection(strConnString)
            objConn.Open()

            '*** Save File
            For Each filePath As String In dlg.FileNames
                Dim fileName As String = Path.GetFileName(filePath)
                File.Copy(filePath, strFullPath & fileName, True)

                Dim strSQL As String = "INSERT INTO mytable (FileName,FilePath,FileSize) " &
                " VALUES (@FileName,@FilePath,@FileSize)"

                Dim objCmd = New SqlCommand(strSQL, objConn)
                objCmd.Parameters.AddWithValue("@FileName", fileName)
                objCmd.Parameters.AddWithValue("@FilePath", strFolder & fileName)
                objCmd.Parameters.AddWithValue("@FileSize", (New FileInfo(filePath).Length) / 1024)
                objCmd.ExecuteNonQuery()
            Next

            objConn.Close()
            ShowData()
        End If
    End Sub

        private void btnUpload_Click(object sender, EventArgs e)
        {
            string strFolder = "/myFile/";
            string strFullPath = Application.StartupPath + strFolder;

            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Multiselect = true;
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                //*** Create Folder
                if (!Directory.Exists(strFullPath))
                {
                    Directory.CreateDirectory(strFullPath);
                }

                //*** Save to Database
                var objConn = new SqlConnection(strConnString);
                objConn.Open();

                //*** Save File
                foreach (string filePath in dlg.FileNames)
                {
                    string fileName = Path.GetFileName(filePath);
                    File.Copy(filePath, strFullPath + fileName, true);

                    string strSQL = "INSERT INTO mytable (FileName,FilePath,FileSize) "
                               + " VALUES (@FileName,@FilePath,@FileSize)";

                    var objCmd = new SqlCommand(strSQL, objConn);
                    objCmd.Parameters.AddWithValue("@FileName", fileName);
                    objCmd.Parameters.AddWithValue("@FilePath", strFolder + fileName);
                    objCmd.Parameters.AddWithValue("@FileSize", (new FileInfo(filePath).Length) / 1024);
                    objCmd.ExecuteNonQuery();
                }

                objConn.Close();
                ShowData();
            }
        }