Crear Json desde SQLServer queries

El uso de Json se ha hecho muy popular y común para transmitir datos por medio de la web y diferentes sistemas internos y externos de diferentes empresas. Ahora a partir de SQLServer 2016 podemos crear queries que pueden generar resultados con Json.

Con el uso de Json se puede manipular el tipo de resultado que vamos a crear. Vamos a ver unos ejemplos de como se puede logar esto.

Primero vamos a crear una tabla para poder crear los queries.

Las tablas y los datos los encontramos en este link https://www.progress.com/tutorials/ado-net/code-examples-sql-server

Con el siguiente ejemplo podemos ver como generar el resultado de Json. Usando “FOR JSON PATH” y también podemos controlar el formato.

SELECT * FROM emp FOR JSON PATH

[{"empno":1,"ename":"JOHNSON","job":"ADMIN","mgr":6,"hiredate":"1990-12-17T00:00:00","sal":18000.00,"dept":4},{"empno":2,"ename":"HARDING","job":"MANAGER","mgr":9,"hiredate":"1998-02-02T00:00:00","sal":52000.00,"comm":300.00,"dept":3},{"empno":3,"ename":"TAFT","job":"SALES I","mgr":2,"hiredate":"1996-01-02T00:00:00","sal":25000.00,"comm":500.00,"dept":3},{"empno":4,"ename":"HOOVER","job":"SALES I","mgr":2,"hiredate":"1990-04-02T00:00:00","sal":27000.00,"dept":3},{"empno":5,"ename":"LINCOLN","job":"TECH","mgr":6,"hiredate":"1994-06-23T00:00:00","sal":22500.00,"comm":1400.00,"dept":4},{"empno":6,"ename":"GARFIELD","job":"MANAGER","mgr":9,"hiredate":"1993-05-01T00:00:00","sal":54000.00,"dept":4},{"empno":7,"ename":"POLK","job":"TECH","mgr":6,"hiredate":"1997-09-22T00:00:00","sal":25000.00,"dept":4},{"empno":8,"ename":"GRANT","job":"ENGINEER","mgr":10,"hiredate":"1997-03-30T00:00:00","sal":32000.00,"dept":2},{"empno":9,"ename":"JACKSON","job":"CEO","hiredate":"1990-01-01T00:00:00","sal":75000.00,"dept":4},{"empno":10,"ename":"FILLMORE","job":"MANAGER","mgr":9,"hiredate":"1994-08-09T00:00:00","sal":56000.00,"dept":2},{"empno":11,"ename":"ADAMS","job":"ENGINEER","mgr":10,"hiredate":"1996-03-15T00:00:00","sal":34000.00,"dept":2},{"empno":12,"ename":"WASHINGTON","job":"ADMIN","mgr":6,"hiredate":"1998-04-16T00:00:00","sal":18000.00,"dept":4},{"empno":13,"ename":"MONROE","job":"ENGINEER","mgr":10,"hiredate":"2000-12-03T00:00:00","sal":30000.00,"dept":2},{"empno":14,"ename":"ROOSEVELT","job":"CPA","mgr":9,"hiredate":"1995-10-12T00:00:00","sal":35000.00,"dept":1}]

Con el atributo de ROOT se puede crear un nodo principal para agregar todo el resto del JSON

SELECT empno,ename,job,mgr,hiredate,sal,comm,dept FROM emp

FOR JSON PATH, ROOT('emp')

