Navigation

Search

Categories

On this page

A Styled GridView
Make your own AJAX "loading" icons
Getting column information using T-SQL
Using the Timer Control With an UpdatePanel - Part 2.
Using the Timer Control With an UpdatePanel
Adding a Custom Key/Value Pair to Web.Config - Encrypting Sections of it
Biking in PEI
Cascading Dropdown Boxes with AJAX
LINQPad
Getting a Record Count
SqlConnection.RetrieveStatistics()
Simple LINQ Search
Editing with a ListView
Simple LINQ Search
Paging with LINQ to SQL

Archive

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

RSS 2.0 | Atom 1.0 | CDF

Send mail to the author(s) E-mail

Total Posts: 112
This Year: 50
This Month: 4
This Week: 0
Comments: 0

Sign In

 Wednesday, May 07, 2008
Wednesday, May 07, 2008 12:43:11 PM (Eastern Standard Time, UTC-05:00) ( )


I've been following Matt Berseth's .NET Developer's Blog and he has some great stuff on here. The example below is from his post on Building a VS2008 Styled Grid with the GridView Control.  I've made simpler variation of his here.

First the style sheet where all of the real magic happens:

body 
{
    background-color: Aliceblue;
    font-family:Tahoma;
    font-size: 12px;
}

.grid 
{ 
    width:90%; 
    font-family:Tahoma; 
    display: block;
    margin-left: auto;
    margin-right: auto      
}

.director
{
    color: Green;
}

.grid .datatable
{
    width:100%;
    color:#666;
}        
.grid .datatable TH
{
    font-size:12px;
    font-weight:bold;
    letter-spacing:0px;
    text-align:left;
    padding:2px 4px;
    color:#333333;
    border-bottom:solid 2px #bbd9ee;
}
.grid .datatable TH A
{ 
    text-decoration:none;
    padding-right:18px; 
    color:#0066cc;
}

.grid .datatable .row TD
{
    font-size:11px;
    text-align:left;
    padding:6px 4px;
    border-bottom:solid 1px #bbd9ee;
}      
.grid .datatable .row:hover
{
    background-color:#fffacd;
    color:#000;
}
.grid .datatable .row TD.first { padding-left:10px; }
.grid .datatable TH.first { padding-left:10px; }
.grid .datatable .row:hover .first
{
    background-repeat:no-repeat;   
    background-image:url(../img/bullet.gif);
}  

Then the .aspx page itself

<form id="form1" runat="server">
    <div>
    <asp:SqlDataSource
        id="srcMovies"
        ConnectionString='<%$ ConnectionStrings:MyDatabase %>'
        SelectCommand="SELECT Title, Director FROM Movies ORDER BY Title"
        Runat="server" />       
             
       <div class="grid"> 
        <asp:GridView ID="GridView1" runat="server" BorderStyle="None" DataSourceID="srcMovies" HorizontalAlign="Center" 
        CssClass="datatable" AutoGenerateColumns="false" BorderWidth="1px" BackColor="White" GridLines="Horizontal">
       <RowStyle CssClass="row" />
       <Columns>
             <asp:BoundField DataField="Title" HeaderText="Title" ItemStyle-CssClass="first" />
             <asp:BoundField DataField="Director" HeaderText="Director" ItemStyle-CssClass="director" />       
       </Columns>         
        </asp:GridView>        
       </div>        
    </div>
    </form>
Wednesday, May 07, 2008 12:41:45 PM (Eastern Standard Time, UTC-05:00) ( )
 Wednesday, April 30, 2008
Wednesday, April 30, 2008 1:25:42 PM (Eastern Standard Time, UTC-05:00) (  |  )


SELECT ORDINAL_POSITION , COLUMN_NAME , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , IS_NULLABLE , COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyAwesomeTable' ORDER BY ORDINAL_POSITION ASC;
Another method is
sp_help 'tablename' 
 
 Tuesday, April 29, 2008
Tuesday, April 29, 2008 11:49:27 AM (Eastern Standard Time, UTC-05:00) (  |  )

In the previous example, Using a Timer Control With an Update Panel, we just refreshed a panel from data in a list.  In this more practical example, we pull the data from a live database which refreshes an UpdatePanel and an AJAX script manager.  Here is a working demo.

