ARTICLE

Rotate Ads With AdRotator Using SQL Server in ASP.NET

Posted by Rohatash Kumar Articles | ASP.NET Programming March 27, 2012
Today, I have provided an article showing you how to rotate ads with AdRotator using SQL Server in ASP.NET.
Reader Level:
Download Files:
 

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:

Image0.gif

Now you have to create a web site.

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

img5.gif

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

img6.gif

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.

Image1.gif

.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">

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

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

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

    <div>

        &nbsp;Upload_Add:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

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

        &nbsp;

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

        &nbsp;

        <br />

        &nbsp;Navigate_Url:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

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

        <br />

        Alternate_Text:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

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

        <br />

        Impression:&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

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

        <br />

        Keyword:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

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

        <br />

        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        <br />

        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

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

        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        <br />

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

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

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

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

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


Image2.gif

 

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


Image3.gif

 

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


Image4.gif

 

Now click on the Show ads link to display ads.


Image5.gif

 

Now refresh the page to change ads.


Image6.gif

 

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

 

Some Helpful Resources

COMMENT USING
Employers - Post Free Jobs