First we created this procedure:
Create procedure Donor.usp_myDonations
@PersonKey int
AS
Select FirstName,
LastName,
donationDate ,
DonationAmount
From Person p
Inner Join Donation d
on p.PersonKey=d.PersonKey
Where d.PersonKey=@personKey
Next we need to give the user permissions on this
GRANT EXECUTE ON [Donor].[usp_myDonations] TO [genericdonor]
Now here is the Default.aspx code
<%@ 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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:GridView ID="GridView1" runat="server" BackColor="White"
BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3"
CellSpacing="1" GridLines="None">
<RowStyle BackColor="#DEDFDE" ForeColor="Black" />
<FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
Here is the Default.aspx.cs
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 _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//connect to databse
//call stored procedure--pass it a person key
//display results in grid
SqlConnection connect = new SqlConnection("Data source=localhost;initial catalog=communityassist;user=genericdonor;password=pass");
//windows login would be integrated security=true
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_myDonations";
cmd.Parameters.AddWithValue("@PersonKey", int.Parse(TextBox1.Text));
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "donations");
GridView1.DataSource = ds.Tables["donations"];
GridView1.DataBind();
double total = 0;
foreach (DataRow row in ds.Tables["donations"].Rows)
{
total += double.Parse(row["DonationAmount"].ToString());
}
Label1.Text = "You have contributed " + total.ToString("$ ##0.00");
}
}
No comments:
Post a Comment