Rotate Ads With AdRotator Using SQL Server in ASP.NET

Introduction

Today, I have provided an article showing you how to rotate ads with AdRotator using SQL Server in ASP.NET. There are many ways in which a user can Rotate Ads With AdRotator. Here, you will see AdRotator control with SQL Server. In this article, you learn how to dynamically create ads for our Web Application. We will explore how to easily rotate ads using SQL Server when the user refreshes the page. When the ads are clicked, it will navigate to a new web location. However the ads are rotated only when the user refreshes the page. All you have to do is implement and hook it up to your website. First of all you create a table in a SQL Server Database. Then start Visual Studio .NET and make a new ASP.NET web site using Visual Studio 2010.

Creating Table in SQL Server Database

Now create a table named Advertisement with the columns add_id, url, navigate_url, alter_text, impression and keyword. Set the identity property=true for add_id. The table looks as in the following:

Now you have to create a web site.

Go to Visual Studio 2010
New-> Select a website application
Click OK

Now add a new page to the website.

Go to the Solution Explorer
Right-click on the Project name
Select add new item
Add new web page and give it a name
Click OK

Design the page and place the required controls in it. As you can see from this design, a user can enter a url, navigate_url, alter_text, impression and keyword.

.aspx Code

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

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

<title>Untitled Page</title>

</head>

<body>

<div>

Upload_Add:

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

<asp:Button ID="Button1" runat="server" Text="Upload" OnClick="Button_Click" />

<br />

Navigate_Url:

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

<br />

Alternate_Text:

<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

<br />

Impression:

<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

<br />

Keyword:

<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>

<br />

<asp:Button ID="Button2" runat="server" Text="Save" OnClick="Save_Click" />

<br />

<asp:LinkButton ID="LinkButton1" runat="server" OnClick="navi">Show ads</asp:LinkButton>

</div>

</form>

</body>

</html>

XML file elements

Here is a list and a description of the <Ad> tag items.

ImageUrl - The URL of the image to display.
NavigateUrl - The URL where the page will go after AdRotator image is clicked.
AlternateText - Text to display if the image is unavailable.
Keyword - Category of the ad, which can be used to filter for specific ads.
Impressions - Frequency of ad to be displayed. This number is used when you want some ads to be displayed more frequently than others.
Height - Height of the ad in pixels.
Width - Width of the ad in pixel.

We create an image folder in the application which contains some images to rotate in the AdRotator control. Now add a XML file. To do so, right-click the App_Data folder > Add New Item > 'XML File' > Rename it to text.xml and click Add.

Now create another page and drag and drop an AdRotator control from the toolbox to the .aspx and bind it to the advertisement file. To bind the AdRotator to our XML file, we will make use of the "AdvertisementFile" property of the AdRotator control as shown below:

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

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

<title>Untitled Page</title>

</head>

<body>

<div>

<asp:AdRotator ID="AdRotator1" runat="server"

AdvertisementFile="Text.xml" Height="100px" />

</div>

<div>

</div>

</form>

</body>

</html>

Now add the following namespace:

using System.Data.SqlClient;

using System.Data;

using System.IO;

using System.Data.SqlClient;

using System.Xml;

Now write the connection string to connect to the database:

string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;";

Now the .cs file looks like the following code:

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.IO;

using System.Data.SqlClient;

using System.Xml;

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

{

SqlConnection con;

SqlCommand cmd;

SqlDataReader dr;

protected void Button_Click(object sender, EventArgs e)

{

String x = FileUpload1.FileName;

Session["temp"] = x;

String y = "E:\\WebSite1\\Image\\";

String z = y+x;

FileUpload1.SaveAs(z);

Response.Write(z);

}

protected void Save_Click(object sender, EventArgs e)

{

String url="Image/" + Session["temp"].ToString();

int imp=Convert.ToInt32(TextBox3.Text);

con = new SqlConnection(@"Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");

cmd = new SqlCommand("insert into Advertisement values('"+url+"','"+TextBox1.Text+"','"+TextBox2.Text+"',"+imp+",'"+TextBox4.Text+"')", con);

con.Open();

cmd.ExecuteNonQuery(); cmd = new SqlCommand("select * from Advertisement", con);

con.Close();

con.Open();

dr = cmd.ExecuteReader();

XmlWriter xwrite = XmlWriter.Create("E:\\WebSite1\\Text.xml");

xwrite.WriteStartDocument();

xwrite.WriteStartElement("Advertisements");

while (dr.Read())

{

xwrite.WriteStartElement("Ad");

xwrite.WriteElementString("ImageUrl", dr[1].ToString());

xwrite.WriteElementString("NavigateUrl", dr[2].ToString());

xwrite.WriteElementString("AlternateText", dr[3].ToString());

xwrite.WriteElementString("Impressions", dr[4].ToString());

xwrite.WriteElementString("Keyword", dr[5].ToString());

xwrite.WriteEndElement();

}

xwrite.WriteEndElement();

xwrite.WriteEndDocument();

xwrite.Close();

}

protected void navi(object sender, EventArgs e)

{

Response.Redirect("Default.aspx");

}

Now run the application and test it.

Now upload images and fill in the required field and click on the save Button and also upload some other ad images.

The data will be saved in the SQL server table. The table will then looks like this.

Now click on the Show ads link to display ads.

Now refresh the page to change ads.

Note: You can see a demo of this article by downloading this application.

Some Helpful Resources

4 Comments

Join the conversation! Your thoughts help the community grow.

Manav Pandya10y ago
Nice share sir ...
Jayesh Rohit11y ago
its create invalid xml file... plz give me sollution of that..
Hasmukh Savaliya13y ago
You have given example above has no any database file or backup file....
Manish Singh14y ago
It's really help for me.Thanks for sharing.