{"emp":[{"empno":1,"ename":"JOHNSON","job":"ADMIN","mgr":6,"hiredate":"1990-12-17T00:00:00","sal":18000.00,"dept":4},{"empno":2,"ename":"HARDING","job":"MANAGER","mgr":9,"hiredate":"1998-02-02T00:00:00","sal":52000.00,"comm":300.00,"dept":3},{"empno":3,"ename":"TAFT","job":"SALES I","mgr":2,"hiredate":"1996-01-02T00:00:00","sal":25000.00,"comm":500.00,"dept":3},{"empno":4,"ename":"HOOVER","job":"SALES I","mgr":2,"hiredate":"1990-04-02T00:00:00","sal":27000.00,"dept":3},{"empno":5,"ename":"LINCOLN","job":"TECH","mgr":6,"hiredate":"1994-06-23T00:00:00","sal":22500.00,"comm":1400.00,"dept":4},{"empno":6,"ename":"GARFIELD","job":"MANAGER","mgr":9,"hiredate":"1993-05-01T00:00:00","sal":54000.00,"dept":4},{"empno":7,"ename":"POLK","job":"TECH","mgr":6,"hiredate":"1997-09-22T00:00:00","sal":25000.00,"dept":4},{"empno":8,"ename":"GRANT","job":"ENGINEER","mgr":10,"hiredate":"1997-03-30T00:00:00","sal":32000.00,"dept":2},{"empno":9,"ename":"JACKSON","job":"CEO","hiredate":"1990-01-01T00:00:00","sal":75000.00,"dept":4},{"empno":10,"ename":"FILLMORE","job":"MANAGER","mgr":9,"hiredate":"1994-08-09T00:00:00","sal":56000.00,"dept":2},{"empno":11,"ename":"ADAMS","job":"ENGINEER","mgr":10,"hiredate":"1996-03-15T00:00:00","sal":34000.00,"dept":2},{"empno":12,"ename":"WASHINGTON","job":"ADMIN","mgr":6,"hiredate":"1998-04-16T00:00:00","sal":18000.00,"dept":4},{"empno":13,"ename":"MONROE","job":"ENGINEER","mgr":10,"hiredate":"2000-12-03T00:00:00","sal":30000.00,"dept":2},{"empno":14,"ename":"ROOSEVELT","job":"CPA","mgr":9,"hiredate":"1995-10-12T00:00:00","sal":35000.00,"dept":1}]}

Podemos agrupar o crear Json dentro del Json principal como podemos ver en el siguiente ejemplo.

SELECT empno [emp.empno],ename [emp.ename],job,mgr,hiredate,sal,comm,dept FROM emp

FOR JSON PATH, ROOT('emp')

{"emp":[{"emp":{"empno":1,"ename":"JOHNSON"},"job":"ADMIN","mgr":6,"hiredate":"1990-12-17T00:00:00","sal":18000.00,"dept":4},{"emp":{"empno":2,"ename":"HARDING"},"job":"MANAGER","mgr":9,"hiredate":"1998-02-02T00:00:00","sal":52000.00,"comm":300.00,"dept":3},{"emp":{"empno":3,"ename":"TAFT"},"job":"SALES I","mgr":2,"hiredate":"1996-01-02T00:00:00","sal":25000.00,"comm":500.00,"dept":3},{"emp":{"empno":4,"ename":"HOOVER"},"job":"SALES I","mgr":2,"hiredate":"1990-04-02T00:00:00","sal":27000.00,"dept":3},{"emp":{"empno":5,"ename":"LINCOLN"},"job":"TECH","mgr":6,"hiredate":"1994-06-23T00:00:00","sal":22500.00,"comm":1400.00,"dept":4},{"emp":{"empno":6,"ename":"GARFIELD"},"job":"MANAGER","mgr":9,"hiredate":"1993-05-01T00:00:00","sal":54000.00,"dept":4},{"emp":{"empno":7,"ename":"POLK"},"job":"TECH","mgr":6,"hiredate":"1997-09-22T00:00:00","sal":25000.00,"dept":4},{"emp":{"empno":8,"ename":"GRANT"},"job":"ENGINEER","mgr":10,"hiredate":"1997-03-30T00:00:00","sal":32000.00,"dept":2},{"emp":{"empno":9,"ename":"JACKSON"},"job":"CEO","hiredate":"1990-01-01T00:00:00","sal":75000.00,"dept":4},{"emp":{"empno":10,"ename":"FILLMORE"},"job":"MANAGER","mgr":9,"hiredate":"1994-08-09T00:00:00","sal":56000.00,"dept":2},{"emp":{"empno":11,"ename":"ADAMS"},"job":"ENGINEER","mgr":10,"hiredate":"1996-03-15T00:00:00","sal":34000.00,"dept":2},{"emp":{"empno":12,"ename":"WASHINGTON"},"job":"ADMIN","mgr":6,"hiredate":"1998-04-16T00:00:00","sal":18000.00,"dept":4},{"emp":{"empno":13,"ename":"MONROE"},"job":"ENGINEER","mgr":10,"hiredate":"2000-12-03T00:00:00","sal":30000.00,"dept":2},{"emp":{"empno":14,"ename":"ROOSEVELT"},"job":"CPA","mgr":9,"hiredate":"1995-10-12T00:00:00","sal":35000.00,"dept":1}]}

