// Dapper Plus

// Doc: https://dapper-tutorial.net/dapper-query-data

​

// @nuget: Dapper -Version 1.60.6

// @nuget: Z.Dapper.Plus

​

using Dapper;

using Z.Dapper.Plus;

using Z.BulkOperations;

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.Mvc;

​

namespace DapperDemo

{

    public class HomeController : Controller

    {

        //It is a limitation on fiddle to use Index action, 

        //but the actual action definition is as follows in the comment

        //and the "id" parameter is provided hardcoded for demo purposes only

        //public ActionResult Details(int id)

        public ActionResult Index()

        {

            int id = 1;

            CreateDatabase();

            Customer customer = new Customer();

            using (IDbConnection db = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))

            {

                customer = db.Query<Customer>("Select * From Customers WHERE CustomerID =" + id, new { id }).SingleOrDefault();

            }

            return View(customer);

        }

        public static void CreateDatabase()

        {

            using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))

            {

                connection.Open();

                using (var command = connection.CreateCommand())

                {

                    command.CommandText = @"   

CREATE TABLE [dbo].[Customers] (

    [CustomerID] INT            IDENTITY (1, 1) NOT NULL,

    [FirstName]  NVARCHAR (MAX) NULL,

    [LastName]   NVARCHAR (MAX) NULL,

    [Email]      NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_dbo.Customers] PRIMARY KEY CLUSTERED ([CustomerID] ASC)

);

​

INSERT INTO Customers (FirstName, LastName, Email) Values ('Carson', 'Alexander', 'carson.alexander@example.com');

INSERT INTO Customers (FirstName, LastName, Email) Values ('Meredith', 'Alonso', 'Meredith.Alonso@example.com');

INSERT INTO Customers (FirstName, LastName, Email) Values ('Arturo', 'Anand', 'Arturo.Anand@example.com');

INSERT INTO Customers (FirstName, LastName, Email) Values ('Gytis', 'Barzdukas', 'Gytis.Barzdukas@example.com');

INSERT INTO Customers (FirstName, LastName, Email) Values ('Yan', 'Li', 'Yan.Li@example.com');

";

                    command.ExecuteNonQuery();

                }

            }

        }

    }

}