ABM y listado de una tabla de Sql Server con Ado.Net

Veremos todos los pasos que debemos enfrentar cuando queremos implementar una aplicación que se conecta a un servidor de base de datos SQL Server y proceder a efectuar un listado, alta, baja y modificación de una tabla.

Emplearemos la tecnología Ado.Net para el acceso a Sql Server.

Problema

Crear con el "Microsoft SQL Server Management Studio" una base de datos llamada "base1" y luego una tabla llamada "articulos" con la siguiente estructura:

ASP.Net MVC Ado.Net Altas bajas y modificaciones

Cargar un par de registros en la tabla:

Resolveremos el problema enunciando uno a uno los pasos que debemos dar en el Visual Studio .Net para ir creando y codificando cada uno de los archivos necesarios en las carpetas "Controllers", "Views" y "Models".

Creamos el proyecto (Proyecto6)

Creación del proyecto. Para esto seleccionamos desde el menú la opción "Archivo" -> "Nuevo" -> "Proyecto..."

Aparece un diálogo donde debemos indicar del lado izquierdo que utilizaremos el lenguaje Visual C# y del lado de la derecha seleccionamos "Aplicación web ASP.NET (.Net Framework)" y en la parte inferior definimos el "nombre", "ubicación" y "nombre de la solución" (podemos usar el mismo texto para el "nombre de la solución" y "nombre"):

Aparece un segundo diálogo donde seleccionaremos que cree un proyecto vacío y utilice el patrón MVC:

Ya tenemos nuestro proyecto creado, ahora modificaremos el archivo "Web.Config" donde debemos definir la cadena de conexión con la base de datos "base1":

<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  https://go.microsoft.com/fwlink/?LinkId=301880
  -->
<configuration>

  <connectionStrings>
    <add name="administracion" connectionString="Initial Catalog=base1;Data Source=DIEGO-PC\SQLEXPRESS;Integrated Security=true"/>
  </connectionStrings>

  <appSettings>
    <add key="webpages:Version" value="3.0.0.0"/>
    <add key="webpages:Enabled" value="false"/>
    <add key="ClientValidationEnabled" value="true"/>
    <add key="UnobtrusiveJavaScriptEnabled" value="true"/>
  </appSettings>
  <system.web>
    <compilation debug="true" targetFramework="4.6.1"/>
    <httpRuntime targetFramework="4.6.1"/>
  </system.web>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35"/>
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35"/>
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35"/>
        <bindingRedirect oldVersion="1.0.0.0-5.2.3.0" newVersion="5.2.3.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs"
        type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.5.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701"/>
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
        type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.5.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+"/>
    </compilers>
  </system.codedom>
</configuration>

Agregamos el elemento connectionStrings donde definimos la propiedad connectionString con la cadena de conexión:

  <connectionStrings>
    <add name="administracion" connectionString="Initial Catalog=base1;Data Source=DIEGO-PC\SQLEXPRESS;Integrated Security=true"/>
  </connectionStrings>

La base de datos se define en la propiedad Initial Catalog, luego en la propiedad Data Source le asignamos el nombre de nuestro servidor. Para conocer el nombre de nuestro servidor de SQL Server desde el Management Studio presionamos el botón derecho del mouse:

Microsoft Visual Studio - Nuevo Proyecto MVC con C# con Modelo Vista Controlador

Crearemos ahora dos clases en la carpeta "Models":

Articulo.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Proyecto6.Models
{
    public class Articulo
    {
        public int Codigo { get; set; }
        public string Descripcion { get; set; }
        public float Precio { get; set; }
    }
}

La clase Articulo representa el modelo de datos (coincide con la estructura de la tabla "articulos" creada en SQL Server)

Y la segunda clase a crear es "MantenimientoArticulo":

MantenimientoArticulo.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;


namespace Proyecto6.Models
{
    public class MantenimientoArticulo
    {
        private SqlConnection con;