De esta manera podemos generar documentos o resultados de Json con nuestros datos de SQLServer

Crear el patrón de diseño master-slave con Azure y SQLServer

La nube tiene una gran capacidad para aumentar o disminuir los recursos dependiendo de cuanto necesiten nuestras aplicaciones y nos da la libertad de configurar todo a nuestro gusto. Tanto las bases de datos como las aplicaciones, máquinas virtuales, etc.

Hace un tiempo tenía que desarrollar una aplicación que requería de alto rendimiento y una distribución geográfica en América, Asia y Europa. Con Azure esto lo podemos hacer de forma fácil.

Con este patrón de diseño de base de datos podemos tener una base de datos master que podemos usar para escribir y actualizar los datos que requerimos y otros slaves que podemos usar para lectura. 

En el caso de mi aplicación la parte de lectura era lo más demandante por lo tanto se dejaron 4 bases de datos para esta tarea y de esta manera se aumento el tiempo de respuesta de nuestra aplicación.

Primero se requiere tener una cuenta en Azure. Después debemos crear un servidor de SQL en alguna región que las tenemos disponibles en Azure.

Con la base el servidor listo creamos una nuestra base de datos. Lo siguiente es crear los servidores en las otras regiones que se necesiten. Ya con esto listo podemos iniciar la sincronización de nuestras bases de datos con un simple clic.

Para poder ver el menú de sonorización vamos a ir a la base de datos que deseamos usar como principal en Azure.

vamos a dar clic al menú de Geo-Replication.

La selección de las bases de datos para lectura es por medio de un clic

Como vemos en la imagen anterior tenemos una base de datos que es la primaria o master con la cual podemos escribir/actualizar datos. Y luego tenemos las secundarias que las podemos usar para leer y las tenemos en diferentes regiones.

En el menú de “Target Regions” es donde podemos seleccionar a cuáles regiones queremos replicar nuestra base de datos.

A partir de esto punto ya tenemos todo listo para que nuestros datos estén disponibles en diferentes regiones en el mundo.

¿Como migrar datos y estructura para bases de datos de SQL Server?

En muchas ocasiones nos toca migrar datos de una base de datos a otra de SQL server, comprar datos o estructura entre diferentes datos. Hay muchas herramientas muy buenas para hacer esta tarea sin embarga muchas tienen un alto costo.

Hace ya varios años que he utilizados las herramientas de Visual Studio para hacer esta tarea. Se puede hacer con bases de datos locales o también para migrar datos a la nube. Con un detalle que son gratuitas si ya se cuenta con el Visual Studio. De igualmente, estas vienen con la versión gratuita de VS.

Lo primero que tenemos que hacer es abrir el VS y vamos al menú de Tools

Debemos buscar las herramientas que dicen SQL Server. En este punto vamos a poder hacer la migración, comparación de datos y la comparación de la estructura.

Lo primero que vamos hacer es dar clic al botón de “New Data Comparison” esta opción nos permite compara los datos de todas las tablas o algunas para poder hacer la migración de datos o actualizar lo que se necesita.

Después de esto vamos a ver la siguiente venta

Donde vamos a poder seleccionar la base de datos de origen que serial la de la izquierda y luego la base de datos de destinos donde queremos actualizar los datos que serian los de la derecha. Si vemos los check boxes de la izquierda podemos cambiar algunas opciones para ver el nivel de comparación de datos que deseamos visualizar.

• Pprimero nos permite ver los datos que son diferentes y podemos actualizar en la base de datos de destino
• El segundo cuales datos están únicamente en el destino y podemos insertar en la base de datos de destino
• El tercero nos muestra los datos que existen únicamente en la base de datos de destino y podemos borrar
•  Y la ultima opciones es para ver los datos que son iguales en las dos bases de datos 

