Use AdventureWorks
Insert into Customer(customerID,LastName, FirstName, ModifiedDate)
Values(30003,N'Κονγεροσ',N'Στεφενοσ', GETDATE())
Select * from Customer where CustomerID=30003
Thursday, July 28, 2011
Entering Non English Text into SQL Server
The trick for entering non English text into a record is to put an N before the single quote. this designates it as Unicode:
Use AdventureWorks
Insert into Customer(customerID,LastName, FirstName, ModifiedDate)
Values(30003,N'Κονγεροσ',N'Στεφενοσ', GETDATE())
Select * from Customer where CustomerID=30003
Use AdventureWorks
Insert into Customer(customerID,LastName, FirstName, ModifiedDate)
Values(30003,N'Κονγεροσ',N'Στεφενοσ', GETDATE())
Select * from Customer where CustomerID=30003
First LINQ Examples
We created a new empty web site. First we added a new web form. Then we added a new Link To SQL Classes. From the View Menu we added the Server Explorer. In it we right clicked on Data Connections and added a new Data connection to the Automart Database. Then we dragged the Employee, Location and Person tables onto the designer
To the web form we added a drop down list and a Grid view. Here is the code for the web page.
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>Employees</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
onselectedindexchanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList><br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
Here is the code behind, using the LINQ syntax. Notice its resemblance to SQL.
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
automartDataContext dc = new automartDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillDropDown();
}
}
protected void FillDropDown()
{
var local = from l in dc.Locations
orderby l.LocationName
select new { l.LocationName, l.LocationID };
DropDownList1.DataSource = local.ToList();
DropDownList1.DataTextField = "LocationName";
DropDownList1.DataValueField = "LocationID";
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, "Select Location");
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
var emp = from ee in dc.Employees
orderby ee.Person.LastName
where ee.LocationID==int.Parse(DropDownList1.SelectedValue.ToString())
select new { ee.Person.LastName, ee.Person.FirstName, ee.HireDate };
GridView1.DataSource = emp.ToList();
GridView1.DataBind();
}
}
Here is a picture of the program running:
To the web form we added a drop down list and a Grid view. Here is the code for the web page.
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>Employees</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
onselectedindexchanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList><br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
Here is the code behind, using the LINQ syntax. Notice its resemblance to SQL.
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
automartDataContext dc = new automartDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillDropDown();
}
}
protected void FillDropDown()
{
var local = from l in dc.Locations
orderby l.LocationName
select new { l.LocationName, l.LocationID };
DropDownList1.DataSource = local.ToList();
DropDownList1.DataTextField = "LocationName";
DropDownList1.DataValueField = "LocationID";
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, "Select Location");
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
var emp = from ee in dc.Employees
orderby ee.Person.LastName
where ee.LocationID==int.Parse(DropDownList1.SelectedValue.ToString())
select new { ee.Person.LastName, ee.Person.FirstName, ee.HireDate };
GridView1.DataSource = emp.ToList();
GridView1.DataBind();
}
}
Here is a picture of the program running:
Wednesday, July 27, 2011
First Class
Here is a simple class. We divided the class into two files, the header that defines the class structure with its private and public members and a cpp file that actually implements the class methods.
First the header file: (Note: we have more fields--class variables--than we actually use)
BankAccount.h
#include
class BankAccount
{
private:
//private fields
int accountID;
std::string name;
double deposit;
double withdrawal;
double balance;
double initialBalance;
public:
//overloaded constuctors
BankAccount(int, double);
BankAccount(double);
BankAccount(int, std::string, double);
//Get methods for returning values
int GetAccountID();
std::string GetName();
double GetBalance();
//sets to change values
void SetAccountID(int);
void SetName(std::string);
//member functions
void AddDeposit(double);
void SubtractWithdrawal(double);
};
Here is the implementation:
BankAccount.cpp
#include "BankAccount.h"
#include
//constructors
BankAccount::BankAccount(double iBalance)
{
initialBalance=iBalance;
}
BankAccount::BankAccount(int accID, double iBalance)
{
initialBalance=iBalance;
balance=initialBalance;
SetAccountID(accID);
}
BankAccount::BankAccount(int accID, std::string name, double iBalance)
{
initialBalance=iBalance;
balance=initialBalance;
SetAccountID(accID);
SetName(name);
}
//gets
int BankAccount::GetAccountID()
{
return accountID;
initialBalance=0;
}
std::string BankAccount::GetName()
{
return name;
}
double BankAccount::GetBalance()
{
return balance;
}
//sets
void BankAccount::SetAccountID(int accID)
{
accountID=accID;
}
void BankAccount::SetName(std::string aName)
{
name=aName;
}
void BankAccount::AddDeposit(double depositAmt)
{
balance += depositAmt;
}
void BankAccount::SubtractWithdrawal(double withdrwl)
{
if (balance-withdrwl >=0)
{
balance-= withdrwl;
}
}
And here is the program file
Program.cpp
#include <iostream>
#include "BankAccount.h"
#include <string>
using namespace std;
int main()
{
string aName="Jones";
BankAccount account1(1,aName,1000);
account1.AddDeposit(500);
account1.AddDeposit(250.75);
account1.SubtractWithdrawal(225.43);
cout << "for account " << account1.GetAccountID() << "belonging to "
<< account1.GetName() << "the current balance is " << account1.GetBalance()
<< endl;
char c;
cin >> c;
}
Monday, July 25, 2011
Script For Service Broker presentation
use master
GO
ALTER DATABASE Automart
SET ENABLE_BROKER;
USEAutomart
CREATE MESSAGE TYPE
[//AWDB/1DBSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
[//AWDB/1DBSample/ReplyMessage]
VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT [//AWDB/1DBSample/SampleContract]
([//AWDB/1DBSample/RequestMessage]
SENT BY INITIATOR,
[//AWDB/1DBSample/ReplyMessage]
SENT BY TARGET
);
CREATE QUEUE TargetQueue1DB;
CREATE SERVICE
[//AWDB/1DBSample/TargetService]
ON QUEUE TargetQueue1DB
([//AWDB/1DBSample/SampleContract]);
CREATE QUEUE InitiatorQueue1DB;
CREATE SERVICE
[//AWDB/1DBSample/InitiatorService]
ON QUEUE InitiatorQueue1DB;
USE Automart;
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
FROM SERVICE
[//AWDB/1DBSample/InitiatorService]
TO SERVICE
N'//AWDB/1DBSample/TargetService'
ON CONTRACT
[//AWDB/1DBSample/SampleContract]
WITH
ENCRYPTION = OFF;
SELECT @RequestMsg =N'Message for Target service. ';
SEND
ON CONVERSATION @InitDlgHandle
MESSAGE TYPE
[//AWDB/1DBSample/RequestMessage]
(@RequestMsg);
SELECT @RequestMsg AS SentRequestMsg;
COMMIT TRANSACTION;
GO
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;
BEGIN TRANSACTION;
WAITFOR
(
RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM TargetQueue1DB
),
TIMEOUT 1000;
SELECT @RecvReqMsg AS ReceivedRequestMsg;
IF @RecvReqMsgName = N'//AWDB/1DBSample/RequestMessage'
BEGIN
DECLARE @ReplyMsg NVARCHAR(100);
SELECT @ReplyMsg =
N'Message for Initiator service. ';
SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE
[//AWDB/1DBSample/ReplyMessage]
(@ReplyMsg);
END CONVERSATION @RecvReqDlgHandle;
END
SELECT @ReplyMsg AS SentReplyMsg;
COMMIT TRANSACTION;
GO
DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
WAITFOR
(
RECEIVE TOP(1)
@RecvReplyDlgHandle
= conversation_handle,
@RecvReplyMsg
= message_body
FROM InitiatorQueue1DB
),
TIMEOUT 1000;
END
CONVERSATION @RecvReplyDlgHandle;
SELECT
@RecvReplyMsg AS ReceivedReplyMsg;
COMMIT TRANSACTION;
GO
IF EXISTS (SELECT * FROM sys.services
WHERE name = N'//AWDB/1DBSample/TargetService')
DROP SERVICE [//AWDB/1DBSample/TargetService];
IF EXISTS (SELECT * FROM sys.service_queues
WHERE name = N'TargetQueue1DB')
DROP QUEUE TargetQueue1DB;
-- Drop the intitator queue and service if they already exist.
IF EXISTS (SELECT * FROM sys.services
WHERE name = N'//AWDB/1DBSample/InitiatorService')
DROP SERVICE [//AWDB/1DBSample/InitiatorService];
IF EXISTS (SELECT * FROM sys.service_queues
WHERE name = N'InitiatorQueue1DB')
DROP QUEUE InitiatorQueue1DB;
IF EXISTS (SELECT * FROM sys.service_contracts
WHERE name = N'//AWDB/1DBSample/SampleContract')
DROP CONTRACT[//AWDB/1DBSample/SampleContract];
IF EXISTS (SELECT * FROM sys.service_message_types
WHERE name =N'//AWDB/1DBSample/RequestMessage')
DROP MESSAGE TYPE [//AWDB/1DBSample/RequestMessage];
IF EXISTS (SELECT * FROM sys.service_message_types
WHERE name = N'//AWDB/1DBSample/ReplyMessage')
DROP MESSAGE TYPE [//AWDB/1DBSample/ReplyMessage];
GO
GO
ALTER DATABASE Automart
SET ENABLE_BROKER;
USEAutomart
CREATE MESSAGE TYPE
[//AWDB/1DBSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
[//AWDB/1DBSample/ReplyMessage]
VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT [//AWDB/1DBSample/SampleContract]
([//AWDB/1DBSample/RequestMessage]
SENT BY INITIATOR,
[//AWDB/1DBSample/ReplyMessage]
SENT BY TARGET
);
CREATE QUEUE TargetQueue1DB;
CREATE SERVICE
[//AWDB/1DBSample/TargetService]
ON QUEUE TargetQueue1DB
([//AWDB/1DBSample/SampleContract]);
CREATE QUEUE InitiatorQueue1DB;
CREATE SERVICE
[//AWDB/1DBSample/InitiatorService]
ON QUEUE InitiatorQueue1DB;
USE Automart;
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
FROM SERVICE
[//AWDB/1DBSample/InitiatorService]
TO SERVICE
N'//AWDB/1DBSample/TargetService'
ON CONTRACT
[//AWDB/1DBSample/SampleContract]
WITH
ENCRYPTION = OFF;
SELECT @RequestMsg =N'
SEND
ON CONVERSATION @InitDlgHandle
MESSAGE TYPE
[//AWDB/1DBSample/RequestMessage]
(@RequestMsg);
SELECT @RequestMsg AS SentRequestMsg;
COMMIT TRANSACTION;
GO
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;
BEGIN TRANSACTION;
WAITFOR
(
RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM TargetQueue1DB
),
TIMEOUT 1000;
SELECT @RecvReqMsg AS ReceivedRequestMsg;
IF @RecvReqMsgName = N'//AWDB/1DBSample/RequestMessage'
BEGIN
DECLARE @ReplyMsg NVARCHAR(100);
SELECT @ReplyMsg =
N'
SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE
[//AWDB/1DBSample/ReplyMessage]
(@ReplyMsg);
END CONVERSATION @RecvReqDlgHandle;
END
SELECT @ReplyMsg AS SentReplyMsg;
COMMIT TRANSACTION;
GO
DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
WAITFOR
(
RECEIVE TOP(1)
@RecvReplyDlgHandle
= conversation_handle,
@RecvReplyMsg
= message_body
FROM InitiatorQueue1DB
),
TIMEOUT 1000;
END
CONVERSATION @RecvReplyDlgHandle;
SELECT
@RecvReplyMsg AS ReceivedReplyMsg;
COMMIT TRANSACTION;
GO
IF EXISTS (SELECT * FROM sys.services
WHERE name = N'//AWDB/1DBSample/TargetService')
DROP SERVICE [//AWDB/1DBSample/TargetService];
IF EXISTS (SELECT * FROM sys.service_queues
WHERE name = N'TargetQueue1DB')
DROP QUEUE TargetQueue1DB;
-- Drop the intitator queue and service if they already exist.
IF EXISTS (SELECT * FROM sys.services
WHERE name = N'//AWDB/1DBSample/InitiatorService')
DROP SERVICE [//AWDB/1DBSample/InitiatorService];
IF EXISTS (SELECT * FROM sys.service_queues
WHERE name = N'InitiatorQueue1DB')
DROP QUEUE InitiatorQueue1DB;
IF EXISTS (SELECT * FROM sys.service_contracts
WHERE name = N'//AWDB/1DBSample/SampleContract')
DROP CONTRACT[//AWDB/1DBSample/SampleContract];
IF EXISTS (SELECT * FROM sys.service_message_types
WHERE name =N'//AWDB/1DBSample/RequestMessage')
DROP MESSAGE TYPE [//AWDB/1DBSample/RequestMessage];
IF EXISTS (SELECT * FROM sys.service_message_types
WHERE name = N'//AWDB/1DBSample/ReplyMessage')
DROP MESSAGE TYPE [//AWDB/1DBSample/ReplyMessage];
GO
Function pointer and templates
Here are a couple of silly functions that show the use of Function pointers and templates. Function pointers are pointers to functions with a particular signature. In our case the function points to any function that returns and integer and takes two integer values. Pointer functions are closely related to Delegates in C#.
Templates are called Generics in C# and Java. they are functions or classes whose data type is not determined until run time. Both of these tools can prove a valuable way to manage code for scalability and flexibility.
We used three files. First I will show the header file. This file contains all the function prototypes and the complete template function. You cannot separate a template function's implementation from its type declaration.
AdvancedFunctions.h
int Add(int, int);
int Subtract(int, int);
int Multiply(int, int);
int Divide(int, int);
void DoMath(int, int, int(*pfunction)(int, int));
template <typename any>
void Addition(any a, any b)
{
cout << a + b << endl;
}
Here is the Function definition file:
FunctionDefinitions.cpp
#include <iostream>
#include "AdvancedFunctions.h"
using namespace std;
int Add (int n1, int n2)
{
return n1+n2;
}
int Subtract(int n1,int n2)
{
return n1-n2;
}
int Multiply(int n1, int n2)
{
return n1*n2;
}
int Divide(int n1, int n2)
{
int quotient=0;
if (n2 !=0)
{
quotient=n1/n2;
}
return quotient;
}
void DoMath(int n1, int n2, int(*pfunction)(int , int ))
{
cout << pfunction(n1,n2) << endl;
}
And lastly, here is the Program file with the main:
Program.cpp
#include <iostream>
#include "AdvancedFunctions.h"
#include <string>
using namespace std;
int main()
{
DoMath(5,7,Add);
cout << endl;
DoMath(13,4,Subtract);
cout << endl;
DoMath(13,4,Multiply);
cout << endl;
DoMath(13,4,Divide);
Addition(6,7);
Addition(4.3345,13.567);
string x = "hello";
string y="World";
Addition(x,y);
char c;
cin >> c;
}
Templates are called Generics in C# and Java. they are functions or classes whose data type is not determined until run time. Both of these tools can prove a valuable way to manage code for scalability and flexibility.
We used three files. First I will show the header file. This file contains all the function prototypes and the complete template function. You cannot separate a template function's implementation from its type declaration.
AdvancedFunctions.h
int Add(int, int);
int Subtract(int, int);
int Multiply(int, int);
int Divide(int, int);
void DoMath(int, int, int(*pfunction)(int, int));
template <typename any>
void Addition(any a, any b)
{
cout << a + b << endl;
}
Here is the Function definition file:
FunctionDefinitions.cpp
#include <iostream>
#include "AdvancedFunctions.h"
using namespace std;
int Add (int n1, int n2)
{
return n1+n2;
}
int Subtract(int n1,int n2)
{
return n1-n2;
}
int Multiply(int n1, int n2)
{
return n1*n2;
}
int Divide(int n1, int n2)
{
int quotient=0;
if (n2 !=0)
{
quotient=n1/n2;
}
return quotient;
}
void DoMath(int n1, int n2, int(*pfunction)(int , int ))
{
cout << pfunction(n1,n2) << endl;
}
And lastly, here is the Program file with the main:
Program.cpp
#include <iostream>
#include "AdvancedFunctions.h"
#include <string>
using namespace std;
int main()
{
DoMath(5,7,Add);
cout << endl;
DoMath(13,4,Subtract);
cout << endl;
DoMath(13,4,Multiply);
cout << endl;
DoMath(13,4,Divide);
Addition(6,7);
Addition(4.3345,13.567);
string x = "hello";
string y="World";
Addition(x,y);
char c;
cin >> c;
}
Chapter 6_9
Here is the Chapter 6 9 assignment. Fist a screen shot of the program running then the text file:
4
Sam Stone
2000
Freida Class
100500
Tammy Tubbs
5000
Red Raptor
55000
Here is the code
#include <iostream>
#include<fstream>
#include <string>
using namespace std;
struct Contributors
{
string name;
double amount;
};
const double GRAND= 10000;
void GetContributors()
{
int number=0;
fstream patrons("C:\\Users\\sconger\\Desktop\\donors.txt", ios::in | ios::app);
patrons >> number;
cout << "The number of contriburtors is " << number << endl;
patrons.ignore();
Contributors * donorList = new Contributors[number];
while (!patrons.eof())
{
for(int i=0;i<number;i++)
{
getline(patrons,donorList[i].name);
patrons>>donorList[i].amount;
patrons.ignore();
}
patrons.ignore();
}
patrons.close();
cout << "Grand Patrons" << endl;
cout << "****************************"<<endl;
for(int i=0;i<number;i++)
{
if(donorList[i].amount >= GRAND)
{
cout << donorList[i].name << "\t\t" << donorList[i].amount << endl;
}
}
cout << "Patrons" << endl;
cout << "****************************"<<endl;
for(int i=0;i<number;i++)
{
if(donorList[i].amount < GRAND)
{
cout << donorList[i].name << "\t\t" << donorList[i].amount << endl;
}
}
delete[] donorList;
}
int main()
{
GetContributors();
char c;
cin >> c;
}
4
Sam Stone
2000
Freida Class
100500
Tammy Tubbs
5000
Red Raptor
55000
Here is the code
#include <iostream>
#include<fstream>
#include <string>
using namespace std;
struct Contributors
{
string name;
double amount;
};
const double GRAND= 10000;
void GetContributors()
{
int number=0;
fstream patrons("C:\\Users\\sconger\\Desktop\\donors.txt", ios::in | ios::app);
patrons >> number;
cout << "The number of contriburtors is " << number << endl;
patrons.ignore();
Contributors * donorList = new Contributors[number];
while (!patrons.eof())
{
for(int i=0;i<number;i++)
{
getline(patrons,donorList[i].name);
patrons>>donorList[i].amount;
patrons.ignore();
}
patrons.ignore();
}
patrons.close();
cout << "Grand Patrons" << endl;
cout << "****************************"<<endl;
for(int i=0;i<number;i++)
{
if(donorList[i].amount >= GRAND)
{
cout << donorList[i].name << "\t\t" << donorList[i].amount << endl;
}
}
cout << "Patrons" << endl;
cout << "****************************"<<endl;
for(int i=0;i<number;i++)
{
if(donorList[i].amount < GRAND)
{
cout << donorList[i].name << "\t\t" << donorList[i].amount << endl;
}
}
delete[] donorList;
}
int main()
{
GetContributors();
char c;
cin >> c;
}
Thursday, July 21, 2011
XML, XSLT and ASP.Net
Here is the example we did in class. First we created the XSLT document. The XSLT document is designed to take an XML document and transform it into some other kind of document--in our case an html document. Here is a picture of the web page running:
Here is the code for the XSLT:
memo.xslt
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
xmlns="http://www.w3.org/1999/xhtml" xmlns:m ="http://www.spconger.com/memo" >
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<h2>Automart Memo</h2>
<hr/>
<p>
<strong>To: </strong>
<xsl:value-of select="m:memo/m:header/m:to"/>
</p>
<p>
<strong>From: </strong>
<xsl:value-of select="m:memo/m:header/m:from"/>
</p>
<p>
<strong>Date: </strong>
<xsl:value-of select="m:memo/m:header/m:date"/>
</p>
<p>
<strong>About: </strong>
<xsl:value-of select="m:memo/m:header/m:about"/>
</p>
<hr/>
<xsl:for-each select="//m:memo/m:body/m:p">
<p>
<xsl:value-of select="."/>
</p>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
We started a new empty Web Site in visual Studio. We added a new web form and then added an existing item--the XSLT document. Here is the code for the Web Form. We added a GridView, a button and an XML control. For the Gridview we also added a new column which was a button, (type link, command Select, text Select) Most of the other markup for the web grid is just formatting and could be left out.
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>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" BackColor="White"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4">
<Columns>
<asp:ButtonField CommandName="Select" Text="Select" />
</Columns>
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="Get memo"
onclick="Button1_Click" />
<asp:Xml ID="Xml1" runat="server"></asp:Xml>
</div>
</form>
</body>
</html>
In the code behind we used basic ADO. We needed to Add the System.Data, System.Data.SqlClient and the System.Xml libraries in the using section.
First we create a connection object to connect to the database then a command object to pass sql to the server through the connection. A data reader reads the data into the Grid.
In the button we create a new command that selects the XML file for the selected memo and attaches it to the XML Control. We also call the XSLT file we had made.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data; //add these three libraries
using System.Data.SqlClient;
using System.Xml;
public partial class _Default : System.Web.UI.Page
{
SqlConnection connect;
protected void Page_Load(object sender, EventArgs e)
{
connect = new SqlConnection("Data Source=localhost;initial catalog=Automart;user=EmployeeLogin;password=pass");
string sql = "Select AutomartMemoID, MemoDescription from AutomartMemos";
SqlCommand cmd = new SqlCommand(sql, connect);
SqlDataReader reader = null;
connect.Open();
reader = cmd.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
reader.Dispose();
connect.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
XmlDocument xDoc = new XmlDocument();
string sql = "Select memo from AutomartMemos where AutomartMemoID=@ID";
SqlCommand cmd = new SqlCommand(sql, connect);
cmd.Parameters.AddWithValue("@ID", int.Parse(GridView1.SelectedRow.Cells[1].Text));
XmlReader xreader = null;
connect.Open();
xreader = cmd.ExecuteXmlReader();
xDoc.Load(xreader);
xreader.Close();
connect.Close();
Xml1.Document = xDoc;
Xml1.TransformSource = MapPath("memo.xslt");
}
}
Here is the code for the XSLT:
memo.xslt
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
xmlns="http://www.w3.org/1999/xhtml" xmlns:m ="http://www.spconger.com/memo" >
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<h2>Automart Memo</h2>
<hr/>
<p>
<strong>To: </strong>
<xsl:value-of select="m:memo/m:header/m:to"/>
</p>
<p>
<strong>From: </strong>
<xsl:value-of select="m:memo/m:header/m:from"/>
</p>
<p>
<strong>Date: </strong>
<xsl:value-of select="m:memo/m:header/m:date"/>
</p>
<p>
<strong>About: </strong>
<xsl:value-of select="m:memo/m:header/m:about"/>
</p>
<hr/>
<xsl:for-each select="//m:memo/m:body/m:p">
<p>
<xsl:value-of select="."/>
</p>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
We started a new empty Web Site in visual Studio. We added a new web form and then added an existing item--the XSLT document. Here is the code for the Web Form. We added a GridView, a button and an XML control. For the Gridview we also added a new column which was a button, (type link, command Select, text Select) Most of the other markup for the web grid is just formatting and could be left out.
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>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" BackColor="White"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4">
<Columns>
<asp:ButtonField CommandName="Select" Text="Select" />
</Columns>
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="Get memo"
onclick="Button1_Click" />
<asp:Xml ID="Xml1" runat="server"></asp:Xml>
</div>
</form>
</body>
</html>
In the code behind we used basic ADO. We needed to Add the System.Data, System.Data.SqlClient and the System.Xml libraries in the using section.
First we create a connection object to connect to the database then a command object to pass sql to the server through the connection. A data reader reads the data into the Grid.
In the button we create a new command that selects the XML file for the selected memo and attaches it to the XML Control. We also call the XSLT file we had made.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data; //add these three libraries
using System.Data.SqlClient;
using System.Xml;
public partial class _Default : System.Web.UI.Page
{
SqlConnection connect;
protected void Page_Load(object sender, EventArgs e)
{
connect = new SqlConnection("Data Source=localhost;initial catalog=Automart;user=EmployeeLogin;password=pass");
string sql = "Select AutomartMemoID, MemoDescription from AutomartMemos";
SqlCommand cmd = new SqlCommand(sql, connect);
SqlDataReader reader = null;
connect.Open();
reader = cmd.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
reader.Dispose();
connect.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
XmlDocument xDoc = new XmlDocument();
string sql = "Select memo from AutomartMemos where AutomartMemoID=@ID";
SqlCommand cmd = new SqlCommand(sql, connect);
cmd.Parameters.AddWithValue("@ID", int.Parse(GridView1.SelectedRow.Cells[1].Text));
XmlReader xreader = null;
connect.Open();
xreader = cmd.ExecuteXmlReader();
xDoc.Load(xreader);
xreader.Close();
connect.Close();
Xml1.Document = xDoc;
Xml1.TransformSource = MapPath("memo.xslt");
}
}
Wednesday, July 20, 2011
Function Overloading
A function can be "overloaded" by changing its signature, that is by changing the types or numbers of parameters. It is the data type that matters not the variable name. Additionally the order of the parameters does not count. So (double, int) is the same as (int, double) as far as the compiler is concerned.
We also used separated the function prototypes from the function bodies again using header files.
Here is the header file:
Payrollheader.h
double GetPay(double rate, int hours);
double GetPay(double salary);
double GetPay(double contractAmt, double percent);
Here is the function definition file
PayrollFunctions.cpp
double GetPay(double rate, int hours)
{
double pay=0;
if (hours >40)
{
pay=rate * (hours+((hours-40) * 1.5));
}
else
{
pay=rate*hours;
}
return pay;
}
double GetPay(double salary)
{
return salary / 52;
}
double GetPay(double contractAmt, double percent)
{
return contractAmt * percent;
}
And here is the program file which calls each overloaded version of the GetPay() function:
Program.cpp
#include <iostream>
#include "PayrollHeader.h"
using namespace std;
int main()
{
cout << "how many hours did you work? "<< endl;
int hours;
cin >> hours;
cout << "What was your hourly rate?"<<endl;
double rate;
cin >> rate;
cout << "Your weekly pay is " << GetPay(rate,hours) << endl;
cout << "*************************************" <<endl;
cout << "What is your annual salary?" << endl;
double sal;
cin >> sal;
cout << "Your weekly pay is " << GetPay(sal) <<endl;
cout << "*************************************" <<endl;
cout << "What is your total contract amount?" << endl;
double cAmt;
cin >>cAmt;
cout << "What percent is this week of your contract? " << endl;
double prct;
cin >> prct;
double pay=GetPay(cAmt,prct);
cout << "Your weekly pay is " << pay << endl;
char c;
cin >> c;
}
We also used separated the function prototypes from the function bodies again using header files.
Here is the header file:
Payrollheader.h
double GetPay(double rate, int hours);
double GetPay(double salary);
double GetPay(double contractAmt, double percent);
Here is the function definition file
PayrollFunctions.cpp
double GetPay(double rate, int hours)
{
double pay=0;
if (hours >40)
{
pay=rate * (hours+((hours-40) * 1.5));
}
else
{
pay=rate*hours;
}
return pay;
}
double GetPay(double salary)
{
return salary / 52;
}
double GetPay(double contractAmt, double percent)
{
return contractAmt * percent;
}
And here is the program file which calls each overloaded version of the GetPay() function:
Program.cpp
#include <iostream>
#include "PayrollHeader.h"
using namespace std;
int main()
{
cout << "how many hours did you work? "<< endl;
int hours;
cin >> hours;
cout << "What was your hourly rate?"<<endl;
double rate;
cin >> rate;
cout << "Your weekly pay is " << GetPay(rate,hours) << endl;
cout << "*************************************" <<endl;
cout << "What is your annual salary?" << endl;
double sal;
cin >> sal;
cout << "Your weekly pay is " << GetPay(sal) <<endl;
cout << "*************************************" <<endl;
cout << "What is your total contract amount?" << endl;
double cAmt;
cin >>cAmt;
cout << "What percent is this week of your contract? " << endl;
double prct;
cin >> prct;
double pay=GetPay(cAmt,prct);
cout << "Your weekly pay is " << pay << endl;
char c;
cin >> c;
}
Tuesday, July 19, 2011
Presentation links
Here are links to today's presentation and the presentations last Thursday:
Snapshot Presentation
Partition Presentation
Retrieving and Manipulating data
Snapshot Presentation
Partition Presentation
Retrieving and Manipulating data
XML schema and XML in SQL Server
Remember we made the xml file for the memos in Visual Studio and then used the xml menu build schema option to create the schema.
Here is the Xml Schema collection
Create xml schema collection automartmemos
as
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.spconger.com/memo" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="memo">
<xs:complexType>
<xs:sequence>
<xs:element name="header">
<xs:complexType>
<xs:sequence>
<xs:element name="to" type="xs:string" />
<xs:element name="from" type="xs:string" />
<xs:element name="about" type="xs:string" />
<xs:element name="date" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="body">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="p" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
Now we create a table that has a field that uses the xml data type. We bind that field to the xml schema collection that we have just created.
Create table AutomartMemos
(
AutomartMemoID int identity(1,1) primary key,
MemoDescription nvarchar(255),
Memo xml (dbo.automartmemos)
)
Now we insert into the database. The xml below will fail because the from and about elements are in reverse order. When it matches the structure of the xml document against the schema it will see the difference and throw an error.
Insert into AutomartMemos(MemoDescription, Memo)
Values('Too much of nothing',
'<?xml version="1.0" encoding="utf-8"?>
<memo xmlns="http://www.spconger.com/memo">
<header>
<to>everyone</to>
<about>nothing</about>
<from>me</from>
<date>7/19/2011</date>
</header>
<body>
<p>There is nothing going on today</p>
<p>But we have to appear busy</p>
</body>
</memo>')
Below is an example of xml raw with all the possible options. It is set to provide a root element named persons and a grouping element named person. After the SQL I show a small part of the output.
Select * From Person
for xml raw('person'), root('persons'),elements
<persons>
<person>
<Personkey>1</Personkey>
<LastName>Anderson</LastName>
<FirstName>Jay</FirstName>
</person>
<person>
<Personkey>2</Personkey>
<LastName>Zimmerman</LastName>
<FirstName>Toby</FirstName>
</person>
<person>
<Personkey>3</Personkey>
<LastName>Mann</LastName>
<FirstName>Louis</FirstName>
</person>
. . .
Here is an example of xml auto. It allows for deeper and more complex
hierarchies of xml. I am showing a fragment of the output.
Select LastName, firstname, LicenseNumber, VehicleMake, VehicleYear
From Person
inner join customer.vehicle
on Person.Personkey=Customer.Vehicle.PersonKey
order by Lastname
for xml auto, root('vehicles'), elements
<Person>
<LastName>Fabre</LastName>
<firstname>Jill</firstname>
<customer.vehicle>
<LicenseNumber>LKJ098</LicenseNumber>
<VehicleMake>Ford Freestar</VehicleMake>
<VehicleYear>2004</VehicleYear>
</customer.vehicle>
<customer.vehicle>
<LicenseNumber>OPO234</LicenseNumber>
<VehicleMake>Jeep Cherokee</VehicleMake>
<VehicleYear>1998</VehicleYear>
</customer.vehicle>
</Person>
Here is the Xml Schema collection
Create xml schema collection automartmemos
as
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.spconger.com/memo" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="memo">
<xs:complexType>
<xs:sequence>
<xs:element name="header">
<xs:complexType>
<xs:sequence>
<xs:element name="to" type="xs:string" />
<xs:element name="from" type="xs:string" />
<xs:element name="about" type="xs:string" />
<xs:element name="date" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="body">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="p" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
Now we create a table that has a field that uses the xml data type. We bind that field to the xml schema collection that we have just created.
Create table AutomartMemos
(
AutomartMemoID int identity(1,1) primary key,
MemoDescription nvarchar(255),
Memo xml (dbo.automartmemos)
)
Now we insert into the database. The xml below will fail because the from and about elements are in reverse order. When it matches the structure of the xml document against the schema it will see the difference and throw an error.
Insert into AutomartMemos(MemoDescription, Memo)
Values('Too much of nothing',
'<?xml version="1.0" encoding="utf-8"?>
<memo xmlns="http://www.spconger.com/memo">
<header>
<to>everyone</to>
<about>nothing</about>
<from>me</from>
<date>7/19/2011</date>
</header>
<body>
<p>There is nothing going on today</p>
<p>But we have to appear busy</p>
</body>
</memo>')
Below is an example of xml raw with all the possible options. It is set to provide a root element named persons and a grouping element named person. After the SQL I show a small part of the output.
Select * From Person
for xml raw('person'), root('persons'),elements
<persons>
<person>
<Personkey>1</Personkey>
<LastName>Anderson</LastName>
<FirstName>Jay</FirstName>
</person>
<person>
<Personkey>2</Personkey>
<LastName>Zimmerman</LastName>
<FirstName>Toby</FirstName>
</person>
<person>
<Personkey>3</Personkey>
<LastName>Mann</LastName>
<FirstName>Louis</FirstName>
</person>
. . .
Here is an example of xml auto. It allows for deeper and more complex
hierarchies of xml. I am showing a fragment of the output.
Select LastName, firstname, LicenseNumber, VehicleMake, VehicleYear
From Person
inner join customer.vehicle
on Person.Personkey=Customer.Vehicle.PersonKey
order by Lastname
for xml auto, root('vehicles'), elements
<Person>
<LastName>Fabre</LastName>
<firstname>Jill</firstname>
<customer.vehicle>
<LicenseNumber>LKJ098</LicenseNumber>
<VehicleMake>Ford Freestar</VehicleMake>
<VehicleYear>2004</VehicleYear>
</customer.vehicle>
<customer.vehicle>
<LicenseNumber>OPO234</LicenseNumber>
<VehicleMake>Jeep Cherokee</VehicleMake>
<VehicleYear>1998</VehicleYear>
</customer.vehicle>
</Person>
Monday, July 18, 2011
Functions Arrays and Header files
For these examples we pass Arrays as pointers to arrays. I also separate the function prototypes, the actual function definitions and the main into three files. The prototypes are put in a header file. the actual definitions are stored in a .cpp file, as is the main() function.
Both the function definitions and the main must have an include statement that includes the header file.
Here is the main file.
#include <iostream>
#include "ArrayFunction.h"
using namespace std;
int main()
{
CreateArray();
char c;
cin >> c;
}
Notice how simple and clean it is. It only calls the starting function. Here is the
header file:
ArrayFunction.h
void CreateArray();
void FillArray(int * arr, int size);
void DisplayArray(const int * arr, int size);
int SumArray(const int * arr, int size);
A couple of notes on the function prototypes. Notice we are passing pointers to an array called "arr." Whenever you pass an array as an argument you are in fact passing an pointer. This format just makes it explicit.
Also note the use the "const" keyword. It protects the array from being changed by the function it is passed to. I didn't do it for the FillArray() function because it needs to change the array.
Here is the file that has the actual function definitions:
FunctionArrays.cpp
#include <iostream>
#include "ArrayFunction.h"
#include <cmath>
#include <ctime>
using namespace std;
void CreateArray()
{
int arr[10];
FillArray(arr,10);
}
void FillArray(int * arr, int size)
{
srand(time(0));
for(int i=0;i<size;i++)
{
int number = rand();
arr[i]=number;
}
DisplayArray(arr, size);
}
int SumArray(const int * arr, int size)
{
int total=0;
for(int i=0;i<size;i++)
{
total += arr[i];
}
return total;
}
void DisplayArray(const int *arr, int size)
{
for (int i=0;i<size;i++)
{
cout << arr[i]<<endl;
}
cout << "\n\nthe total is: "<< SumArray(arr, size) << endl;
}
This kind of separation of prototype from definition adds to clarity and re-usability of the code. We will see more of this when we do classes.
Both the function definitions and the main must have an include statement that includes the header file.
Here is the main file.
#include <iostream>
#include "ArrayFunction.h"
using namespace std;
int main()
{
CreateArray();
char c;
cin >> c;
}
Notice how simple and clean it is. It only calls the starting function. Here is the
header file:
ArrayFunction.h
void CreateArray();
void FillArray(int * arr, int size);
void DisplayArray(const int * arr, int size);
int SumArray(const int * arr, int size);
A couple of notes on the function prototypes. Notice we are passing pointers to an array called "arr." Whenever you pass an array as an argument you are in fact passing an pointer. This format just makes it explicit.
Also note the use the "const" keyword. It protects the array from being changed by the function it is passed to. I didn't do it for the FillArray() function because it needs to change the array.
Here is the file that has the actual function definitions:
FunctionArrays.cpp
#include <iostream>
#include "ArrayFunction.h"
#include <cmath>
#include <ctime>
using namespace std;
void CreateArray()
{
int arr[10];
FillArray(arr,10);
}
void FillArray(int * arr, int size)
{
srand(time(0));
for(int i=0;i<size;i++)
{
int number = rand();
arr[i]=number;
}
DisplayArray(arr, size);
}
int SumArray(const int * arr, int size)
{
int total=0;
for(int i=0;i<size;i++)
{
total += arr[i];
}
return total;
}
void DisplayArray(const int *arr, int size)
{
for (int i=0;i<size;i++)
{
cout << arr[i]<<endl;
}
cout << "\n\nthe total is: "<< SumArray(arr, size) << endl;
}
This kind of separation of prototype from definition adds to clarity and re-usability of the code. We will see more of this when we do classes.
Thursday, July 14, 2011
Trigger, Stored Proc and some ASP.Net
--trigger
Create trigger Employee.tr_FiveTimeDiscount
on Employee.VehicleService
For Insert
As
Declare @VehicleID int
Declare @PersonID int
Declare @Count int
Select @VehicleID =VehicleID
From Inserted
Select @PersonID=PersonKey
From Customer.vehicle
Where VehicleId=@VehicleID
if exists
(Select RegisteredCustomerID
From Customer.RegisteredCustomer
Where PersonKey=@PersonID)
Begin
Select @Count=COUNT(VehicleServiceID)
From Employee.VehicleService vs
Inner Join Customer.Vehicle v
on v.VehicleId=vs.VehicleID
Where PersonKey=@PersonID
if @Count >= 5
Begin
print 'Congratulations, you qualify for an extra 10% discount'
End
End
Select v.Personkey, COUNT(VehicleServiceID)
From Employee.VehicleService vs
Inner Join Customer.vehicle v
on v.VehicleId=vs.VehicleID
Group by v.PersonKey
Select * from Customer.vehicle
Where PersonKey=24
Insert into Employee.VehicleService
(VehicleID, LocationID, ServiceDate, ServiceTime)
Values(17,1,GETDATE(),'10:45:00')
--login procedure
Go
Create proc Customer.usp_CustomerLogin
@email nvarchar(255),
@password nvarchar(20)
As
If exists
(Select RegisteredCustomerID
From customer.RegisteredCustomer
Where Email=@email
And CustomerPassword=@password)
Begin
Select PersonKey from customer.RegisteredCustomer
Where Email=@email
And CustomerPassword=@password
End
Alter proc Customer.TestGetVehicle
@PersonID int
As
Select * from vehicle
Where PersonKey=@personID
GO
<%@ 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>
<p>
<asp:Label ID="Label1" runat="server" Text="Enter Email"></asp:Label>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox> <br />
<asp:Label ID="Label2" runat="server" Text="Enter password"></asp:Label>
<asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox> <br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click" />
</p>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
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 btnSubmit_Click(object sender, EventArgs e)
{
int personkey=0;
SqlConnection connect = new SqlConnection
("Data source=localhost;initial catalog=Automart;user=CustomerLogin;password=pass");
SqlCommand cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Customer.usp_CustomerLogin";
cmd.Parameters.AddWithValue("@email", txtEmail.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
DataTable ds = new DataTable();
SqlDataReader reader = null;
connect.Open();
reader = cmd.ExecuteReader();
ds.Load(reader);
reader.Close();
connect.Close();
foreach (DataRow row in ds.Rows)
{
personkey = int.Parse(row["PersonKey"].ToString());
}
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = connect;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "Customer.TestGetVehicle";
cmd2.Parameters.AddWithValue("@PersonID", personkey);
SqlDataReader reader2 = null;
connect.Open();
reader2 = cmd2.ExecuteReader();
GridView1.DataSource = reader2;
GridView1.DataBind();
reader2.Dispose();
connect.Close();
}
}
Create trigger Employee.tr_FiveTimeDiscount
on Employee.VehicleService
For Insert
As
Declare @VehicleID int
Declare @PersonID int
Declare @Count int
Select @VehicleID =VehicleID
From Inserted
Select @PersonID=PersonKey
From Customer.vehicle
Where VehicleId=@VehicleID
if exists
(Select RegisteredCustomerID
From Customer.RegisteredCustomer
Where PersonKey=@PersonID)
Begin
Select @Count=COUNT(VehicleServiceID)
From Employee.VehicleService vs
Inner Join Customer.Vehicle v
on v.VehicleId=vs.VehicleID
Where PersonKey=@PersonID
if @Count >= 5
Begin
print 'Congratulations, you qualify for an extra 10% discount'
End
End
Select v.Personkey, COUNT(VehicleServiceID)
From Employee.VehicleService vs
Inner Join Customer.vehicle v
on v.VehicleId=vs.VehicleID
Group by v.PersonKey
Select * from Customer.vehicle
Where PersonKey=24
Insert into Employee.VehicleService
(VehicleID, LocationID, ServiceDate, ServiceTime)
Values(17,1,GETDATE(),'10:45:00')
--login procedure
Go
Create proc Customer.usp_CustomerLogin
@email nvarchar(255),
@password nvarchar(20)
As
If exists
(Select RegisteredCustomerID
From customer.RegisteredCustomer
Where Email=@email
And CustomerPassword=@password)
Begin
Select PersonKey from customer.RegisteredCustomer
Where Email=@email
And CustomerPassword=@password
End
Alter proc Customer.TestGetVehicle
@PersonID int
As
Select * from vehicle
Where PersonKey=@personID
GO
<%@ 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>
<p>
<asp:Label ID="Label1" runat="server" Text="Enter Email"></asp:Label>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox> <br />
<asp:Label ID="Label2" runat="server" Text="Enter password"></asp:Label>
<asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox> <br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click" />
</p>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
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 btnSubmit_Click(object sender, EventArgs e)
{
int personkey=0;
SqlConnection connect = new SqlConnection
("Data source=localhost;initial catalog=Automart;user=CustomerLogin;password=pass");
SqlCommand cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Customer.usp_CustomerLogin";
cmd.Parameters.AddWithValue("@email", txtEmail.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
DataTable ds = new DataTable();
SqlDataReader reader = null;
connect.Open();
reader = cmd.ExecuteReader();
ds.Load(reader);
reader.Close();
connect.Close();
foreach (DataRow row in ds.Rows)
{
personkey = int.Parse(row["PersonKey"].ToString());
}
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = connect;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "Customer.TestGetVehicle";
cmd2.Parameters.AddWithValue("@PersonID", personkey);
SqlDataReader reader2 = null;
connect.Open();
reader2 = cmd2.ExecuteReader();
GridView1.DataSource = reader2;
GridView1.DataBind();
reader2.Dispose();
connect.Close();
}
}
Wednesday, July 13, 2011
Loops,Selection, Simple File IO
#include <iostream>
#include <cmath>
#include <ctime>
#include <string>
#include <Windows.h>
#include <fstream>
/*
these are examples of loops
and branches
*/
using namespace std;
void SimpleForLoop();
void WhileLoop();
void DoLoops ();
void IfExamples();
void Menu();
void Pause();
void WriteFile();
void ReadFile();
int main()
{
//SimpleForLoop();
//WhileLoop();
//DoLoops();
//IfExamples();
Menu();
char c;
cin >> c;
}
void SimpleForLoop()
{
int myArray[5];
int i;
int total=0;
srand(time(0));
for(i =0; i<5; i++)
{
int number=rand();
myArray[i]=number;
}
for(int x=0; x<5; x++)
{
cout << myArray[x] <<endl;
total += myArray[x];
}
cout << "the total is " << total << " the Average is " << double(total) / i << endl;
}
void WhileLoop()
{
int i=1;
int number;
int total=0;
while( i != 0)
{
cout << "Enter an integer, 0 to exit" << endl;
cin >> i;
total += i;
}
cout << "Your total is " << total;
}
void DoLoops ()
{
int i = 0;
int total=0;
do
{
cout << "Enter an integer, 0 to exit" << endl;
cin >> i;
total += i;
}while(i !=0);
cout << "the total is " << total << " the Average is " << double(total) / i << endl;
}
void TwoDArray()
{
string myTwoDArray[3] [2] =
{
{"Twilight", "Meyers"},
{"Ulysses", "Joyce"},
{ "King Lear", "Shakespear"}
};
/*myTwoDArray[1,0]="Ulysses";
myTwoDArray[1,1]="Joyce";
myTwoDArray[2,0]="King Lear";
myTwoDArray[2,1]="Shakespear";*/
}
void IfExamples()
{
string message;
cout << "Enter the temperature" << endl;
int temp;
cin >> temp;
//or is ||
if (temp > -30 && temp < 0)
{
message="Way too cold";
}
else
if (temp >1 && temp < 20)
{
message ="Still too Cold";
}
else
if(temp >21 && temp < 50)
{
message="Cool";
}
else
if(temp >51 && temp < 80)
{
message="comfortable";
}
else
{
message="hot";
}
cout << message << endl;
}
void Menu()
{
int choice=999;
/* SimpleForLoop();
void WhileLoop();
void DoLoops ();
void IfExamples();
void Menu();*/
while (choice != 0)
{
system("cls");
cout << "Simple For Loop: 1" <<endl;
cout << "While Loops: 2" <<endl;
cout << "Do Loops: 3" << endl;
cout << "If Examples: 4" <<endl;
cout << "Write Example 5"<<endl;
cout << "Read File 6"<<endl;
cout <<"Exit: 0" << endl;
cout << "Enter your menu choice" << endl;
cin >> choice;
switch (choice)
{
case 1:
SimpleForLoop();
Pause();
break;
case 2:
WhileLoop();
Pause();
break;
case 3:
DoLoops();
Pause();
break;
case 4:
IfExamples();
Pause();
break;
case 5:
WriteFile();
Pause();
break;
case 6:
ReadFile();
Pause();
break;
case 0:
return;
default:
cout << "Enter a menu choice";
Pause();
}
}
}
void Pause()
{
cout<<"\n\nPress any key to continue";
char c;
cin >> c;
}
void WriteFile()
{
cin.get();
string message;
fstream outfile("C:\\Users\\sconger\\Desktop\\Test.txt",ios::out);
cout << "Enter your message" <<endl;
getline(cin, message);
outfile<<message;
outfile.close();
cout << "The file has been written" << endl;
}
void ReadFile()
{
cin.get();
string message;
fstream infile("C:\\Users\\sconger\\Desktop\\Test.txt",ios::in );
getline(infile,message);
cout << message << endl;
}
#include <cmath>
#include <ctime>
#include <string>
#include <Windows.h>
#include <fstream>
/*
these are examples of loops
and branches
*/
using namespace std;
void SimpleForLoop();
void WhileLoop();
void DoLoops ();
void IfExamples();
void Menu();
void Pause();
void WriteFile();
void ReadFile();
int main()
{
//SimpleForLoop();
//WhileLoop();
//DoLoops();
//IfExamples();
Menu();
char c;
cin >> c;
}
void SimpleForLoop()
{
int myArray[5];
int i;
int total=0;
srand(time(0));
for(i =0; i<5; i++)
{
int number=rand();
myArray[i]=number;
}
for(int x=0; x<5; x++)
{
cout << myArray[x] <<endl;
total += myArray[x];
}
cout << "the total is " << total << " the Average is " << double(total) / i << endl;
}
void WhileLoop()
{
int i=1;
int number;
int total=0;
while( i != 0)
{
cout << "Enter an integer, 0 to exit" << endl;
cin >> i;
total += i;
}
cout << "Your total is " << total;
}
void DoLoops ()
{
int i = 0;
int total=0;
do
{
cout << "Enter an integer, 0 to exit" << endl;
cin >> i;
total += i;
}while(i !=0);
cout << "the total is " << total << " the Average is " << double(total) / i << endl;
}
void TwoDArray()
{
string myTwoDArray[3] [2] =
{
{"Twilight", "Meyers"},
{"Ulysses", "Joyce"},
{ "King Lear", "Shakespear"}
};
/*myTwoDArray[1,0]="Ulysses";
myTwoDArray[1,1]="Joyce";
myTwoDArray[2,0]="King Lear";
myTwoDArray[2,1]="Shakespear";*/
}
void IfExamples()
{
string message;
cout << "Enter the temperature" << endl;
int temp;
cin >> temp;
//or is ||
if (temp > -30 && temp < 0)
{
message="Way too cold";
}
else
if (temp >1 && temp < 20)
{
message ="Still too Cold";
}
else
if(temp >21 && temp < 50)
{
message="Cool";
}
else
if(temp >51 && temp < 80)
{
message="comfortable";
}
else
{
message="hot";
}
cout << message << endl;
}
void Menu()
{
int choice=999;
/* SimpleForLoop();
void WhileLoop();
void DoLoops ();
void IfExamples();
void Menu();*/
while (choice != 0)
{
system("cls");
cout << "Simple For Loop: 1" <<endl;
cout << "While Loops: 2" <<endl;
cout << "Do Loops: 3" << endl;
cout << "If Examples: 4" <<endl;
cout << "Write Example 5"<<endl;
cout << "Read File 6"<<endl;
cout <<"Exit: 0" << endl;
cout << "Enter your menu choice" << endl;
cin >> choice;
switch (choice)
{
case 1:
SimpleForLoop();
Pause();
break;
case 2:
WhileLoop();
Pause();
break;
case 3:
DoLoops();
Pause();
break;
case 4:
IfExamples();
Pause();
break;
case 5:
WriteFile();
Pause();
break;
case 6:
ReadFile();
Pause();
break;
case 0:
return;
default:
cout << "Enter a menu choice";
Pause();
}
}
}
void Pause()
{
cout<<"\n\nPress any key to continue";
char c;
cin >> c;
}
void WriteFile()
{
cin.get();
string message;
fstream outfile("C:\\Users\\sconger\\Desktop\\Test.txt",ios::out);
cout << "Enter your message" <<endl;
getline(cin, message);
outfile<<message;
outfile.close();
cout << "The file has been written" << endl;
}
void ReadFile()
{
cin.get();
string message;
fstream infile("C:\\Users\\sconger\\Desktop\\Test.txt",ios::in );
getline(infile,message);
cout << message << endl;
}
Tuesday, July 12, 2011
Indexes
--Indexes
--Clustered -- physically orders the table--by default the
--primary key is a clustered index
--unclustered indexes which form a B-Tree
--unique indexes
Use CommunityAssist
--dropped the primary key and its clustered index
Alter table personAddress
Drop Constraint PK__PersonAd__7CE0EF7203317E3D
--Add a new unclusted primary key
Alter Table PersonAddress
Add Constraint PK_PersonAddress primary key nonclustered(PersonAddressKey)
--create a new unclustered index
Create clustered index Ix_ClusteredLastName on PersonAddress(PersonKey)
--not sure if any change can be seen
Select * From PersonAddress
--creating non clustered indexes
Create index ix_LastName on Person(lastName)
--non clustered index on multiple fields
Create index ix_cityState on PersonAddress(City, [State])
--creating a unique index--fails because non unique data
--in table
Create unique index ix_ContactInfo on PersonContact(ContactInfo)
--disable an index
Alter index ix_LastName on Person Disable
--re-enable and rebuild an index
Alter index ix_LastName on Person Rebuild
--select with forced use of indexes
--generally SQL's query optimizer will ignore
--indexes if the number of table rows is too small
Select Lastname, Firstname, street, city, [State], zip
From Person p with (index (ix_LastName))
inner join PersonAddress pa with (index (Ix_ClusteredLastName))
on p.PersonKey=pa.PersonKey
Where LastName='Smith'
--Clustered -- physically orders the table--by default the
--primary key is a clustered index
--unclustered indexes which form a B-Tree
--unique indexes
Use CommunityAssist
--dropped the primary key and its clustered index
Alter table personAddress
Drop Constraint PK__PersonAd__7CE0EF7203317E3D
--Add a new unclusted primary key
Alter Table PersonAddress
Add Constraint PK_PersonAddress primary key nonclustered(PersonAddressKey)
--create a new unclustered index
Create clustered index Ix_ClusteredLastName on PersonAddress(PersonKey)
--not sure if any change can be seen
Select * From PersonAddress
--creating non clustered indexes
Create index ix_LastName on Person(lastName)
--non clustered index on multiple fields
Create index ix_cityState on PersonAddress(City, [State])
--creating a unique index--fails because non unique data
--in table
Create unique index ix_ContactInfo on PersonContact(ContactInfo)
--disable an index
Alter index ix_LastName on Person Disable
--re-enable and rebuild an index
Alter index ix_LastName on Person Rebuild
--select with forced use of indexes
--generally SQL's query optimizer will ignore
--indexes if the number of table rows is too small
Select Lastname, Firstname, street, city, [State], zip
From Person p with (index (ix_LastName))
inner join PersonAddress pa with (index (Ix_ClusteredLastName))
on p.PersonKey=pa.PersonKey
Where LastName='Smith'
Functions and Views
--serviceprice is in Customer.Autoservice
--Discount percent is in Employee.VehicleService detail
--tax percent is in Employee.VehicleServiceDetail
--maybe three functions
--one for price with discounts for each service,
--one for tax
--one for total
--used with sums
--then a view that shows these
Select * from Customer.AutoService
Select * From Employee.VehicleServiceDetail
Go
Create function Employee.func_PriceWithDiscount
--parameters the user provides
(@ServiceID int, @Discount decimal(3,2))
returns money --datatype returned
As
Begin --begin function
--declare variables
Declare @price money
Declare @ServiceCost money
--Getting the price from the autoservice table
Select @price = ServicePrice from Customer.AutoService
Where AutoServiceID=@ServiceID
--calculating discount
Set @ServiceCost=@price - (@price * @Discount)
Return @ServiceCost
End
--using the Function
Select
vd.VehicleServiceID,ServiceDate,
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent))
as [Before Tax]
From Employee.VehicleServiceDetail vd
Inner Join Employee.VehicleService vs
on vs.VehicleServiceID=vd.VehicleServiceID
Where vd.VehicleServiceID=3
Group By vd.VehicleServiceID, ServiceDate
--using the function again
Select *, Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)
as [Before Tax]
From Employee.VehicleServiceDetail
where VehicleServiceID=3
Select * from Customer.AutoService where AutoServiceID=7
Go
--create the tax function
Create Function Employee.GetTax
(@subtotal money, @TaxPercent decimal(3,2))
Returns money
As
Begin
return @subtotal * @TaxPercent
End
Go
--create a view that uses the two functions to return the details
--of a transaction
Create View Employee.PriceDetail
As
Select VehicleServiceID,
Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) as Subtotal,
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)
as Tax,
Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)
as Total
From Employee.VehicleServiceDetail
Group by VehicleServiceID
Go
--create a view that shows the summary of a transaction
Create View Employee.PriceTotal
As
Select VehicleServiceID,
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)) as Subtotal,
Sum(Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Tax,
sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Total
From Employee.VehicleServiceDetail
Group by VehicleServiceID
--select from the two views
Select * from Employee.PriceDetail where VehicleServiceID=24
Select * From Employee.PriceTotal Where VehicleServiceID=24
--Discount percent is in Employee.VehicleService detail
--tax percent is in Employee.VehicleServiceDetail
--maybe three functions
--one for price with discounts for each service,
--one for tax
--one for total
--used with sums
--then a view that shows these
Select * from Customer.AutoService
Select * From Employee.VehicleServiceDetail
Go
Create function Employee.func_PriceWithDiscount
--parameters the user provides
(@ServiceID int, @Discount decimal(3,2))
returns money --datatype returned
As
Begin --begin function
--declare variables
Declare @price money
Declare @ServiceCost money
--Getting the price from the autoservice table
Select @price = ServicePrice from Customer.AutoService
Where AutoServiceID=@ServiceID
--calculating discount
Set @ServiceCost=@price - (@price * @Discount)
Return @ServiceCost
End
--using the Function
Select
vd.VehicleServiceID,ServiceDate,
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent))
as [Before Tax]
From Employee.VehicleServiceDetail vd
Inner Join Employee.VehicleService vs
on vs.VehicleServiceID=vd.VehicleServiceID
Where vd.VehicleServiceID=3
Group By vd.VehicleServiceID, ServiceDate
--using the function again
Select *, Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)
as [Before Tax]
From Employee.VehicleServiceDetail
where VehicleServiceID=3
Select * from Customer.AutoService where AutoServiceID=7
Go
--create the tax function
Create Function Employee.GetTax
(@subtotal money, @TaxPercent decimal(3,2))
Returns money
As
Begin
return @subtotal * @TaxPercent
End
Go
--create a view that uses the two functions to return the details
--of a transaction
Create View Employee.PriceDetail
As
Select VehicleServiceID,
Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) as Subtotal,
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)
as Tax,
Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)
as Total
From Employee.VehicleServiceDetail
Group by VehicleServiceID
Go
--create a view that shows the summary of a transaction
Create View Employee.PriceTotal
As
Select VehicleServiceID,
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)) as Subtotal,
Sum(Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Tax,
sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Total
From Employee.VehicleServiceDetail
Group by VehicleServiceID
--select from the two views
Select * from Employee.PriceDetail where VehicleServiceID=24
Select * From Employee.PriceTotal Where VehicleServiceID=24
Thursday, July 7, 2011
Views, Procedures
Here is the code, such as it is from the morning class
Select * from Customer.RegisteredCustomer
Select * from Person
--views, parameterized views
--Service history for a particular vehicle by license plate
--Customer name and vehicles by license plate
--service costs and descriptions
--things that need to be done stored procedures
--record the services performed --stored procedure
--get the amount due--function
--maintenance recommendations--stored procedure
--Edit to correct mis-entry or change customer information--stored procedure
--
--
--we did this both as a view and as a stored procedure. The difference is the view
--does not have the parameter @license nor the where clause at the end
Go
Create Proc Employee.usp_VehicleHistory
@License nvarchar(10)
As
Select LastName as [Last Name],
LicenseNumber as [License],
VehicleMake as [Make],
VehicleYear as [Year],
ServiceDate as [Service Date],
ServiceName as [Service],
serviceNotes as [Notes]
From Person p
Inner Join Customer.Vehicle v
on p.PersonKey = v.PersonKey
Inner Join Employee.VehicleService vs
on v.VehicleID=vs.VehicleID
Inner Join Employee.VehicleServiceDetail vd
on vs.VehicleServiceId=vd.VehicleServiceID
Inner Join Customer.AutoService a
on a.AutoServiceID=vd.AutoServiceID
Where LicenseNumber=@License
Select * from Customer.AutoService
Select * from Employee.VehicleHistory
Where License='NET200'
--this view we made with the graphical designer
Select * from dbo.vw_HumanResources
Where LocationName='Federal Way'
Order by LastName
Execute Employee.usp_VehicleHistory 'ITC226'
--vehicleID get by license plate
--LocationID
--ServiceDate Service time
Alter procedure Employee.usp_NewService
@License nvarchar(10),
@LocationID int
As
Begin try
Declare @serviceDate Date
Declare @ServiceTime Time
Set @serviceDate=GETDATE()
Set @ServiceTime=GETDATE()
Declare @VehicleID int
Select @VehicleID=VehicleID From Customer.vehicle
Where LicenseNumber=@License
Insert into Employee.VehicleService
(VehicleID,LocationID,ServiceDate,ServiceTime)
Values (@VehicleID, @LocationID, @serviceDate, @ServiceTime)
End Try
Begin Catch
Print error_message()
End Catch
Exec Employee.usp_NewService
@License= 'ITC226',
@LocationID=2
Select * from Customer.RegisteredCustomer
Select * from Person
--views, parameterized views
--Service history for a particular vehicle by license plate
--Customer name and vehicles by license plate
--service costs and descriptions
--things that need to be done stored procedures
--record the services performed --stored procedure
--get the amount due--function
--maintenance recommendations--stored procedure
--Edit to correct mis-entry or change customer information--stored procedure
--
--
--we did this both as a view and as a stored procedure. The difference is the view
--does not have the parameter @license nor the where clause at the end
Go
Create Proc Employee.usp_VehicleHistory
@License nvarchar(10)
As
Select LastName as [Last Name],
LicenseNumber as [License],
VehicleMake as [Make],
VehicleYear as [Year],
ServiceDate as [Service Date],
ServiceName as [Service],
serviceNotes as [Notes]
From Person p
Inner Join Customer.Vehicle v
on p.PersonKey = v.PersonKey
Inner Join Employee.VehicleService vs
on v.VehicleID=vs.VehicleID
Inner Join Employee.VehicleServiceDetail vd
on vs.VehicleServiceId=vd.VehicleServiceID
Inner Join Customer.AutoService a
on a.AutoServiceID=vd.AutoServiceID
Where LicenseNumber=@License
Select * from Customer.AutoService
Select * from Employee.VehicleHistory
Where License='NET200'
--this view we made with the graphical designer
Select * from dbo.vw_HumanResources
Where LocationName='Federal Way'
Order by LastName
Execute Employee.usp_VehicleHistory 'ITC226'
--vehicleID get by license plate
--LocationID
--ServiceDate Service time
Alter procedure Employee.usp_NewService
@License nvarchar(10),
@LocationID int
As
Begin try
Declare @serviceDate Date
Declare @ServiceTime Time
Set @serviceDate=GETDATE()
Set @ServiceTime=GETDATE()
Declare @VehicleID int
Select @VehicleID=VehicleID From Customer.vehicle
Where LicenseNumber=@License
Insert into Employee.VehicleService
(VehicleID,LocationID,ServiceDate,ServiceTime)
Values (@VehicleID, @LocationID, @serviceDate, @ServiceTime)
End Try
Begin Catch
Print error_message()
End Catch
Exec Employee.usp_NewService
@License= 'ITC226',
@LocationID=2
Wednesday, July 6, 2011
Arrays, Enums and Structures
Here is the review of arrays and the examples for structures and enums
#include <iostream>
#include <string>
using namespace std;
void ArrayReview();
void PrintArray(int myArray3[], int size);
void CharacterArrays();
void StructureExample();
void ArrayOfStructures();
void EnumerationExamples();
struct product
{
string productName;
double productWeight;
double Price;
};
enum colors
{
red, green, blue, purple, orange, yellow
};
int main()
{
//ArrayReview();
//CharacterArrays();
//StructureExample();
//ArrayOfStructures();
EnumerationExamples();
char c;
cin >> c;
}
void ArrayReview()
{
int myArray[4];
myArray[0]=2;
myArray[1]=14;
myArray[2]=5;
myArray[3]=9;
int myArray2[]={2, 4, 5, 6, 9};
PrintArray(myArray,4);
}
void PrintArray(int myArray3[], int size)
{
for (int i =0;i<size;i++)
{
cout << myArray3[i] << endl;
}
}
void CharacterArrays()
{
char city[50];
cout << "Enter a City Name" << endl;
cin.getline(city,50);
cout << "\n\nYour city is : " << city;
}
void StructureExample()
{
product poprocks;
poprocks.Price=1.25;
poprocks.productName="pop rocks";
poprocks.productWeight=3;
cout << "You bought 3 packages of " << poprocks.productName << endl;
cout << "that will be $"<<poprocks.Price * 3 << endl;
cout << "thank you " << endl;
}
void ArrayOfStructures()
{
product grocery[3];
product bread;
bread.Price=3.25;
bread.productName="Daves Killer bread";
bread.productWeight=12;
product chocolate;
chocolate.Price=3.79;
chocolate.productName="Theo Dark Chocolate";
chocolate.productWeight=2.75;
product steaksauce;
steaksauce.Price=4.10;
steaksauce.productName="A1";
steaksauce.productWeight=8;
grocery[0]=bread;
grocery[1]=chocolate;
grocery[2]=steaksauce;
double total=0;
for(int i=0;i<3;i++)
{
cout << "you bought " << grocery[i].productName << " at a price of "
<< grocery[i].Price << endl;
total +=grocery[i].Price;
}
cout << "Your total is $" << total << endl;
}
void UnionExample()
{
union test
{
double double_val;
int int_val;
};
cout << sizeof(long) << " bytes";
test test1;
test1.int_val=45;
test test2;
test2.double_val=3.45;
cout << test1.int_val / test2.double_val << endl;
}
void EnumerationExamples()
{
colors spectrum;
spectrum= orange;
cout << "Our color is " << spectrum << " the next color is " << spectrum + 1
<< "the previous color was " << spectrum-1 << endl;
}
#include <iostream>
#include <string>
using namespace std;
void ArrayReview();
void PrintArray(int myArray3[], int size);
void CharacterArrays();
void StructureExample();
void ArrayOfStructures();
void EnumerationExamples();
struct product
{
string productName;
double productWeight;
double Price;
};
enum colors
{
red, green, blue, purple, orange, yellow
};
int main()
{
//ArrayReview();
//CharacterArrays();
//StructureExample();
//ArrayOfStructures();
EnumerationExamples();
char c;
cin >> c;
}
void ArrayReview()
{
int myArray[4];
myArray[0]=2;
myArray[1]=14;
myArray[2]=5;
myArray[3]=9;
int myArray2[]={2, 4, 5, 6, 9};
PrintArray(myArray,4);
}
void PrintArray(int myArray3[], int size)
{
for (int i =0;i<size;i++)
{
cout << myArray3[i] << endl;
}
}
void CharacterArrays()
{
char city[50];
cout << "Enter a City Name" << endl;
cin.getline(city,50);
cout << "\n\nYour city is : " << city;
}
void StructureExample()
{
product poprocks;
poprocks.Price=1.25;
poprocks.productName="pop rocks";
poprocks.productWeight=3;
cout << "You bought 3 packages of " << poprocks.productName << endl;
cout << "that will be $"<<poprocks.Price * 3 << endl;
cout << "thank you " << endl;
}
void ArrayOfStructures()
{
product grocery[3];
product bread;
bread.Price=3.25;
bread.productName="Daves Killer bread";
bread.productWeight=12;
product chocolate;
chocolate.Price=3.79;
chocolate.productName="Theo Dark Chocolate";
chocolate.productWeight=2.75;
product steaksauce;
steaksauce.Price=4.10;
steaksauce.productName="A1";
steaksauce.productWeight=8;
grocery[0]=bread;
grocery[1]=chocolate;
grocery[2]=steaksauce;
double total=0;
for(int i=0;i<3;i++)
{
cout << "you bought " << grocery[i].productName << " at a price of "
<< grocery[i].Price << endl;
total +=grocery[i].Price;
}
cout << "Your total is $" << total << endl;
}
void UnionExample()
{
union test
{
double double_val;
int int_val;
};
cout << sizeof(long) << " bytes";
test test1;
test1.int_val=45;
test test2;
test2.double_val=3.45;
cout << test1.int_val / test2.double_val << endl;
}
void EnumerationExamples()
{
colors spectrum;
spectrum= orange;
cout << "Our color is " << spectrum << " the next color is " << spectrum + 1
<< "the previous color was " << spectrum-1 << endl;
}
First Pointers
Below is the code we did in class on pointers. It includes basic pointers, A dynamic array using a pointer and the keyword "new" and a dynamic structure.
#include <iostream>
#include <string>
using namespace std;
//function proto types
void SimplePointer();
void DynamicArrays();
void DynamicStructure();
struct menu
{
string item;
double price;
};
int main()
{
//SimplePointer();
//DynamicArrays();
DynamicStructure();
char c;
cin >> c;
}
void SimplePointer()
{
int number=456;
int * p_number; // pointer
p_number= &number; //assigning the address of a variable to the pointer
int *p_number2; //a second pointer
p_number2=p_number; //assigning the address of one pointer to another
cout << " the number is " << number << " the address of the number is " << p_number
<< " the value stored at that address is " << *p_number
<< "the second pointer has an address of " << p_number2 << "and has an value of "
<< *p_number2 << endl;
}
void DynamicArrays()
{
cout << "Enter the size of the array you want " <<endl;
int size;
cin >> size;
//if you use a pointer you can assign the size
//of an array at run time
//otherwise it requires a constant
int *p_array=new int[size];
for (int i =0;i<size;i++)
{
cout << "Enter your value : ";
cin >> p_array[i];
}
cout << "the second element of the array is " << p_array[1] << "| " << p_array <<"| "
<< p_array + 2<< " |"<< *p_array + 2 << endl;
//you should always delete anything
//you allocate with "new"
delete p_array;
}
void DynamicStructure()
{
//a dynamic structure
//this allows you to assign values to the structure
//during run time
menu * p_menu = new menu;
cout << "Enter the menu item name " << endl;
getline(cin, p_menu->item);
cout << "Enter the Price "<< endl;
cin >> p_menu->price;
cout << "You ordered " << p_menu->item << " at " << p_menu ->price << endl;
delete p_menu;
}
#include <iostream>
#include <string>
using namespace std;
//function proto types
void SimplePointer();
void DynamicArrays();
void DynamicStructure();
struct menu
{
string item;
double price;
};
int main()
{
//SimplePointer();
//DynamicArrays();
DynamicStructure();
char c;
cin >> c;
}
void SimplePointer()
{
int number=456;
int * p_number; // pointer
p_number= &number; //assigning the address of a variable to the pointer
int *p_number2; //a second pointer
p_number2=p_number; //assigning the address of one pointer to another
cout << " the number is " << number << " the address of the number is " << p_number
<< " the value stored at that address is " << *p_number
<< "the second pointer has an address of " << p_number2 << "and has an value of "
<< *p_number2 << endl;
}
void DynamicArrays()
{
cout << "Enter the size of the array you want " <<endl;
int size;
cin >> size;
//if you use a pointer you can assign the size
//of an array at run time
//otherwise it requires a constant
int *p_array=new int[size];
for (int i =0;i<size;i++)
{
cout << "Enter your value : ";
cin >> p_array[i];
}
cout << "the second element of the array is " << p_array[1] << "| " << p_array <<"| "
<< p_array + 2<< " |"<< *p_array + 2 << endl;
//you should always delete anything
//you allocate with "new"
delete p_array;
}
void DynamicStructure()
{
//a dynamic structure
//this allows you to assign values to the structure
//during run time
menu * p_menu = new menu;
cout << "Enter the menu item name " << endl;
getline(cin, p_menu->item);
cout << "Enter the Price "<< endl;
cin >> p_menu->price;
cout << "You ordered " << p_menu->item << " at " << p_menu ->price << endl;
delete p_menu;
}
Subscribe to:
Posts (Atom)