Arnoldus
Home Page
Albums
Search
About MeExpand About Me
GuestbookExpand Guestbook
Download
MultiLang1Expand MultiLang1
Skip Navigation Links   |   Login
The Multi Language Site in C#

Multi Language Site: DataBase Structures (Part 2.2)



This is the continuation of Part 2.1 (Multi Language Site: DataBase Structures: Multi Media)


Media Classes

The classes and methods were developed by Matt Gieselman from the original PersonalWebSite code, and further adapted by me.

The Media classes (included in the download) define two structures:

       public Media (int mediaID, int albumID, string application, string original,
       string thumbnail, bool isPublic, int type, DateTime date, string mode,
       string metering, int iso, string aperture, string flash, string exposureTime,
       string make, string model, string distance, int views, int rating, string caption,
       string keywords, string title)


and:

public Album(int albumID, int count, string application, int titleID, string title, string lang, bool isPublic, int ThumbnailMediaID, string Directory)


and two overloads:

public Album(int albumID, int count, string application, int titleID, string title, string lang, bool isPublic)
public Album (int albumID, int titleID, string title, string lang, bool isPublic)


and contain the following methods:
  • static public void AddMedia(int albumID, string name, string captionEn, string captionNl, string captionIt, string keywordsEn, string keywordsNl, string keywordsIt, byte[] bytes)
  • static public void EditMedia (string Caption, string Keywords, int MediaID)
  •  static public void RemoveMedia (int MediaID)
  • static public Media GetMedia (int mediaID)
  •  static public List<Media> GetRandomMedia ()
  • static public List<Media> GetMediaList(int mediaID, PhotoSize photoSize)
  • static public List<Media> GetMovieList(int mediaID)
  • static public Stream GetMedia(int mediaID, PhotoSize photoSize)
  • static public void UpdateMediaViews (int mediaID)
  • static private Stream GetMedia (Media media, string album, PhotoSize photoSize)
  • static public List<Media> GetGalleryMedia (int AlbumID)
  • static public Stream GetFirstMedia(int albumID, PhotoSize photoSize)
  • static public List<Media> GetMostPopularMedia ()
  • static public List<Media> GetMedia (string Query)
  • static public string GetAlbumTitle (int AlbumID)
  • static public string GetAlbumDirectory(int AlbumID)
  •  static public void AddAlbum (string TitleEn, string TitleNl, string TitleIt, bool IsPublic)
  • static public void RemoveAlbum (int AlbumID)
  • static public void EditAlbum (string Title, bool IsPublic, int AlbumID, int TitleID)
  • static public List<Album> GetFullAlbum ()
  • static public List<Album> GetWorkingAlbum()
  • static public DataSet GetAlbumTitles (int AlbumID)
  • static public List<Album> GetFullAlbums ()
  • static public int GetRandomAlbumID ()


As you van see, the classes handle data for a single language: the album class includes a single title (and the titleID). When records are added to the database, one record for each language is added. When editing or retrieving, a single record is handled (selected by the working language) as in the following code fragments:

    static public void AddAlbum (string TitleEn, string TitleNl, string TitleIt,
    bool IsPublic)
    {
        string strApplication = (string)ConfigurationManager.AppSettings["Application"];
        int ThumbnailMediaID = 0;
        using (SqlConnection connection = new SqlConnection
  (ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand ("AddAlbum", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add (new SqlParameter ("@IsPublic", IsPublic));
                command.Parameters.Add (new SqlParameter ("@ThumbnailMediaID",
                ThumbnailMediaID));
                command.Parameters.Add (new SqlParameter ("@TitleEn", TitleEn));
                command.Parameters.Add (new SqlParameter ("@TitleNl", TitleNl));
                command.Parameters.Add (new SqlParameter ("@TitleIt", TitleIt));
                command.Parameters.Add (new SqlParameter ("@Application", strApplication));
                connection.Open ();
                command.ExecuteNonQuery ();
            }
        }
    }


which is implemented by the AddAlbum stored procedure as:

/** Object: StoredProcedure [dbo].[AddAlbum]    Script Date: 07/15/2008 15:03:50 **/

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
 
CREATE PROCEDURE [dbo].[AddAlbum]
 
      @IsPublic bit,
      @ThumbnailMediaID int,
      @TitleEn nvarchar(50),
      @TitleNl nvarchar(50),
      @TitleIt nvarchar(50),
      @Application nvarchar(50)
AS
 
      DECLARE @AlbumID int
 
      INSERT INTO [Albums] (
            [IsPublic],
            [ThumbnailMediaID],
            [Application],
            [Directory])
      VALUES (
            @IsPublic,
            @ThumbnailMediaID,
            @Application,
            @TitleEn)
 
      SET @AlbumID = @@IDENTITY
 
 
      INSERT INTO [AlbumTitles] (
            [AlbumID],
            [Title],
            [Lang])
      VALUES (
            @AlbumID,
            @TitleEn,
            "en")
 
      INSERT INTO [AlbumTitles] (
            [AlbumID],
            [Title],
            [Lang])
      VALUES (
            @AlbumID,
            @TitleNl,
            "nl")
 
      INSERT INTO [AlbumTitles] (
            [AlbumID],
            [Title],
            [Lang])
      VALUES (
            @AlbumID,
            @TitleIt,
            "it")
 
RETURN


Continue to: Multi Language Site: DataBase Structures (Part 2.3): Media Classes II.