Хранение изображения в БД
Есть локальное приложение на WinForms с пользовательскими данными. Данные хранятся в MySql. Мне нужно хранить фотографии каждого сотрудника в базе и отображать с данными сотрудника на DataGridView.
Теперь возникает вопрос, где лучше хранить данные? Как лучше реализовать?
В MySql создаю колонку с BLOB. На это ругается datagrid. Как реализовать такого рода приложение?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Drawing.Imaging;
using System.IO;
namespace LOCAL_PROJECT
{
public partial class mary : Form
{
public mary()
{
InitializeComponent();
}
//CONNECTION STRING
DataTable dt = new DataTable("sta");
MySqlConnection con = new MySqlConnection("server = 192.168.1.199; UserId = admin; Password = 12345; database = telecom");
MySqlDataAdapter adapter = new MySqlDataAdapter();
int id;
MySqlCommand command;
public void Spisok()
{
con.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(" SELECT * FROM `sta` ", con);
DataSet dset = new DataSet();
adapter.Fill(dset);
dataGridView1.DataSource = dset.Tables[0];
con.Close();
}
private void Mary_Load(object sender, EventArgs e)
{
Spisok();
dataGridView1.EnableHeadersVisualStyles = false;
dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.Lime;
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("Times New Roman", 10, FontStyle.Bold);
dataGridView1.ColumnHeadersHeight = 35;
dataGridView1.DefaultCellStyle.Font = new Font("Microsoft Sans Serif", 10);
dataGridView1.Columns[0].Width = 40;
dataGridView1.Columns[1].Width = 95;
dataGridView1.Columns[2].Width = 120;
dataGridView1.Columns[3].Width = 70;
dataGridView1.Columns[4].Width = 70;
dataGridView1.Columns[5].Width = 70;
dataGridView1.Columns[6].Width = 66;
dataGridView1.Columns[7].Width = 73;
dataGridView1.Columns[8].Width = 70;
dataGridView1.Columns[9].Width = 50;
dataGridView1.Columns[10].Width = 50;
dataGridView1.Columns[11].Width = 50;
dataGridView1.Columns[12].Width = 50;
dataGridView1.Columns[13].Width = 50;
dataGridView1.Columns[14].Width = 50;
dataGridView1.Columns[15].Width = 58;
dataGridView1.Columns[16].Width = 58;
dataGridView1.Columns[17].Width = 58;
dataGridView1.Columns[18].Width = 58;
dataGridView1.Columns[19].Width = 68;
// dataGridView1.Columns[20].Width =75;
dataGridView1.Columns[0].HeaderCell.Value = "ID";
dataGridView1.Columns[1].HeaderCell.Value = "Name";
dataGridView1.Columns[2].HeaderCell.Value = "Type";
dataGridView1.Columns[3].HeaderCell.Value = "yaer";
dataGridView1.Columns[4].HeaderCell.Value = "prefix";
dataGridView1.Columns[5].HeaderCell.Value = "size";
dataGridView1.Columns[6].HeaderCell.Value = "free size";
dataGridView1.Columns[7].HeaderCell.Value = "Internet size";
dataGridView1.Columns[8].HeaderCell.Value = "freesizeint";
dataGridView1.Columns[9].HeaderCell.Value = "workintnumber";
dataGridView1.Columns[10].HeaderCell.Value = "abonplata";
dataGridView1.Columns[11].HeaderCell.Value = "dslam48";
dataGridView1.Columns[12].HeaderCell.Value = "dslam128";
dataGridView1.Columns[13].HeaderCell.Value = "dslam288";
dataGridView1.Columns[14].HeaderCell.Value = "dslam896";
dataGridView1.Columns[15].HeaderCell.Value = "kabel30";
dataGridView1.Columns[16].HeaderCell.Value = "kabel50";
dataGridView1.Columns[17].HeaderCell.Value = "kabel100";
dataGridView1.Columns[18].HeaderCell.Value = "kabel200";
dataGridView1.Columns[19].HeaderCell.Value = "responsible officer";
DataGridViewImageColumn imageColumn = new DataGridViewImageColumn();
imageColumn = (DataGridViewImageColumn)dataGridView1.Columns[20];
imageColumn.Name = "IMAGE";
imageColumn.ImageLayout = DataGridViewImageCellLayout.Stretch;
// SUM
textBoxSum.Text = (from DataGridViewRow row in dataGridView1.Rows where row.Cells[5].FormattedValue.ToString() != string.Empty select Convert.ToInt32(row.Cells[5].FormattedValue)).Sum().ToString();
textBoxSumbosgowrum.Text = (from DataGridViewRow row in dataGridView1.Rows where row.Cells[6].FormattedValue.ToString() != string.Empty select Convert.ToInt32(row.Cells[6].FormattedValue)).Sum().ToString();
textBoxSumInternet.Text = (from DataGridViewRow row in dataGridView1.Rows where row.Cells[7].FormattedValue.ToString() != string.Empty select Convert.ToInt32(row.Cells[7].FormattedValue)).Sum().ToString();
textBoxSumBosInternet.Text = (from DataGridViewRow row in dataGridView1.Rows where row.Cells[8].FormattedValue.ToString() != string.Empty select Convert.ToInt32(row.Cells[8].FormattedValue)).Sum().ToString();
}
private void ExecuteQuery(string txtQuery)
{
Spisok();
con.Open();
command = con.CreateCommand();
command.CommandText = txtQuery;
command.ExecuteNonQuery();
con.Close();
}
private void Button4_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void Button1_Click(object sender, EventArgs e)
{
this.Hide();
menu menuu = new menu();
menuu.Show();
}
// Метод автонумерация ID
private DataTable AutoNumberedTable(DataTable SourceTable)
{
DataTable ResultTable = new DataTable();
DataColumn AutoNumberColumn = new DataColumn
{
ColumnName = "id",
DataType = typeof(int),
AutoIncrement = true,
AutoIncrementSeed = 1,
AutoIncrementStep = 1
};
ResultTable.Columns.Add(AutoNumberColumn);
ResultTable.Merge(SourceTable);
return ResultTable;
}
private void Button5_Click(object sender, EventArgs e)
{
con.Open();
string sql = "insert into sta (id, name, satstype, year, prefix, size, freesize, internet_size, freesizeint, workintnumber, abonplata, dslam48, dslam128, dslam288, dslam896, kabel30, kabel50, kabel100, kabel200, montyor, image) Values (null, '" + textBox1.Text + "', '" + comboBox1.Text + "', '" + textBox2.Text + "', " +
"'" + textBox3.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + textBox8.Text + "', '" + comboBox2.Text + "', '" + textBox9.Text + "', '" + textBox10.Text + "', '" + textBox11.Text + "', '" + textBox12.Text + "', '" + textBox13.Text + "', '" + textBox14.Text + "', '" + textBox15.Text + "', '" + textBox16.Text + "', '" + textBox17.Text + "', '" + Path.GetFileName(pictureBox2.ImageLocation) + "')";
this.dataGridView1.DataSource = AutoNumberedTable(dt);
MySqlCommand cmd = new MySqlCommand(sql, con);
cmd.ExecuteNonQuery();
con.Close();
File.Copy(textBox18.Text, Application.StartupPath + @"\Image\" + Path.GetFileName(pictureBox2.ImageLocation));
MessageBox.Show("Added!");
textBox1.Text = string.Empty;
comboBox1.Text = string.Empty;
textBox2.Text = string.Empty;
textBox3.Text = string.Empty;
textBox4.Text = string.Empty;
textBox5.Text = string.Empty;
textBox6.Text = string.Empty;
textBox7.Text = string.Empty;
textBox9.Text = string.Empty;
textBox10.Text = string.Empty;
textBox11.Text = string.Empty;
textBox12.Text = string.Empty;
textBox13.Text = string.Empty;
textBox14.Text = string.Empty;
textBox15.Text = string.Empty;
textBox8.Text = string.Empty;
comboBox2.Text = string.Empty;
textBox17.Text = string.Empty;
Spisok(); // метод коннектит базой данных
}
// Метод делете
private void Delete(int id)
{
string sql = "DELETE FROM sta WHERE tb=" + id + "";
command = new MySqlCommand(sql, con);
//OPEN CON, EXECUTE DELETE, CLOSE CON
try
{
con.Open();
adapter = new MySqlDataAdapter(command)
{
DeleteCommand = con.CreateCommand()
};
adapter.DeleteCommand.CommandText = sql;
// PROMT FOR CONFIRMATION
if (command.ExecuteNonQuery() > 0)
{
MessageBox.Show("Deleted!");
}
con.Close();
Spisok();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//Delete button
private void Button3_Click(object sender, EventArgs e)
{
string selected = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
int tb = Convert.ToInt32(selected);
Delete(id);
}
private void DataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
try
{
id = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString());
id = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString());
// Byte[] img = (Byte[])dataGridView1.CurrentRow.Cells[20].Value;
// MemoryStream ms = new MemoryStream(img);
// pictureBox2.Image = Image.FromStream(ms);
textBox1.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();
comboBox1.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();
textBox2.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
textBox3.Text = dataGridView1.CurrentRow.Cells[4].Value.ToString();
textBox4.Text = dataGridView1.CurrentRow.Cells[5].Value.ToString();
textBox5.Text = dataGridView1.CurrentRow.Cells[6].Value.ToString();
textBox6.Text = dataGridView1.CurrentRow.Cells[7].Value.ToString();
textBox7.Text = dataGridView1.CurrentRow.Cells[8].Value.ToString();
textBox8.Text = dataGridView1.CurrentRow.Cells[9].Value.ToString();
textBox9.Text = dataGridView1.CurrentRow.Cells[11].Value.ToString();
textBox10.Text = dataGridView1.CurrentRow.Cells[12].Value.ToString();
textBox11.Text = dataGridView1.CurrentRow.Cells[13].Value.ToString();
textBox12.Text = dataGridView1.CurrentRow.Cells[14].Value.ToString();
textBox13.Text = dataGridView1.CurrentRow.Cells[15].Value.ToString();
textBox14.Text = dataGridView1.CurrentRow.Cells[16].Value.ToString();
textBox15.Text = dataGridView1.CurrentRow.Cells[17].Value.ToString();
textBox16.Text = dataGridView1.CurrentRow.Cells[18].Value.ToString();
comboBox2.Text = dataGridView1.CurrentRow.Cells[10].Value.ToString();
textBox17.Text = dataGridView1.CurrentRow.Cells[19].Value.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
con.Close();
}
}
private void TextBox13_TextChanged(object sender, EventArgs e)
{
con.Open();
string query = "Select * From sta where name like '%" + textBox21.Text + "%' OR satstype like '%" + textBox21.Text + "%' OR prefix like '%" + textBox21.Text + "%' OR size like '%" + textBox21.Text + "%' OR freesize like '%" + textBox21.Text + "%' OR internet_size like '%" + textBox21.Text + "%' OR freesizeint like '%" + textBox21.Text + "%' OR potsplata like '%" + textBox21.Text + "%' OR dslam like '%" + textBox21.Text + "%' OR year like '%" + textBox21.Text + "%' OR geolocationC like '%" + textBox21.Text + "%' OR geolocationC like '%" + textBox21.Text + "%' OR geolocationB like '%" + textBox21.Text + "%'";
DataSet dset = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter(query, con);
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
adapter.Fill(dset);
dataGridView1.DataSource = dset.Tables[0];
con.Close();
}
private void Button6_Click(object sender, EventArgs e)
{
try
{
con.Open();
command = new MySqlCommand("update sta SET name='" + textBox1.Text + "', satstype='" + comboBox1.Text + "', year='" + textBox2.Text + "', prefix='" + textBox3.Text + "', size='" + textBox4.Text + "', freesize='" + textBox5.Text + "', internet_size='" + textBox6.Text + "', freesizeint='" + textBox7.Text + "', WHERE tb='" + tb + "'", con);
command.ExecuteNonQuery();
MessageBox.Show("Obnowit edildi!");
con.Close();
Spisok();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
Openpage openpage = new Openpage();
openpage.textBox1.Text = this.dataGridView1.CurrentRow.Cells[7].Value.ToString();
openpage.textBox2.Text = this.dataGridView1.CurrentRow.Cells[8].Value.ToString();
openpage.textBox3.Text = this.dataGridView1.CurrentRow.Cells[9].Value.ToString();
openpage.textBox8.Text = this.dataGridView1.CurrentRow.Cells[11].Value.ToString();
openpage.textBox7.Text = this.dataGridView1.CurrentRow.Cells[12].Value.ToString();
openpage.textBox6.Text = this.dataGridView1.CurrentRow.Cells[13].Value.ToString();
openpage.textBox5.Text = this.dataGridView1.CurrentRow.Cells[14].Value.ToString();
openpage.textBox12.Text = this.dataGridView1.CurrentRow.Cells[15].Value.ToString();
openpage.textBox11.Text = this.dataGridView1.CurrentRow.Cells[16].Value.ToString();
openpage.textBox10.Text = this.dataGridView1.CurrentRow.Cells[17].Value.ToString();
openpage.textBox9.Text = this.dataGridView1.CurrentRow.Cells[18].Value.ToString();
openpage.ShowDialog();
}
private void DataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void pictureBox2_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Choose Image(*.JPG; *.PNG; *.GIF) | *.jpg; *.png; *.gif";
if (dialog.ShowDialog() == DialogResult.OK)
{
pictureBox2.Image = new Bitmap(dialog.FileName);
pictureBox2.ImageLocation = dialog.FileName;
pictureBox2.SizeMode = PictureBoxSizeMode.StretchImage;
}
}
private void button2_Click(object sender, EventArgs e)
{
}
}
}
