Cool DVD Movie Catalog

Introduction

This article is about how to implement a catalog of DVDs with Linq, SQL Server 2005.

Background

In my website www.cinextreno.com there is a version made. NET 1.1 makes about 4 years, and decided to create a new version.

Using the code

The catalog consists of three tables dvd_genero (categories), dvd_video (catalog), dvd_negocio (bussines)

BLL:

using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.Linq;

public class cDVDdan
{
    // Connection to the database
    private DVDdanDataContext db = new DVDdanDataContext();

    // Get the categories of DVDs  
    
    public IEnumerable<dvd_genero> GetGenerosByPage(int negocio)
    {
        var qry =
            from gen in db.dvd_generos
            where gen.gen_negocio == negocio
            select gen;          

        return qry;
    }
    //Get the DVDs which are premieres 
    public IEnumerable<dvd_video> GetVideosEstrenosByPage(int negocio)
    {
        var qry =
            from c in db.dvd_videos
            where c.video_negocio == negocio && c.video_estreno == 1
            orderby c.video_id descending
            select c;

        return qry.Skip(0).Take(20);
    }
    // Page listing DVDs
    public IEnumerable<dvd_video> GetVideosByPage(int startRowIndex, int pageSize,
 string sortParam,
           string searchText, int genero, int negocio)
    {
        var qry = from c in db.dvd_videos
                  where c.video_negocio == negocio
                  select c;

        if (genero != 0)
            qry = qry.Where(c => c.video_gen_codigo == genero);           
       
        // Search
        if (genero == 0 && !string.IsNullOrEmpty(searchText))
            qry = qry.Where(c => c.video_titulo.Contains(searchText) || 
                c.video_tituloriginal.Contains(searchText) ||
                c.video_director.Contains(searchText) ||
                c.video_actores.Contains(searchText) ||
                c.video_codigo.Contains(searchText));

        return qry.OrderByDescending(c => c.video_id).Skip(startRowIndex).Take(pageSize);
    }
        // Count Page listing DVDs
    public int GetVideosCount(string searchText, int genero, int negocio)
    {
        var qry = from c in db.dvd_videos
                where c.video_negocio == negocio
                select c;
    // Categories
        if(genero != 0)
            qry = qry.Where(c => c.video_gen_codigo == genero);  

        // Buscar            
        if (genero == 0 && !string.IsNullOrEmpty(searchText))
            return qry.Where(c => c.video_titulo.Contains(searchText) || 
                c.video_tituloriginal.Contains(searchText) ||
                c.video_director.Contains(searchText) ||
                c.video_actores.Contains(searchText) ||
                c.video_codigo.Contains(searchText)).Count();

        return qry.Count();
    }

}

Categories : Generos.ascx

<asp:ListView ID="lvGeneros" runat="server" 
    DataSourceID="ObjectDataSourceGenero">    
    <LayoutTemplate>
        <ul ID="itemPlaceholderContainer" runat="server" style="">
            <li ID="itemPlaceholder" runat="server" />
            </ul>
            <div style="">
            </div>
        </LayoutTemplate>                
        <EmptyDataTemplate>
            No hay datos para retornar.
        </EmptyDataTemplate>        
        <ItemTemplate>
            <li style="">             
                <asp:HyperLink ID="HyperLink1" runat="server" 
Text='<%# Eval("gen_nombre") %>' 
                    NavigateUrl = '<%# string.Format("~/Catalogo/Default.aspx?gen={0}", 
Eval("gen_codigo")) %>' />              
            </li>
        </ItemTemplate>
        <ItemSeparatorTemplate>           
        </ItemSeparatorTemplate>
</asp:ListView>
<asp:ObjectDataSource ID="ObjectDataSourceGenero" runat="server" 
    SelectMethod="GetGenerosByPage" TypeName="cDVDdan">
    <SelectParameters>
        <asp:SessionParameter DefaultValue="1" Name="negocio" SessionField="negocio" 
            Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>

Premieres: Estrenos.ascx

<asp:DataList ID="DataListEstrenos" runat="server" 
    DataSourceID="ObjectDataSourceEstrenos" RepeatColumns="5">
    <ItemTemplate>
        <asp:Image ID="Image1" runat="server" 
            ImageUrl='<%# Eval("video_imagen", 
"http://www.wcomercio.com/dvd/imagessmall/{0}.jpg") %>' />
    </ItemTemplate>
</asp:DataList>
<asp:ObjectDataSource ID="ObjectDataSourceEstrenos" runat="server" 
SelectMethod="GetVideosEstrenosByPage"
    TypeName="cDVDdan">
    <SelectParameters>
        <asp:SessionParameter DefaultValue="1" Name="negocio" 
