serving the solutions day and night

Pages

Saturday, April 10, 2010

Storing Images, Documents, FingerPrints, Signatures in Mysql (medium blob data type) using C# .Net

Storing Images, Documents, Fingerprints, Signatures in Mysql (medium blob data type) using C# .Net.
Mysql table structure, C# code  and Mysql tips.

1) MySql Table Structure
DROP TABLE IF EXISTS `image_ot`.`entry_image`;
CREATE TABLE  `image_ot`.`entry_image` (
  `entry_id` bigint(20) NOT NULL DEFAULT '0',
  `binary_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `binary_data` mediumblob,
  `binary_length` bigint(20) DEFAULT NULL,
  `binary_name` varchar(255) DEFAULT NULL,
  `binary_type` enum('photo','fingerprint','signature','document') DEFAULT NULL,
  PRIMARY KEY (`entry_id`,`binary_id`),
  KEY `i_sk_entry_image_binary_type` (`binary_type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2)C# Code
using MySql.Data.MySqlClient;
private void InsertPhoto()
{
 string DB_Connection_OT = "server=localhost;database=image_ot;uid=u_id;password=p_w";
 string sImageFile = "c:\\photos\\IMG_00561.jpg";
 string sDocFile = "C:\\docs\\smartcard.docx";
 try
 {
  MySqlConnection conOT = new MySqlConnection(DB_Connection_OT);
  conOT.Open();
  MySqlCommand cmdOT = conOT.CreateCommand();
  FileStream fsImage = new FileStream(sImageFile, FileMode.Open, FileAccess.Read);
  byte[] byteImageContent = new byte[fsImage.Length];
  BinaryReader bireImage = new BinaryReader(fsImage);
  byteImageContent = bireImage.ReadBytes((int)fsImage.Length);
  fsImage.Close();

  string strSql_entry_image = "insert into " +
   "entry_image(entry_id, binary_data, binary_length, binary_name, binary_type) " +
   "values (@entry_id, @binary_data, @binary_length, @binary_name, @binary_type)";
  cmdOT.Parameters.Add(new MySqlParameter("@entry_id", D_entry_id));
  cmdOT.Parameters.Add(new MySqlParameter("@binary_data", (object)byteImageContent));
  cmdOT.Parameters.Add(new MySqlParameter("@binary_length", byteImageContent.Length));
  cmdOT.Parameters.Add(new MySqlParameter("@binary_name", "Jpeg"));
  cmdOT.Parameters.Add(new MySqlParameter("@binary_type", "photo"));
  cmdOT.CommandText = strSql_entry_image;
  cmdOT.ExecuteNonQuery();
 }
 catch (Exception e)
 {  //error   }
 }

Tips Mysql Set Password:
update user set password=PASSWORD("NEWPASSWORD") where User='mak';

1 comment:

James Tow said...

Thank you SO much for posting what you did. I was able to take what you did and create an SSIS package (C#) to take a file created in the package and upload it to MySQL. I couldn't have done it without your post and help. Thanks!