Making a custom demo for HAS

I'm making a custom demo for a project that am part of, this is in order to contain the rules of work for this particular client.

So far useful links

http://stackoverflow.com/questions/14729630/sqlite-provider-in-visual-studio-2012

http://munnaondotnet.blogspot.mx/2011/07/edit-sqlite-database-using-visual.html

http://stackoverflow.com/questions/11291423/sqlite-with-visual-studio-2012-and-net-4-5

http://sqlserversamples.codeplex.com/

http://stackoverflow.com/questions/15084753/dbcontext-entityset-null-when-entities-are-set-to-internal-access/20652225#20652225

Creando un sitio web ASP .Net. Parte 5

Para continuar con la creación de nuestro sitio web vamos a crear una conexión con la base de datos la cual nos va a permitir obtener información de la base que creamos.

Crear un usuario en la base de datos

-- Create user for SQL Authentication

CREATE LOGIN clothesUsr WITH PASSWORD = 'HolaMundo'

,DEFAULT_DATABASE = LuisClothes

GO

-- Add User to first database

USE LuisClothes;

CREATE USER clothesUsr FOR LOGIN clothesUsr;

EXEC sp_addrolemember 'db_datareader', 'clothesUsr'

EXEC sp_addrolemember 'db_datawriter', 'clothesUsr'

GO

Crear un rol para ejecución de procedimientos almacenados.

-- 1 - db_executestoredprocedures

-- 1a - Create role

CREATE ROLE db_produccion

GO

Una vez que terminemos la creación de nuestros procedimientos y funciones almacenadas ejecutaremos las siguientes sentencias.

-- 1b - Grant permissions

GRANT EXECUTE TO db_produccion

GO

También vamos a crear el rol para la selección de funciones.

-- 2 - db_selecttablevaluedfunctions

-- 2a - Create role

CREATE ROLE db_select_funtions

GO

Para dar permisos de seleccion a nuestras funciones se ejecutara el siguiente script una vez que terminemos la creacion de las funciones.

-- 2 - Create permissions

DECLARE @Function_Name nvarchar(250);

DECLARE @CMDEXEC1 nvarchar(2000);

DECLARE db_cursor CURSOR FOR 

SELECT [name]

FROM sys.objects

WHERE Type = 'TF'

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @Function_Name

WHILE @@FETCH_STATUS = 0  

BEGIN

  

 SET @CMDEXEC1 = 'GRANT SELECT ON [' + @Function_Name + '] TO db_select_funtions;'

 --- SELECT @CMDEXEC1

 EXEC(@CMDEXEC1)

 FETCH NEXT FROM db_cursor INTO @Function_Name

END

CLOSE db_cursor  

DEALLOCATE db_cursor

GO

Ahora vamos a crear algunos procedimientos almacenados como por ejemplo la creación de un producto. Para esto en nuestro Object Explorer vamos a dar clic derecho en stored procedures.

Con lo que nos a generar una plantilla para nuestro procedimiento almacenado.

-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:   <Description,,>

-- =============================================

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

      -- Add the parameters for the stored procedure here

      <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

      <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    -- Insert statements for procedure here

      SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

GO

Ahora solo tenemos que dar la definición de nuestro procedimiento.

-- ================================================

-- Project Luis Clothes.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Roque Rueda.

-- Create date: 24/11/2013

-- Description:   Creates a new product record in

--              the database. If you need to inser

--                      a new row you should give a -1

--                      product id(this is the default value)

-- =============================================

CREATE PROCEDURE Insert_Product (

      -- stored procedure params

      @product_id             int = -1,

      @product_name           nvarchar(150),

      @product_price          decimal(10, 2) = 1,

      @product_photo          nvarchar(2083),

      @product_type           int

      )

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

     

      -- Update information or insert if not exists

      UPDATE Product SET Name = @product_name,

                                Price = @product_price,

                                Photo = @product_photo,

                                IdType = @product_type

      WHERE Id = @product_id;

      IF @@ROWCOUNT = 0 -- Check if the row was updated.

      INSERT INTO Product (Name,

                                    Price,

                                    Photo,

                                    IdType,

                                    CreationDate)

      VALUES (@product_name, -- Name

                  @product_price, -- Price

                  @product_photo, -- Photo

                  @product_type, -- Typeid from ProductType

                  GETDATE()); -- Gets the current date

                 

      -- Reset SET NONCOUNT

      SET NOCOUNT OFF;

END

GO

Por que hacer store procedure para hacer los llamados a base de datos. Supongamos que por algún motivo necesitamos saber qué fecha fue modificado un producto. Por desgracia la tabla ya fue creada y nuestro programa ya está utilizando. Algo como lo siguiente:

            string connectionString = "connection Settings";

            using (var conn = new SqlConnection(connectionString))

            using (var command = new SqlCommand("InsertProduct", conn)

            {

                CommandType = System.Data.CommandType.StoredProcedure

            })

            {

                conn.Open();

                command.Parameters.AddWithValue("@product_id", 1);

                ...

                command.Parameters.AddWithValue("@@product_type", 1);

                command.ExecuteNonQuery();

                conn.Close();

            }

Para nuestra aplicación que está usando el procedimiento almacenado lo que se debe hacer es nada... así es ya que solo se proporciona el nombre y parámetros mientras la interfaz y el numero de parámetros se mantengan igual no hay necesidad de cambiar.

-- Roque Rueda add a column to know when the produc

-- was modified.

ALTER TABLE Product

ADD LastModified DATETIME

Si se agrega la columna lo único que debemos realizar es agregar las sentencias necesarias en nuestro store procedure.

-- ================================================

-- Project Luis Clothes.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Roque Rueda.

-- Create date: 24/11/2013

-- Description:   Creates a new product record in

--              the database. If you need to inser

--                      a new row you should give a -1

--                      product id(this is the default value)

-- =============================================

ALTER PROCEDURE InsertProduct (

      -- stored procedure params

      @product_id             int = -1,

      @product_name           nvarchar(150),

      @product_price          decimal(10, 2) = 1,

      @product_photo          nvarchar(2083),

      @product_type           int

      )

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

     

      -- Update information or insert if not exists

      UPDATE Product SET Name = @product_name,

                                Price = @product_price,

                                Photo = @product_photo,

                                IdType = @product_type,

                                LastModified = GETDATE()

      WHERE Id = @product_id;

      IF @@ROWCOUNT = 0 -- Check if the row was updated.

      INSERT INTO Product (Name,

                                    Price,

                                    Photo,

                                    IdType,

                                    CreationDate)

      VALUES (@product_name, -- Name

                  @product_price, -- Price

                  @product_photo, -- Photo

                  @product_type, -- Typeid from ProductType

                  GETDATE()); -- Gets the current date

                 

      -- Reset SET NONCOUNT

      SET NOCOUNT OFF;

END

GO

Si se dan cuenta no se afectaría en ningún momento el código de la aplicación. Es por eso que en este proyecto vamos a seguir este modelo, aunque en algunos casos no utilicemos un store procedure, tratare de explicarle el por qué no de uso.