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;
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 }
}
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:
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!
Post a Comment