/// <summary>
/// update an existing picture entry
/// </summary>
/// <param name="picture"></param>
private void UpdatePicture(IPictureModel picture)
{
_Pictures[picture.ID] = (PictureModel)picture;
PictureModel p = (PictureModel)picture;
SqlCommand c = new SqlCommand(null, dbc);
c.CommandText = "UPDATE Pictures SET FileName = @FileName, Make = @Make, FNumber = @FNumber, ExposureTime = @ExposureTime, ISOValue = @ISOValue, Flash = @Flash, ExposureProgram = @ExposureProgram, Keywords = @Keywords, ByLine = @ByLine, CopyrightNotice = @CopyrightNotice, Headline = @Headline, Caption = @Caption, fk_Cameras_ID = @fk_Cameras_ID, fk_Photographers_ID = @fk_Photographers_ID WHERE ID = @ID";
SqlParameter filename = new SqlParameter("@FileName", SqlDbType.Text, p.FileName.Length);
filename.Value = p.FileName;
c.Parameters.Add(filename);
SqlParameter Make = new SqlParameter("@Make", SqlDbType.Text, string.IsNullOrEmpty(p.EXIF.Make) ? 1 : p.EXIF.Make.Length);
Make.Value = p.EXIF.Make;
c.Parameters.Add(Make);
SqlParameter FNumber = new SqlParameter("@FNumber", SqlDbType.Decimal, 0);
FNumber.Precision = 18;
FNumber.Scale = 2;
FNumber.Value = (decimal)p.EXIF.FNumber;
c.Parameters.Add(FNumber);
SqlParameter ExposureTime = new SqlParameter("@ExposureTime", SqlDbType.Decimal, 0);
ExposureTime.Precision = 18;
ExposureTime.Scale = 2;
ExposureTime.Value = (decimal)p.EXIF.ExposureTime;
c.Parameters.Add(ExposureTime);
SqlParameter ISOValue = new SqlParameter("@ISOValue", SqlDbType.Decimal, 0);
ISOValue.Precision = 18;
ISOValue.Scale = 2;
ISOValue.Value = (decimal)p.EXIF.ISOValue;
c.Parameters.Add(ISOValue);
SqlParameter Flash = new SqlParameter("@Flash", SqlDbType.Bit, 0);
Flash.Value = p.EXIF.Flash;
c.Parameters.Add(Flash);
SqlParameter ExposureProgram = new SqlParameter("@ExposureProgram", SqlDbType.Int, 0);
ExposureProgram.Value = p.EXIF.ExposureProgram;
c.Parameters.Add(ExposureProgram);
SqlParameter Keywords = new SqlParameter("@Keywords", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.Keywords) ? 1 : p.IPTC.Keywords.Length);
Keywords.Value = p.IPTC.Keywords;
c.Parameters.Add(Keywords);
SqlParameter ByLine = new SqlParameter("@ByLine", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.ByLine) ? 1 : p.IPTC.ByLine.Length);
ByLine.Value = p.IPTC.ByLine;
c.Parameters.Add(ByLine);
SqlParameter CopyrightNotice = new SqlParameter("@CopyrightNotice", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.CopyrightNotice) ? 1 : p.IPTC.CopyrightNotice.Length);
CopyrightNotice.Value = p.IPTC.CopyrightNotice;
c.Parameters.Add(CopyrightNotice);
SqlParameter Headline = new SqlParameter("@Headline", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.Headline) ? 1 : p.IPTC.Headline.Length);
Headline.Value = p.IPTC.Headline;
c.Parameters.Add(Headline);
SqlParameter Caption = new SqlParameter("@Caption", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.Caption) ? 1 : p.IPTC.Caption.Length);
Caption.Value = p.IPTC.Caption;
c.Parameters.Add(Caption);
SqlParameter fk_Cameras_ID = new SqlParameter("@fk_Cameras_ID", SqlDbType.Int, 0);
try
{
fk_Cameras_ID.Value = p.Camera.ID;
}
catch (NullReferenceException)
{
fk_Cameras_ID.Value = DBNull.Value;
}
c.Parameters.Add(fk_Cameras_ID);
SqlParameter ID = new SqlParameter("@ID", SqlDbType.Int, 0);
ID.Value = p.ID;
c.Parameters.Add(ID);
SqlParameter fk_Photographers_ID = new SqlParameter("@fk_Photographers_ID", SqlDbType.Int, 0);
try
{
fk_Photographers_ID.Value = p.Photographer.ID;
}
catch (NullReferenceException)
{
fk_Photographers_ID.Value = DBNull.Value;
}
c.Parameters.Add(fk_Photographers_ID);
c.Prepare();
try
{
c.ExecuteReader();
}
catch (SqlException e)
{
Trace.WriteLine(e.Message);
}
}
/// <summary>
/// insert a new picture entry into database
/// </summary>
/// <param name="picture"></param>
private void InsertPicture(IPictureModel picture)
{
PictureModel p = (PictureModel)picture;
SqlCommand c = new SqlCommand(null, dbc);
c.CommandText = "INSERT INTO Pictures (FileName, Make, FNumber, ExposureTime, ISOValue, Flash, ExposureProgram, Keywords, ByLine, CopyrightNotice, Headline, Caption, fk_Cameras_ID, fk_Photographers_ID) VALUES (@FileName, @Make, @FNumber, @ExposureTime, @ISOValue, @Flash, @ExposureProgram, @Keywords, @ByLine, @CopyrightNotice, @Headline, @Caption, @fk_Cameras_ID, @fk_Photographers_ID);";
SqlParameter filename = new SqlParameter("@FileName", SqlDbType.Text, p.FileName.Length);
filename.Value = p.FileName;
c.Parameters.Add(filename);
SqlParameter Make = new SqlParameter("@Make", SqlDbType.Text, string.IsNullOrEmpty(p.EXIF.Make) ? 1 : p.EXIF.Make.Length);
Make.Value = p.EXIF.Make;
c.Parameters.Add(Make);
SqlParameter FNumber = new SqlParameter("@FNumber", SqlDbType.Decimal, 0);
FNumber.Precision = 18;
FNumber.Scale = 2;
FNumber.Value = (decimal)p.EXIF.FNumber;
c.Parameters.Add(FNumber);
SqlParameter ExposureTime = new SqlParameter("@ExposureTime", SqlDbType.Decimal, 0);
ExposureTime.Precision = 18;
ExposureTime.Scale = 2;
ExposureTime.Value = (decimal)p.EXIF.ExposureTime;
c.Parameters.Add(ExposureTime);
SqlParameter ISOValue = new SqlParameter("@ISOValue", SqlDbType.Decimal, 0);
ISOValue.Precision = 18;
ISOValue.Scale = 2;
ISOValue.Value = (decimal)p.EXIF.ISOValue;
c.Parameters.Add(ISOValue);
SqlParameter Flash = new SqlParameter("@Flash", SqlDbType.Bit, 0);
Flash.Value = p.EXIF.Flash;
c.Parameters.Add(Flash);
SqlParameter ExposureProgram = new SqlParameter("@ExposureProgram", SqlDbType.Int, 0);
ExposureProgram.Value = p.EXIF.ExposureProgram;
c.Parameters.Add(ExposureProgram);
SqlParameter Keywords = new SqlParameter("@Keywords", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.Keywords) ? 1 : p.IPTC.Keywords.Length);
Keywords.Value = p.IPTC.Keywords;
c.Parameters.Add(Keywords);
SqlParameter ByLine = new SqlParameter("@ByLine", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.ByLine) ? 1 : p.IPTC.ByLine.Length);
ByLine.Value = p.IPTC.ByLine;
c.Parameters.Add(ByLine);
SqlParameter CopyrightNotice = new SqlParameter("@CopyrightNotice", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.CopyrightNotice) ? 1 : p.IPTC.CopyrightNotice.Length);
CopyrightNotice.Value = p.IPTC.CopyrightNotice;
c.Parameters.Add(CopyrightNotice);
SqlParameter Headline = new SqlParameter("@Headline", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.Headline) ? 1 : p.IPTC.Headline.Length);
Headline.Value = p.IPTC.Headline;
c.Parameters.Add(Headline);
SqlParameter Caption = new SqlParameter("@Caption", SqlDbType.Text, string.IsNullOrEmpty(p.IPTC.Caption) ? 1 : p.IPTC.Caption.Length);
Caption.Value = p.IPTC.Caption;
c.Parameters.Add(Caption);
SqlParameter fk_Cameras_ID = new SqlParameter("@fk_Cameras_ID", SqlDbType.Int, 0);
try
{
fk_Cameras_ID.Value = p.Camera.ID;
}
catch (NullReferenceException)
{
fk_Cameras_ID.Value = DBNull.Value;
}
c.Parameters.Add(fk_Cameras_ID);
SqlParameter fk_Photographers_ID = new SqlParameter("@fk_Photographers_ID", SqlDbType.Int, 0);
try
{
fk_Photographers_ID.Value = p.Photographer.ID;
}
catch (NullReferenceException)
{
fk_Photographers_ID.Value = DBNull.Value;
}
c.Parameters.Add(fk_Photographers_ID);
c.Prepare();
try
{
c.ExecuteNonQuery();
c.CommandText = "SELECT ID FROM Pictures WHERE FileName LIKE @File;";
SqlParameter fileName = new SqlParameter("@File", SqlDbType.Text, p.FileName.Length);
fileName.Value = p.FileName;
c.Parameters.Add(fileName);
SqlDataReader dr = c.ExecuteReader();
if (dr.Read())
{
picture.ID = dr.GetInt32(0);
_Pictures.Add(picture.ID, (PictureModel)picture);
}
}
catch (SqlException e)
{
Trace.WriteLine(e.Message);
}
}
/// <summary>
/// returns all pictures where filename contains namePart
/// </summary>
/// <param name="namePart"></param>
/// <returns></returns>
IEnumerable <IPictureModel> FilteredPictures(string namePart)
{
Dictionary <int, PictureModel> filteredPictures = new Dictionary <int, PictureModel>();
SqlCommand c = new SqlCommand(null, dbc);
c.CommandText = "SELECT ID, FileName, Make, FNumber, ExposureTime, ISOValue, Flash, ExposureProgram, Keywords, ByLine, CopyrightNotice, Headline, Caption, fk_Cameras_ID, fk_Photographers_ID FROM Pictures WHERE CHARINDEX(LOWER(@namePart), LOWER(cast(FileName as varchar(max)))) > 0";
SqlParameter filename = new SqlParameter("@namePart", SqlDbType.VarChar, namePart.Length);
filename.Value = namePart;
c.Parameters.Add(filename);
SqlDataReader dr = c.ExecuteReader();
while (dr.Read())
{
int ID = dr.GetInt32(0);
string Filename = dr.GetString(1);
if (_Pictures.ContainsKey(ID))
{
filteredPictures.Add(ID, _Pictures[ID]);
}
else
{
filteredPictures.Add(ID, new PictureModel(Filename));
}
PictureModel pm = filteredPictures[ID];
pm.ID = ID;
pm.FileName = Filename;
if (namePart != null)
{
if (!pm.FileName.ToLower().Contains(namePart.ToLower()))
{
continue;
}
}
pm.EXIF.Make = dr.GetString(2);
pm.EXIF.FNumber = dr.GetDecimal(3);
pm.EXIF.ExposureTime = dr.GetDecimal(4);
pm.EXIF.ISOValue = dr.GetDecimal(5);
pm.EXIF.Flash = dr.GetBoolean(6);
pm.EXIF.ExposureProgram = (ExposurePrograms)dr.GetInt32(7);
pm.IPTC.Keywords = dr.GetString(8);
pm.IPTC.ByLine = dr.GetString(9);
pm.IPTC.CopyrightNotice = dr.GetString(10);
pm.IPTC.Headline = dr.GetString(11);
pm.IPTC.Caption = dr.GetString(12);
try
{
pm.Camera = GetCamera(dr.GetInt32(13));
}
catch (SqlNullValueException)
{
pm.Camera = null;
}
try
{
pm.Photographer = GetPhotographer(dr.GetInt32(14));
}
catch (SqlNullValueException)
{
pm.Photographer = null;
}
}
dr.Close();
return(filteredPictures.Values.ToList());
}
