Thursday, March 22, 2007

Ok, yesterday,  I almost (thank you Bryan) put up a set of definitive code to retrieve images from a SQL Compact database.   Note I edited the code to incorporate comments received,

So today I'll put up the flip side to insert/update records into a SQL Compact database.   This works great for applications like signature recording, biometrics or photo capture:

 

StringBuilder sbsql = new StringBuilder();

sbsql.Append("INSERT INTO ");
sbsql.Append("[OUR TABLE] ");
sbsql.Append("(");
sbsql.Append("[label pic] "); // image field in our database
sbsql.Append(") ");
sbsql.Append("VALUES ");
sbsql.Append("(");
sbsql.Append("@label_pic "); // Maps To Database Field - [label pic]
sbsql.Append(") ");

 

cn.Open();//  note cn - is a sqlceconnection object

SqlCeCommand cmd = new SqlCeCommand(sbsql.ToString(), cn);

cmd.CommandType = CommandType.Text;

SqlCeParameter tempparam = new SqlCeParameter(); tempparam.ParameterName = "@label_pic"
tempparam.SqlDbType = System.Data.SqlDbType.Image;
if (label_pic == null)
{
tempparam.Value = DBNull.Value;
}
else
{
MemoryStream mslabel_pic = new System.IO.MemoryStream();
label_pic.Save(mslabel_pic, System.Drawing.Imaging.ImageFormat.Bmp); // label pic is the Bitmap variable that holds our picture
Byte[] bitslabel_pic = mslabel_pic.ToArray();
mslabel_pic.Close();
tempparam.Value = bitslabel_pic;
}

 

cmd.Parameters.Add(tempparam);

cmd.ExecuteNonQuery();

cmd.Dispose();

cn.Close();

 

So I hope I've shared with you,  what we all agree is the way to get pictures both in and out of a SQL Compact database.     I certainly have found having a generic way to get images in and out of SQL Compact saves lots of time and I hope this helps you too.

Theme design by Jelle Druyts

Pick a theme: