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