Adding images to slideshow using a database

We can use a database to load image name and order to display. This kind of method eliminates to renaming images in a specific way and changing the application code. Therefore we do not have to rebuild and redeploy the application. We use Microsoft SQL Server 2014 as our database server.

Steps in Microsoft SQL Server Management Studio

  1. Create a database named Sample
  2. Create a table called tblImageData
  3. Insert images name and their order into tblImageDatatable
  4. Create a stored procedure named spGetImageData to get data of all images

Sample query in Microsoft SQL Server Management Studio

--create the database
create database Sample;

--create the table
create table tblImageData
(
 Id int identity(1, 1) primary key,
 Name nvarchar(50),
 [Order] int
)

--insert image data into the table
insert into tblImageData values ('Chrysanthemum.jpg', 1)
insert into tblImageData values ('Desert.jpg', 2)
insert into tblImageData values ('Hydrangeas.jpg', 3)
insert into tblImageData values ('Jellyfish.jpg', 4)
insert into tblImageData values ('Koala.jpg', 5)
insert into tblImageData values ('Lighthouse.jpg', 6)
insert into tblImageData values ('Penguins.jpg', 7)
insert into tblImageData values ('Tulips.jpg', 8)
insert into tblImageData values ('Welcome.jpg', 9)

--create the stored procedure
create proc spGetImageData
as
begin
 select *
 from tblImageData
end

We have to add connection string into the Web.config file.

<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=.;Initial Catalog=Sample;Integrated Security=SSPI"
providerName="System.Data.SqlClient" />
</connectionStrings>

Then we need to add another WebForm called DatabaseImageSlideShow.aspx but it is much like the previous WebForm XmlImageSlideShow.aspx.

Sample code in DatabaseImageSlideShow.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DatabaseImageSlideShow.aspx.cs" Inherits="AjaxWebApplication.DatabaseImageSlideShow" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Timer ID="Timer1" runat="server" Interval="1000" OnTick="Timer1_Tick"></asp:Timer>
<asp:Image ID="Image1" runat="server" Height="200" Width="200" />

Image Displaying: <asp:Label ID="lblImageName" runat="server" Text="Label"></asp:Label>

Image Order: <asp:Label ID="lblImageOrder" runat="server" Text="Label"></asp:Label>

<asp:Button ID="Button1" runat="server" Text="Stop slideshow" OnClick="Button1_Click" />
</ContentTemplate>
</asp:UpdatePanel></div>
</form>
</body>
</html>

Small changes have to be applied to the code behind file of this WebForm.

Steps in DatabaseImageSlideShow.aspx.cs

  1. we have to add two additional namespaces which are Configuration and System.Data.SqlClient.
  2. we have to replace
ds.ReadXml(Server.MapPath("~/Data/ImageData.xml"));

using

string cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection con = new SqlConnection(cs);
SqlDataAdapter da = new SqlDataAdapter("spGetImageData", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds, "image");

Sample code in DatabaseImageSlideShow.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace AjaxWebApplication
{
public partial class DatabaseImageSlideShow : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SetImageUrl();
}
}

protected void Timer1_Tick(object sender, EventArgs e)
{
int i = (int)ViewState["ImageOrder"];
i++;
ViewState["ImageOrder"] = i;

DataRow dr = ((DataSet)ViewState["ImageData"]).Tables["image"].Select().FirstOrDefault(x => x["order"].ToString() == i.ToString());

if (dr != null)
{
Image1.ImageUrl = "~/OriginalImages/" + dr["name"].ToString();
lblImageName.Text = dr["name"].ToString();
lblImageOrder.Text = dr["order"].ToString();
}
else
{
SetImageUrl();
}
}

protected void Button1_Click(object sender, EventArgs e)
{
if (Timer1.Enabled)
{
Timer1.Enabled = false;
Button1.Text = "Start slideshow";
}
else
{
Timer1.Enabled = true;
Button1.Text = "Stop slideshow";
}
}

private void SetImageUrl()
{
DataSet ds = new DataSet();
string cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection con = new SqlConnection(cs);
SqlDataAdapter da = new SqlDataAdapter("spGetImageData", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds, "image");
ViewState["ImageData"] = ds;
ViewState["ImageOrder"] = 1;

DataRow dr = ds.Tables["image"].Select().FirstOrDefault(x => x["order"].ToString() == "1");
Image1.ImageUrl = "~/OriginalImages/" + dr["name"].ToString();
lblImageName.Text = dr["name"].ToString();
lblImageOrder.Text = dr["order"].ToString();
}
}
}

Now you can have the slideshow as same as in the previous post. If you want to add a new image into the slide show you need to add it to OriginalImages folder and then you need to insert that image details into tblImageData table. That is all you want to do. You can download the source code from GitHub using https://github.com/hasiabewardana/ExperimentalAjaxWebApplication link. Hey hey! we are stopping here. Stay tuned with us.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s