ARTICLE

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

Posted by Paul Abraham Articles | ASP.NET Programming January 19, 2004
In this article, we will build an additional module for ASP.net CommunityStarterKit that enables you to publish audio and video contents. This module works together with Windows Media Services and it is written in C#. You have to install CommunityStarterKit (C# version for VS) to follow this article.
Reader Level:

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

COMMENT USING