Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Add images to slideshow using database table - Part 138

Suggested Videos
Part 135 - Display images in sequence in an image slideshow
Part 136 - Provide capability to start and stop image slideshow
Part 137 - Add images to slideshow using xml file

In Part 137, we discussed storing the image data in an xml file. In this video, we will be using a database table to store image data. So, we can safely delete ImageData.xml file. Please watch Part 137, before proceeding.



To store image data, create table tblImages
Create table tblImages
(
[ID] int identity primary key,
[Name] nvarchar(50),
[Order] int
)



SQL script to insert image data
Insert into tblImages values('Chrysanthemum.jpg',1)
Insert into tblImages values('Desert.jpg',2)
Insert into tblImages values('Hydrangeas.jpg',3)
Insert into tblImages values('Jellyfish.jpg',4)
Insert into tblImages values('Koala.jpg',5)
Insert into tblImages values('Lighthouse.jpg',6)
Insert into tblImages values('Penguins.jpg',7)
Insert into tblImages values('Tulips.jpg',8)
Insert into tblImages values('MyImage.jpg',9)

Stored procedure to retrieve image data
Create procedure spGetImageData
as
Begin
Select [Name], [Order] from tblImages
End

After the table is created, create a connection string in web.config.
<connectionStrings>
  <add name="DBCS" 
       connectionString="data source=.;Integrated Security=SSPI;database=Sample" 
       providerName="System.Data.SqlClient" />
</connectionStrings>

We now have to write ADO.NET code to retrieve image data from the database table. The rest of the logic remains unchanged. Here's the complete code for your reference.
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.Data.SqlClient;
using System.Configuration;

namespace ImageSlideShow
{
    public partial class _Default : 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["ImageDisplayed"];
            i = i + 1;
            ViewState["ImageDisplayed"] = i;

            DataRow imageDataRow = ((DataSet)ViewState["ImageData"]).Tables["image"].Select().FirstOrDefault(x => x["order"].ToString() == i.ToString());
            if (imageDataRow != null)
            {
                Image1.ImageUrl = "~/Images/" + imageDataRow["name"].ToString();
                lblImageName.Text = imageDataRow["name"].ToString();
                lblImageOrder.Text = imageDataRow["order"].ToString();
            }
            else
            {
                SetImageUrl();
            }
        }

        private void SetImageUrl()
        {
            DataSet ds = new DataSet();
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            SqlConnection con = new SqlConnection(CS);
            SqlDataAdapter da = new SqlDataAdapter("spGetImageData", con);
            da.Fill(ds, "image");
            ViewState["ImageData"] = ds;
            ViewState["ImageDisplayed"] = 1;

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

        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";
            }
        }
    }
}

To add a new image to the slideshow
1. Copy the image to the images folder
2. Insert the new image name and it's order into tblImages table.

2 comments:

  1. Sir how to get next and previous button in this slider.... pls help!
    and ur mail id pls..

    ReplyDelete
  2. Fantastic tutorials I am having a problem it wont load my images from database it all works with the XML file, I have tested my sql connection string and it works I get the following error InvalidOperationException on da.FIll(ds, "image"); I would appreciate any help you can provide

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.