C# - Insert, Update, Delete, Search Image In MySQL Database

How To Insert Update Delete Search Display Images In MySQL Database Using C#

c# and mysql add, edit, remove, find, show images


In This C# Tutorial We Will See How To :

- Add Image Into MySQL Database
- Edit Selected Image From MySQL Database
- Delete Selected Image From MySQL Database
- Search Image By ID From MySQL Database
- Show Image From MySQL Database Into Datagridview

Using CSharp Programming Language.


                     =>   Part 2

                      =>  Part 3

                      =>  Part 4

                       => Part 5

                       => Part 6

                       => Part 7

                       => Part 8


Project Source Code:

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 MySql.Data.MySqlClient;
using System.IO;
using System.Drawing.Imaging;

namespace Csharp_And_MySQL
{
    public partial class Display_Insert_Update_Delete_Search_Image_In_MySQL_Database : Form
    {
        public Display_Insert_Update_Delete_Search_Image_In_MySQL_Database()
        {
            InitializeComponent();
        }
        MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;Initial Catalog='db_images';username=root;password=");
       
        private void Display_Insert_Update_Delete_Search_Image_In_MySQL_Database_Load(object sender, EventArgs e)
        {
            FillDGV("");
        }

        public void FillDGV( string valueToSearch)
        {

            MySqlCommand command = new MySqlCommand("SELECT * FROM myimages WHERE CONCAT(ID, Name, Description) LIKE '%" + valueToSearch + "%'", connection);

            MySqlDataAdapter adapter = new MySqlDataAdapter(command);

            DataTable table = new DataTable();

            adapter.Fill(table);

            dataGridView1.RowTemplate.Height = 60;

            dataGridView1.AllowUserToAddRows = false;

            dataGridView1.DataSource = table;

            DataGridViewImageColumn imgCol = new DataGridViewImageColumn();
            imgCol = (DataGridViewImageColumn)dataGridView1.Columns[3];
            imgCol.ImageLayout = DataGridViewImageCellLayout.Stretch;

            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;

        }

        private void BTN_CHOOSE_IMAGE_Click(object sender, EventArgs e)
        {

            OpenFileDialog opf = new OpenFileDialog();

            opf.Filter = "Choose Image(*.JPG;*.PNG;*.GIF)|*.jpg;*.png;*.gif";

            if(opf.ShowDialog() == DialogResult.OK)
            {
                pictureBox1.Image = Image.FromFile(opf.FileName);
            }

        }

        private void dataGridView1_Click(object sender, EventArgs e)
        {

            Byte[] img = (Byte[])dataGridView1.CurrentRow.Cells[3].Value;

            MemoryStream ms = new MemoryStream(img);

            pictureBox1.Image = Image.FromStream(ms);

            textBoxID.Text   = dataGridView1.CurrentRow.Cells[0].Value.ToString();
            textBoxName.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();
            textBoxDesc.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();

        }

        private void BTN_INSERT_Click(object sender, EventArgs e)
        {

            MemoryStream ms = new MemoryStream();
            pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat);
            byte[] img = ms.ToArray();

            MySqlCommand command = new MySqlCommand("INSERT INTO myimages(ID, Name, Description, Image) VALUES (@id,@name,@desc,@img)", connection);

            command.Parameters.Add("@id", MySqlDbType.VarChar).Value = textBoxID.Text;
            command.Parameters.Add("@name", MySqlDbType.VarChar).Value = textBoxName.Text;
            command.Parameters.Add("@desc", MySqlDbType.VarChar).Value = textBoxDesc.Text;
            command.Parameters.Add("@img", MySqlDbType.Blob).Value = img;

            ExecMyQuery(command, "Data Inserted");

        }

        public void ExecMyQuery(MySqlCommand mcomd, string myMsg)
        {
            connection.Open();
            if(mcomd.ExecuteNonQuery() == 1)
            {

                MessageBox.Show(myMsg);

            }else{

                MessageBox.Show("Query Not Executed");

            }

            connection.Close();

            FillDGV("");
        }

        private void BTN_UPDATE_Click(object sender, EventArgs e)
        {

            MemoryStream ms = new MemoryStream();
            pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat);
            byte[] img = ms.ToArray();

            MySqlCommand command = new MySqlCommand("UPDATE myimages SET Name=@name, Description=@desc, Image=@img WHERE ID = @id", connection);

            command.Parameters.Add("@id", MySqlDbType.VarChar).Value = textBoxID.Text;
            command.Parameters.Add("@name", MySqlDbType.VarChar).Value = textBoxName.Text;
            command.Parameters.Add("@desc", MySqlDbType.VarChar).Value = textBoxDesc.Text;
            command.Parameters.Add("@img", MySqlDbType.Blob).Value = img;

            ExecMyQuery(command, "Data Updated");

        }

        private void BTN_DELETE_Click(object sender, EventArgs e)
        {

            MySqlCommand command = new MySqlCommand("DELETE FROM myimages WHERE ID = @id", connection);

            command.Parameters.Add("@id", MySqlDbType.VarChar).Value = textBoxID.Text;

            ExecMyQuery(command, "Data Deleted");

            ClearFields();

        }

        private void textBoxSearch_TextChanged(object sender, EventArgs e)
        {
            FillDGV(textBoxSearch.Text);
        }

        private void BTN_FIND_Click(object sender, EventArgs e)
        {
            MySqlCommand command = new MySqlCommand("SELECT * FROM myimages WHERE ID = @id", connection);
            command.Parameters.Add("@id", MySqlDbType.VarChar).Value = textBoxID.Text;

            MySqlDataAdapter adapter = new MySqlDataAdapter(command);

            DataTable table = new DataTable();

            adapter.Fill(table);

            if(table.Rows.Count <= 0)
            {
                MessageBox.Show("No Data Found");
                ClearFields();
            }
            else
            {

                textBoxID.Text = table.Rows[0][0].ToString();
                textBoxName.Text = table.Rows[0][1].ToString();
                textBoxDesc.Text = table.Rows[0][2].ToString();

                byte[] img = (byte[])table.Rows[0][3];
                MemoryStream ms = new MemoryStream(img);
                pictureBox1.Image = Image.FromStream(ms);

            }
        }

        private void BTN_NEW_Click(object sender, EventArgs e)
        {
            ClearFields();
        }

        public void ClearFields()
        {
            textBoxID.Text = "";
            textBoxName.Text = "";
            textBoxDesc.Text = "";

            pictureBox1.Image = null;

        }

    }
}


///////////////OUTPUT:


show, find, add, edit, remove, images from mysql database using c#






Share this

Related Posts

Previous
Next Post »

3 comments

comments
1 mai 2017 à 06:59 delete

search box doesn't work. When i search nothing show, everything gone.

Reply
avatar