WeB LoG'S JuUiER: Coding C# with ADO.NET and image type

Friday, August 7, 2009

Coding C# with ADO.NET and image type

//Refer from window application code

table name 'tb1'


......... Create connection to database ..........

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=localhost;"
+ "Initial Catalog=sample;"
+ "Persist Security Info=True;"
+ "User ID=usersql;"
+ "Password=passsql;";


......... Insert statement image file to database ..........


SqlTransaction ts = conn.BeginTransaction();

int result = -1;

try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = ts;
cmd.CommandType = CommandType.Text;

cmd.CommandText = "insert into tb1(c1, c8) values(@c1, @c8)";

cmd.Parameters.Add("@c1", SqlDbType.BigInt).Value = "1";


string pathImage = "D:\photo.jpg"

Image image = Image.FromFile(pathImage);
Image newImage = resizeImage(image, 100, 100);

string newPath = @"temp_photo.png";
newImage.Save(newPath, System.Drawing.Imaging.ImageFormat.Png);

// ถ้าเราใช้ vista หรือ windows 7 เราต้องเปิด solution นี้แบบ Rus as administrator
    // จริงจะไม่เกิด exception แบบนี้
    // สรุป คือ insufficient permission ว่างั้นสำหรับเขียนไฟล์อ่ะนะ


FileStream fs = new FileStream(newImage, FileMode.Open, FileAccess.Read);

BinaryReader br = new BinaryReader(fs);
byte[] photo = br.ReadBytes((int)fs.Length);
br.Close();
fs.Close();

//cmd.Parameters.Add("@c8", SqlDbType.Image).Value = photo;
cmd.Parameters.AddWithValue("@c8", photo);
//cmd.Parameters.Add("@c8", SqlDbType.Image, photo.Length).Value = photo;

result = cmd.ExecuteNonQuery();

ts.Commit();

System.IO.File.Delete(newPath);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace.ToString());
ts.Rollback();
}
finally
{
conn.Close();
}

......... Select statement image file from database ..........

//// if don't want generate new file by datetime use follow this code
//// and set FileStream FileMode.Create not use CreateNew

/*
string strfn = @"temp_picture";
if (pictureBox1.Image != null)
{
 pictureBox1.Image.Dispose();
 if (File.Exists(strfn))
     File.Delete(strfn);
}
*/

SqlCommand cmdSelect=new SqlCommand("select c8" +
" from tb1 where c1 = @c1", conn);
cmdSelect.Parameters.Add("@c1",SqlDbType.Int,4);
cmdSelect.Parameters["@c1"].Value = numericUpDown2.Value.ToString();

if (conn.State != ConnectionState.Open)
conn.Open();

try
{
/*
object obj = cmdSelect.ExecuteScalar();
if (obj == null) return;
*/

byte[] barrImg = (byte[])cmdSelect.ExecuteScalar();
string strfn = Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs = new FileStream(strfn,
                FileMode.CreateNew, FileAccess.Write);

//// if file exist it will be overwrite by FileMode.Create
/*
FileStream fs = new FileStream(strfn,
FileMode.Create, FileAccess.Write); fs.Write(barrImg, 0, barrImg.Length);
*/

fs.Flush();
fs.Close();
pictureBox1.Image = Image.FromFile(strfn);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace.ToString());
}
finally
{
conn.Close();
}

......... Select statement * from database to datagrid view ..........

SqlDataAdapter dp = new SqlDataAdapter("select c1, c8 from tb1", conn);
DataTable dt = new DataTable();
dt.Clear();
dp.Fill(dt);
dgvResult.DataSource = dt;


......... Init value form select statement * from database ..........

SqlDataAdapter dp = new SqlDataAdapter("select max(c1) from tb1", conn);

//DataSet ds = new DataSet();
//DataTable dt = new DataTable();
//ds.Clear();
//dp.Fill(ds);
//dt = ds.Tables[0];

DataTable dt = new DataTable();
dt.Clear();
dp.Fill(dt);

int counRow = dt.Rows.Count;

if (counRow > 0)
{
//DataRow dr = dt.Rows[0];
//numericUpDown1.Value = decimal.Parse(dr[0]);

string max = dt.Rows[0][0].ToString();
max = max == "" ? "0" : max;
progressBar1.Value = int.Parse(max);
numericUpDown1.Value = decimal.Parse(max) + 1;
}
else
{
numericUpDown1.Value = decimal.Parse("1");
}

....... Resize image file .......

public Image resizeImage( Image img, int width, int height )
{
Bitmap b = new Bitmap( width, height ) ;
Graphics g = Graphics.FromImage( (Image ) b ) ;

g.DrawImage( img, 0, 0, width, height ) ;
g.Dispose() ;

return (Image ) b ;
}