AdRotator Using Database


Introduction
——————-
This article explains the concept of Asp.Net AdRotator control fetching ad information from the database and rotates the ads on certain time interval. This article also gives a tip to fetch ad information from an XML file.

The AdRotator Control presents ad images each time a user enters or refreshes a webpage. When the ads are clicked, it will navigate to a new Web location. Each time the page is loaded into the browser, an ad is randomly selected from a predefined list. Previously the predefined list means an XML File, which contains the information about the ads to be displayed. But in Asp.Net 2.0 we can maintain the list in any data source. In this article, we explained the AdRotator control to fetch ads information from both database and XML file, and display those ads in randomly with the help of Timer control. You can say it as an AJAX AdRotator Control.

AdRotator with XML File
———————————–
Open a project as Asp.Net Ajax enabled website in Microsoft Visual Studio 2005, drag and drop an UpdatePanel and add an AdRotator control and Timer Control into it. Set the UpdateMode property of UpdatePanel to conditional. Set the Timer Control Interval property to your desired time. The Interval is in milliseconds. Double click in the Timer Control, it will create a Timer1_Tick event in your code-behind. Add a single line of code as follows

protected void Timer1_Tick(object sender, EventArgs e)
{
UpdatePanel1.Update();
}

Now add an XML file into your project, name it as Ads.xml and specify the AdvertisementFile property of the AdRotator control to point this XML file. So your AdRotator control’s html code will like like

<asp:AdRotator ID=”AdRotator1″ runat=”server” AdvertisementFile=”~/Ads.xml” />

Now we will fill the Ads.xml file with advertisement information. The AdvertisementFile must have the following attributes.

1. ImageUrl: The location of the ad image file.
2. NavigateUrl: The Web location to navigate to when the image ad is clicked.
3. AlternateText: The text to provide as the ALT attribute of the image control when the ad is rendered. This can also be seen as ToolTip.
4. Keyword: Here you can specify some keyword related with the ads image to fetch.
5. Impressions: An integer number to specify the weight of the ad. If the number is larger, then more time the image ad will appear.

The ImageUrl is the only mandatory attribute, rest is optional. Let us have a look on the XML Advertisement file we create for you.
<?xml version=”1.0″ encoding=”utf-8″ ?>
<Advertisements>
<Ad>
<ImageUrl>http://www.anyserver.com/ads/859866.jpg</ImageUrl&gt;
<NavigateUrl>
http://www.anyserver.com</NavigateUrl>
<AlternateText>anyserver.com</AlternateText>
<Keyword>Servers</Keyword>
<Impressions>80</Impressions>
</Ad>
<Ad>
<ImageUrl>http://www.yourserver.com/ads/309548.gif</ImageUrl&gt;
<NavigateUrl>http://www.yourserver.com</NavigateUrl&gt;
<AlternateText>YourServer.com</AlternateText>
<Keyword>MyServer</Keyword>
<Impressions>80</Impressions>
</Ad>
</Advertisements>

That’s it. Easy and save your project. View your project in browser, you can see the rotation of ads in your webpage, at the interval you fixed in the Timer Control.

Disadvantage of AdRotator with XML File
———————————————————–

One of the main disadvantage of AdRotator when functioning with XML file is real time maintainance. Yes if you want to add any new ads or you want to remove any existing ads is not an easy job when your ads is large in number. Everytime to have to find and replace or remove the appropriate tags from the xml file and then you have to upload to the server which is time consuming and hectic. To overcome this difficulty, Asp.Net 2.0 introduce the concept of builing AdRotator control with database. Let us all jump to study how it is easy to integrate an AdRotator with database rather than an XML file.

AdRotator with Database
———————————–

AdRotator control can be bind with any data source. For this article, we shall take MS SQL Server as our data source. We need to create a Table with the following columns in it.

Create Table YourAdRotatorTableName
(
AlternateText VarChar(100),
ImageUrl VarChar(100),
NavigateUrl VarChar(200)
)

These columns in the table are already fixed with the AdRotator control’s attributes. So we no need to specify it in the property tab of the AdRotator control. In case, if you change the column name in the database table, then you have to specify it property in the AlternateTextField, ImageUrlField, NavigateUrlField and KeywordFilter attributes of the AdRotator control.

But I don’t know why ImpressionField is missing in the property tab.

To start with, add another Aspx file to your project, add a ScriptManager and an UpdatePanel. Drag and drop an AdRotator with a Timer Control into the UpdatePanel. Set the Interval property to your desired time, which is in milliseconds. In the code-behind file, let us write a method to fetch ads information from the above table.

private DataTable FetchAdsFromDB()
{
string sql = “select * from YourAdRotatorTableName”;
SqlDataAdapter da = new SqlDataAdapter(sql, );
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

The above method fetches ads information from the table and returns it as a database. You have to mention the DataSource of the AdRotator to this method in your Page_Load event.

AdRotator1.DataSource = FetchAdsFromDB();
AdRotator1.DataBind();

We should not forget the Timer Control and the AJAX functionality to be implemented with this AdRotator. So double click on the Timer control to create an event Timer1_Tick in your code-behind file. Just bind your FetchAdsFromDB method again to the

AdRotator control. Save the changes and run your application, you can see the Ads Rotating in your webpage.

protected void Timer1_Tick(object sender, EventArgs e)
{
AdRotator1.DataSource = FetchAdsFromDB();
AdRotator1.DataBind();
}

Advantages of AdRotator with Database

The main advantage of AdRotator with database is easy maintainance of ads. You can activate or deactivate any time you want. You can manage n-number of Ads in you application with any ads size.

Advertisements

8 thoughts on “AdRotator Using Database

  1. HI, unfortunately i was not so lucky, i think the code works as i can see it scrolling through the url’s if i hover over the rotator but its not displaying the images, and its putting ‘controls’ infront of the URL, ‘Controls’ being the containing folder for the rotator which i created as a user control!

    PLEASE Help.

    Thanks

  2. There is an issue with the “Impressions” field when getting values the database. After many tests, Ads where displayed without using the Impressions value. With a little bit of ILSpy, I found that only field value of string, datetime or decimal where bind from the AdRotator SQLdatasource. So to make the Ads respect the Impressions value, I used a string field for the Impressions value in my database. Voilà !

  3. public partial class ad_db : System.Web.UI.Page
    {
    SqlConnection con;
    public void getcon()
    {
    con = new SqlConnection(“Data Source=joker-pc;Initial Catalog=mytest;Integrated Security=True”);
    con.Open();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
    getcon();
    AdRotator1.DataSource = FetchAdsFromDB();
    AdRotator1.DataBind();
    }
    private DataTable FetchAdsFromDB()
    {
    getcon();
    string sql = “select * from Advertisement”;
    SqlDataAdapter da = new SqlDataAdapter(sql, con);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
    }
    protected void Timer1_Tick1(object sender, EventArgs e)
    {
    AdRotator1.DataSource = FetchAdsFromDB();
    AdRotator1.DataBind();
    }
    ~~~~~~~~~~~~~~~~~““`

    i couldn’t find the problm
    its not displaying anythig

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