--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();
}
}
No comments:
Post a Comment