Audio Video Modules for ASP.NET Community Starter Kit : Part II

Audio-Video Module

In this section we will create the Audio-Video module step by step and you can use these instructions as a recipe for any other new modules.

Step 1. Design a database table for the Audio-Video module.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Community_AudioVideos]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Community_AudioVideos]
GO
CREATE TABLE [dbo].[Community_AudioVideos] (
[AV_ContentPageId] [int] NOT NULL ,
[AV_BroadcastLink] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AV_SortOrder] [int] NULL ,
[Video] [bit] NULL ,
[Width] [smallint] NULL ,
[Height] [smallint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Community_AudioVideos] WITH NOCHECK ADD
CONSTRAINT [PK_Community_AudioVideos] PRIMARY KEY CLUSTERED
(
[AV_ContentPageId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Community_AudioVideos] WITH NOCHECK ADD
CONSTRAINT [DF_Community_AudioVideos_Video] DEFAULT (1) FOR [Video],
CONSTRAINT [DF_Community_AudioVideos_Width] DEFAULT (250) FOR [Width],
CONSTRAINT [DF_Community_AudioVideos_Height] DEFAULT (250) FOR [Height]
GO
ALTER TABLE [dbo].[Community_AudioVideos] ADD
CONSTRAINT [FK_Community_AudioVideos_Community_ContentPages] FOREIGN KEY
(
[AV_ContentPageId]
) REFERENCES [dbo].[Community_ContentPages] (
[contentPage_id]
) ON DELETE CASCADE

Figure 3 (Partial ERD diagram that represents Audio-Video content)

The "Community_Audio_Videos" table works together with its master table "Community_ContentPages" and they are joined with primary keys " AV_ContentPageId" and "ContentPageId" . The following list describes important attributes of the table Community_ AudioVideos:

The AV_BroadCastlink attribute represents the url for a particular clip that is served by Windows Media server.
The "Video" attribute acts as flag to determine whether the content is a video content(Video=1) or an audio content(Video=0).
The Width and Height attributes represents the size of Windows Media Player.
The "AV_SortOrder" attribute determines the display -order in the content list.

Step 2: Design a Business Entity Components(BEC) to represent a particular Audio-Video content.

namespace ASPNET.StarterKit.Communities
{
using System;
using System.Data.SqlClient;
/// <summary>
/// Represents all information about a particular AudioVideo Content.
/// </summary>
public class AudioVideoInfo:ContentInfo
{

string strAV_BroadcastLink=String.Empty;
bool bVideo;
short sWidth=0;
short sHeight=0;
/// <summary>
/// AudioVideoInfo Constructor
/// Calls base ContentInfo constructor to initialize general
// content information and then initialize link specific
// information from a SqlDataReader.
/// </summary>
/// <param name="dr"></param>
public AudioVideoInfo(SqlDataReader dr) : base(dr)
{
this.strAV_BroadcastLink = (string)dr["AV_BroadcastLink"];
this.bVideo=(bool)dr["Video"];
this.sWidth=(short)dr["Width"];
this.sHeight=(short)dr["Height"];
}
//*********************************************************************
//
// BroadcastLink Property
//
// Represents the URL for the Broadcast
//
//*********************************************************************
public string BroadcastLink
{
get {return this.strAV_BroadcastLink;}
set {this.strAV_BroadcastLink =value;}
}
/// <summary>
/// Indicates whether the content is video or audio
/// </summary>public bool Video
{
set
{
this.bVideo=value;
}
get
{
return bVideo;
}
}

/// <summary>
/// represents the width of media player
/// </summary>
public short Width
{
set
{
this.sWidth=value;
}
get
{
return (short) this.sWidth;
}
}
/// <summary>
/// represents the Height of media player
/// </summary>
public short Height
{
set
{
this.sHeight=value;
}
get
{
return (short) this.sHeight;
}
}
}
}

Figure 4 (BEC class for Audio-Video module)

3. Create a Database Gateway for Audio-Video content.

We have implemented the "AudioVideoUtility" class as Database-Gateway for the Audio-Video module. It is used to insert or update an Audio-Video content and it is also used to list all contents of a audio-video section. (see figure 5) . We haven't implemented any delete functionality on the AudioVideoUtility, class because we use the " void ContentPageUtility.DeleteContentPage(int contentPageID, int sectionID)" method to delete an Audio-Video content. This method will delete a particular row in the master table "Community_ContentPages" and the delete-cascade trigger will delete the its counter part in the "Community_AudioVideos" table.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using ASPNET.StarterKit.Communities;
namespace ASPNET.StarterKit.Communities.AudioVideos
{
/// <summary>
/// Database Gateway for Audio-Video content
/// </summary>e
public class AudioVideoUtility
{
/// <summary>
/// Adds a new AudioVideo record to the database
/// </summary>
public static int AddAudioVideoLink
(
int sectionID,
string username,
string linkTitle,
string broadcastLink,
bool bVideo,
short sWidth,
short sHeight,
tring linkDescription,int moderationStatus,
int topicID
)
{
int result=0;
SqlConnection conPortal=null;
try
{
conPortal = new SqlConnection(CommunityGlobals.ConnectionString);
SqlCommand cmd = new SqlCommand("Community_AddAudioVideoContent", conPortal);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction =
parameterDirection.ReturnValue;
cmd.Parameters.Add("@communityID", CommunityGlobals.CommunityID);
cmd.Parameters.Add("@sectionID", sectionID);
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@linkTitle", linkTitle.Trim());
cmd.Parameters.Add("@AV_BroadcastLink", broadcastLink.Trim());
cmd.Parameters.Add("@Video",bVideo);
cmd.Parameters.Add("@Width",sWidth);
cmd.Parameters.Add("@Height",sHeight);
cmd.Parameters.Add("@linkDescription", linkDescription.Trim());cmd.Parameters.Add
("@metaDescription", ContentPageUtility.CalculateMetaDescription(linkDescription));
cmd.Parameters.Add("@metaKeys", ContentPageUtility.CalculateMetaKeys(linkDescription));
cmd.Parameters.Add("@moderationStatus", moderationStatus);
cmd.Parameters.Add("@topicID", topicID);
conPortal.Open();
cmd.ExecuteNonQuery();
result = (int)cmd.Parameters["@RETURN_VALUE"].Value;
// Add Search Keys
SearchUtility.AddSearchKeys(conPortal, sectionID, result, linkTitle, linkDescription);
}
catch(Exception oException)
{
string strErrorMessage=oException.Message;
throw oException;
}
finally
{
conPortal.Close();
}
return result;
}
//*********************************************************************
//
// EditAudioVideo Method
//
// Edits an existing broadcastlink in the database.
//
//*********************************************************************
public static void EditAudioVideo
(
string username,
int sectionID,
int contentPageID,
string linkTitle,
string broadcastLink,
bool bVideo,
short sWidth ,
short sHeight,
string linkDescription,
int topicID
)
{
SqlConnection conPortal = null;
try
{
conPortal = new SqlConnection(CommunityGlobals.ConnectionString);
SqlCommand cmd = new SqlCommand("Community_AudioVideoEditAudoVideo",
onPortal);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@communityID", CommunityGlobals.CommunityID);
cmd.Parameters.Add("@contentPageID", contentPageID);
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@linkTitle", linkTitle.Trim());
cmd.Parameters.Add("@AV_BroadcastLink", broadcastLink.Trim());
cmd.Parameters.Add("@Video", bVideo);
cmd.Parameters.Add("@Width", sWidth);
cmd.Parameters.Add("@Height", sHeight);
cmd.Parameters.Add("@linkDescription", linkDescription.Trim());
cmd.Parameters.Add("@metaDescription", ContentPageUtility.CalculateMetaDescription
linkDescription));
cmd.Parameters.Add("@metaKeys", ContentPageUtility.CalculateMetaKeys
linkDescription));
cmd.Parameters.Add("@topicID", topicID);
conPortal.Open();
cmd.ExecuteNonQuery();
// Edit Search Keys
SearchUtility.EditSearchKeys(conPortal, sectionID, contentPageID, linkTitle,
inkDescription);
}
catch(Exception oException)
{
string strMessage=oException.Message;
throw oException;
}
finally
{
conPortal.Close();
}
}
//*********************************************************************
//
// GetAllAudioVideos Method
//
// Retrieves all audiovideo contents for a particular section.
//
//*********************************************************************
public static ArrayList GetAllAudioVideos(string username,int sectionID, int pageSize, int
ageIndex, string sortOrder)
{
ArrayList colAVLinks = new ArrayList();
SqlConnection conPortal = null;
try
{conPortal =new SqlConnection(CommunityGlobals.ConnectionString);
SqlCommand cmd = new SqlCommand("Community_AudioVideosGetAll", conPortal);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@communityID", CommunityGlobals.CommunityID);
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@sectionID", sectionID);
cmd.Parameters.Add("@pageSize", pageSize);
cmd.Parameters.Add("@pageIndex", pageIndex);
cmd.Parameters.Add("@sortOrder", sortOrder);
conPortal.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
colAVLinks.Add(new AudioVideoInfo(dr));
}
catch(Exception oException)
{
string strTraceMessage=oException.Message;
}
finally
{
conPortal.Close();
}
return colAVLinks;
}
//*********************************************************************
//
// GetAudioVideo Method
//
// Retrieves a particular AudioVideo content from the database.
//
//*********************************************************************
public static ContentInfo GetAudioVideo(string username, int contentPageID)
{
AudioVideoInfo oAVInfo = null;
SqlConnection conPortal=null;
try
{
conPortal = new SqlConnection(CommunityGlobals.ConnectionString);
SqlCommand cmd = new SqlCommand("Community_AudioVideosGetAudioVideo",
onPortal);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@communityID", CommunityGlobals.CommunityID);
cmd.Parameters.Add("@contentPageID", contentPageID);
cmd.Parameters.Add("@username", username);
conPortal.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
oAVInfo = new AudioVideoInfo(dr);
}
catch(Exception oException)
{
string strTraceMessahe=oException.Message;
}
finally
{
conPortal.Close();
}
return (ContentInfo) oAVInfo;
}
}
}

Figure 5 (Database Gateway for Audio-Video module)

continue article