        private void Conectar()
        {
            string constr = ConfigurationManager.ConnectionStrings["administracion"].ToString();
            con = new SqlConnection(constr);
        }

        public int Alta(Articulo art)
        {
            Conectar();
            SqlCommand comando = new SqlCommand("insert into articulos(codigo,descripcion,precio) values (@codigo,@descripcion,@precio)", con);
            comando.Parameters.Add("@codigo", SqlDbType.Int);
            comando.Parameters.Add("@descripcion", SqlDbType.VarChar);
            comando.Parameters.Add("@precio", SqlDbType.Float);
            comando.Parameters["@codigo"].Value = art.Codigo;
            comando.Parameters["@descripcion"].Value = art.Descripcion;
            comando.Parameters["@precio"].Value = art.Precio;
            con.Open();
            int i = comando.ExecuteNonQuery();
            con.Close();
            return i;
        }

        public List<Articulo> RecuperarTodos()
        {
            Conectar();
            List<Articulo> articulos = new List<Articulo>();

            SqlCommand com = new SqlCommand("select codigo,descripcion,precio from articulos", con);
            con.Open();
            SqlDataReader registros = com.ExecuteReader();
            while (registros.Read())
            {
                Articulo art = new Articulo
                {
                    Codigo = int.Parse(registros["codigo"].ToString()),
                    Descripcion = registros["descripcion"].ToString(),
                    Precio = float.Parse(registros["precio"].ToString())
                };
                articulos.Add(art);
            }
            con.Close();
            return articulos;
        }

        public Articulo Recuperar(int codigo)
        {
            Conectar();
            SqlCommand comando = new SqlCommand("select codigo,descripcion,precio from articulos where codigo=@codigo", con);
            comando.Parameters.Add("@codigo", SqlDbType.Int);
            comando.Parameters["@codigo"].Value = codigo;
            con.Open();
            SqlDataReader registros = comando.ExecuteReader();
            Articulo articulo = new Articulo();
            if (registros.Read())
            {
                articulo.Codigo = int.Parse(registros["codigo"].ToString());
                articulo.Descripcion = registros["descripcion"].ToString();
                articulo.Precio = float.Parse(registros["precio"].ToString());
            }
            con.Close();
            return articulo;
        }


        public int Modificar(Articulo art)
        {
            Conectar();
            SqlCommand comando = new SqlCommand("update articulos set descripcion=@descripcion,precio=@precio where codigo=@codigo", con);
            comando.Parameters.Add("@descripcion", SqlDbType.VarChar);
            comando.Parameters["@descripcion"].Value = art.Descripcion;
            comando.Parameters.Add("@precio", SqlDbType.Float);
            comando.Parameters["@precio"].Value = art.Precio;
            comando.Parameters.Add("@codigo", SqlDbType.Int);
            comando.Parameters["@codigo"].Value = art.Codigo;
            con.Open();
            int i = comando.ExecuteNonQuery();
            con.Close();
            return i;
        }

        public int Borrar(int codigo)
        {
            Conectar();
            SqlCommand comando = new SqlCommand("delete from articulos where codigo=@codigo", con);
            comando.Parameters.Add("@codigo", SqlDbType.Int);
            comando.Parameters["@codigo"].Value = codigo;
            con.Open();
            int i = comando.ExecuteNonQuery();
            con.Close();
            return i;
        }
    }
}