Cuando tenemos las dos bases de datos seleccionadas debemos de dar clic al botón de siguiente.

Después de esto vamos a ver una pantalla que nos permite seleccionar cuales son las tablas que deseamos sincronizar. Podemos seleccionar todas o únicamente la que requerimos.

Damos clic en finalizar y el proceso inicia la comparación de todas las tablas seleccionadas. Cuando el proceso termina vamos a ver el resultado para cada uno de las categorías o los check boxes que seleccionamos.

Vamos a poder ver un botón en la parte superior que dice “Update Target” con el que podemos sincronizar los datos.

Ahora también podemos navegar por los datos para seleccionar únicamente lo que deseamos sincronizar.

Para poder comparar y sincronizar la estructura debemos ir a la opción que dice “New Schema Comparison”

Con esto vamos poder ver la estructura de las dos bases de datos y hacer los cambios necesarios. En la siguiente ventana solamente debemos seleccionar la base de datos de origen y la base de datos de destino. Con esto estamos listos para verificar la estructura de la base de datos.

Crear una aplicación de Microsoft MVC core 2.0 con identity

Primero vamos a ir a Visual Studio 2017 y crear una aplicación en el folder Web.  

Cuando creamos el proyecto le debemos dar el nombre (en mi caso "IdentityTest") y buscamos el directorio donde vamos a guardar nuestro proyecto.  

Luego en la siguiente pantalla seleccionamos el proyecto de tipo Web Application. Luego debemos dar clic en el botón "Change Authenication". Como podemos observar antes de cambiar esta opción en el área que dice Athentication dice "No Authentication"  

En la opción Change Authentication debemos seleccionar "Individual User Accounts". Después de esto damos clic en Ok. 

Como vemos el texto del área de Authentication cambia por "Individual User Account" .

Ahora lo que tenemos que hacer es dar clic en el botón de ok para que VS2017 termine de configurar el proyecto.  

Cuando VS2017 termina de crear nuestro proyecto debemos ir al archivo "appsettingsjson". Cuando abrimos el archivo vamos a ver un connectionString que se llama default. 

Se tiene que cambiar este connectionString con el de su base de datos. No es necesario tener los objetos de identity en la base de datos. Estos se pueden crear con el proceso que vamos a ver en unos momentos.  

Ahora vamos a ir al Startup.cs class para ver la configuración básica que se creó para la autenticación de nuestros sistemas.  

public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

            services.AddIdentity<ApplicationUser, IdentityRole>()
                .AddEntityFrameworkStores<ApplicationDbContext>()
                .AddDefaultTokenProviders();

            services.AddMvc()
                .AddRazorPagesOptions(options =>
                {
                    options.Conventions.AuthorizeFolder("/Account/Manage");
                    options.Conventions.AuthorizePage("/Account/Logout");
                });

            // Register no-op EmailSender used by account confirmation and password reset during development
            // For more information on how to enable account confirmation and password reset please visit https://go.microsoft.com/fwlink/?LinkID=532713
            services.AddSingleton<IEmailSender, EmailSender>();
        }

Como se puede observar se está usando el DefaulConnection para identificar cual es la base de datos que deseamos usar.  

También dentro de nuestro método ConfigurationServices podemos agregar algunas configuraciones adicionales a nuestro sistema de autenticación. Podemos agregar la longitud de la contraseña, etc.  

Ahora si estamos listos para correr nuestra aplicación.  cuando está corriendo la aplicación nos vamos a ir a login para ingresar a nuestra app. A continuación, nos dirigimos a la página para registrar usuarios y tratamos de crear un usuario. En ese momento vamos a ver la siguiente página que parece un error.  

Damos clic en el botón que dice Apply Migrations con esto creamos los objetos para el servicio de identity en nuestro servidor. Cuando se termina el proceso de migración podemos refrescar la página y todo va a funcionar bien. Además, si vamos a la base de datos vamos a poder ver los objetos creados.  

Con esto ya tenemos listo nuestro sistema mvc core 2.0 usando identity.  Ahora puede crear y autenticar sus usuarios.

Puede encontrar el ejemplo aquí   Ejemplo

ImplementarEF Core 2.0