DataSet vs. DataReader

We have to research when our application consumes lot of time in executing and also when its scalability becomes a question. Before doing so I always used DataSet to get the data from the database. Whether it is for large application or too small, but then I found something was doing wrong.

DataSet is a collection of in memory tables and datareader provides the ability to expose the data from database.

Both are very widely used in asp.net applications to get/fetch the data from the database. But for a scalable, fast, and reliable application one has to know the best practices in developing application.

DataSet

We should use when the application is:

·         Windows application

·         Not too large data

·         Returning multiple tables

·         If, to be serialized

·         Disconnected architecture

·         To return from a WCF  service

·         To send across layers

·         Caching the data

·         Do not need to open or close connection

Example for dataset:

public DataSet GetRecord(Guid id, string procedureName)

{

    DataSet resultSet = new DataSet();

    SqlConnection connection = new SqlConnection(System.Configuration.

    ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

    SqlCommand command = new SqlCommand(procedureName, connection);

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters["pID"].Value = id.ToString();

    IDataAdapter adapter = new SqlDataAdapter(command);

    try

    {

        adapter.Fill(resultSet);

    }

    catch (Exception ex)

    {

        throw new PerformanceException(ex.Message, ex.InnerException);

    }

    return resultSet;

}

DataReader

DataReader is a stream which is readonly and forward only. It fetches the record from databse and stores in the network buffer and gives whenever requests. DataReader releasese the records as query executes and do not wait for the entire query to execute. Therefore it is very fast as compare to the dataset. It releases only when read method is called.

Its usages:

·         Web application

·         Large data

·         Returning multiple tables

·         For Fast data access

·         Needs explicitly closed

·         Output parameter value will only available after close

·         returns only a row after read

Example for DataReader:

public SqlDataReader GetRecord(Guid id, string procedureName)

{

    SqlDataReader resultReader = null;

    SqlConnection connection = new SqlConnection(

      ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

    SqlCommand command = new SqlCommand(procedureName, connection);

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters["pID"].Value = id.ToString();

    try

    {

        connection.Open();

        resultReader = command.ExecuteReader(CommandBehavior.CloseConnection);

    }

    catch (Exception ex)

    {

        if (resultReader != null || connection.State == ConnectionState.Open)

        {

            resultReader.Close();

            connection.Close();

        }

        throw new PerformanceException(ex.Message, ex.InnerException);

    }

    return resultReader;

}

Difference between Stored Procedure and Function in SQL Server

Store Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. 

Basic Difference

1.       Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

2.       Functions can have only input parameters for it whereas Procedures can have input/output parameters .

3.       Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..

4.       Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1.       Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

2.       Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

3.       Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

4.       Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

5.       Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

6.       Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

7.       We can go for Transaction Management in Procedure whereas we can't go in Function.

Create Dynamic Image Gallery Slideshow using ASP.NET and jQuery Lightbox

In this post, I am going to explain how to create dynamic image gallery using ASP.NET 4.0 and jQuery. The user uploads multiple images at once and those images will be added to a photo gallery or album. As part of uploading image process, we need to store original images on website’s uploads folder. At the same time, we also need to create thumbnail of images. We can divide complete functionality in below three processes:

1. User uploads multiples images to create a new photo gallery. Store gallery details into database.
2. Create thumbnail for uploaded images.
3. Display gallery using jQuery colorbox plugin to get lightbox effect.

Upload multiple images and create gallery

First of all create table tblGallery into database to implement slideshow functionality. SQL Scripts can be downloaded  Here.

Create database Album

use Album

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblGallery](

      [GalleryId] [int] IDENTITY(1,1) NOT NULL,

      [GalleryName] [varchar](50) NOT NULL,

      [GalleryDescription] [varchar](500) NOT NULL,

      [GalleryCreatedDate] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

      [GalleryId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

--

CREATE PROCEDURE [dbo].[usp_CreateNewGallery]

      -- Add the parameters for the stored procedure here

      (

      @GalleryName varchar(50),

      @GalleryDescription varchar(500),

      @GalleryCreatedDate datetime,

      @MaxGalleryId int output

      )

AS

BEGIN

     

      insert into tblGallery values (@GalleryName,@GalleryDescription,@GalleryCreatedDate)

      Select @MaxGalleryId= MAX(GalleryId)from tblGallery

     

END

GO

CreateAlbum.aspx Source Code :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CreateAlbum.aspx.cs" Inherits="CreateAlbum" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Create Album</title>

</head>

<body>

    <form id="form1" runat="server">

        <div>

            <table>

                <tr>

                    <td>Enter Gallery Name

                    </td>

                    <td>

                        <asp:TextBox ID="txtGalleryName" runat="server" Width="90%" MaxLength="100">

                        </asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td></td>

                    <td>

                        <asp:RequiredFieldValidator ID="rfvtxtGalleryName" runat="server" ErrorMessage="Enter Gallery Name"

                            ForeColor="Red" ControlToValidate="txtGalleryName">

                        </asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>Enter Gallery Description

                    </td>

                    <td>

                        <asp:TextBox ID="txtGalleryDescrption" runat="server" TextMode="MultiLine" Width="90%"

                            Height="50px"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td></td>

                    <td>

                        <asp:RequiredFieldValidator ID="rfvtxtGalleryDescrption" runat="server" ErrorMessage="Enter Gallery Description"

                            ForeColor="Red" ControlToValidate="txtGalleryDescrption">

                        </asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>Upload Photos to Album</td>

                    <td>

                        <asp:FileUpload Multiple="true" ID="MultipleFileUpload" runat="server"></asp:FileUpload></td>

                </tr>

                <tr>

                    <td></td>

                    <td>

                        <asp:RequiredFieldValidator ID="rfvFileUploadGallery" runat="server" ErrorMessage="Upload Gallery Photos"

                            ForeColor="Red" ControlToValidate="MultipleFileUpload">

                        </asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />

                    </td>

                </tr>

            </table>

        </div>

    </form>

</body>

</html>

CreateAlbum.aspx Code Behind:

using System;

using System.Collections.Generic;

using System.Drawing;

using System.Drawing.Imaging;

using System.IO;

using System.Linq;

using System.Text;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class CreateAlbum : System.Web.UI.Page

{

    Logic obj = new Logic();

    string FileName;

    string error;

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void btnUpload_Click(object sender, EventArgs e)

    {

        try

        {

            if (MultipleFileUpload.HasFile)

            {

                int MaxGalleryId, ReturnValue;

                ReturnValue = obj.fnCreateNewPhotoGallery(txtGalleryName.Text, txtGalleryDescrption.Text, DateTime.Now, out MaxGalleryId);

                if (ReturnValue != 0)

                {

                    string GalleryPath = System.Configuration.ConfigurationManager.AppSettings["GalleryPath"] + MaxGalleryId;

                    Directory.CreateDirectory(Server.MapPath(GalleryPath));

                    string ThumbnailPath = System.Configuration.ConfigurationManager.AppSettings["ThumbnailPath"] + MaxGalleryId;

                    Directory.CreateDirectory(Server.MapPath(ThumbnailPath));

                    StringBuilder UploadedFileNames = new StringBuilder();

                     HttpFileCollection hfc = Request.Files;

                     for (int i = 0; i < hfc.Count; i++)

                     {

                         HttpPostedFile hpf = hfc[i];

                         FileName = System.IO.Path.GetFileName(hpf.FileName);

                         if (hpf.ContentLength > 0)

                         {

                             if (hpf.ContentLength < 5307200)

                             {

                                

                                 string Ext = System.IO.Path.GetExtension(hpf.FileName);

                                 if ((Ext == ".png") || (Ext == ".jpg") || (Ext == ".jpeg") || (Ext == ".bmp") || (Ext == ".gif"))

                                 {

                                     String UploadedFile = hpf.FileName;

                                     int ExtractPos = UploadedFile.LastIndexOf("\\") + 1;

                                     //to retrieve only Filename from the complete path

                                     String UploadedFileName = DateTime.Now.ToString("ddMMyyhhmmss") + UploadedFile.Substring(ExtractPos, UploadedFile.Length - ExtractPos);

                                     string SaveAsImage = System.IO.Path.Combine(Server.MapPath(GalleryPath + "/"), UploadedFileName);

                                     hpf.SaveAs(SaveAsImage);

                                     //Create thumbnail for uploaded file and save thumbnail on disk

                                     Bitmap Thumbnail = CreateThumbnail(SaveAsImage, 200, 200);

                                     string SaveAsThumbnail = System.IO.Path.Combine(Server.MapPath(ThumbnailPath + "/"), UploadedFileName);

                                     Thumbnail.Save(SaveAsThumbnail);

                                 }

                                 else

                                 {

                                     error = "'" + FileName.ToString() + "'" + " Failed :" + "'" + Ext.ToString() + "'" + " Extension not supported... " + "";

                                 }

                             }

                             else

                             {

                                 error = "'" + FileName.ToString() + "'" + " Failed : " + " file length should not exceed 3MB... " + "";

                             }

                         }

                         else

                         {

                             error = "'" + FileName.ToString() + "'" + " Failed : " + " File is Empty... " + "";

                         }

                     }

                    HTMLHelper.jsAlertAndRedirect(this, "Gallery created successfully. ", "Album.aspx?GalleryId=" + MaxGalleryId);

                }

            }

        }

        catch

        {

            HTMLHelper.jsAlertAndRedirect(this, "Gallery is not created. Some exception occured ", "CreateAlbum.aspx");

        }

    }

    /// <summary>

    /// CreateThumbnail function returns a Bitmap image of the changed thumbnail image which we can save on the disk.

    /// </summary>

    public Bitmap CreateThumbnail(string ImagePath, int ThumbnailWidth, int ThumbnailHeight)

    {

        System.Drawing.Bitmap Thumbnail = null;

        try

        {

            Bitmap ImageBMP = new Bitmap(ImagePath);

            ImageFormat loFormat = ImageBMP.RawFormat;

            decimal lengthRatio;

            int ThumbnailNewWidth = 0;

            int ThumbnailNewHeight = 0;

            decimal ThumbnailRatioWidth;

            decimal ThumbnailRatioHeight;

            // If the uploaded image is smaller than a thumbnail size the just return it

            if (ImageBMP.Width <= ThumbnailWidth && ImageBMP.Height <= ThumbnailHeight)

                return ImageBMP;

            // Compute best ratio to scale entire image based on larger dimension.

            if (ImageBMP.Width > ImageBMP.Height)

            {

                ThumbnailRatioWidth = (decimal)ThumbnailWidth / ImageBMP.Width;

                ThumbnailRatioHeight = (decimal)ThumbnailHeight / ImageBMP.Height;

                lengthRatio = Math.Min(ThumbnailRatioWidth, ThumbnailRatioHeight);

                ThumbnailNewWidth = ThumbnailWidth;

                decimal lengthTemp = ImageBMP.Height * lengthRatio;

                ThumbnailNewHeight = (int)lengthTemp;

            }

            else

            {

                ThumbnailRatioWidth = (decimal)ThumbnailWidth / ImageBMP.Width;

                ThumbnailRatioHeight = (decimal)ThumbnailHeight / ImageBMP.Height;

                lengthRatio = Math.Min(ThumbnailRatioWidth, ThumbnailRatioHeight);

                ThumbnailNewHeight = ThumbnailHeight;

                decimal lengthTemp = ImageBMP.Width * lengthRatio;

                ThumbnailNewWidth = (int)lengthTemp;

            }

            Thumbnail = new Bitmap(ThumbnailNewWidth, ThumbnailNewHeight);

            Graphics g = Graphics.FromImage(Thumbnail);

            g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;

            g.FillRectangle(Brushes.White, 0, 0, ThumbnailNewWidth, ThumbnailNewHeight);

            g.DrawImage(ImageBMP, 0, 0, ThumbnailNewWidth, ThumbnailNewHeight);

            ImageBMP.Dispose();

        }

        catch

        {

            return null;

        }

        return Thumbnail;

    }

}

Display gallery using jQuery colorbox plugin

Create new webform Album.aspx to display gallery slideshow using jQuery colorbox plugin. Write following code in .aspx page: 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Album.aspx.cs" Inherits="Album" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>View Gallery</title>

    <script src="js/jquery-1.8.js" type="text/javascript"> </script>

    <script src="js/jquery.colorbox.js" type="text/javascript"></script>

    <script>

        $(document).ready(function () {

            //Examples of how to assign the ColorBox event to elements

            $(".group1").colorbox({ rel: 'group1', transition: "fade", slideshow: "true" });

        });

    </script>

    <link rel="stylesheet" href="css/colorbox.css" />

</head>

<body>

    <form id="form1" runat="server">

        <div>

            <h1><%= GalleryName %></h1>

            <h2><%= GalleryDescription %></h2>

            <asp:DataList ID="dlGallery" runat="server" RepeatColumns="4" RepeatDirection="Horizontal"

                Width="100%">

                <ItemTemplate>

                    <table border="1">

                        <tr>

                            <td>

                                <a href='<%#Eval("GalleryImagePath") %>' class='group1' rel='group1' title='<%= GalleryName %> : <%= GalleryDescription %>'>

                                    <img src='<%#Eval("ThumbnailImagePath") %>' alt='' />

                                </a>

                            </td>

                        </tr>

                        <br />

                    </table>

                </ItemTemplate>

            </asp:DataList>

        </div>

    </form>

</body>

</html>

• href='<%#Eval("GalleryImagePath") %>' : This will have image path.  
• rel='group1' : This allows to group any combination of elements together for a gallery.
• title='<%= GalleryName %> : <%= GalleryDescription %>' : This attribute is used to display image caption.

1. CreateAlbum.aspx : To upload images and create gallery.
2. AlbumList.aspx : To view complete list of created galleries.
3. Album.aspx : To display images of a specific gallery using lightbox effect.

Download Here