<%@ Page Language="vb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Timer Movies</title>
    <style type="text/css">
    
    .message
    {
        margin-left: 20px;
        font-style:italic;
    }
    
    </style>
</head>
<body>
    <form id="form1" runat="server">
    
    <asp:ScriptManager ID="sm1" runat="server" />
    
    <asp:Timer ID="Timer1" Interval="5000" runat="server" />
    
    <asp:UpdatePanel ID="up1" runat="server">
    <Triggers>
        <asp:AsyncPostBackTrigger ControlID="Timer1" EventName="Tick" />
    </Triggers>
    <ContentTemplate>
    Last Refresh <%= DateTime.Now.ToString("T") %><hr />
    <asp:ListView
        id="lstMovies"
        DataSourceID="srcMovies"
        Runat="server">
        <LayoutTemplate>
            <div id="itemPlaceholder" runat="server">
            </div>
        </LayoutTemplate>
        <ItemTemplate>
            <div>
                Movie Title: <%# Eval("Title") %><div class="message">
                Director: <%# Eval("Director") %></div>
            </div>    
        </ItemTemplate>
    </asp:ListView>    
    </ContentTemplate>      
    </asp:UpdatePanel>
    
    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="Message"
        SelectMethod="Select"
        Runat="server" />
    
    </form>
</body>
</html>

*** Message.vb ***
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Public Class Message

    Private _Title As String

    Public Property Title() As String
        Get
            Return _Title
        End Get
        Set(ByVal value As String)
            _Title = value
        End Set
    End Property

    Private _Director As String

    Public Property Director() As String
        Get
            Return _Director
        End Get
        Set(ByVal value As String)
            _Director = value
        End Set
    End Property


    Public Shared Function [Select]() As ArrayList
        Dim results As New ArrayList()
        Dim conString As String = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
        Dim commandText As String = "SELECT Title, Director FROM Movies ORDER BY Id DESC"
        Dim con As New SqlConnection(conString)
        Dim cmd As New SqlCommand(commandText, con)
        Using con
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                results.Add(New Message(reader))
            End While
        End Using
        Return results
    End Function

    Public Sub New(ByVal reader As SqlDataReader)
        Title = CType(reader("Title"), String)
        Director = CType(reader("Director"), String)
    End Sub

End Class
 
Tuesday, April 29, 2008 9:55:25 AM (Eastern Standard Time, UTC-05:00) (  |  )

This is an interesting example on how to use the Timer control inside of an UpdatePanel to refresh part of a page with no user intervention.   Here is a working demo.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Collections.Generic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

    protected sub Page_Load(sender as object, e as EventArgs)
        dim quotes as new List(of string)()
        quotes.Add("A fool and his money are soon parted")
        quotes.Add("A penny saved is a penny earned")
        quotes.Add("An apple a day keeps the doctor away")
        
        dim rnd as new Random()
        lblQuote.Text = quotes(rnd.Next(quotes.Count))
    end sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Timer Quote</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:ScriptManager ID="ScriptManager1" runat="server" />
    
    <asp:Timer ID="Timer1" Interval="2000" runat="server" />

    Page Time: <%= DateTime.Now.ToString("T") %>
    
    <fieldset>
    <legend>Quote</legend>
    <asp:UpdatePanel ID="up1" runat="server">
    <Triggers>
        <asp:AsyncPostBackTrigger ControlID="Timer1" EventName="Tick" />
    </Triggers>
    <ContentTemplate>
        <asp:Label ID="lblQuote" runat="server" />
    </ContentTemplate>
    </asp:UpdatePanel>
    </fieldset>
        
    </div>
    </form>
</body>
</html>
 Thursday, April 24, 2008
Thursday, April 24, 2008 9:14:46 AM (Eastern Standard Time, UTC-05:00) ( )

The web.config file is commonly used to add database connection strings, mail server settings, system-wide settings etc.  It's easier to store such settings here rather than hard-code them within the pages for obvious reasons. Adding custom key/value pairs in the web.config is pretty easy. Just make sure that the <appSettings> section is outside of the <system.web> section.

