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.

picture1.gif

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:

  1. The AV_BroadCastlink attribute represents the url for a particular clip that is served by Windows Media server.

  2. The "Video" attribute acts as flag to determine whether the content is a video content(Video=1) or an audio content(Video=0).

  3. The Width and Height attributes represents the size of Windows Media Player.

  4. 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