/// <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()); }