<configuration>

<appSettings>
      <add key="myCity" value="Anchorage" />
</appSettings>
<system.web>
....
</system.web>

 

This key/value is accessed in your application like this:

Public Sub Page_Load()
         Dim strCity As String
         strCity = ConfigurationSettings.AppSettings("myCity")
End Sub

Another way to return a custom key's value is to pass the key name to a function.  This function also adds the key name and value to cache if it's not already there

'Return the value of the specified custom key, stored in the Web.Config file.
' If the value has been already requested before, the function returns its 
' cached value.
' The second optional parameter is the path of the Web.Config file where this 
' custom key is stored, and it is necessary to add a dependency to that file,
' so that the cached value is discarded if the file is edited.
' 
' Example: Dim myCity = GetCustomKeyValue("myCity") 

Function GetCustomKeyValue(ByVal key As String, Optional ByVal webConfigUrl As String = "/Web.Config") As String
    ' if this is not the first time this value is needed,
    '  we can find it in the cache
    Dim value As String = CType(HttpContext.Current.Cache(key), String) 

    ' if the retrieved string is empty, the value is not present into the cache,
    '  thus it is retrieved it from Web.Config, and then cached
    If value Is Nothing OrElse value = "" Then
        value = ConfigurationSettings.AppSettings(key)
        HttpContext.Current.Cache.Insert(key, value, New Caching.CacheDependency(webConfigUrl))
    End If 

    Return value
End Function

Lastly, ASP.NET 2.0 makes it really easy to encrypt parts of the web.config to secure sensitive parts of the file. Here are some articles on how to do it.

http://odetocode.com/Blogs/scott/archive/2006/01/08/2707.aspx

http://www.developerfusion.co.uk/show/5263/

http://channel9.msdn.com/Showpost.aspx?postid=134210

Not only can you encrypt config sections using aspnet_regiis from the command line, but you can also encrypt and unencrypt Web.config on the fly in code. The code for protecting and unprotecting sections in your Web.config is fairly trivial, because WebConfigurationManager-related classes handle all the work for you.  I added two buttons to a web page, called btnProtect and btnUnProtect, to protect and unprotect on the fly.  Here is the code of interest:

Protected Sub UnProtect_Click(ByVal sender As Object, ByVal e As EventArgs) 
    UnProtectSection("appSettings") 
End Sub 

Protected Sub Protect_Click(ByVal sender As Object, ByVal e As EventArgs) 
    ProtectSection("appSettings", "DataProtectionConfigurationProvider") 
End Sub 

Private Sub ProtectSection(ByVal sectionName As String, ByVal provider As String) 
    Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath) 
    
    Dim section As ConfigurationSection = config.GetSection(sectionName) 
    
    If section IsNot Nothing AndAlso Not section.SectionInformation.IsProtected Then 
        section.SectionInformation.ProtectSection(provider) 
        config.Save() 
    End If 
End Sub 

Private Sub UnProtectSection(ByVal sectionName As String) 
    Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath) 
    
    Dim section As ConfigurationSection = config.GetSection(sectionName) 
    
    If section IsNot Nothing AndAlso section.SectionInformation.IsProtected Then 
        section.SectionInformation.UnprotectSection() 
        config.Save() 
    End If 
End Sub 
 

Here is what the application settings look like when encrypted:

<appSettings configProtectionProvider=
        "DataProtectionConfigurationProvider">
  <EncryptedData>
   <CipherData>
    <CipherValue>
        AQAAANCMnd8BFdERjHoAwE/Cl+sBAAA
        AXmrl4EN1VUSGDS9ZSSydRwQAAAACAA
        AAAAADZgAAqAAAABAAAAA280OtZlZwu
        D3U+ihvi2zpAAAAAASAAACgAAAAEAAA
        AJ6AnDzWM1o3osh/Y6fcYtwAAQAA1PR
        +wzfwgBgZ4y0yHU4uxaaMET13u21Bv3
        zVE7aA7Z5pCWAYs54LNLNYQ673kmzAL
        osWb7OMuzW6BPwMp18gKNQXOFSGNgA1
        ...
    </< SPAN>CipherValue>
   </< SPAN>CipherData>
  </< SPAN>EncryptedData>
