//--------------------------------------------------
//  Presentation tier scripts
//--------------------------------------------------

//--------------------------------------------------
// Web.Config settings
//--------------------------------------------------

  <connectionStrings>
    <add
       name="Test1"
       connectionString="Data Source=LocalHost;Initial Catalog=DatabaseName;User ID=UserLogin;Password=UserPassword"
       providerName="System.Data.SqlClient"
   />
  </connectionStrings>


//--------------------------------------------------
//  Using directives
//--------------------------------------------------

using System.Web.Configuration;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessLogic;
using DataAccess;

//--------------------------------------------------
//Get the connectionstring from web.config
//--------------------------------------------------

string connectionString = WebConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;


//--------------------------------------------------
//  Regions
//--------------------------------------------------

#region " Click event handlers for page controls "

#endregion

#region " Click event handlers for grid views "

#endregion

#region " Local methods "

    public void SetFormMode()
    {
        //This method will initialize settings for the page
    }

#endregion

#region " Local functions "

#endregion

#region " Local utilities "

#endregion

//--------------------------------------------------
//  code snippets
//--------------------------------------------------

// Placed in the Page_load event
//
//        if (!IsPostBack) 
//        {
//        SetFormMode();
//        }


//Instantiate an instance of a business or data access tier
//Invoke a method to create a DataTable

DataAccess.daObject objMyobject = new DataAccess.daObject();
DataTable dtMydatatable = objMyobject.GetSomeResult(parameterlist);

//--------------------------------------------------
//  Business Logic Tier scripts
//--------------------------------------------------


//--------------------------------------------------
// using Directives
//--------------------------------------------------

using System.Data;
using System.Data.SqlClient;
using DataAccess;

//Note: make your class public

//--------------------------------------------------
// Region Directives 
//--------------------------------------------------

#region " Public properties "
 
#endregion

#region " Read methods "

#endregion

#region " Insert methods "

#endregion

#region " Update methods "

#endregion

#region " Delete methods "

#endregion

//--------------------------------------------------
//  Error handling roperties 
//--------------------------------------------------


        public static bool pTransactionSuccessful;
        public bool TransactionSuccessful()
        {
            return pTransactionSuccessful;
        }
        public static string pErrorMessage;
        public string ErrorMessage()
        {
            return pErrorMessage;
        }
        public static int pErrorNumber;
        public int ErrorNumber()
        {
            return pErrorNumber;
        }
        public static int pErrorClass;
        public int ErrorClass()
        {
            return pErrorClass;
        }
        public static int pErrorState;
        public int ErrorState()
        {
            return pErrorState;
        }
        public static int pErrorLineNumber;
        public int ErrorLineNumber()
        {
            return pErrorLineNumber;
        }


//--------------------------------------------------
// Generic read method 
//--------------------------------------------------


         public DataTable GetSomeResult(int Parameter1, string ConnectionString)
        {
            //Instantiate an instance of data access tier
            //In this case, daUser is a reference to the C# class in the DataAccess project daUser.cs
            //With the using DataAccess; directive DataAccess. is not necessary but it is included here
            //to clarify.
            DataAccess.daUser objdaUser = new DataAccess.daUser();

            //Invoke DataAccess tier method passing paramter(s) and connection string
            DataTable dtSomeResult = objdaUser.GetSomeResult(Parameter1, ConnectionString);

            //Set property indicating whether transaction successful
            pTransactionSuccessful = objdaUser.TransactionSuccessful();

            if (pTransactionSuccessful == false)
            {
                //Pass up error messages if any
                pErrorMessage = objdaUser.ErrorMessage();
                pErrorNumber = objdaUser.ErrorNumber();
                pErrorClass = objdaUser.ErrorClass();
                pErrorState = objdaUser.ErrorState();
                pErrorLineNumber = objdaUser.ErrorLineNumber();
            }

            //Return ADO.Net data object to presentation tier
            return dtSomeResult;

            // or for example...
            //pSomeproperty = objdaUser.SomeProperty();

        }

//--------------------------------------------------
//   DataAccess Tier scripts
//--------------------------------------------------

//--------------------------------------------------
//  using Directives
//--------------------------------------------------

using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

//Note: make your class public

//--------------------------------------------------
// Region Directives 
//--------------------------------------------------

#region " Public properties "
 
#endregion

#region " Read methods "

#endregion

#region " Insert methods "

#endregion

#region " Update methods "

#endregion

#region " Delete methods "

#endregion

//--------------------------------------------------
// Error handling properties 
//--------------------------------------------------


        public static bool pTransactionSuccessful;
        public bool TransactionSuccessful()
        {
            return pTransactionSuccessful;
        }
        public static string pErrorMessage;
        public string ErrorMessage()
        {
            return pErrorMessage;
        }
        public static int pErrorNumber;
        public int ErrorNumber()
        {
            return pErrorNumber;
        }
        public static int pErrorClass;
        public int ErrorClass()
        {
            return pErrorClass;
        }
        public static int pErrorState;
        public int ErrorState()
        {
            return pErrorState;
        }
        public static int pErrorLineNumber;
        public int ErrorLineNumber()
        {
            return pErrorLineNumber;
        }

