Click here to Skip to main content
11,438,069 members (51,699 online)
Click here to Skip to main content
Add your own
alternative version

PinPoint your exact location and show the world where you are

, 16 Jul 2005 CPOL
Geo-Locating C# Smart Client Application using the 2.0 Framework
pinpoint.zip
PinPonitSource.zip
PinPoint Setup.doc
PinPointWebSiteSource.zip
��if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Appointments_AppointmentUser]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Appointments] DROP CONSTRAINT FK_Appointments_AppointmentUser

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteScheduleItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[DeleteScheduleItem]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertScheduleItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[InsertScheduleItem]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectScheduleDay]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[SelectScheduleDay]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectScheduleItems]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[SelectScheduleItems]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[SelectUser]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateScheduleItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[UpdateScheduleItem]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AppointmentUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[AppointmentUsers]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Appointments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Appointments]

GO



CREATE TABLE [dbo].[AppointmentUsers] (

	[UserID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,

	[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Appointments] (

	[AppointmentID] [uniqueidentifier] NOT NULL ,

	[UserID] [uniqueidentifier] NOT NULL ,

	[RecurrencePattern] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[StartDate] [datetime] NULL ,

	[EndDate] [datetime] NULL ,

	[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[Address] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[Latitude] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[Longitude] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[IsIntersection] [bit] NULL 

) ON [PRIMARY]

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE DeleteScheduleItem



	@AppointmentID	uniqueidentifier,

	@UserID		uniqueidentifier,

	@RecurrencePattern	varchar(2000),

	@StartDate		datetime,

	@EndDate		datetime,

	@Description		varchar(100),

	@Address		varchar(300),

	@City			varchar(50),

	@State			varchar(2),

	@Zip			varchar(10),

	@Latitude		varchar(20),

	@Longitude		varchar(20),

	@IsIntersection		bit



AS



DELETE FROM	Appointments

WHERE AppointmentID = @AppointmentID

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE InsertScheduleItem



	@AppointmentID	uniqueidentifier,

	@UserID		uniqueidentifier,

	@RecurrencePattern	varchar(2000),

	@StartDate		datetime,

	@EndDate		datetime,

	@Description		varchar(100),

	@Address		varchar(300),

	@City			varchar(50),

	@State			varchar(2),

	@Zip			varchar(10),

	@Latitude		varchar(20),

	@Longitude		varchar(20),

	@IsIntersection		bit



AS



INSERT INTO Appointments

	(AppointmentID, UserID, RecurrencePattern,  StartDate,  EndDate, Description, Address, City, 

	State, Zip, Latitude, Longitude, IsIntersection)

VALUES

	(@AppointmentID,  @UserID, @RecurrencePattern, @StartDate, @EndDate, @Description, 

	@Address, @City, @State, @Zip, @Latitude, @Longitude, @IsIntersection )

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE SelectScheduleDay



	@UserID		uniqueidentifier,

	@Date			datetime

	

AS



SELECT Appointments.*, AppointmentUsers.FirstName as FirstName FROM 

	Appointments

INNER JOIN AppointmentUsers ON

	Appointments.UserID = AppointmentUsers.UserID

WHERE

	Appointments.UserID = @UserID

AND

	CAST(CONVERT(varchar,StartDate,101) AS DateTime) <= @Date

AND

	CAST(CONVERT(varchar,EndDate,101) + ' 23:59' AS DateTime) >= @Date

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE SelectScheduleItems



	@UserID		uniqueidentifier,

	@StartDate		datetime,

	@EndDate		datetime

	

AS



SELECT * FROM 

	Appointments

WHERE

	UserID = @UserID

AND

	StartDate >= @StartDate

AND

	EndDate <= @EndDate

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE SelectUser



	@UserID		uniqueidentifier

	

AS



SELECT TOP 1 UserID FROM 

	AppointmentUsers

WHERE

	UserID = @UserID

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE UpdateScheduleItem



	@AppointmentID	uniqueidentifier,

	@UserID		uniqueidentifier,

	@RecurrencePattern	varchar(2000),

	@StartDate		datetime,

	@EndDate		datetime,

	@Description		varchar(100),

	@Address		varchar(300),

	@City			varchar(50),

	@State			varchar(2),

	@Zip			varchar(10),

	@Latitude		varchar(20),

	@Longitude		varchar(20),

	@IsIntersection		bit



AS



UPDATE 

	Appointments

SET

	RecurrencePattern = @RecurrencePattern, 

	StartDate = @StartDate, 

	EndDate = @EndDate, 

	Description = @Description, 

	Address = @Address,  

	City = @City,

	State = @State, 

	Zip = @Zip, 

	Latitude = @Latitude, 

	Longitude = @Longitude, 

	IsIntersection = @IsIntersection

WHERE

	AppointmentID = @AppointmentID

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

TylerBrinks
Web Developer PageLabs
United States United States
I'm the founder of PageLabs, a web-based performance and SEO optimization site.

Give your site a boost in performance, even take a free speed test!

http://www.pagelabs.com
Follow on   Twitter

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150506.1 | Last Updated 16 Jul 2005
Article Copyright 2005 by TylerBrinks
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid