Assignment 3B

Accessing a Database

Objectives

In this assignment you will create a database, create a table, and at the same time create a form to add data to the database and also access this data. I also recommend looking at the next assignment CET 4584 Assignment 4B - Creating a Login System as it contains some helpful hints for this assignment.

Assignment

You are going to create from scratch a simple login system. As part of this system you are going to need to use some of the code that I will supply you (for the database). You will also need to be able to script these tables on your hosting environment.

Here is the SQL Script for your User table

GO

CREATE TABLE [dbo].[User](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](63)NOT NULL,
    [UserPassword] [nvarchar](63) NOT NULL,
    [UserEmail] [nvarchar](127) NOT NULL,
    [UserCreatedDate] [datetime] NOT NULL,
    [UserLastLoginDate] [datetime] NULL,
    [UserIsDeletedBit] [bit] NOT NULL,
    [UserType] [varchar](20) NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
    [UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

In addtion to this table you will also need the stored procedures to add users and also access the table of users.

CREATE PROCEDURE [dbo].[sp_CreateUser]
    @UserName nvarchar(63),
    @UserPassword nvarchar(63),
    @UserEmail nvarchar(63)
AS

BEGIN TRY
        BEGIN TRAN
        DECLARE @userid AS INT
        INSERT  INTO [User]
                (
                  [UserName],
                  [UserPassword],
                  [UserEmail],
                  [UserCreatedDate],
                  [UserLastLoginDate],
                  [UserIsDeletedBit],
                  [UserType]
              )
        VALUES  (
                  @UserName,
                  @UserPassword,
                  @UserEmail,
                  GETDATE(),
                  NULL,
                  0,
                  'Guest'
                )

     SET @userid = SCOPE_IDENTITY()

     COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SELECT  ERROR_NUMBER() ErrorNumber,
                ERROR_MESSAGE() [Message]
    END CATCH

    RETURN @userid
GO

CREATE PROCEDURE [dbo].[sp_GetAllUsers]
AS
BEGIN

SELECT * FROM [User]

END
GO

These 2 stored procedures are designed for SQL Server, so if you are using a different database then you will need to modify them for use with that specific database. If you have made version for other databases - I will be happy to post them with the assignment for the students.

The final turn-in for this assignment will be a URL - in this URL you should be able to enter the information for the user. After submitting the information you should be taken to a screen which shows the list of all users (without passwords). You can show this in a datagrid.

Information

For this assignment I highly recommend using the dboperations class to control access to the database. I have made this class with instructions available to you at

http://cet4429.pbworks.com/Coding-Correctly-in-DOTNET

You will also need to create a connection string. This is not necessarily difficult, and I have a lecture going over how to do this (in the CET 4584 lectures at http://cop4813eaglin.pbworks.com/CET+4584+Lectures

Estimated Completion Time

This will be the most challenging assignment for the new programmer. There are a lot of aspects to this assignment that will be new. Start immediately. An experienced programmer will complete this in a couple of hours. A less experienced programmer can take up to 40 hours to complete this.

Supporting Lectures 

From the lectures at http://cop4813eaglin.pbworks.com/CET+4584+Lectures I recommend going through the Quick Tour of Visual Studio 2005, Getting Started Using SQL Server 2005, Creating a Database Connection String, and the Vendor Database ASPNET Case Study. These contain all the skills and software you will need to complete this assignment.

Also a little help - you are welcome to use some of my code and techniques. The Database Common or DBOperations class that I created can make your accessing the database a much simpler task for large scale projects.

For GoDaddy Users here is a very useful page http://help.godaddy.com/article/688 on creating an ASP.Net web site on GoDaddy.

Questions and Answers

I will answer student questions here for this assignment.

External Resources

The "How Do I" video series available on MSDN may be your best friend over the next few weeks. This series is located at http://msdn.microsoft.com/en-us/vstudio/bb507746.aspx - you will want to bokmark this and be familiar with it.

Grading Criteria

If you can create the entry page - that is 5 points, if you can create the display page, you now have the other 5 points.