//--------------------------------------------------
// Generic read method
//--------------------------------------------------


          public DataTable GetSomeResult(int Parameter1, string ConnectionString)
        {
            // Set up parameters in parameter array 
            SqlParameter[] arParms = new SqlParameter[1];

            arParms[0] = new SqlParameter("@Parameter1", SqlDbType.Int);
            arParms[0].Value = Parameter1;

            pTransactionSuccessful = true;

            DataTable dtSomeInfo = new DataTable("SomeInfo");

            try
            {
                DataSet dsSomeInfo = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "GetSomeInfo", arParms);
                dtSomeInfo = dsSomeInfo.Tables[0];
            }
            catch (SqlException ReadError)
            {
                pErrorMessage = ReadError.Message.ToString();
                pErrorNumber = ReadError.Number;
                pErrorClass = ReadError.Class;
                pErrorState = ReadError.State;
                pErrorLineNumber = ReadError.LineNumber;

                pTransactionSuccessful = false;
            }
            return dtSomeInfo;
        }

//--------------------------------------------------
// Generic insert/update/delete (ExecuteNonQuery) method
//--------------------------------------------------



        public void DoSomething(string Parameter1, bool Parameter2, string ConnectionString)
        {
            // Set up parameters in parameter array 
            SqlParameter[] arParms = new SqlParameter[2];

            arParms[0] = new SqlParameter("@Parameter1", SqlDbType.NVarChar);
            arParms[0].Value = Parameter1;
            arParms[1] = new SqlParameter("@Parameter2", SqlDbType.bit);
            arParms[1].Value = Parameter2;

            //Example of an output paramter
            // arParms[n] = new SqlParameter("@Paramtern", SqlDbType.Bit);
            // arParms[n].Direction = ParameterDirection.Output;
            //Remember to adjust the array dimension when adding or subtracting elements.

            pTransactionSuccessful = true;

            try
            {
                SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "InsertSomething", arParms);
            }
            catch (SqlException InsertError)
            {
                pErrorMessage = InsertError.Message.ToString();
                pErrorNumber = InsertError.Number;
                pErrorClass = InsertError.Class;
                pErrorState = InsertError.State;
                pErrorLineNumber = InsertError.LineNumber;

                pTransactionSuccessful = false;
            }

               //If using output paramters
               //pSomeParameter = (bool)arParms[n].Value;


        }


//--------------------------------------------------
//   Data Tier scripts
//--------------------------------------------------

--CREATE DATABASE DatabaseName
--USE DatabaseName

CREATE DATABASE jhpISC559
USE jhpISC559

/*
This login will be the login used by our 
application for SQL Sever authentication
rather than Windows integrated security.
*/

--EXEC sp_addlogin 'login', 'password', 'DatabaseName'
--EXEC sp_dropuser 'login'
--EXEC sp_changedbowner 'login'
--EXEC sp_addUser 'login'
--EXEC sp_dropuser 'login'
--EXEC sp_droplogin 'login'

EXEC sp_addlogin 'jhp', 'jhppass', 'jhpISC559'
EXEC sp_adduser 'jhp'

--GRANT EXECUTE ON sprocName TO Login
--ALTER LOGIN login WITH PASSWORD = 'newpassword'

--DROP DATABASE DatabaseName

CREATE TABLE tblUser
(
UserID INT IDENTITY(1,1) PRIMARY KEY,
Login NVARCHAR(50) NOT NULL,
Password NVARCHAR(50) NOT NULL
)

INSERT INTO tblUser (Login, Password) VALUES ('hpardue', 'mypass')

SELECT * 
FROM tblUser
WHERE Login = 'hpardue' AND
      Password = 'mypass'

CREATE PROC ValidateUser
 @Login NVARCHAR(50),
 @Password NVARCHAR(50),
 @IsFound BIT OUTPUT
AS
BEGIN
IF EXISTS(SELECT * 
          FROM tblUser
          WHERE Login = @Login AND
                Password = @Password)
   SET @IsFound = 1
ELSE
   SET @IsFound = 0
END;
go;
GRANT EXECUTE ON ValidateUser TO jhp

--ALTER LOGIN login WITH PASSWORD = 'newpassword'
ALTER LOGIN jhp WITH PASSWORD = 'jhppass1'

--DROP DATABASE DatabaseName

/*
Sample SQL DDL statements for enforcing constraints
*/

/*
--Add a primary key attribute
ALTER TABLE tblTableName
ADD PrimaryKeyID INT IDENTITY(1,1) PRIMARY KEY

--Add a primary key constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_PK PRIMARY KEY (PrimaryKeyID)

--Add an IS-A foreign key in a SubType relation
ALTER TABLE tblSubTypeTableName
ADD SuperTypePrimaryKeyID INT NULL UNIQUE --makes this an alternate key

--Add an alternate key attribute
ALTER TABLE tblTableName
ADD AlternateKeyID INT NOT NULL UNIQUE

--Add an alternate key constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Unique UNIQUE(attributelist) -- makes this an alternate key

--Add a foreign key attribute
ALTER TABLE tblChildTableName
ADD ForeignKeyID INT NULL REFERENCES tblParentTableName(PrimaryKeyID)

--Add a foreign key to an existing attribute
ALTER TABLE tblChildTableName
ADD CONSTRAINT ConstraintName_fk FOREIGN KEY (ForeignKeyID)
REFERENCES tblParentTableName(ForeignKeyID)

--Make an existing attribute required (NOT NULL)
ALTER TABLE tblTableName
ALTER COLUMN ColumnName INT NOT NULL

--Add a DEFAULT constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Default
DEFAULT 'some value' FOR ColumnName

--Add a CHECK constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Check
CHECK (tblTableName.ColumnName IN('value1', 'value2', 'value3', 'etc...')) 

ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Check
CHECK (attribute <, >, =, >=, <= some_value)

--Drop a constraint
ALTER TABLE tblTableName
DROP CONSTRAINT ConstraintName_Check

--Drop a column
ALTER TABLE tblTableName
DROP COLUMN ColumnName

*/