La clase MantenimientoArticulo tiene una serie de métodos que nos permiten insertar una fila en la tabla "Articulos" llamando al método "Alta". Llega como parámetro un objeto de la clase "Articulo" llamado "art":

        public int Alta(Articulo art)
        {

Lllamamos al método "Conectar" que tiene por objetivo iniciar la variable "con" con la conexión a la base de datos "base1":

            Conectar();

Luego creamos un objeto de la clase SqlCommand y pasamos como parámetro el comando SQL que nos permite cargar una fila en la tabla artículos:

            SqlCommand comando = new SqlCommand("insert into articulos(codigo,descripcion,precio) values (@codigo,@descripcion,@precio)", con);

Definimos los tipos y valores parametrizados:

            comando.Parameters.Add("@codigo", SqlDbType.Int);
            comando.Parameters.Add("@descripcion", SqlDbType.VarChar);
            comando.Parameters.Add("@precio", SqlDbType.Float);
            comando.Parameters["@codigo"].Value = art.Codigo;
            comando.Parameters["@descripcion"].Value = art.Descripcion;
            comando.Parameters["@precio"].Value = art.Precio;

Abrimos la conexión y pedimos a SQL Server que ejecute el comando respectivo:

            con.Open();
            int i = comando.ExecuteNonQuery();
            con.Close();
            return i;
        }

Los métodos "Modificar", "Borrar", "Recuperar" y "RecuperarTodos" son similares pero implementando los algoritmos respectivos.

Ahora creamos el "Controlador" como hicimos en conceptos anteriores.

El controlador principal siempre lo llamaremos "Home", para esto presionamos el botón derecho del mouse sobre la carpeta "Controllers" y seleccionar "Agregar" -> Controlador...:

En el diálogo seleccionamos "Controlador de MVC 5: en blanco":.

Visual Studio .Net nos genera el esqueleto básico de nuestro controlador llamado HomeController solo con la acción Index:

HomeController.cs"
```
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace Proyecto6.Controllers
{
    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            return View();
        }
    }
}
```

Listado

Veamos ahora que debemos implementar en la acción "Index" del controlador "HomeController" (el objetivo de este controlador será mostrar todos los artículos en una página HTML):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Proyecto6.Models;

namespace Proyecto6.Controllers
{
    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            return View(ma.RecuperarTodos());
        }
    }
}

Creamos un objeto de la clase "MantenimientoArticulo" y llamamos al método "RecuperarTodos" y se lo pasamos a la vista.

La vista todavía no existe por lo que presionamos el botón derecho del mouse sobre "Index()" y seleccionamos "Agregar Vista", en este diálogo en plantilla seleccionamos "Empty":

Ahora se ha generado el archivo "Index.cshtml" :


@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <div> 
    </div>
</body>
</html>

Para que se muestren todos los artículos debemos implementar el siguiente algoritmo en el archivo Index.cshtml:

@model IEnumerable<Proyecto6.Models.Articulo>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <table class="table">
        <tr>
            <th>
                Codigo
            </th>
            <th>
                Descripcion
            </th>
            <th>
                Precio
            </th>
            <th>
                Borrar
            </th>
            <th>
                Modificar
            </th>
        </tr>

        @foreach (var articulo in Model)
        {
            <tr>
                <td>
                    @articulo.Codigo
                </td>
                <td>
                    @articulo.Descripcion
                </td>
                <td>
                    @articulo.Precio
                </td>
                <td>
                    <a href="/Home/Baja?cod=@articulo.Codigo">borra?</a>
                </td>
                <td>
                    <a href="/Home/Modificacion?cod=@articulo.Codigo">modifica?</a>
                </td>
            </tr>
        }

    </table>
    <a href="/Home/Alta">Agregar</a>
</body>
</html>

Desde el controlador recordemos que llamamos al método View y le pasamos como parámetro lo que retorna el método RecuperarTodos. El método RecuperarTodos de la clase MantenimientoArticulo retorna un objeto List<Articulo>:

        public ActionResult Index()
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            return View(ma.RecuperarTodos());
        }

En la vista con la siguiente sintaxis indicamos el modelo:

@model IEnumerable<Proyecto6.Models.Articulo>

Mediante un foreach recorremos la lista que llegó desde el controlador y se almacenó en la propiedad Model:

        @foreach (var articulo in Model)
        {
            <tr>
                <td>
                    @articulo.Codigo
                </td>
                <td>
                    @articulo.Descripcion
                </td>
                <td>
                    @articulo.Precio
                </td>
                <td>
                    <a href="/Home/Baja?cod=@articulo.Codigo">borra?</a>
                </td>
                <td>
                    <a href="/Home/Modificacion?cod=@articulo.Codigo">modifica?</a>
                </td>
            </tr>
        }

