Uploading Text file into mysql using Windows application c#

Question

I am trying to upload text file into mysql database using C# windows application (visual studio2019). But facing some issues while uploading file into database.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Diagnostics;
using System.IO;



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


        MySqlConnection connection;


        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            //To where your opendialog box get starting location. My initial directory location is desktop.
            openFileDialog1.InitialDirectory = "C://Desktop";
            //Your opendialog box title name.
            openFileDialog1.Title = "Select file to be upload.";
            //which type file format you want to upload in database. just add them.
            openFileDialog1.Filter = "Select Valid Document(*.txt;*.pdf; *.doc; *.xlsx; *.html)|*.txt;*.pdf; *.docx; *.xlsx; *.html";
            //FilterIndex property represents the index of the filter currently selected in the file dialog box.
            openFileDialog1.FilterIndex = 1;
            try
            {
                if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    if (openFileDialog1.CheckFileExists)
                    {
                        string path = System.IO.Path.GetFullPath(openFileDialog1.FileName);
                        label1.Text = path;
                    }
                }
                else
                {
                    MessageBox.Show("Please Upload document.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                string filename = System.IO.Path.GetFileName(openFileDialog1.FileName);
                if (filename == null)
                {
                    MessageBox.Show("Please select a valid document.");
                }
                else
                {
                    //we already define our connection globaly. We are just calling the object of connection.
                    MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=");
                    //MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM fileuploadwindows.fileupload", connection);
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand("insert into fileupload values('\\Document\\" + filename + "')", connection);
                    string path = Application.StartupPath.Substring(0, (Application.StartupPath.Length - 10));
                    System.IO.File.Copy(openFileDialog1.FileName, path + "\\Document\\" + filename);
                    cmd.ExecuteNonQuery();
                    connection.Open();
                    MessageBox.Show("Document uploaded.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}

I am not getting anything into database. Solve with the error please. Data is not coming into database. It is showing text file already exist or No database selected or column count doesnt match value count at row 1.

Help with the solution.

After saving into database i want to view its content from database in gridview.

Do you want it to be added to the table as a blob (Meaning the actual file saved unto a column of a table)? — Maico
– Maico, Commented Apr 12, 2020 at 12:57
Actually anything is fine but for now i tried to add in table in varbinary. Because i also need to retrieve it after from database. @Maico — sonali
– sonali, Commented Apr 12, 2020 at 13:00

Maico · Accepted Answer · 2020-04-12 20:25:33Z

Try this:

public void SaveToMysql() {

            byte[] rawData = File.ReadAllBytes("your file location");
            FileInfo info = new FileInfo("your file location");


            using (MySqlConnection connection = new MySqlConnection("server=localhost;uid=root;pwd=P@ssw0rd;database=yourDb;"))
            {
                using (MySqlCommand command = new MySqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "INSERT INTO file (fileName, fileBlob) VALUES (?fileName, ?fileName);";                    
                    MySqlParameter blobName = new MySqlParameter("?fileName", MySqlDbType.String);
                    MySqlParameter blobData  = new MySqlParameter("?rawData", MySqlDbType.Blob, rawData.Length);                    

                    blobData.Value = rawData;
                    blobName.Value = info.Name;

                    command.Parameters.Add(blobData);
                    command.Parameters.Add(blobName);

                    connection.Open();

                    command.ExecuteNonQuery();

                }
            }
        }

Solution 1: Adding by cell and row manually, but a lot of work and dirty too.

Solution 2: Encapsulate your Result into a strongly type object like:

public class FileTable {
    public string FileName {get;set;}
        ................ etc...
}

After retrieving it, put it in a collection. Assign a BindingSource object to your datagridview. Then on the BindingSoure.DataSource, assign the collection that you gathered that contains the data for your file information list.

Example:

List<FileTable> list = new List<FileTable>();
.....Assuming your list has been populated with the data from the mysql database table
BindingSource1.DataSource = list;

Learn introducing some OOP patterns so your coding goes much easier.

Maico · Accepted Answer · 2020-04-12 13:17:45Z

1

Try this:

public void SaveToMysql() {

            byte[] rawData = File.ReadAllBytes("your file location");
            FileInfo info = new FileInfo("your file location");


            using (MySqlConnection connection = new MySqlConnection("server=localhost;uid=root;pwd=P@ssw0rd;database=yourDb;"))
            {
                using (MySqlCommand command = new MySqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "INSERT INTO file (fileName, fileBlob) VALUES (?fileName, ?fileName);";                    
                    MySqlParameter blobName = new MySqlParameter("?fileName", MySqlDbType.String);
                    MySqlParameter blobData  = new MySqlParameter("?rawData", MySqlDbType.Blob, rawData.Length);                    

                    blobData.Value = rawData;
                    blobName.Value = info.Name;

                    command.Parameters.Add(blobData);
                    command.Parameters.Add(blobName);

                    connection.Open();

                    command.ExecuteNonQuery();

                }
            }
        }

answered Apr 12, 2020 at 13:17

Maico

1715 bronze badges

3 Comments

sonali Over a year ago

can i also retrive it from database and view it in gridview???

Maico Over a year ago

Yes you can, create a query then get the result and convert it back to a stream, the question is that, do you want to display it as a file on the datagridview column? or you want to show the content inside the datagridiew column?

sonali Over a year ago

I want to display it as a file on the datagridview... like " id1 test.txt " in datagrid. Thanks.

Collectives™ on Stack Overflow

Uploading Text file into mysql using Windows application c#

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related