</< SPAN>appSettings>
 Wednesday, April 23, 2008
Wednesday, April 23, 2008 12:31:12 PM (Eastern Standard Time, UTC-05:00) ( )
Wednesday, April 23, 2008 12:28:28 PM (Eastern Standard Time, UTC-05:00) (  |  )


Making the selections in a dropdownbox dependent upon the selected item in another one is a pretty simple task. Here is a working demo.

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Cascading DropDownList Controls With AJAX</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:ScriptManager
        id="sm1"
        Runat="server" />

    <asp:UpdatePanel
        id="UpdatePanel1"
        Runat="server">
   
    <ContentTemplate>
        
    <asp:Label
        id="lblTitle"
        Text="Title:"
        AssociatedControlID="ddlTitle"
        Runat="server" />
    <asp:DropDownList
        id="ddlTitle"
        DataSourceID="srcTitle"
        DataTextField="Title"
        DataValueField="Title"
        AutoPostBack="true"
        Runat="server" />
    <asp:SqlDataSource
        id="srcTitle"
        ConnectionString='<%$ ConnectionStrings:MyDatabase %>'
        SelectCommand="SELECT Title FROM Movies ORDER BY Title"
        Runat="server" />
    
    <br /><br />

    
    <asp:Label
        id="Label1"
        Text="Director:"
        AssociatedControlID="ddlDirector"
        Runat="server" />
    <asp:DropDownList
        id="ddlDirector"
        DataSourceID="srcDirector"
        DataTextField="Director"
        AutoPostBack="true"
        Runat="server" />
    <asp:SqlDataSource
        id="srcDirector"
        ConnectionString='<%$ ConnectionStrings:MyDatabase %>'
        SelectCommand="SELECT Director FROM Movies WHERE Title=@Title ORDER BY Title"
        Runat="server">
        <SelectParameters>
            <asp:ControlParameter Name="Title" ControlID="ddlTitle" />
        </SelectParameters>
    </asp:SqlDataSource>    
    
    </ContentTemplate>
    </asp:UpdatePanel>    
    
    </div>
    </form>
</body>
</html>
Wednesday, April 23, 2008 8:35:17 AM (Eastern Standard Time, UTC-05:00) ( )

I came across this cool product called LINQPad which is a free utility used to right LINQ queries in C# and VB.NET.

 

 

 Monday, April 21, 2008
Monday, April 21, 2008 2:53:36 PM (Eastern Standard Time, UTC-05:00) (  |  )


There are a lot of ways to grab the number of records returned in a dataset.   This is one of my useful ones. Here is a working demo.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 
    Private Sub Page_Load()
        lblMovieCount.Text = GetMovieCount().ToString()
    End Sub
 
    Private Function GetMovieCount() As Integer
        Dim result As Integer = 0        

        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)