SessionField="negocio" Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>

Listing of DVDs: Catalogo.ascx

<asp:DataList ID="DataListVideos" runat="server" RepeatColumns="5">
    <ItemTemplate>
        <asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("video_imagen", 
"http://www.wcomercio.com/dvd/imagessmall/{0}.jpg") %>' />
    </ItemTemplate>
</asp:DataList>
<table width="100%" align="right">
    <tr>
        <td width="76%" align="left">
            <asp:Label ID="lblStatus" runat="server" 
Font-Name="verdana" Font-Size="10pt" />
        </td>
        <td width="6%">
            <a href="datalistpaging.aspx#this" id="hrefFirst" 
onserverclick="ShowFirst" runat="server">
                <<<</a>
        </td>
        <td width="6%">
            <a href="datalistpaging.aspx#this" id="hrefPrevious" 
onserverclick="ShowPrevious"
                runat="server"><<</a>
        </td>
        <td width="6%">
            <a href="datalistpaging.aspx#this" id="hrefNext" 
onserverclick="ShowNext" runat="server">
                ></a>
        </td>
        <td width="6%">
            <a href="datalistpaging.aspx#this" id="hrefLast" 
onserverclick="ShowLast" runat="server">
                >></a>
        </td>
    </tr>
</table>
<asp:Label ID="intCurrIndex" Visible="False" runat="server" />
<asp:Label ID="intPageSize" Visible="False" runat="server" />
<asp:Label ID="intRecordCount" Visible="False" runat="server" />

Listing of DVDs: Catalogo.cs

The pagination of a DataList

public partial class Controles_Catalogo : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack) {
            intPageSize.Text = "10";
            intCurrIndex.Text = "0";
            DataBindDataList();
        }
    }

    public void ShowFirst(object s, EventArgs e) {
        intCurrIndex.Text = "0";
        DataBindDataList();
    }

    public void ShowPrevious(object s, EventArgs e)
    {
        intCurrIndex.Text = (Convert.ToInt32(intCurrIndex.Text) - 
Convert.ToInt32(intPageSize.Text)).ToString();
        if(Convert.ToInt32(intCurrIndex.Text) < 0)
            intCurrIndex.Text = "0";

        DataBindDataList();
    }

    public void ShowNext(object s, EventArgs e)
    {
        if(Convert.ToInt32(intCurrIndex.Text) + 1 < Convert.ToInt32(intRecordCount.Text))
            intCurrIndex.Text = (Convert.ToInt32(intCurrIndex.Text) + 
Convert.ToInt32(intPageSize.Text)).ToString();

        DataBindDataList();
    }

    public void ShowLast(object s, EventArgs e)
    {       
        int tmpInt = Convert.ToInt32(intRecordCount.Text) % 
Convert.ToInt32(intPageSize.Text);
        if(tmpInt > 0)
            intCurrIndex.Text = (Convert.ToInt32(intRecordCount.Text) - 
tmpInt).ToString();
        else
            intCurrIndex.Text = (Convert.ToInt32(intRecordCount.Text) - 
Convert.ToInt32(intPageSize.Text)).ToString();

        DataBindDataList();
    }   

    private void DataBindDataList()
    {
        string txt = Request.QueryString["txt"];
        string genero = Request.QueryString["gen"] ?? "0";

        cDVDdan c = new cDVDdan();
        intRecordCount.Text = c.GetVideosCount(txt, Convert.ToInt32(genero), 1).ToString();
        DataListVideos.DataSource = c.GetVideosByPage(Convert.ToInt32(intCurrIndex.Text),
            Convert.ToInt32(intPageSize.Text), "", txt, Convert.ToInt32(genero), 1);
        DataListVideos.DataBind();

        PrintStatus();
    }

     private void PrintStatus() {
        lblStatus.Text = "Total:<b>" + intRecordCount.Text;
        lblStatus.Text += "</b> - Pagina:<b> ";
        lblStatus.Text += (Convert.ToInt32(Convert.ToInt32(intCurrIndex.Text) / 
Convert.ToInt32(intPageSize.Text) + 1)).ToString();
        lblStatus.Text += "</b> de <b>";

        if ((Convert.ToInt32(intRecordCount.Text) % 
Convert.ToInt32(intPageSize.Text)) > 0)
            lblStatus.Text += (Convert.ToInt32(Convert.ToInt32(intRecordCount.Text) / 
Convert.ToInt32(intPageSize.Text) + 1)).ToString();
        else
            lblStatus.Text += (Convert.ToInt32(intRecordCount.Text) / 
Convert.ToInt32(intPageSize.Text)).ToString();

        lblStatus.Text += "</b>";
     }
}