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>
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 />
<br />
<asp:Button ID="Button2" runat="server" Text="Save" OnClick="Save_Click" />
<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