Si ejecutamos ahora nuestra aplicación ya podemos ver el listado completo de artículos:

Como vemos se muestra una tabla HTML con todos los datos de la tabla "Articulos".

Hemos dispuesto en la parte final de la página un hipervínculo a la acción "Alta" del controlador "Home" (todavía no la hemos codificado)

Alta

Ya tenemos la página principal que muestra todas las filas de la tabla "articulos", ahora implementaremos el alta de artículos. Debemos definir dos acciones más en la clase "HomeController":

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Proyecto6.Models;

namespace Proyecto6.Controllers
{
    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            return View(ma.RecuperarTodos());
        }

        public ActionResult Alta()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Alta(FormCollection collection)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            Articulo art = new Articulo
            {
                Codigo = int.Parse(collection["codigo"]),
                Descripcion = collection["descripcion"],
                Precio = float.Parse(collection["precio"].ToString())
            };
            ma.Alta(art);
            return RedirectToAction("Index");
        }
    }
}

Si bien las dos acciones se llaman igual "Alta" la segunda se le agrega el atributo [HttpPost].

La primera de las acciones Alta() tiene por objetivo mostrar la vista con un formulario HTML que permita ingresar los datos de un artículo. Luego cuando se presione el botón de tipo "submit" se ejecutará la otra acción Alta que recibe como parámetro los datos cargados en el formulario.

Solo la primer acción "Alta" se le debe asociar una vista. Para esto presionamos el botón derecho del mouse en Alta() y seleccionamos "Agregar Vista":

Tenemos ahora creada la vista Alta.cshtml:


@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Alta</title>
</head>
<body>
    <div>
        <form method="post" action="/Home/Alta">
            Codigo:
            <input type="text" name="codigo" />
            <br />
            Descripcion:
            <input type="text" name="descripcion" />
            <br />
            Precio:
            <input type="text" name="precio" />
            <br />
            <input type="submit" value="Confirmar" />
        </form>
    </div>
</body>
</html>

Cuando lo ejecutamos aparece la siguiente página en el navegador:

Como podemos observar la página HTML dispone un formulario donde se deben ingresar el código, descripción y precio del artículo.

Cuando se presiona el botón se ejecuta la acción "Alta" del controlador "Home":

        <form method="post" action="/Home/Alta">

Cuando se presiona el botón de tipo submit se ejecuta la acción:

        [HttpPost]
        public ActionResult Alta(FormCollection collection)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            Articulo art = new Articulo
            {
                Codigo = int.Parse(collection["codigo"]),
                Descripcion = collection["descripcion"],
                Precio = float.Parse(collection["precio"].ToString())
            };
            ma.Alta(art);
            return RedirectToAction("Index");
        }

Como podemos ver a este método llega un objeto de la clase FormCollection donde se almacenan todos los datos cargados en el formulario.

No se asocia una vista a esta acción ya que mediante el método RedirectToAction se solicita la ejecución de otra acción, en éste caso la acción "Index" que tiene por objetivo mostrar el listado completo de artículos.

Baja

Implementaremos ahora el algoritmo para borrar un artículo cuando se presione el hipervínculo "borra?".

Debemos codificar la siguiente acción llamada Baja en el controlador HomeController:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Proyecto6.Models;

namespace Proyecto6.Controllers
{
    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            return View(ma.RecuperarTodos());
        }

        public ActionResult Alta()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Alta(FormCollection collection)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            Articulo art = new Articulo
            {
                Codigo = int.Parse(collection["codigo"]),
                Descripcion = collection["descripcion"],
                Precio = float.Parse(collection["precio"].ToString())
            };
            ma.Alta(art);
            return RedirectToAction("Index");
        }

        public ActionResult Baja(int cod)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            ma.Borrar(cod);
            return RedirectToAction("Index");
        }
    }
}

