// Dapper Plus

// Doc: https://dapper-tutorial.net/dapper-insert-and-update

​

// @nuget: Dapper -Version 1.60.6

// @nuget: Z.Dapper.Plus

// @nuget: CsvHelper

​

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;

using CsvHelper;

using System.IO;

​

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

        // GET: Customer/Create

        //public ActionResult ImportCustomers()

        public ActionResult Index()

        {

            CreateDatabase();

            CustomerData data = new CustomerData()

            {

                Text = "Sally, Whittaker,sally.whittaker@example.com" + Environment.NewLine +

                       "Belinda, Jameson, belinda.jameson@example.com" + Environment.NewLine +

                       "Jeff, Smith, jeff.smith@example.com" + Environment.NewLine +

                       "Sandy, Allen, sandy.allen@example.com" + Environment.NewLine

            };

            return View(data);

        }

​

        [HttpPost]

        public ActionResult ImportCustomers(CustomerData data)

        {

            DapperPlusManager.Entity<Customer>().Table("Customers");

​

            try

            {

                List<Customer> customers = new List<Customer>();

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

                {                 

                    using (var csv = new CsvReader(new StringReader(data.Text)))

                    {

                        csv.Configuration.HasHeaderRecord = false;

                        while (csv.Read())

                        {

                            string[] result = new string[3];

                            string value;

​

                            for (int i = 0; csv.TryGetField<string>(i, out value); i++)

                            {

                                result[i] = value;

                            }

​

                            Customer c = new Customer()

                            {

                                FirstName = result[0],

                                LastName = result[1],

                                Email = result[2]

                            };

​

                            customers.Add(c);

                        }

                    }

​

                    db.BulkInsert(customers);

                }

​

                return RedirectToAction("Index", "Customer");

            }

            catch (Exception e)

            {

                return View();

            }

        }

        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();

                }

            }

        }

    }

}