Here is the link to the Full Text Index materials:
http://wash.tonge.us/cmsms/index.php/itc226-index.html
Thursday, July 29, 2010
More asp.net code
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");
}
}
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");
}
}
Monday, July 26, 2010
Further function examples
functionExamples.h
//function prototypes
//passing a pointer
int DestroyTokyo ( int * p);
void UseDestroyTokyo();
void PauseIt();
int * ReturnPointer();
//function overloading
double CalcPay(double rate, double hours);
double CalcPay(double salary);
double CalcPay(double rate, int pieces);
//default value
double CalculateTip(double amount, double percent=0.15);
//template
//void Add (Any a, Any b);
FunctionBodies.cpp
#include <iostream>
#include <string>
#include "functionExamples.h"
using namespace std;
int num2 = 8;
int DestroyTokyo(int * p)
{
int result=0;
if(*p > 5)
{
result=1;
}
return result;
}
void UseDestroyTokyo()
{
cout << "Enter a number: ";
int num;
cin >> num;
cout<< endl;
int * pdt=#
int dt=DestroyTokyo(pdt);
if (dt ==1)
{
cout << "Tokyo is destroyed" << endl;
}
else
{
cout<< "Tokyo is Safe " << endl;
}
}
void PauseIt()
{
cout << "enter any character and press Enter to exit"
<<endl;
char c;
cin >> c;
}
int * ReturnPointer()
{
//int num2=8;
num2=10;
int * ptr = &num2;
return ptr;
}
double CalcPay(double rate, double hours)
{
double pay;
if (hours > 40)
{
pay =rate*(40 + ((hours -40)*1.5));
}
else
{
pay=rate * hours;
}
return pay;
}
double CalcPay(double salary)
{
return salary / 52;
}
double CalcPay(double rate, int pieces)
{
return rate * pieces;
}
double CalculateTip(double amount, double percent)
{
return amount * percent;
}
//template <class Any>
//void Add (Any a, Any b)
//{
// cout << a + b << endl;
//}
Program.cpp
#include <iostream>
#include <string>
#include "functionExamples.h"
using namespace std;
int main()
{
/* int * p = ReturnPointer();
cout << p << " " << *p << endl;
UseDestroyTokyo();
cout << p << " " << *p << endl;*/
/* cout << "Enter the hourly rate " ;
double r;
cin >> r;
cout << "Enter the hours " ;
double hrs;
cin >> hrs;
cout << "Your weekly pay is " << CalcPay(r,hrs) << endl;
cout << "Enter your Annual Salary " ;
double s;
cin >> s;
cout << " Based on your salary your weekly pay is "
<< CalcPay(s) << endl;
cin.get();
cout << "Enter the rate per Piece ";
double rp;
cin >> rp;
cout << endl;
cout << "enter the number of pieces ";
int p;
cin>>p;
cout << "You get " << CalcPay(rp,p)
<< " for " << p << " pieces" <<endl;*/
/*cout << "Enter the Amount of the meal" ;
double amt;
cin >> amt;
cout << "Enter the percentage" ;
double perc;
cin >> perc;
cout << "the tip would be " << CalculateTip(amt, perc)
<< endl;*/
PauseIt();
}
Here is the template function that works
#include <iostream>
#include <string>
using namespace std;
template <class Any>
void Add(Any a, Any b)
{
cout << a + b << endl;
}
void PauseIt()
{
cout << "enter any character and press Enter to exit"
<<endl;
char c;
cin >> c;
}
int main()
{
int a=3,b=5;
Add(a,b);
double c=2.3, d=4.3;
Add(c,d);
cout << " enter the first word ";
string x, y;
getline(cin,x);
cout << endl;
cout << "Enter the second word ";
getline(cin, y);
Add(x,y);
PauseIt();
}
//function prototypes
//passing a pointer
int DestroyTokyo ( int * p);
void UseDestroyTokyo();
void PauseIt();
int * ReturnPointer();
//function overloading
double CalcPay(double rate, double hours);
double CalcPay(double salary);
double CalcPay(double rate, int pieces);
//default value
double CalculateTip(double amount, double percent=0.15);
//template
//void Add (Any a, Any b);
FunctionBodies.cpp
#include <iostream>
#include <string>
#include "functionExamples.h"
using namespace std;
int num2 = 8;
int DestroyTokyo(int * p)
{
int result=0;
if(*p > 5)
{
result=1;
}
return result;
}
void UseDestroyTokyo()
{
cout << "Enter a number: ";
int num;
cin >> num;
cout<< endl;
int * pdt=#
int dt=DestroyTokyo(pdt);
if (dt ==1)
{
cout << "Tokyo is destroyed" << endl;
}
else
{
cout<< "Tokyo is Safe " << endl;
}
}
void PauseIt()
{
cout << "enter any character and press Enter to exit"
<<endl;
char c;
cin >> c;
}
int * ReturnPointer()
{
//int num2=8;
num2=10;
int * ptr = &num2;
return ptr;
}
double CalcPay(double rate, double hours)
{
double pay;
if (hours > 40)
{
pay =rate*(40 + ((hours -40)*1.5));
}
else
{
pay=rate * hours;
}
return pay;
}
double CalcPay(double salary)
{
return salary / 52;
}
double CalcPay(double rate, int pieces)
{
return rate * pieces;
}
double CalculateTip(double amount, double percent)
{
return amount * percent;
}
//template <class Any>
//void Add (Any a, Any b)
//{
// cout << a + b << endl;
//}
Program.cpp
#include <iostream>
#include <string>
#include "functionExamples.h"
using namespace std;
int main()
{
/* int * p = ReturnPointer();
cout << p << " " << *p << endl;
UseDestroyTokyo();
cout << p << " " << *p << endl;*/
/* cout << "Enter the hourly rate " ;
double r;
cin >> r;
cout << "Enter the hours " ;
double hrs;
cin >> hrs;
cout << "Your weekly pay is " << CalcPay(r,hrs) << endl;
cout << "Enter your Annual Salary " ;
double s;
cin >> s;
cout << " Based on your salary your weekly pay is "
<< CalcPay(s) << endl;
cin.get();
cout << "Enter the rate per Piece ";
double rp;
cin >> rp;
cout << endl;
cout << "enter the number of pieces ";
int p;
cin>>p;
cout << "You get " << CalcPay(rp,p)
<< " for " << p << " pieces" <<endl;*/
/*cout << "Enter the Amount of the meal" ;
double amt;
cin >> amt;
cout << "Enter the percentage" ;
double perc;
cin >> perc;
cout << "the tip would be " << CalculateTip(amt, perc)
<< endl;*/
PauseIt();
}
Here is the template function that works
#include <iostream>
#include <string>
using namespace std;
template <class Any>
void Add(Any a, Any b)
{
cout << a + b << endl;
}
void PauseIt()
{
cout << "enter any character and press Enter to exit"
<<endl;
char c;
cin >> c;
}
int main()
{
int a=3,b=5;
Add(a,b);
double c=2.3, d=4.3;
Add(c,d);
cout << " enter the first word ";
string x, y;
getline(cin,x);
cout << endl;
cout << "Enter the second word ";
getline(cin, y);
Add(x,y);
PauseIt();
}
Thursday, July 22, 2010
wpf windows form
Here is the code for the Windows Presentation Foundation Form:
First here is the XAML
<Window x:Class="DonorWindows.Window1"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="Window1" Height="575" Width="475">
<Grid Height="575" Width="475" Background="Cornsilk">
<Label Height="28" HorizontalAlignment="Left" Margin="12,12,0,0" Name="label1" VerticalAlignment="Top" Width="175" FontSize="16" Foreground="DarkBlue">Enter New Donation</Label>
<Label Height="28" HorizontalAlignment="Left" Margin="24,62,0,0" Name="label2" VerticalAlignment="Top" Width="120">First Name</Label>
<TextBox Height="23" Margin="0,67,161,0" Name="txtFirstName" VerticalAlignment="Top" HorizontalAlignment="Right" Width="153" Background="PowderBlue" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,107,0,0" Name="label3" VerticalAlignment="Top" Width="120">LastName</Label>
<TextBox Background="PowderBlue" Height="23" Margin="161,112,129,0" Name="txtLastName" VerticalAlignment="Top" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,151,0,0" Name="label4" VerticalAlignment="Top" Width="120">Street</Label>
<TextBox Background="PowderBlue" Margin="161,151,129,0" Name="txtStreet" Height="23" VerticalAlignment="Top" />
<Label Height="28" HorizontalAlignment="Left" Margin="31,190,0,0" Name="label5" VerticalAlignment="Top" Width="120">Apartment</Label>
<TextBox Background="PowderBlue" Height="23" Margin="157,195,133,0" Name="txtApartment" VerticalAlignment="Top" />
<Label Height="28" HorizontalAlignment="Left" Margin="31,228,0,0" Name="label6" VerticalAlignment="Top" Width="120">City</Label>
<TextBox Background="PowderBlue" Height="23" Margin="157,233,133,0" Name="txtCity" VerticalAlignment="Top" />
<Label HorizontalAlignment="Left" Margin="31,272,0,255" Name="label7" Width="120">State</Label>
<TextBox Background="PowderBlue" Margin="157,277,0,276" Name="txtState" HorizontalAlignment="Left" Width="42" />
<Label Height="28" HorizontalAlignment="Left" Margin="31,0,0,217" Name="label8" VerticalAlignment="Bottom" Width="120">Zip Code</Label>
<TextBox Background="PowderBlue" Height="23" Margin="157,0,133,217" Name="txtZip" VerticalAlignment="Bottom" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,0,0,175" Name="label9" VerticalAlignment="Bottom" Width="120">HomePhone</Label>
<TextBox Background="PowderBlue" Height="23" Margin="161,0,161,175" Name="txtHome" VerticalAlignment="Bottom" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,0,0,135" Name="label10" VerticalAlignment="Bottom" Width="120">Work Phone</Label>
<TextBox Background="PowderBlue" Height="23" Margin="161,0,161,135" Name="txtWork" VerticalAlignment="Bottom" />
<Label Height="28" HorizontalAlignment="Left" Margin="31,0,0,95" Name="label11" VerticalAlignment="Bottom" Width="120">Email</Label>
<TextBox Background="PowderBlue" Height="23" Margin="168,0,154,95" Name="txtEmail" VerticalAlignment="Bottom" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,0,0,56" Name="label12" VerticalAlignment="Bottom" Width="120">Donation</Label>
<TextBox Background="PowderBlue" Height="23" Margin="161,0,161,56" Name="txtDonation" VerticalAlignment="Bottom" />
<Button Height="23" HorizontalAlignment="Right" Margin="0,0,42,52" Name="button1" VerticalAlignment="Bottom" Width="75" Click="button1_Click">Submit</Button>
</Grid>
</Window>
Now here is the c# code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using System.Data.SqlClient;
namespace DonorWindows
{
///
/// Interaction logic for Window1.xaml
///
public partial class Window1 : Window
{
public Window1()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
//connect to the database
//pass it some command any parameters
//deal with results
SqlConnection connect = new SqlConnection
("Data source=localhost;initial catalog=communityAssist;user=genericdonor;password=pass");
SqlCommand cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "donor.usp_RegisterDonor";
cmd.Parameters.AddWithValue("@Lastname", txtLastName.Text);
cmd.Parameters.AddWithValue("@firstname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@Street", txtStreet.Text);
cmd.Parameters.AddWithValue("@Apartment", txtApartment.Text);
cmd.Parameters.AddWithValue("@City", txtCity.Text);
cmd.Parameters.AddWithValue("@State", txtState.Text);
cmd.Parameters.AddWithValue("@Zip", txtZip.Text);
cmd.Parameters.AddWithValue("@DonationAmount", double.Parse(txtDonation.Text));
if (txtHome.Text != "")
{
cmd.Parameters.AddWithValue("@HomePhone", txtHome.Text);
}
if (txtWork.Text != "")
{
cmd.Parameters.AddWithValue("@WorkPhone", txtWork.Text);
}
if (txtEmail.Text != "")
{
cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
}
int result;
connect.Open();
result=(int)cmd.ExecuteNonQuery();
connect.Close();
if (result > 0)
{
MessageBox.Show( "Thank you for your donation");
}
else
{
MessageBox.Show( "there are problems with your donation");
}
}
}
}
Notes:
The only difference between this code and the web page code is the use of the MessageBox object and I changed the connection string to connect as generalDonor. We discovered a couple of things about connecting as generaldonor. One, the create table command in the trigger gave problems; two I had to assign insert permissions on the underlying tables for the stored procedure to work fully.
A better strategy might be to create the schema with its objects and then create a role that assigns all the permissions needed on those and other objects. then attach a user to both the role and the schema. We might look at this later.
First here is the XAML
<Window x:Class="DonorWindows.Window1"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="Window1" Height="575" Width="475">
<Grid Height="575" Width="475" Background="Cornsilk">
<Label Height="28" HorizontalAlignment="Left" Margin="12,12,0,0" Name="label1" VerticalAlignment="Top" Width="175" FontSize="16" Foreground="DarkBlue">Enter New Donation</Label>
<Label Height="28" HorizontalAlignment="Left" Margin="24,62,0,0" Name="label2" VerticalAlignment="Top" Width="120">First Name</Label>
<TextBox Height="23" Margin="0,67,161,0" Name="txtFirstName" VerticalAlignment="Top" HorizontalAlignment="Right" Width="153" Background="PowderBlue" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,107,0,0" Name="label3" VerticalAlignment="Top" Width="120">LastName</Label>
<TextBox Background="PowderBlue" Height="23" Margin="161,112,129,0" Name="txtLastName" VerticalAlignment="Top" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,151,0,0" Name="label4" VerticalAlignment="Top" Width="120">Street</Label>
<TextBox Background="PowderBlue" Margin="161,151,129,0" Name="txtStreet" Height="23" VerticalAlignment="Top" />
<Label Height="28" HorizontalAlignment="Left" Margin="31,190,0,0" Name="label5" VerticalAlignment="Top" Width="120">Apartment</Label>
<TextBox Background="PowderBlue" Height="23" Margin="157,195,133,0" Name="txtApartment" VerticalAlignment="Top" />
<Label Height="28" HorizontalAlignment="Left" Margin="31,228,0,0" Name="label6" VerticalAlignment="Top" Width="120">City</Label>
<TextBox Background="PowderBlue" Height="23" Margin="157,233,133,0" Name="txtCity" VerticalAlignment="Top" />
<Label HorizontalAlignment="Left" Margin="31,272,0,255" Name="label7" Width="120">State</Label>
<TextBox Background="PowderBlue" Margin="157,277,0,276" Name="txtState" HorizontalAlignment="Left" Width="42" />
<Label Height="28" HorizontalAlignment="Left" Margin="31,0,0,217" Name="label8" VerticalAlignment="Bottom" Width="120">Zip Code</Label>
<TextBox Background="PowderBlue" Height="23" Margin="157,0,133,217" Name="txtZip" VerticalAlignment="Bottom" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,0,0,175" Name="label9" VerticalAlignment="Bottom" Width="120">HomePhone</Label>
<TextBox Background="PowderBlue" Height="23" Margin="161,0,161,175" Name="txtHome" VerticalAlignment="Bottom" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,0,0,135" Name="label10" VerticalAlignment="Bottom" Width="120">Work Phone</Label>
<TextBox Background="PowderBlue" Height="23" Margin="161,0,161,135" Name="txtWork" VerticalAlignment="Bottom" />
<Label Height="28" HorizontalAlignment="Left" Margin="31,0,0,95" Name="label11" VerticalAlignment="Bottom" Width="120">Email</Label>
<TextBox Background="PowderBlue" Height="23" Margin="168,0,154,95" Name="txtEmail" VerticalAlignment="Bottom" />
<Label Height="28" HorizontalAlignment="Left" Margin="24,0,0,56" Name="label12" VerticalAlignment="Bottom" Width="120">Donation</Label>
<TextBox Background="PowderBlue" Height="23" Margin="161,0,161,56" Name="txtDonation" VerticalAlignment="Bottom" />
<Button Height="23" HorizontalAlignment="Right" Margin="0,0,42,52" Name="button1" VerticalAlignment="Bottom" Width="75" Click="button1_Click">Submit</Button>
</Grid>
</Window>
Now here is the c# code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using System.Data.SqlClient;
namespace DonorWindows
{
///
/// Interaction logic for Window1.xaml
///
public partial class Window1 : Window
{
public Window1()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
//connect to the database
//pass it some command any parameters
//deal with results
SqlConnection connect = new SqlConnection
("Data source=localhost;initial catalog=communityAssist;user=genericdonor;password=pass");
SqlCommand cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "donor.usp_RegisterDonor";
cmd.Parameters.AddWithValue("@Lastname", txtLastName.Text);
cmd.Parameters.AddWithValue("@firstname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@Street", txtStreet.Text);
cmd.Parameters.AddWithValue("@Apartment", txtApartment.Text);
cmd.Parameters.AddWithValue("@City", txtCity.Text);
cmd.Parameters.AddWithValue("@State", txtState.Text);
cmd.Parameters.AddWithValue("@Zip", txtZip.Text);
cmd.Parameters.AddWithValue("@DonationAmount", double.Parse(txtDonation.Text));
if (txtHome.Text != "")
{
cmd.Parameters.AddWithValue("@HomePhone", txtHome.Text);
}
if (txtWork.Text != "")
{
cmd.Parameters.AddWithValue("@WorkPhone", txtWork.Text);
}
if (txtEmail.Text != "")
{
cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
}
int result;
connect.Open();
result=(int)cmd.ExecuteNonQuery();
connect.Close();
if (result > 0)
{
MessageBox.Show( "Thank you for your donation");
}
else
{
MessageBox.Show( "there are problems with your donation");
}
}
}
}
Notes:
The only difference between this code and the web page code is the use of the MessageBox object and I changed the connection string to connect as generalDonor. We discovered a couple of things about connecting as generaldonor. One, the create table command in the trigger gave problems; two I had to assign insert permissions on the underlying tables for the stored procedure to work fully.
A better strategy might be to create the schema with its objects and then create a role that assigns all the permissions needed on those and other objects. then attach a user to both the role and the schema. We might look at this later.
Wednesday, July 21, 2010
Functions example
#include <iostream>
using namespace std;
int cube(int);
void GetCube();
void PauseIt();
//array function
void CreateArray();
void FillArray(int[], int);
void DisplayArray(int[], int);
//pointer function
//first some simple functions
int add(int, int);
int subtract(int, int);
int multiply(int,int);
//the pointer function
void DoMath(int, int, int (*pFunction)(int,int));
void UseDoMath(); //use the pointer function
int main()
{
//GetCube();
//CreateArray();
UseDoMath();
PauseIt();
return 0;
}
int cube(int x)
{
return x * x * x;
}
void GetCube()
{
cout << "enter an int: " ;
int y;
cin >> y;
cout << endl;
//int myCube = cube(y);
cout << "the cube of " << y << " is " << cube(y) << endl;
}
void PauseIt()
{
cout<<endl;
cout <<"************************************" << endl;
cout << "Enter any character and press enter to exit" << endl;
char c;
cin >> c;
}
void CreateArray()
{
const int size=6;
int numbers[size];
FillArray(numbers, size);
}
void FillArray(int myArray[],int size)
{
for (int i=0;i<size;i++)
{
myArray[i]=i*7%2 +i;
}
DisplayArray(myArray,size);
}
void DisplayArray(int myArray[], int size)
{
int total=0;
for(int i=0; i < size;i++)
{
cout << myArray[i] << endl;
total+=myArray[i];
}
cout << "the total is " << total <<endl;
cout << "the average is " << double(total/size)<< endl;
}
int Add(int x, int y)
{
return x+y;
}
int Subtract(int x, int y)
{
return x-y;
}
int Multiply(int x, int y)
{
return x * y;
}
void DoMath(int x, int y, int (*pFunction)(int , int ))
{
cout << (*pFunction)(x,y) << endl;
cout << endl;
}
void UseDoMath()
{
int num1,num2;
cout << "enter the first number: ";
cin >> num1;
cout << endl;
cout << "Enter the second number: ";
cin >>num2;
cout << endl;
cout << "the sum is " ;
DoMath(num1, num2,Add);
cout << "The Difference is " ;
DoMath(num1,num2, Subtract);
cout << "The Product is " ;
DoMath(num1,num2, Multiply);
}
using namespace std;
int cube(int);
void GetCube();
void PauseIt();
//array function
void CreateArray();
void FillArray(int[], int);
void DisplayArray(int[], int);
//pointer function
//first some simple functions
int add(int, int);
int subtract(int, int);
int multiply(int,int);
//the pointer function
void DoMath(int, int, int (*pFunction)(int,int));
void UseDoMath(); //use the pointer function
int main()
{
//GetCube();
//CreateArray();
UseDoMath();
PauseIt();
return 0;
}
int cube(int x)
{
return x * x * x;
}
void GetCube()
{
cout << "enter an int: " ;
int y;
cin >> y;
cout << endl;
//int myCube = cube(y);
cout << "the cube of " << y << " is " << cube(y) << endl;
}
void PauseIt()
{
cout<<endl;
cout <<"************************************" << endl;
cout << "Enter any character and press enter to exit" << endl;
char c;
cin >> c;
}
void CreateArray()
{
const int size=6;
int numbers[size];
FillArray(numbers, size);
}
void FillArray(int myArray[],int size)
{
for (int i=0;i<size;i++)
{
myArray[i]=i*7%2 +i;
}
DisplayArray(myArray,size);
}
void DisplayArray(int myArray[], int size)
{
int total=0;
for(int i=0; i < size;i++)
{
cout << myArray[i] << endl;
total+=myArray[i];
}
cout << "the total is " << total <<endl;
cout << "the average is " << double(total/size)<< endl;
}
int Add(int x, int y)
{
return x+y;
}
int Subtract(int x, int y)
{
return x-y;
}
int Multiply(int x, int y)
{
return x * y;
}
void DoMath(int x, int y, int (*pFunction)(int , int ))
{
cout << (*pFunction)(x,y) << endl;
cout << endl;
}
void UseDoMath()
{
int num1,num2;
cout << "enter the first number: ";
cin >> num1;
cout << endl;
cout << "Enter the second number: ";
cin >>num2;
cout << endl;
cout << "the sum is " ;
DoMath(num1, num2,Add);
cout << "The Difference is " ;
DoMath(num1,num2, Subtract);
cout << "The Product is " ;
DoMath(num1,num2, Multiply);
}
Tuesday, July 20, 2010
Web Page Code
Here is the default.aspx
<%@ 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>
<h1>Make a Donation</h1>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="First Name"></asp:Label>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label2" runat="server" Text="Last Name"></asp:Label>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="label3" runat="server" Text="Street"></asp:Label>
<asp:TextBox ID="txtStreet" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label4" runat="server" Text="Apartment"></asp:Label>
<asp:TextBox ID="txtApartment" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label5" runat="server" Text="City"></asp:Label>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label6" runat="server" Text="State"></asp:Label>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label7" runat="server" Text="Zip code"></asp:Label>
<asp:TextBox ID="txtZip" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label8" runat="server" Text="home phone"></asp:Label>
<asp:TextBox ID="txtHome" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label9" runat="server" Text="work Phone"></asp:Label>
<asp:TextBox ID="txtWork" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label10" runat="server" Text="email"></asp:Label>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label11" runat="server" Text="Donation Amount"></asp:Label>
<asp:TextBox ID="txtDonation" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />
<asp:Label
ID="lblalerts" runat="server" Text=""></asp:Label>
</div>
</form>
</body>
</html>
Here is the c# code 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 the database
//pass it some command any parameters
//deal with results
SqlConnection connect = new SqlConnection
("Data source=localhost;initial catalog=communityAssist;integrated security=true");
SqlCommand cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "donor.usp_RegisterDonor";
cmd.Parameters.AddWithValue("@Lastname", txtLastName.Text);
cmd.Parameters.AddWithValue("@firstname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@Street", txtStreet.Text);
cmd.Parameters.AddWithValue("@Apartment", txtApartment.Text);
cmd.Parameters.AddWithValue("@City", txtCity.Text);
cmd.Parameters.AddWithValue("@State", txtState.Text);
cmd.Parameters.AddWithValue("@Zip", txtZip.Text);
cmd.Parameters.AddWithValue("@DonationAmount", double.Parse(txtDonation.Text));
if (txtHome.Text != "")
{
cmd.Parameters.AddWithValue("@HomePhone", txtHome.Text);
}
if (txtWork.Text != "")
{
cmd.Parameters.AddWithValue("@WorkPhone", txtWork.Text);
}
if (txtEmail.Text != "")
{
cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
}
int result;
connect.Open();
result=(int)cmd.ExecuteNonQuery();
connect.Close();
if (result > 0)
{
lblalerts.Text = "Thank you for your donation";
}
else
{
lblalerts.Text = "there are problems with your donation";
}
}
}
<%@ 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>
<h1>Make a Donation</h1>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="First Name"></asp:Label>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label2" runat="server" Text="Last Name"></asp:Label>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="label3" runat="server" Text="Street"></asp:Label>
<asp:TextBox ID="txtStreet" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label4" runat="server" Text="Apartment"></asp:Label>
<asp:TextBox ID="txtApartment" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label5" runat="server" Text="City"></asp:Label>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label6" runat="server" Text="State"></asp:Label>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label7" runat="server" Text="Zip code"></asp:Label>
<asp:TextBox ID="txtZip" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label8" runat="server" Text="home phone"></asp:Label>
<asp:TextBox ID="txtHome" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label9" runat="server" Text="work Phone"></asp:Label>
<asp:TextBox ID="txtWork" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label10" runat="server" Text="email"></asp:Label>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label11" runat="server" Text="Donation Amount"></asp:Label>
<asp:TextBox ID="txtDonation" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />
<asp:Label
ID="lblalerts" runat="server" Text=""></asp:Label>
</div>
</form>
</body>
</html>
Here is the c# code 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 the database
//pass it some command any parameters
//deal with results
SqlConnection connect = new SqlConnection
("Data source=localhost;initial catalog=communityAssist;integrated security=true");
SqlCommand cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "donor.usp_RegisterDonor";
cmd.Parameters.AddWithValue("@Lastname", txtLastName.Text);
cmd.Parameters.AddWithValue("@firstname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@Street", txtStreet.Text);
cmd.Parameters.AddWithValue("@Apartment", txtApartment.Text);
cmd.Parameters.AddWithValue("@City", txtCity.Text);
cmd.Parameters.AddWithValue("@State", txtState.Text);
cmd.Parameters.AddWithValue("@Zip", txtZip.Text);
cmd.Parameters.AddWithValue("@DonationAmount", double.Parse(txtDonation.Text));
if (txtHome.Text != "")
{
cmd.Parameters.AddWithValue("@HomePhone", txtHome.Text);
}
if (txtWork.Text != "")
{
cmd.Parameters.AddWithValue("@WorkPhone", txtWork.Text);
}
if (txtEmail.Text != "")
{
cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
}
int result;
connect.Open();
result=(int)cmd.ExecuteNonQuery();
connect.Close();
if (result > 0)
{
lblalerts.Text = "Thank you for your donation";
}
else
{
lblalerts.Text = "there are problems with your donation";
}
}
}
Wednesday, July 14, 2010
File IO
Here is the file input and output stuff. I will post a script for loops and if then statements later
IOStuff.cpp
#include <iostream>
#include <fstream>//for file stream
#include <string>
#include <cstdlib>
using namespace std;
void WriteFile();
void ReadFile();
int main()
{
WriteFile();
ReadFile();
char c;
cin >> c;
}
void WriteFile()
{
/* ofstream outFile;
outFile.open("C:\\users\\ItStudent\\desktop\\stuff.txt");*/
fstream outFile("C:\\users\\ItStudent\\desktop\\stuff.txt",
ios::out | ios::app);
cout << "How many items do you want to enter?" << endl;
int items;
cin >> items;
cin.get(); //
for(int i=1;i<=items;i++)
{
cout << "Enter the item name" << endl;
char line[100];
cin.getline(line, 100);
outFile << line << "\t";
//cin.get();
cout << "Enter the price" << endl;
double price=0;
cin >> price;
outFile << price << endl;
cin.get();
}
outFile.close();
}
void ReadFile()
{
// ifstream inFile();
//inFile.open("C:\\users\\ItStudent\\desktop\\stuff.txt");
fstream inFile("C:\\users\\ItStudent\\desktop\\stuff.txt",
ios::in);
string line;
if (inFile.is_open())
{
while(!inFile.eof())
{
getline(inFile,line);
cout << line << endl;
}
inFile.close();
}
else
{
cout << "Could not open the file" << endl;
}
}
IOStuff.cpp
#include <iostream>
#include <fstream>//for file stream
#include <string>
#include <cstdlib>
using namespace std;
void WriteFile();
void ReadFile();
int main()
{
WriteFile();
ReadFile();
char c;
cin >> c;
}
void WriteFile()
{
/* ofstream outFile;
outFile.open("C:\\users\\ItStudent\\desktop\\stuff.txt");*/
fstream outFile("C:\\users\\ItStudent\\desktop\\stuff.txt",
ios::out | ios::app);
cout << "How many items do you want to enter?" << endl;
int items;
cin >> items;
cin.get(); //
for(int i=1;i<=items;i++)
{
cout << "Enter the item name" << endl;
char line[100];
cin.getline(line, 100);
outFile << line << "\t";
//cin.get();
cout << "Enter the price" << endl;
double price=0;
cin >> price;
outFile << price << endl;
cin.get();
}
outFile.close();
}
void ReadFile()
{
// ifstream inFile();
//inFile.open("C:\\users\\ItStudent\\desktop\\stuff.txt");
fstream inFile("C:\\users\\ItStudent\\desktop\\stuff.txt",
ios::in);
string line;
if (inFile.is_open())
{
while(!inFile.eof())
{
getline(inFile,line);
cout << line << endl;
}
inFile.close();
}
else
{
cout << "Could not open the file" << endl;
}
}
Tuesday, July 13, 2010
Partitioning
Here, is a link to Zhong's page partition info
and here is the example script:
partitioning
create schema Archive
go
Alter database CommunityAssist
Add FileGroup F1
Alter database CommunityAssist
Add FileGroup F2
Alter database CommunityAssist
Add Filegroup F3
Go
Alter Database CommunityAssist
Add File
(Name=FG1_da, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CommunityAssist_3.ndf',
size=2MB)
to fileGroup F1
Alter Database CommunityAssist
Add File
(Name=FG_da, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CommunityAssist_4.ndf',
size=2MB)
to fileGroup F2
Alter Database CommunityAssist
Add File
(Name=FG3_da, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CommunityAssist_5.ndf',
size=2MB)
to fileGroup F3
GO
Create partition function ServiceGrantFunc (datetime)
AS
Range Right
For Values('1/1/2010','1/1/2011')
Go
Create partition scheme ServiceGrantScheme
As
Partition ServiceGrantFunc
To (F1,F2,f3)
Go
Create table Archive.ServiceGrant
(
Grantkey int not null,
GrantAmount money,
GrantDate datetime not null,
PersonKey int,
ServiceKey int,
EmployeeKey int)
on ServiceGrantScheme(GrantDate)
Go
Create clustered index ix_GrantDate
on Archive.ServiceGrant(GrantDate)
on ServiceGrantScheme(GrantDate)
Go
Alter table Archive.ServiceGrant
add constraint PK_GrantKey Primary Key nonclustered(GrantKey,GrantDate)
Insert into Archive.ServiceGrant(GrantKey,GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey)
Values(1,500,'2/3/2009',1,2,1),
(2,100,'2/3/2011',1,2,1),
(3,50,'4/1/2010',1,2,1)
Select * from Archive.ServiceGrant
where $partition.ServiceGrantFunc(GrantDate)=1
Select * from Archive.ServiceGrant
where $partition.ServiceGrantFunc(GrantDate)=2
Select * from Archive.ServiceGrant
where $partition.ServiceGrantFunc(GrantDate)=3
and here is the example script:
partitioning
create schema Archive
go
Alter database CommunityAssist
Add FileGroup F1
Alter database CommunityAssist
Add FileGroup F2
Alter database CommunityAssist
Add Filegroup F3
Go
Alter Database CommunityAssist
Add File
(Name=FG1_da, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CommunityAssist_3.ndf',
size=2MB)
to fileGroup F1
Alter Database CommunityAssist
Add File
(Name=FG_da, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CommunityAssist_4.ndf',
size=2MB)
to fileGroup F2
Alter Database CommunityAssist
Add File
(Name=FG3_da, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CommunityAssist_5.ndf',
size=2MB)
to fileGroup F3
GO
Create partition function ServiceGrantFunc (datetime)
AS
Range Right
For Values('1/1/2010','1/1/2011')
Go
Create partition scheme ServiceGrantScheme
As
Partition ServiceGrantFunc
To (F1,F2,f3)
Go
Create table Archive.ServiceGrant
(
Grantkey int not null,
GrantAmount money,
GrantDate datetime not null,
PersonKey int,
ServiceKey int,
EmployeeKey int)
on ServiceGrantScheme(GrantDate)
Go
Create clustered index ix_GrantDate
on Archive.ServiceGrant(GrantDate)
on ServiceGrantScheme(GrantDate)
Go
Alter table Archive.ServiceGrant
add constraint PK_GrantKey Primary Key nonclustered(GrantKey,GrantDate)
Insert into Archive.ServiceGrant(GrantKey,GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey)
Values(1,500,'2/3/2009',1,2,1),
(2,100,'2/3/2011',1,2,1),
(3,50,'4/1/2010',1,2,1)
Select * from Archive.ServiceGrant
where $partition.ServiceGrantFunc(GrantDate)=1
Select * from Archive.ServiceGrant
where $partition.ServiceGrantFunc(GrantDate)=2
Select * from Archive.ServiceGrant
where $partition.ServiceGrantFunc(GrantDate)=3
Full stored procedure
Here is the full stored procedure, plus a second one that returns all donor information and a query that total donation amounts. We talked about doing a stored procedure to handle logins.
I will also post a more complete descriptions of the assignments on the syllabus before thurdsay's class.
Stored Procedure for entering donors
/******************************
Allow a donor to enter a donation
If the donor does not exist in the database
Then add the donor
If the donor does exist
We update the information
either way we take the donation
adding the donor:
inserting into Person
Get the person key
Insert into address
Check if home phone is not null
if not null insert into Person contact
Check if workphone is not null
If not null insert into personcontact
Check if email not null
if not null insert into personcontact
Insert into donation
if they do exist in database
only
Enter the donation
****************************/
--the actual procedure
Alter Proc Donor.usp_RegisterDonor
--set up the parameters
@lastname nvarchar (255),
@firstname nvarchar (255),
@Street nvarchar(255),
@Apartment nvarchar(255),
@city nvarchar(255),
@state nvarchar(2) ,
@zip nvarchar(10) ,
@homephone nvarchar(255) ,
@workphone nvarchar(255),
@email nvarchar(255),
@donationAmount money
AS --beginning of procedure body
--declare internal variables
Declare @personkey int
Declare @donationDate Datetime
Set @donationDate=GETDATE()
If Exists
(Select Firstname, lastname from Person
where LastName=@lastname and FirstName=@firstname)
Begin --begin if true
--get the existing person key
Select @personkey=Personkey from Person
Where LastName=@lastname and FirstName=@firstname
End --end if true
Else--if no exists
Begin --begins the else
--insert into person
Begin tran -- beginning of transaction
Begin try
Insert into Person(FirstName, LastName)
Values(@firstname, @Lastname)
--get the new personkey
Select @personkey =ident_Current('Person')
--insert into person Address
Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@Street, @Apartment,@state,@city,@zip,@personkey)
--insert if not null
if @homephone is not null
Begin
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values (@homephone,@personkey,1)
End
if @Workphone is not null
Begin
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values (@Workphone,@personkey,2)
End
if @email is not null
Begin
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values (@email,@personkey,6)
End --end if null
Commit tran
end try--of try
Begin Catch
Rollback tran
return
End catch --end catch
End --end of the else
--do this insert no matter what
Insert into Donation(DonationDate, DonationAmount, PersonKey)
Values(@donationDate, @donationAmount, @personkey)
Test for the store procedure
--test 1
exec Donor.usp_RegisterDonor
@lastname = 'Flinstone',
@firstname ='Fred',
@Street='201 Granite way',
@Apartment= null,
@city ='Bedrock',
@state ='WA',
@zip ='98188' ,
@homephone='3604441234',
@workphone='3601212555',
@email ='ff@gmail.com',
@donationAmount =500
looking to see if the procedure did as advertised
Select * from Person
Select * from PersonAddress where PersonKey=56
Select * from PersonContact where PersonKey=56
Select * from donation where PersonKey=56
Go
procedure for donation information
Create procedure donor.Owninfo
@lastname nvarchar(255)
As
Select Lastname, firstname, street, City, [State], Zip, contactInfo,
DonationDate,DonationAmount
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
inner join Donation d
on p.PersonKey=d.PersonKey
where LastName=@lastName
Test the procedure
exec Donor.OwnInfo 'Jetson'
A query to get the total donations for each donor
Select p.PersonKey, lastname, firstname, SUM(donationAmount) as [Total donation]
From Donation d
inner join person p
on p.personkey=d.Personkey
Group by p.PersonKey, lastname, firstname
I will also post a more complete descriptions of the assignments on the syllabus before thurdsay's class.
Stored Procedure for entering donors
/******************************
Allow a donor to enter a donation
If the donor does not exist in the database
Then add the donor
If the donor does exist
We update the information
either way we take the donation
adding the donor:
inserting into Person
Get the person key
Insert into address
Check if home phone is not null
if not null insert into Person contact
Check if workphone is not null
If not null insert into personcontact
Check if email not null
if not null insert into personcontact
Insert into donation
if they do exist in database
only
Enter the donation
****************************/
--the actual procedure
Alter Proc Donor.usp_RegisterDonor
--set up the parameters
@lastname nvarchar (255),
@firstname nvarchar (255),
@Street nvarchar(255),
@Apartment nvarchar(255),
@city nvarchar(255),
@state nvarchar(2) ,
@zip nvarchar(10) ,
@homephone nvarchar(255) ,
@workphone nvarchar(255),
@email nvarchar(255),
@donationAmount money
AS --beginning of procedure body
--declare internal variables
Declare @personkey int
Declare @donationDate Datetime
Set @donationDate=GETDATE()
If Exists
(Select Firstname, lastname from Person
where LastName=@lastname and FirstName=@firstname)
Begin --begin if true
--get the existing person key
Select @personkey=Personkey from Person
Where LastName=@lastname and FirstName=@firstname
End --end if true
Else--if no exists
Begin --begins the else
--insert into person
Begin tran -- beginning of transaction
Begin try
Insert into Person(FirstName, LastName)
Values(@firstname, @Lastname)
--get the new personkey
Select @personkey =ident_Current('Person')
--insert into person Address
Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@Street, @Apartment,@state,@city,@zip,@personkey)
--insert if not null
if @homephone is not null
Begin
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values (@homephone,@personkey,1)
End
if @Workphone is not null
Begin
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values (@Workphone,@personkey,2)
End
if @email is not null
Begin
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values (@email,@personkey,6)
End --end if null
Commit tran
end try--of try
Begin Catch
Rollback tran
return
End catch --end catch
End --end of the else
--do this insert no matter what
Insert into Donation(DonationDate, DonationAmount, PersonKey)
Values(@donationDate, @donationAmount, @personkey)
Test for the store procedure
--test 1
exec Donor.usp_RegisterDonor
@lastname = 'Flinstone',
@firstname ='Fred',
@Street='201 Granite way',
@Apartment= null,
@city ='Bedrock',
@state ='WA',
@zip ='98188' ,
@homephone='3604441234',
@workphone='3601212555',
@email ='ff@gmail.com',
@donationAmount =500
looking to see if the procedure did as advertised
Select * from Person
Select * from PersonAddress where PersonKey=56
Select * from PersonContact where PersonKey=56
Select * from donation where PersonKey=56
Go
procedure for donation information
Create procedure donor.Owninfo
@lastname nvarchar(255)
As
Select Lastname, firstname, street, City, [State], Zip, contactInfo,
DonationDate,DonationAmount
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
inner join Donation d
on p.PersonKey=d.PersonKey
where LastName=@lastName
Test the procedure
exec Donor.OwnInfo 'Jetson'
A query to get the total donations for each donor
Select p.PersonKey, lastname, firstname, SUM(donationAmount) as [Total donation]
From Donation d
inner join person p
on p.personkey=d.Personkey
Group by p.PersonKey, lastname, firstname
Thursday, July 8, 2010
Donor Stored Procedure phase One
--We created this table for later use
Create table UserLogin
(
UserLoginKey int identity(1,1) primary key,
email nvarchar(255) unique not null,
pwd nvarchar(15) not null
)
go
/*********************************************
* the procedure will register a new donor and
* get the donation
*Check to see if person is in the database
* if they are, update existing information
*If not then enter all the registration information
*Check contact info to determine which ones to write
note for later create a trigger that writes updating information to
a check table
********************************************/
--if the donor schema doesn't exist make it
Create schema Donor
go
--the actual procedure
Create Proc Donor.usp_RegisterDonor
--set up the parameters
@lastname nvarchar (255),
@firstname nvarchar (255),
@Street nvarchar(255),
@Apartment nvarchar(255),
@city nvarchar(255),
@state nvarchar(2) ,
@zip nvarchar(10) ,
@homephone nvarchar(255) ,
@workphone nvarchar(255),
@email nvarchar(255),
@donationAmount money
AS --beginning of procedure body
--declare internal variables
Declare @personkey int
Declare @donationDate Datetime
Set @donationDate=GETDATE()
--insert into person
Insert into Person(FirstName, LastName)
Values(@firstname, @Lastname)
--get the new personkey
Select @personkey =ident_Current('Person')
Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@Street, @Apartment,@state,@city,@zip,@personkey)
--test 1
exec Donor.usp_RegisterDonor
@lastname = 'Jetson',
@firstname ='George',
@Street='10010 North',
@Apartment= null,
@city ='Seattle',
@state ='WA',
@zip ='98188' ,
@homephone=null ,
@workphone=null,
@email =null,
@donationAmount =0
Select * from person
Select * from PersonAddress where PersonKey=52
Create table UserLogin
(
UserLoginKey int identity(1,1) primary key,
email nvarchar(255) unique not null,
pwd nvarchar(15) not null
)
go
/*********************************************
* the procedure will register a new donor and
* get the donation
*Check to see if person is in the database
* if they are, update existing information
*If not then enter all the registration information
*Check contact info to determine which ones to write
note for later create a trigger that writes updating information to
a check table
********************************************/
--if the donor schema doesn't exist make it
Create schema Donor
go
--the actual procedure
Create Proc Donor.usp_RegisterDonor
--set up the parameters
@lastname nvarchar (255),
@firstname nvarchar (255),
@Street nvarchar(255),
@Apartment nvarchar(255),
@city nvarchar(255),
@state nvarchar(2) ,
@zip nvarchar(10) ,
@homephone nvarchar(255) ,
@workphone nvarchar(255),
@email nvarchar(255),
@donationAmount money
AS --beginning of procedure body
--declare internal variables
Declare @personkey int
Declare @donationDate Datetime
Set @donationDate=GETDATE()
--insert into person
Insert into Person(FirstName, LastName)
Values(@firstname, @Lastname)
--get the new personkey
Select @personkey =ident_Current('Person')
Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@Street, @Apartment,@state,@city,@zip,@personkey)
--test 1
exec Donor.usp_RegisterDonor
@lastname = 'Jetson',
@firstname ='George',
@Street='10010 North',
@Apartment= null,
@city ='Seattle',
@state ='WA',
@zip ='98188' ,
@homephone=null ,
@workphone=null,
@email =null,
@donationAmount =0
Select * from person
Select * from PersonAddress where PersonKey=52
Wednesday, July 7, 2010
Pointers and Structures
Here is the code for pointers we did in class:
pointerStuff.cpp
Here is the code for the Structure and enum
pointerStuff.cpp
#include <iostream>
#include <string> //need this for the structure
using namespace std;
//function prototypes
void SimplePointers();
void ArrayPointers();
void DynamicArray();
void StructurePointer();
int main()
{
//to run one at a time simply
//uncomment it and comment the others
//SimplePointers();
//ArrayPointers();
//DynamicArray();
StructurePointer();
char c;
cin >> c;
}
void SimplePointers()
{
int number=23; //declare a variable
//create a pointer that points to
//the address of that variable
int * pNumber=&number;
//show the number and its address
cout << "number is " << number
<< " the address is " << &number << endl;
//do the same using the pointer
//the pointer stores the address not the value
//using the * "dereferences the pointer
//letting you see the value that is stored
//at that address
cout << "pNumber is " << pNumber
<< " *pNumber is " << *pNumber << endl;
//add 3 to number
number += 3;
//the pointer sees the change in value
cout << " now pNumber is " << pNumber
<< " *pNumber is " << pNumber << endl;
/* pNumber += 2; //this moves the address two integer
//address spaces over
//no longer points to number
cout << " now pNumber is " << pNumber
<< " *pNumber is " << pNumber <<
" number = " << number << endl;*/
*pNumber += 2; //changes number value
cout << " now pNumber is " << pNumber
<< " *pNumber is " << *pNumber <<
" number = " << number << endl;
}
void ArrayPointers()
{
//create a simple array of integers
int myArray[]={2,4,7,3,2};
//create a pointer to the array
//a pointer to an array stores the memory
//address of the first member of the array
int *pArray=myArray;
cout << "*pArray is " << *pArray
<< " the Address of the first element is" << pArray
<< endl;
//move the address over two places
// and then dereference it *(pArray+2)
//to display the third value
cout << "The third element is " << *(pArray+2) << endl;
}
void DynamicArray()
{
/*********************************
a dynamic array declared with the new
keyword allows you to set the size of the
array at run time, somthing that is otherwise
forbidden
************************************/
int size;
cout << "Enter how many elements you want " << endl;
cin >> size;
//dymamically allocate memory to the array
int *pDynamic = new int[size];
//loop through the array entering values
for(int i=0;i < size; i++)
{
cout << "enter a value: ";
cin >> pDynamic[i];
}
cout << "************************" << endl;
//loop through it again displaying
//the stored values
for(int i=0;i < size; i++)
{
cout << pDynamic[i] << endl;
}
//remove the dynamically assigned memory
delete pDynamic;
}
void StructurePointer()
{
//create a simple structure
struct name
{
string firstName;
string mI;
string lastName;
};
//create a dynamic pointer to the structure
name *pName = new name;
//assign it some values
cout << "Enter your first name " ;
//the -> is a membership operator
//firstname is a meber of pName
cin >> pName->firstName;
cout << endl;
cout << "Enter your middle initial " ;
cin >> pName ->mI;
cout << endl;
cout << "Enter your last name" ;
//another way to get to a member element
cin >> (*pName).lastName;
cout << endl;
//output the content of the structure
cout << "Welcome " << pName -> firstName << " "
<< pName ->mI << " " << (*pName).lastName
<< endl;
//delete the dynamically assigned memory
delete pName;
}
Here is the code for the Structure and enum
#include <iostream>
#include <string>
using namespace std;
//create an enum and assign values
enum color
{ red=100, green=300, blue=400};
//create a simple structure
struct product
{
string productName;
double price;
};
int main()
{
//declare and assign values to
//the structure
product book=
{
"Hitchhiker's Guide to the Galaxy",
7.45
};
//output the content of the structure
cout << book.productName << " costs $"
<< book.price << endl;
//declare a new instance of the enum
color band;
//assign a value to the instance
band=blue;
//assign that to an integer
int x = band;
//output the integer
cout << x << endl;
//creat an array of the product structure
product books[4]=
{
{"Zombi apocolypse", 14.95},
{"Neverwhere", 9.98},
{"The Martian Chronicles", 7.98},
{"Ulysses", 15.35}
};
//declare the variable sum and initialize it to 0;
double sum=0;
//use a for loop to loop through the structure
//array showing all its contents
for (int i=0;i<4;i++)
{
cout << books[i].productName << ", "
<< books[i].price << endl;
//total the prices of all the books
sum+=books[i].price;
}
//display the total
cout << "The books total value is " << sum << endl;
char c;
cin >> c;
}
Tuesday, July 6, 2010
Donor Schema
Here are the notes we did in class for the Donor Schema. The task is to identify all the stored procedures and views for a given schema, in your case the employee schema. The goal is to never let the users actually touch the underlying tables.
The other thing to keep in mind is that there must be a stored procedure for every action the user would perform on the database.
lastly, create a preliminary set of tests for each procedure. First you should make sure it works as intended, then account for bad inputs and mis-use of the procedure. You don't have to create the procedures or run the tests at this point, though we will do it with some of the procedures in the near future.
Here are the unedited notes:
Donor
-Create a schema for donor
-View donor info --own info (stored procedure) pass a parmeter
test: Can see own information; Can't see somebody else; enter wrong user name
-View donation history
Test: (Does it work the way it is supposed to when everything is right. Think of everything that could be done wrongly)
-Register: stored Procedure (check to make sure not already registered)
-Generic login for donor schema
-login--Stored proc--specific
-Make a donation: Stored procedure ()
-Update own Donor Information : make updates
View Service and General Information
The other thing to keep in mind is that there must be a stored procedure for every action the user would perform on the database.
lastly, create a preliminary set of tests for each procedure. First you should make sure it works as intended, then account for bad inputs and mis-use of the procedure. You don't have to create the procedures or run the tests at this point, though we will do it with some of the procedures in the near future.
Here are the unedited notes:
Donor
-Create a schema for donor
-View donor info --own info (stored procedure) pass a parmeter
test: Can see own information; Can't see somebody else; enter wrong user name
-View donation history
Test: (Does it work the way it is supposed to when everything is right. Think of everything that could be done wrongly)
-Register: stored Procedure (check to make sure not already registered)
-Generic login for donor schema
-login--Stored proc--specific
-Make a donation: Stored procedure ()
-Update own Donor Information : make updates
View Service and General Information
Monday, July 5, 2010
Schema SQL
Here is the sql for creating a schema, a view belonging to that schema a log in and a user
create schema HumanResources
Create view HumanResources.vw_EmployeeInfo
As
Select
SSNumber,
LastName as [Last Name],
FirstName as [First Name],
Street,
Apartment,
ContactInfo,
HireDate,
Dependents
From Person p
inner Join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
inner join Employee e
on p.PersonKey=e.PersonKey
select * from HumanResources.vw_EmployeeInfo
Create login Sue with password='p@ssw0rd1'
Create user SueUser for login Sue with default_schema=HumanResources
Grant Select on Schema::HumanResources to SueUser
Now login as your user and test the view
Select * from vw_EmployeeInfo
create schema HumanResources
Create view HumanResources.vw_EmployeeInfo
As
Select
SSNumber,
LastName as [Last Name],
FirstName as [First Name],
Street,
Apartment,
ContactInfo,
HireDate,
Dependents
From Person p
inner Join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
inner join Employee e
on p.PersonKey=e.PersonKey
select * from HumanResources.vw_EmployeeInfo
Create login Sue with password='p@ssw0rd1'
Create user SueUser for login Sue with default_schema=HumanResources
Grant Select on Schema::HumanResources to SueUser
Now login as your user and test the view
Select * from vw_EmployeeInfo
Subscribe to:
Posts (Atom)