Es decir que cuando desde la vista Index presionamos el hipervínculo para borrar un determinado artículo se ejecuta la acción "Baja" y llega como parámetro el código de articulo a borrar:

        public ActionResult Baja(int cod)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            ma.Borrar(cod);
            return RedirectToAction("Index");
        }

Luego de borrarlo de la tabla de la base de datos procedemos a llamar a la acción "Index".

Modificación

Para la modificación de un artículo debemos implementar dos acciones en el controlador. El código a implementar es:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Proyecto6.Models;

namespace Proyecto6.Controllers
{
    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            return View(ma.RecuperarTodos());
        }

        public ActionResult Alta()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Alta(FormCollection collection)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            Articulo art = new Articulo
            {
                Codigo = int.Parse(collection["codigo"]),
                Descripcion = collection["descripcion"],
                Precio = float.Parse(collection["precio"].ToString())
            };
            ma.Alta(art);
            return RedirectToAction("Index");
        }

        public ActionResult Baja(int cod)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            ma.Borrar(cod);
            return RedirectToAction("Index");
        }

        public ActionResult Modificacion(int cod)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            Articulo art = ma.Recuperar(cod);
            return View(art);
        }

        [HttpPost]
        public ActionResult Modificacion(FormCollection collection)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            Articulo art = new Articulo
            {
                Codigo = int.Parse(collection["codigo"].ToString()),
                Descripcion = collection["descripcion"].ToString(),
                Precio = float.Parse(collection["precio"].ToString())
            };
            ma.Modificar(art);
            return RedirectToAction("Index");
        }
    }
}

Hay que generar la vista para la acción presionando el botón derecho del mouse:

        public ActionResult Modificacion(int cod)


@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Modificacion</title>
</head>
<body>
    <div> 
    </div>
</body>
</html>

Debemos codificar la página para que muestre los datos precargados, Luego el archivo "Modificacion.cshtml" debe ser:

@model Proyecto6.Models.Articulo

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Alta</title>
</head>
<body>
    <div>
        <form method="post" action="/Home/Modificacion">
            Codigo:
            <input type="text" name="codigo" value="@Model.Codigo" readonly />
            <br />
            Descripcion:
            <input type="text" name="descripcion" value="@Model.Descripcion" />
            <br />
            Precio:
            <input type="text" name="precio" value="@Model.Precio"/>
            <br />
            <input type="submit" value="Confirmar" />
        </form>
    </div>
</body>
</html>

Al ejecutar la aplicación y seleccionar el hipervínculo de "modifica?" tenemos la siguiente interfaz:

Como podemos ver disponemos el control input de HTML donde se ingresa el código de solo lectura "readonly".

Cuando se presiona el botón submit se ejecuta la acción donde se cambian los datos de la tabla:

        [HttpPost]
        public ActionResult Modificacion(FormCollection collection)
        {
            MantenimientoArticulo ma = new MantenimientoArticulo();
            Articulo art = new Articulo
            {
                Codigo = int.Parse(collection["codigo"].ToString()),
                Descripcion = collection["descripcion"].ToString(),
                Precio = float.Parse(collection["precio"].ToString())
            };
            ma.Modificar(art);
            return RedirectToAction("Index");
        }

Acotaciones.

En este concepto hemos mostrado una aproximación más realista de los algoritmos que tenemos que implementar en cada una de las carpetas del MVC.

Vemos que se encuentra perfectamente separada la lógica de la aplicación en el "Modelo", la presentación e ingreso de datos en la "Vista" y la coordinación en el "Controlador".

En la vista codificamos fundamentalmente directamente con HTML y accedemos a los datos mediante Razor. Veremos en el concepto siguiente el mismo problema pero utilizando las herramientas que propone Visual Studio .net para generar las vistas en forma automática.