Dim cmd As New SqlCommand("GetMovieCount", con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@ReturnVal", SqlDbType.Int).Direction = ParameterDirection.Output Using con con.Open() cmd.ExecuteNonQuery() result = CType(cmd.Parameters("@ReturnVal").Value, Integer) End Using Return result End Function </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show Movie Count</title> </head> <body> <form id="form1" runat="server"> <div> There are <asp:Label id="lblMovieCount" Runat="server" /> movies in the database. </div> </form> </body> </html>

GetMovieCount.sql

CREATE PROCEDURE dbo.GetMovieCount 
    -- OUTPUT parameter to hold the count. 
    @ReturnVal int OUTPUT 
AS 
    -- This will set @recordcount the the number of records returned by the SELECT query. 
    Set @ReturnVal = (SELECT COUNT(*) FROM Movies)
Monday, April 21, 2008 1:28:00 PM (Eastern Standard Time, UTC-05:00) (  |  )


The SqlConnection object has a new RetrieveStatistics method in .NET 2.0, which provides some interesting statistics that could come in handy while debugging and performance tuning your .NET applications.  Normally statistics are turned off by default, so you have to set StatisticsEnabled = true in order for the SqlConnection object to begin collecting statistics.  Here is a working demo.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    
    Sub Page_Load()
        Dim connectionString As String = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
        Dim con As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand("WAITFOR DELAY '0:0:03';SELECT Title,Director FROM Movies", con)
        con.StatisticsEnabled = True
        Using con
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
        End Using
        grdStats.DataSource = con.RetrieveStatistics()
        grdStats.DataBind()
    End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        td,th
        {
            padding:4px 20px;
        }
    </style>
    <title>Show All Statistics</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <h1>Connection Statistics</h1>
    
    <asp:GridView
        id="grdStats"
        AutoGenerateColumns="false"
        Runat="server">
        <Columns>
        <asp:BoundField DataField="Key" HeaderText="Key" />
        <asp:BoundField DataField="Value" HeaderText="Value" />
        </Columns>    
    </asp:GridView>
    
    </div>
    </form>
</body>
</html>
 Friday, April 18, 2008
Friday, April 18, 2008 2:49:10 PM (Eastern Standard Time, UTC-05:00) ( )
Technorati Tags: ,


This is a simple example using LINQ to create a search form and display the results in a Gridview.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Web.Configuration" %>
<%@ Import Namespace="System.Linq" %>
<%@ Import Namespace="System.Data.Linq" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">




<script runat="server"> Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Dim conString As String = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString Dim db As New DataContext(conString) Dim tMovie = db.GetTable(Of Movie)() grdMovies.DataSource = tMovie.Where(Function(m) m.Director.Contains(txtDirector.Text)) grdMovies.DataBind() End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>SearchMovies.aspx</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label id="lblDirector" Text="Director:" AssociatedControlID="txtDirector" Runat="server" /> <asp:TextBox id="txtDirector" Runat="server" /> <asp:Button id="btnSearch" Text="Search" OnClick="btnSearch_Click" Runat="Server" /> <br /><br /> <asp:GridView id="grdMovies" Runat="server" /> </div> </form> </body> </html>
Friday, April 18, 2008 2:43:57 PM (Eastern Standard Time, UTC-05:00) ( )


Here is an example of how to edit records using a ListView.  A working example is here.

<%@ Page Language="vb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<style type="text/css">

    .movie
    {
       border: solid 1px black;
       padding:5px;
       margin:3px; 
    }

    .edit
    {
        background-color:lightyellow;
    }

</style>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Edit ListView</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:ListView 
            ID="lstMovies"
            DataSourceId="srcMovies"
            DataKeyNames="Id"
            runat="server">
            <LayoutTemplate>
                <asp:Placeholder 
                    id="itemPlaceholder"
                    runat="server" />
            </LayoutTemplate>
            <ItemTemplate>
                <div class="movie">
                <strong><%# Eval("Title") %></strong>
                <br />
                <em>Directed by <%# Eval("Director") %></em>
                <br />
                <asp:LinkButton
                    id="lnkEdit"
                    Text="{Edit}"
                    CommandName="Edit"
                    Runat="server" />
                <asp:LinkButton
                    id="lnkDelete"
                    Text="{Delete}"
                    CommandName="Delete"
                    OnClientClick="return confirm('You can't delete movies')"
                    Runat="server" />
                </div>
            </ItemTemplate>
            <EditItemTemplate>
                <div class="movie edit">
                <asp:Label
                    id="lblTitle"
                    Text="Title:"
                    AssociatedControlID="txtTitle"
                    Runat="server" />
                <br />
                <asp:TextBox
                    id="txtTitle"
                    Text='<%# Bind("Title") %>'
                    Runat="server" />    
                
                <br /><br />

                <asp:Label
                    id="lblDirector"
                    Text="Director:"
                    AssociatedControlID="txtDirector"
                    Runat="server" />
                <br />
                <asp:TextBox
                    id="txtDirector"
                    Text='<%# Bind("Director") %>'
                    Runat="server" />    
                
                <br /><br />
                <asp:LinkButton
                    id="lnkUpdate"
                    Text="Save"
                    CommandName="Update"
                    Runat="server" />
               <asp:LinkButton
                    id="lnkCancel"
                    Text="Cancel"
                    CommandName="Cancel"
                    Runat="server" /> 
                </div>
            </EditItemTemplate>
        </asp:ListView>
    
        <asp:SqlDataSource
            id="srcMovies"
            SelectCommand="SELECT Id, Title, Director FROM Movies"
            UpdateCommand="Update Movie SET Title=@Title, Director=@Director
                WHERE Id=@Id" 
DeleteCommand="Delete Movies WHERE Id=@Id"
ConnectionString='<%$ ConnectionStrings:MyDatabase %>' Runat="server" /> </div> </form> </body> </html>
Friday, April 18, 2008 12:30:59 PM (Eastern Standard Time, UTC-05:00) ( )


This is a simple example using LINQ to create a search form and display the results in a Gridview.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Web.Configuration" %>
<%@ Import Namespace="System.Linq" %>
<%@ Import Namespace="System.Data.Linq" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">




<script runat="server"> Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Dim conString As String = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString Dim db As New DataContext(conString) Dim tMovie = db.GetTable(Of Movie)() grdMovies.DataSource = tMovie.Where(Function(m) m.Director.Contains(txtDirector.Text)) grdMovies.DataBind() End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>SearchMovies.aspx</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label id="lblDirector" Text="Director:" AssociatedControlID="txtDirector" Runat="server" /> <asp:TextBox id="txtDirector" Runat="server" /> <asp:Button id="btnSearch" Text="Search" OnClick="btnSearch_Click" Runat="Server" /> <br /><br /> <asp:GridView id="grdMovies" Runat="server" /> </div> </form> </body> </html>
Friday, April 18, 2008 10:22:44 AM (Eastern Standard Time, UTC-05:00) (  |  )


I've been experimenting with LINQ to SQL using code examples from ASP.NET Unleashed 3.5. It's amazing how little code is needed to do some pretty amazing things without ever coding an SQL or ADO.NET. 

In this example, we're using paging on a GridView using a LINQ to SQL class. Paging in a Gridviews typically works by loading all of the records into memory on the server and then displaying only x records at a time.  This, of course, can place a tremendous load on the server if there are a lot of records.  In this example, using LINQ's Skip and Take operators we pass in the the page number and the size creating a much more efficient way to page through a recordset.

The first step is to create an entity using the Object Relational Designer in Visual Studio 2008.    Simply, create a new LINQ to SQL class and then drag whatever table you want from the database on to the designer surface.  In this example, I created a data entity named Movie along with a class named MoviesLINQPaging.  Inside of my class I created two methods named GetMovies and GetMovieCount. 

Here is a working demo

App_Code/MoviesLINQPaging.vb

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Data.Linq
Imports System.Web

Public Class MoviesLINQPaging

    Public Shared Function GetMovies(ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As IEnumerable(Of Movie)
        Dim db As New MoviesDataContext()
        Return db.Movies.Skip(startRowIndex).Take(maximumRows)
    End Function

    Public Shared Function GetMovieCount() As Integer
        Dim context As HttpContext = HttpContext.Current
        If IsNothing(context.Cache("MovieCount")) Then
            context.Cache("MovieCount") = GetMovieCountFromDB()
        End If
        Return CType(context.Cache("MovieCount"), Integer)
    End Function

    Private Shared Function GetMovieCountFromDB() As Integer
        Dim db As New MoviesDataContext()
        Return db.Movies.Count()
    End Function

End Class

The next step is to create new aspx page with a gridview which uses a ObjectDataSource to display the data. Notice the properties of the ObjectDataSource:  TypeName="MoviesLINQPaging" - this is the class name found in MoviesLINQPaging.vb. SelectMethod="GetMovies" and SelectCountMethod="GetMovieCount" are the two methods inside the MoviesLINQPaging class.

MoviesLINQPaging.aspx
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        .movies td,.movies th
        {
            padding:5px;
        }
    </style>
    <title>Show LINQ Paging</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        AllowPaging="true"
        PageSize="3"
        CssClass="movies"
        Runat="server" />
        
    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="MoviesLINQPaging"
        SelectMethod="GetMovies"
        SelectCountMethod="GetMovieCount"
        EnablePaging="True"
        Runat="server" />    
    
    </div>
    </form>
</body>
</html>