Rotating Image Slider With jQuery From SQL Database

Rohatash Kumar
11y
59.4k
0
4

Article

Recently, I was working on an ASP.Net website. I would like to create a jQuery Image Slider that rotates images automatically. I have tried looking at tutorials around the web, but I can't find a good solution to this. This article will explain how to implement an Image Slider in ASP.Net. This article initially starts with uploading images to a SQL Server database table. So we make a database table in SQL Server to save images. As the article progresses it explains the implementation of fetching images from the Database. One of the important concepts in this article is of when we update images in an Image Slider. First before proceeding we need to understand what the Image Slider is

Know about Image Slider

The times when image effects were done with Flash are gone. Now, new technologies are available to change the way developers work (for example, jQuery). jQuery is light-weight and you can easily customize it. Now you can use jQuery for image effect instead of flash.

The Image Sliders are the perfect solution for a product, work and content showcase. The Image Sliders control is very useful for displaying images and photos and making the viewing of images more pleasant and interesting in our website.

Where can you use them?

Simply, it adds some pleasant and interesting way to show the images on your page and it is also an effective way to get more content into the relatively small space. If you have a product-based business then you can use the slider to showcase your products. If you are a writer then you could use the slider to showcase your books and so on.

Now, let's see how to do that.

My database table

CREATE TABLE [dbo].[Slider_Table](

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

[Title] [varchar](100) NULL,

[Description] [varchar](200) NULL,

[ImageName] [varchar](100) NULL,

[IsActive] [bit] NULL,

CONSTRAINT [PK_Slider_Table] PRIMARY KEY CLUSTERED

(

[Id] ASC

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

) ON [PRIMARY]

My table structure looks like:

Database Table

Now you have a database table. You need to insert the record in the database table. So create a simple page in Visual Studio that contains some ASP.Net controls, such as file upload, TextBox, CheckBox and Button. The form looks as in the following image.

Insert Image Form

Stored Procedure for insert and Update

You now need to create a Stored Procedure in SQL Server Database for inserting records into the table. Later in the article we use it to update records in the database table.

Create procedure [dbo].[SliderInsertImage]

(

@Id int,

@Title varchar(100),

@Description varchar(200),

@ImageName varchar(100),

@IsActive bit,

@mode int

)

if(@mode=0)

begin

insert into Slider_Table values(@Title, @Description, @ImageName, @IsActive)

end

else

Begin

UPDATE Slider_Table SET Title=@Title, Description=@Description, ImageName=@ImageName, IsActive=@IsActive

WHERE Id = @Id

End

The back-end database work is now done. Now again move to the front-end to make a connection with the database. We store only the name of the image in the database table, the column called ImageName. The image will be saved in the application or server. So we make a folder on the server that contains all the uploaded images.

You will use the name like title, description and image name in this application so you can create a property for it in the class file under the app_code folder. The class file looks like the following:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

/// <summary>

/// Summary description for Slider

/// </summary>

public class Slider

{

private int sID;

private string sTitle;

private string sDescription;

private string sImageName;

private bool sIsActive;

public int ID

{

get

{

return sID;

}

set

{

sID = value;

}

//To get or set the Title of Image

public string Title

{

get

{

return sTitle;

}

set

{

sTitle = value;

}

public bool IsActive

{

get

{

return sIsActive;

}

set

{

sIsActive = value;

}

public string Description

{

get

{

return sDescription;

}

set

{

sDescription = value;

}

public string ImageName

{

get

{

return sImageName;

}

set

{

sImageName = value;

}

Now, double-click on the "Save" button and add the following code to it.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data;

using System.Data.SqlClient;

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

{

Slider sliderinsrt = new Slider();

protected void Page_Load(object sender, EventArgs e)

{

Label1.Visible =false ;

}

protected void Button1_Click(object sender, EventArgs e)

{

sliderinsrt.Title = TextBox1.Text;

sliderinsrt.Description = TextBox2.Text;

sliderinsrt.ImageName = FileUpload1.PostedFile.FileName;

SaveImage();

sliderinsrt.IsActive = CheckBox1.Checked;

int mode=0;

SqlConnection con = new SqlConnection("data source=.;uid=sa; pwd=Micr0s0ft;database=PersionalSite;");

con.Open();

SqlCommand cmd = new SqlCommand("SliderInsertImage", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@Id", sliderinsrt.ID);

cmd.Parameters.AddWithValue("@Title", sliderinsrt.Title);

cmd.Parameters.AddWithValue("@Description", sliderinsrt.Description);

cmd.Parameters.AddWithValue("@ImageName", sliderinsrt.ImageName);

cmd.Parameters.AddWithValue("@IsActive", sliderinsrt.IsActive);

cmd.Parameters.AddWithValue("@mode", mode);

cmd.ExecuteNonQuery();

Label1.Visible = true;

Label1.Text = "Successfully Saved";

con.Close();

TextBox1.Text="";

TextBox2.Text="";

CheckBox1.Checked=false;

}

private void SaveImage()

{

string message = string.Empty;

if (FileUpload1.HasFile)

{

string ext = System.IO.Path.GetExtension(this.FileUpload1.PostedFile.FileName);

ext = ext.ToLower();

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

{

string fileName = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);

Session["fileName"] = fileName;

FileUpload1.PostedFile.SaveAs(Server.MapPath("~/UploadSliderImage/" + fileName).Replace("\\", "//"));

}

else

{

message = "Invalid File Format";

}

The code above contains a method named "SaveImage()" that checks the uploaded file extension using GetExtension() method.

string ext = System.IO.Path.GetExtension(this.FileUpload1.PostedFile.FileName);

If the extension of the uploaded file is okay then you need to save the file on the server or application. To do that I have used the following code in the SaveImage Method.

string fileName = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);

Session["fileName"] = fileName;

FileUpload1.PostedFile.SaveAs(Server.MapPath("~/UploadSliderImage/" + fileName).Replace("\\", "//"));

Now run the application and test it.

Running application

Now enter the title and description; tick on the CheckBox and upload a file. The form looks like the following image.

Uploaded file

Now click on the "Save" button.

Successfully Saved

Now open the SQL Server database and check the inserted title, description and upload file name in the table.

Inserted value in Database table

Now check the uploaded file on the server.

Solution Explorer

Similarly, you can upload more images on the server and save the name in the database table.

Now, you need to create a Stored Procedure in the SQL Server Database to select the top 4 images with an is active condition.

create procedure [dbo].[SliderSlectImage]

begin

select top(4)* from Slider_Table where IsActive ='1';

end

Now again move to the front end to make a connection with the database. Now we will make an Image Slider control. In the Image Slider control the Image will be rotated. That means we need a data control to show the images. So you can drag and drop a Repeater control onto the form. The form has the following code{

</head>

<body>

<asp:Repeater ID="Repeater1" runat="server">

<img src='<%# Getpath()%><%# DataBinder.Eval(Container.DataItem, "ImageName")%>' title='<b><%# DataBinder.Eval(Container.DataItem, "Title")%></b> <br> <%# DataBinder.Eval(Container.DataItem, "Description")%>'/>

<%-- <div class="discription">

'<%# DataBinder.Eval(Container.DataItem, "Title")%> <%# DataBinder.Eval(Container.DataItem, "Description")%>'

</div> --%>

</ItemTemplate>

</asp:Repeater>

</div>

$(window).load(function () {

$('#nivo-slider').nivoSlider();

});

</script>

</div>

</form>

</body>

</html>

In the head section you need to add two files, one for CSS and another file with jQuery Code.

These files above are attached in the Zip file. You can download these files from the attached file.

The code above defines the Repeater control with an image control that is bound with title, description and the image path. The path defines the server path of the image using the GetPath Method name with an image name that is coming from the database table.

Now bind the Repeater control with image name, Title and Description. The following code does the binding:

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;

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

{

DataTable dt = new DataTable();

protected void Page_Load(object sender, EventArgs e)

{

imageslider();

}

protected string Getpath()

{

return ResolveUrl("~/UploadSliderImage/");

}

private void imageslider()

{

SqlConnection con = new SqlConnection("data source=.;uid=sa; pwd=Micr0s0ft;database=PersionalSite;");

con.Open();

SqlCommand cmd = new SqlCommand("SliderSlectImage", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter DA = new SqlDataAdapter(cmd);

DA.Fill(dt);

// dt = sliderhelper.GetsliderImage();

Repeater1.DataSource = dt;

Repeater1.DataBind();

}

In the code above SQLDataAdapter fetches the data from the database and fills the data in the DataTable. Now run the application and test it.

Image Slider

Now the Image Slider that rotates the images automatically and has the numbers 1 2 3 4 to select the image manually and you can also select the previous and next image. If you do't want to show the number then you can change it with any image that you like. Similarly you can change previous and next with the image.

Now add an image and CSS file in the head section of the application and run it. Then the Image Slider shows the image instead of the number.

Customize Image Slider

Updating Images in Image Slider

One of the important concepts of this article applies to when we update images in the Image Slider. Before updating images, you need to bind an image name with DropDownList. That means you can select an image name and update it. The following code defines how to bind a DropDownList with Image Name.

You need to create a Stored Procedure in the SQL Server Database to select the image's name.

Create procedure [dbo].[GetImageID]

begin

select Id, ImageName from Slider_Table

end

The back-end database work has been done. Now again move to the front end to make a connection with the database to bind a DropDownList with an image name. Drag and drop a DropDownList control onto the form.

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

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

.style1

{

color: #FFFFFF;

}

</style>

</head>

<div style="color: #FF33CC; background-color: #00CCFF"><span class="style1"><strong>Select Slider Image ID:</strong></span>

<asp:DropDownList ID="DropDownList1" runat="server" Height="22px" Width="140px"

AutoPostBack="True">

</asp:DropDownList>

</div>

</form>

</body>

</html>

Now bind the DropDownList control with the image name. The following code defines the binding code:

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;

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

{

protected void Page_Load(object sender, EventArgs e)

{

ShowImageName();

}

private void ShowImageName()

{

DataTable dt = new DataTable();

SqlConnection con = new SqlConnection("data source=.;uid=sa; pwd=Micr0s0ft;database=PersionalSite;");

con.Open();

SqlCommand cmd = new SqlCommand("GetImageID", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter DA = new SqlDataAdapter(cmd);

DA.Fill(dt);

if (!object.Equals(dt, null))

{

if (dt.Rows.Count > 0)

{

DropDownList1.DataValueField = "id";

DropDownList1.DataTextField = "ImageName";

DropDownList1.DataSource = dt;

DropDownList1.DataBind();

DropDownList1.Items.Insert(0, "Select");

}

Now run the application and test it.

Image name with DropdownList

You now need to create a Stored Procedure in the SQL Server Database to show the title and description for update when we select an image from the DropDownList.

Create procedure [dbo].[GetImagetitleandDescription]

(

@id int

)

begin

select Title, Description,ImageName,Isactive from Slider_Table where id =@id

end

Now when we select an image name from the DropDownList it will also show the image, title and description to update. The following code add with DropDropDownList.

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

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

.style1

{

color: #FFFFFF;

}

</style>

</head>

<div style="color: #FF33CC; background-color: #00CCFF"><span class="style1"><strong>           Select Slider Image ID:</strong></span>

<asp:DropDownList ID="DropDownList1" runat="server" Height="22px" Width="140px"

AutoPostBack="True">

</asp:DropDownList>

<tr>

Title:

</td>

<asp:TextBox ID="TextBoxTitle" runat="server" CssClass="textbox"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBoxTitle"

ErrorMessage="Title can't be empty" CssClass="ErrorMessage"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

Description:

</td>

<asp:TextBox ID="TextBoxdescription" runat="server" Height="100px" TextMode="MultiLine"

Width="90%" CssClass="textbox"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="TextBoxdescription"

ErrorMessage="Description can't be empty" CssClass="ErrorMessage"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

</td> <td align="left">

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Image ID="Image1" runat="server" Width="59px" />

</td>

</tr>

<tr>

Is Active:

</td>

<asp:CheckBox ID="IsActiveCheckBox" runat="server" />

</td>

</tr>

<tr>

<td>

</td>

<asp:Button ID="UpdateButton" runat="server" Text="Update"

onclick="UpdateButton_Click" />

<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

Now bind the DropDownList control with an image name and select the image name to show the Title and Description. The following code defines the binding code:

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;

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

{

DataTable dt = new DataTable();

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

ShowImageName();

Label1.Visible = false;

}

private void ShowImageName()

{

SqlConnection con = new SqlConnection("data source=.;uid=sa; pwd=Micr0s0ft;database=PersionalSite;");

con.Open();

SqlCommand cmd = new SqlCommand("GetImageID", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter DA = new SqlDataAdapter(cmd);

DA.Fill(dt);

if (!object.Equals(dt, null))

{

if (dt.Rows.Count > 0)

{

DropDownList1.DataValueField = "id";

DropDownList1.DataTextField = "ImageName";

DropDownList1.DataSource = dt;

DropDownList1.DataBind();

DropDownList1.Items.Insert(0, "Select");

}

protected void UpdateButton_Click(object sender, EventArgs e)

{

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

{

if (DropDownList1.SelectedIndex != 0)

{

Slider sl = new Slider();

sl.ID = int.Parse(DropDownList1.SelectedItem.Value);

ViewState["SliderImageid"] = sl.ID;

SqlConnection con = new SqlConnection("data source=.;uid=sa; pwd=Micr0s0ft;database=PersionalSite;");

con.Open();

SqlCommand cmd = new SqlCommand("GetImagetitleandDescription", con);

cmd.Parameters.AddWithValue("@id", sl.ID);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter DA = new SqlDataAdapter(cmd);

DA.Fill(dt);

if (!object.Equals(dt, null))

{

if (dt.Rows.Count > 0)

{

//ViewState["CategoriesData"] = dataSet.Tables[0];

TextBoxTitle.Text = dt.Rows[0][0].ToString();

TextBoxdescription.Text = dt.Rows[0][1].ToString();

Image1.ImageUrl = "~/UploadSliderImage/" + dt.Rows[0][2].ToString();

ViewState["imageurl"] = dt.Rows[0][2].ToString();

IsActiveCheckBox.Checked = (bool)dt.Rows[0][3];

}

else

{

TextBoxTitle.Text = "";

TextBoxdescription.Text = "";

IsActiveCheckBox.Checked = false;

Image1.ImageUrl = "";

}

Now run the application and select an image name from the Database.

Update Image

Now at the end if you want to change the title, description and upload file then you need to first change the the title text or description and update another file. To do that you will call the insertupdate Stored Procedure first that is defined in the preceding code.

Create procedure [dbo].[SliderInsertImage]

(

@Id int,

@Title varchar(100),

@Description varchar(200),

@ImageName varchar(100),

@IsActive bit,

@mode int

)

if(@mode=0)

begin

insert into Slider_Table values(@Title, @Description, @ImageName, @IsActive)

end

else

Begin

UPDATE Slider_Table SET Title=@Title, Description=@Description, ImageName=@ImageName, IsActive=@IsActive

WHERE Id = @Id

End

Now update Title, Description, upload file and isactive. The following code defines the C# code:

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.IO;

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

{

DataTable dt = new DataTable();

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

ShowImageName();

Label1.Visible = false;

}

private void ShowImageName()

{

SqlConnection con = new SqlConnection("data source=.;uid=sa; pwd=Micr0s0ft;database=PersionalSite;");

con.Open();

SqlCommand cmd = new SqlCommand("GetImageID", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter DA = new SqlDataAdapter(cmd);

DA.Fill(dt);

if (!object.Equals(dt, null))

{

if (dt.Rows.Count > 0)

{

DropDownList1.DataValueField = "id";

DropDownList1.DataTextField = "ImageName";

DropDownList1.DataSource = dt;

DropDownList1.DataBind();

DropDownList1.Items.Insert(0, "Select");

}

protected void UpdateButton_Click(object sender, EventArgs e)

{

int mode = 1;

Slider slider = new Slider();

slider.ID = (int)ViewState["SliderImageid"];

slider.Title = TextBoxTitle.Text;

slider.Description = TextBoxdescription.Text;

slider.IsActive = IsActiveCheckBox.Checked;

if (FileUpload1.HasFile)

{

slider.ImageName = FileUpload1.FileName;

if (File.Exists(Server.MapPath("~/UploadSliderImage/" + slider.ImageName).Replace("\\", "//")))

{

Label1.Visible = true;

Label1.Text = "File already exists";

return;

}

else

{

if (File.Exists(ResolveUrl(Server.MapPath("~/UploadSliderImage/" + ViewState["imageurl"].ToString()))))

{

File.Delete(ResolveUrl(Server.MapPath("~/UploadSliderImage/") + ViewState["imageurl"].ToString()));

}

FileUpload1.PostedFile.SaveAs(Server.MapPath("~/UploadSliderImage/" + slider.ImageName).Replace("\\", "//"));

Image1.ImageUrl = "~/UploadSliderImage/" + slider.ImageName;

Label1.Visible = true;

Label1.Text = "Updated Successfully";

}

else

{

slider.ImageName = ViewState["imageurl"].ToString();

Image1.ImageUrl = "~/UploadSliderImage/" + ViewState["imageurl"].ToString();

Label1.Visible = true;

Label1.Text = "Updated Successfully";

}

slider.IsActive = IsActiveCheckBox.Checked;

SqlConnection con = new SqlConnection("data source=.;uid=sa; pwd=Micr0s0ft;database=PersionalSite;");

con.Open();

SqlCommand cmd = new SqlCommand("SliderInsertImage", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@Id", slider.ID);

cmd.Parameters.AddWithValue("@Title", slider.Title);

cmd.Parameters.AddWithValue("@Description", slider.Description);

cmd.Parameters.AddWithValue("@ImageName", slider.ImageName);

cmd.Parameters.AddWithValue("@IsActive", slider.IsActive);

cmd.Parameters.AddWithValue("@mode", mode);

SqlDataAdapter DA = new SqlDataAdapter(cmd);

DA.Fill(dt);

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

{

if (DropDownList1.SelectedIndex != 0)

{

Slider sl = new Slider();

sl.ID = int.Parse(DropDownList1.SelectedItem.Value);

ViewState["SliderImageid"] = sl.ID;

SqlConnection con = new SqlConnection("data source=.;uid=sa; pwd=Micr0s0ft;database=PersionalSite;");

con.Open();

SqlCommand cmd = new SqlCommand("GetImagetitleandDescription", con);

cmd.Parameters.AddWithValue("@id", sl.ID);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter DA = new SqlDataAdapter(cmd);

DA.Fill(dt);

if (!object.Equals(dt, null))

{

if (dt.Rows.Count > 0)

{

//ViewState["CategoriesData"] = dataSet.Tables[0];

TextBoxTitle.Text = dt.Rows[0][0].ToString();

TextBoxdescription.Text = dt.Rows[0][1].ToString();

Image1.ImageUrl = "~/UploadSliderImage/" + dt.Rows[0][2].ToString();

ViewState["imageurl"] = dt.Rows[0][2].ToString();

IsActiveCheckBox.Checked = (bool)dt.Rows[0][3];

}

else

{

TextBoxTitle.Text = "";

TextBoxdescription.Text = "";

IsActiveCheckBox.Checked = false;

Image1.ImageUrl = "";

}

Now run the application and test it.

Update

Now select an image name from the DropDownList.

Select Image name

Now update the description and upload a new image.

Update with description and image

Now click on the "update" button.

Updated Successfully

Now check it in the database table.

Updated table

Up Next

Ebook Download

View all

Hands on ASP.NET GridView

Read by 12k people

Download Now!

Learn

View all