Wednesday, 12 December 2018

Simple Way to Create Your MVC APP for Curd Operation.

Simple Way to Create Your MVC APP for Curd Operation.

                               Database SQL Server

Step:1 Create Database For Student
Step:2 Create Table for Student use the following Script to create Table:-



USE [StudentDB]
GO
/****** Object:  Table [dbo].[StudentInfo]    Script Date: 12/13/2018 2:55:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentInfo](
[Roll] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Course] [varchar](100) NULL,
[Mobile] [varchar](100) NULL,
[Address] [varchar](100) NULL,
 CONSTRAINT [PK_StudentInfo] PRIMARY KEY CLUSTERED
(
[Roll] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Step:3 Create The following Procedure using below Scripts:-

Script For getStudentList:-

USE [StudentDB]
GO
/****** Object:  StoredProcedure [dbo].[getStudentList]    Script Date: 12/13/2018 3:00:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Dot Net By Priyanshu
-- Create date: 13-12-2018
-- Description: getStudentList is use  to get all student records List.
-- =============================================
CREATE PROCEDURE [dbo].[getStudentList]
AS
BEGIN
SET NOCOUNT ON;
SELECT [Roll]
      ,[Name]
      ,[Course]
      ,[Mobile]
      ,[Address]
  FROM [StudentDB].[dbo].[StudentInfo]
END

Script For ManageStudent:-

USE [StudentDB]
GO
/****** Object:  StoredProcedure [dbo].[ManageStudent]    Script Date: 12/13/2018 3:00:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Dot Net By Priyanshu
-- Create date: 13-12-2018
-- Description: ManageStudent is Use for Insert or Update the student Record.
-- =============================================
CREATE PROCEDURE [dbo].[ManageStudent](@Roll int
      ,@Name varchar(100)
      ,@Course varchar(100)
      ,@Mobile varchar(100)
      ,@Address varchar(100))
AS
BEGIN
SET NOCOUNT ON;
if(@Roll>0)
begin
update [StudentDB].[dbo].[StudentInfo] set [Name]=@Name,[Course]=@Course,[Mobile]=@Mobile,[Address]=@Address where Roll=@roll;
end
else
begin
insert into [StudentDB].[dbo].[StudentInfo]([Name],[Course],[Mobile],[Address]) values(@Name, @Course,@Mobile,@Address);
end END

Script for getStudentDetail:-

USE [StudentDB]
GO
/****** Object:  StoredProcedure [dbo].[getStudentDetail]    Script Date: 12/13/2018 3:00:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Dot Net By Priyanshu
-- Create date: 13-12-2018
-- Description: getStudentDetails is use to get the particular student record.
-- =============================================
CREATE PROCEDURE [dbo].[getStudentDetail](@roll int)
AS
BEGIN
SET NOCOUNT ON;
SELECT [Roll]
      ,[Name]
      ,[Course]
      ,[Mobile]
      ,[Address]
  FROM [StudentDB].[dbo].[StudentInfo] where Roll=@roll;
END

Script For DeleteStudent:-

USE [StudentDB]
GO
/****** Object:  StoredProcedure [dbo].[DeleteStudent]    Script Date: 12/13/2018 3:00:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Dot Net By Priyanshu
-- Create date: 13-12-2018
-- Description: DeleteStudent is use to delete the particular existing student Record.
-- =============================================
ALTER PROCEDURE [dbo].[DeleteStudent](@roll int)
AS
BEGIN
SET NOCOUNT ON;
Delete  FROM [StudentDB].[dbo].[StudentInfo] where Roll=@roll;
END

                                            Project

Add the MVC Project:-

Step:1 Click on file menu.
Step:2 Click on New.
Step:3 Click on Project.
Step:4 Select ASP.Net Web Application(.Net Framework) 
Step:5 Write the project Name.
Step:6 Select MVC option.
Step:7 Click on Ok Button

Add DataLayer in Solution

Step:1 Right Click on Solution.
Step:2 Click on Add Option.
Step:3 Click on New Project.
Step:4 Click on Visual C# from Left Pane.
Step:5 Select Class Library(.Net Framework).
Step:6 Write Name of Class Library as "DataLayer".

Add Entity Framework in DataLayer

Step:1 Right Click on DataLayer.
Step:2 Click on Add Option.
Step:3 Click on New Item.
Step:4 Select ADO.Net Entity Data Model.
Step:5 Write name of Entity Data Model as  "StudentDBEntities".
Step:6 Click on Add button.
Step:7 Select Ef Designer from Database.
Step:8 Click on Next button.
Step:9 Click On New Connetion button.
Step:10 Select/Enter Server Name.
Step:11 Select/Enter Database Name.
Step:12 Click On Ok button.
Step:13 Select Entity Version 6.x.
Step:14 Select Store Procedure.
Step:15 Click on Finish button.

Add Reference in Web App

1.Add DataLayer Reference in Web App.  
2.Add EntityFramework.6.2.0 from Manage NuGet Packages and install it (if Error Occurs).

Add Model in App:-

Step:1 Right Click over the Model Folder.
Step:2 Click on Add Option.
Step:3 Click on New Item.
Step:4 Select Class Library.
Step:5 Write Class Name "StudentModel".
Step:6 Add the following Code in Model.

StudentModel

using DataLayer;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace MyFirstMVCApp.Models
{
    public class StudentModel
    {
        #region Variable
        // Created Global objStudentModel.
        StudentModel objStudentModel = null;
        #endregion

        #region Properties

        public int Roll { get; set; }
        //Required field Validation using DataAnnotations.
        [Required(ErrorMessage = "Name is Required!")]
        //For Display the field Name.
        [Display(Name = "Name")]
        public string Name { get; set; }
        //Required field Validation using DataAnnotations.
        [Required(ErrorMessage = "Course is Required!")]
        //For Display the field Name.
        [Display(Name = "Course")]
        public string Course { get; set; }
        //Required field Validation using DataAnnotations.
        [Required(ErrorMessage = "Mobile is Required!")]
        //For Display the field Name.
        [Display(Name = "Mobile")]
        public string Mobile { get; set; }
        //Required field Validation using DataAnnotations.
        [Required(ErrorMessage = "Address is Required!")]
        //For Display the field Name.
        [Display(Name = "Name")]
        public string Address { get; set; }

        #endregion

        #region ManageStudent
        //Method for data Insert or Update. On the basis of roll when roll is zero then insert otherwise Update the record.
        public int ManageStudent(StudentModel std)
        {
            using (var context = new StudentDBEntities())
            {
                //Declear SqlParameter and Assign value in it.
                var roll = new SqlParameter("@Roll", SqlDbType.Int);
                roll.Value = std.Roll;
                var name = new SqlParameter("@Name", SqlDbType.VarChar);
                name.Value = std.Name;
                var course = new SqlParameter("@Course", SqlDbType.VarChar);
                course.Value = std.Course;
                var mobile = new SqlParameter("@Mobile", SqlDbType.VarChar);
                mobile.Value = std.Mobile;
                var address = new SqlParameter("@Address", SqlDbType.VarChar);
                address.Value = std.Address;
                var result = 0;
                if (std.Roll != 0)
                {
                    //Calling Procedure [dbo].[ManageStudent] with following Parameter @Roll,@Name,@Course,@Mobile,@Address
                    result = context.Database.ExecuteSqlCommand("Exec [dbo].[ManageStudent] @Roll,@Name,@Course,@Mobile,@Address", roll, name, course, mobile, address);
                }
                else
                {
                    //Calling Procedure [dbo].[ManageStudent] with following Parameter @Roll,@Name,@Course,@Mobile,@Address
                    result = context.Database.ExecuteSqlCommand("Exec [dbo].[ManageStudent] @Roll,@Name,@Course,@Mobile,@Address", roll, name, course, mobile, address);
                }
                return result;
            }
        }
        #endregion

        #region Student Details
        //Method for Get Student details for binding edit from for Update
        public StudentModel StudentDetails(int roll)
        {
            objStudentModel = new StudentModel();
            using (var context = new StudentDBEntities())
            {
                //calling Sql Procedure with roll Parameter to get Student details and set it in Model Properties using Linq. 
                objStudentModel = (from std in context.getStudentDetail(roll)
                                   select new StudentModel
                                   {
                                       Roll = std.Roll,
                                       Name = std.Name,
                                       Course = std.Course,
                                       Mobile = std.Mobile,
                                       Address = std.Address
                                   }).FirstOrDefault();
            }
            return objStudentModel;
        }
        #endregion

        #region Student List
        //Method for Get Students list to display in List.
        public List<StudentModel> StudentList()
        {
            List<StudentModel> StdtList = new List<StudentModel>();
            using (var Context = new StudentDBEntities())
            {//calling Sql Procedure get Students details and set it in List Model Properties using Linq. 
                StdtList = (from std in Context.getStudentList()
                            orderby std.Roll ascending
                            select new StudentModel
                            {
                                Roll = std.Roll,
                                Name = std.Name,
                                Course = std.Course,
                                Mobile = std.Mobile,
                                Address = std.Address
                            }).ToList();
            }
            return StdtList;
        }

        #endregion

        #region Delete Student
        // Method for Delete the existing Records
        public int DeleteStudent(int roll)
        {
            using (var Context = new StudentDBEntities())
            {
                // calling sql procedure with roll parameter to delete record.
                var id = new SqlParameter("@roll", SqlDbType.Int);
                id.Value = roll;
                var result = Context.Database.ExecuteSqlCommand("exec [dbo].[DeleteStudent] @roll", id);
                return result;
            }

        }
        #endregion
    }
}

Add StudentController in App:-

Step:1 Right Click over the Controller Folder.
Step:2 Click on Add Option.
Step:3 Click on Controller Option.
Step:4 Select MVC 5 Controller; Empty.
Step:5 Click on Add Button.
Step:6 Write the Name of Controller as "StudentController"
Step:7 Click on Add Button
Step:8 Add The following Code in Controller

StudentController 

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

namespace MyFirstMVCApp.Controllers
{
    public class StudentController : Controller
    {
        #region Viriable and Object
        // Created Global objStudentModel.
        StudentModel studentModel = null;
        #endregion
        // GET: Student
        public ActionResult Index()
        {
            return View();
        }
        //Action Method for Get RegisterStudent View.
        [HttpGet]
        public ActionResult RegisterStudent()
        {
            return View();
        }
        //Action Method for post the Student details to Model for Insert in Database
        [HttpPost]
        public ActionResult RegisterStudent(StudentModel std)
        {
            studentModel = new StudentModel();
            int result = studentModel.ManageStudent(std);
            if (result != 0)
            {
                TempData["Msg"] = "Record Inserted!";
            }
            else
            {
                TempData["Msg"] = "Record Not Inserted!";
            }
            return RedirectToAction("getStudentList");
        }

        //Action Method for Get the Existing Student Details For Update.
        [HttpGet]
        public ActionResult EditStudent(int id)
        {
            studentModel = new StudentModel();
            studentModel=studentModel.StudentDetails(id);
            return View(studentModel);
        }
        //Action method for past th student details to Update the Existing Student in Database
        [HttpPost]
        public ActionResult EditStudent(StudentModel std)
        {
            studentModel = new StudentModel();
            int result = studentModel.ManageStudent(std);
            if (result != 0)
            {
                TempData["Msg"] = "Record Updated!";
            }
            else
            {
                TempData["Msg"] = "Record Not Updated!";
            }
            return RedirectToAction("getStudentList");
        }

        //Action Method for get All Student Details in a List.
        [HttpGet]
        public ActionResult getStudentList()
        {
            List<StudentModel> studentList = new List<StudentModel>();
            studentModel = new StudentModel();
            studentList = studentModel.StudentList();
            return View(studentList);
        }

        //Action Method for Delete The Existing Student
        public ActionResult DeleteStudent(int id)
        {
            studentModel = new StudentModel();
            int result=studentModel.DeleteStudent(id);
            if (result < 0)
            {
                TempData["Msg"] = "Record Deleted!";
            }
            else
            {
                TempData["Msg"] = "Record Not Deleted!";
            }
            return RedirectToAction("getStudentList");
        }
    }
}

                                          Views

Add getStudentList View in App

Step:1 Right Click Over the Action Method  "public ActionResult getStudentList()".
Step:2 Click On Add View.
Step:3 Select  Template List.
Step:4 Select Model Class  "StudentModel (MyFirstMVCApp.Models)"
Step:5 Click on Add Button.
Step:6 Add the Following Code Which is highlighted.


@model IEnumerable<MyFirstMVCApp.Models.StudentModel>

@{
    ViewBag.Title = "getStudentList";
}

<h2>getStudentList</h2>

<p>
    @Html.ActionLink("Create New", "RegisterStudent")
</p>
<p>
    @TempData["Msg"]
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Roll)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Course)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Mobile)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Address)
        </th>
        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Roll)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Course)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Mobile)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Address)
            </td>
            <td>
                @Html.ActionLink("Edit", "EditStudent", new { id = item.Roll }) |
                @Html.ActionLink("Delete", "DeleteStudent", new { id = item.Roll })
            </td>
        </tr>
    }

</table>


Add RegisterStudent View in App

Step:1 Right Click Over the Action Method  " public ActionResult RegisterStudent()".
Step:2 Click On Add View.
Step:3 Select  Template Create.
Step:4 Select Model Class  "StudentModel (MyFirstMVCApp.Models)"
Step:5 Click on Add Button.
Step:6 Delete or Comment the div which have Roll.
Step:7 Add the Following Code Which is highlighted.


@model MyFirstMVCApp.Models.StudentModel

@{
    ViewBag.Title = "RegisterStudent";
}

<h2>RegisterStudent</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>StudentModel</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Course, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Course, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Course, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Mobile, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Mobile, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Mobile, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "getStudentList")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Add EditStudent View in App

Step:1 Right Click Over the Action Method  " public ActionResult EditStudent()".
Step:2 Click On Add View.
Step:3 Select  Template Edit.
Step:4 Select Model Class  "StudentModel (MyFirstMVCApp.Models)"
Step:5 Click on Add Button.
Step:6 Change the TextBoxfor in HiddenFor only Roll which already highlighted..
Step:7 Add the Following Code Which is highlighted.

@model MyFirstMVCApp.Models.StudentModel

@{
    ViewBag.Title = "EditStudent";
}

<h2>EditStudent</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>StudentModel</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">

            <div class="col-md-10">
                @Html.HiddenFor(model => model.Roll, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Course, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Course, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Course, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Mobile, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Mobile, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Mobile, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "getStudentList")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

_Layout.cshtml view(Its a pre-Generated View)

Step:1 Add the highlighted code only.


<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - My ASP.NET Application</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
</head>
<body>
    <div class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
                @Html.ActionLink("Application name", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
            </div>
            <div class="navbar-collapse collapse">
                <ul class="nav navbar-nav">
                    <li>@Html.ActionLink("Home", "Index", "Home")</li>
                    <li>@Html.ActionLink("About", "About", "Home")</li>
                    <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
                     <li>@Html.ActionLink("Student", "getStudentList", "Student")</li>
                </ul>
            </div>
        </div>
    </div>
    <div class="container body-content">
        @RenderBody()
        <hr />
        <footer>
            <p>&copy; @DateTime.Now.Year - My ASP.NET Application</p>
        </footer>
    </div>

    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>
</html>

Sunday, 4 November 2018

Sample Way to Create ASP.Net MVC Web API


Sample Way to Create ASP.Net MVC Web API  

Step 1: Open the Visual Studio and click File → New → Project menu option.
A new Project dialog opens.

Step 2 − select Templates → Visual C# → Web.
Step 3 − select ASP.NET Web Application
Enter project name Web_API_Demo in the Name field and click Ok .

Step 4 − select the Empty option and check the Web API checkbox and click ok.
Step 5 − It will create a basic MVC project with minimal predefined content.
Step 6 − add a model. Right-click on the Models folder in the solution explorer and select Add → Class.


Step 7 − Select Class in the middle pan and enter Student.cs in the name field.
Step 8 − Add some properties for Student class using the following code.

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

namespace Demo_Web_API_.Models
{
    public class Student
    {
        public int RollNo { get; set; }
        public string Name { get; set; }
        public DateTime JoiningDate { get; set; }
        public string Course { get; set; }
    }
}
Step 9 − add the controller. Right-click on the controller folder in the solution explorer and select Add → Controller.



Step 10 − Select the Web API 2 Controller - Empty option. Then give Name As StudentsController (As below)
Step 11 − Click ‘Add’ button and the Add Controller dialog will appear.



Step 12 − Set the name to StudentsController and click ‘Add’ button.
using Demo_Web_API_.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace Demo_Web_API_.Controllers
{
    public class StudentsController : ApiController
    {
        Student[] students = new Student[]{
         new Student { RollNo = 1, Name = "Ram", JoiningDate =DateTime.Parse(DateTime.Today.ToString()), Course = "MCA" },
         new Student { RollNo = 2, Name = "Sita", JoiningDate =DateTime.Parse(DateTime.Today.ToString()), Course = "BCA" },
         new Student { RollNo = 3, Name = "Mohan", JoiningDate =DateTime.Parse(DateTime.Today.ToString()), Course = "BBA" }
      };

        public IEnumerable<Student> GetAllStudents()
        {
            return students;
        }

        public IHttpActionResult GetStudent(int id)
        {
            var student = students.FirstOrDefault((p) => p.RollNo == id);
            if (student == null)
            {
                return NotFound();
            }
            return Ok(student);
        }
    }
}

Step 13 − Run this application and add at the end of URL /api/Students/ then  press ‘Enter’. You will get below output.





Step 14 − Use this URL http://localhost:63457/api/Students/1 to get following output


Saturday, 27 October 2018

Import Excel File in ASP.Net, C# with MySQL Database.

Import Excel File in ASP.Net, C# with MySQL Database.



Excel File Format



Window before File Upload

Window After File Upload




Window after Data Inserted



DataBase  Figure





CSS

.successPanel {
border: 1px #000;
color: gray;
font-weight: bold;
}

ASPX Page


<%@ Page Title="" Language="C#" MasterPageFile="~/popupMasterPage.Master" AutoEventWireup="true" CodeBehind="UserExcelpopup.aspx.cs" Inherits="CationHRManagementSystem.UserExcelpopup" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">

/********************* JS Link ******************************/  

  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script>

/******************Method for Close Window***************************/

      
  function fnclose() {

            window.close();
            window.opener.location.reload
        }

/************End of Method for Close Window**********************/

/******************Method for Print Data***************************/

              function printdiv(printpage) {
            var headstr = "<html><head><title>Excel file Upload Status</title></head><body style='width:100%;height:100%'>";
            var footstr = "</body>";
            var newstr = document.all.item(printpage).innerHTML;
            var oldstr = document.body.innerHTML;
            document.body.innerHTML = headstr + newstr + footstr;
            window.print();
            document.body.innerHTML = oldstr;
            return false;
        }

/****************End of Method for Print Data***********************/

    </script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <div id="div1" runat="server" style="background: white; overflow: auto; overflow-x: hidden;">
        <table style="width: 100%;">
            <tr>
                <td style="text-align: right">
                    <button id="btnPrint" class="button-style" onclick="printdiv('div_print');">Print</button>&nbsp
                    <asp:Button ID="btnclose" runat="server" CssClass="button-style" OnClientClick="fnclose();" Text="Close" /></td>
            </tr>
        </table>
        <br />
        <br />
        <br />
        <div id="div_print">
            <fieldset>
                <legend class="successPanel">Please Select File:
                </legend>
                <table width="100%">
                    <tr>
                        <td colspan="2" style="text-align: center">
                            <asp:Label ID="lblUploadFile" CssClass="" runat="server"></asp:Label>
                        </td>
                    </tr>
                    <tr>
                        <td style="height: 40px;" align="left">
                            <asp:FileUpload ID="userFileUpload" runat="server" />
                        </td>

                        <td style="height: 40px;" align="right">
                            <asp:Button runat="server" ID="UploadButton" Text="Upload" OnClick="UploadButton_Click"  CssClass="button-style" />
                            <asp:Button runat="server" ID="SaveData" Text="Run" OnClick="SaveData_Click" CssClass="button-style" />
                        </td>
                    </tr>
                    <tr id="tr_msg0" runat="server">
                        <td colspan="2" style="text-align: center;">
                            <asp:Label runat="server" ID="Label4" Text="<u>Status:</u>" CssClass="successPanel" Style="font-weight: bold; font-size: 12pt;" /></td>
                    </tr>
                    <tr id="tr_msg" runat="server">
                        <td style="width: 30%;">

                            <asp:Label runat="server" ID="lblSuccessMsg" Text="<u>Successfully Data:</u>" CssClass="successPanel" Style="font-weight: bold;" />
                            <br />


                            <asp:Label runat="server" ID="lblmsg" Text="" CssClass="successPanel" />
                        </td>
                        <td style="width: 70%; text-align: center;">

                            <asp:Label runat="server" ID="lblFailMsg" Text="<u>Failed Data:</u>" CssClass="successPanel" Style="font-weight: bold;" />


                        </td>
                    </tr>
                    <tr id="tr_msg1" runat="server">
                        <td style="vertical-align: top;">
                            <asp:Label runat="server" ID="lblSuccessData" Text="" CssClass="successPanel" />
                        </td>
                        <td>
                            <table>
                                <tr>
                                    <td style="vertical-align: top;">
                                        <asp:Label runat="server" ID="Label5" Text="<u>Duplicate Email Ids</u>" CssClass="successPanel" />
                                        <asp:Label runat="server" ID="lblFaildData" Text="" CssClass="successPanel" />
                                    </td>
                                    <td style="vertical-align: top;">
                                        <asp:Label runat="server" ID="Label3" Text="<u>In Sufficient Data</u>" CssClass="successPanel" />
                                        <asp:Label runat="server" ID="lblBlkData" Text="" CssClass="successPanel" />
                                    </td>
                                </tr>
                            </table>
                        </td>
                    </tr>

                </table>
            </fieldset>
        </div>
    </div>
</asp:Content>


C# Code


using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Drawing;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;
using System.Configuration;
using Utility;

namespace CationHRManagementSystem
{
    public partial class UserExcelpopup : System.Web.UI.Page
    {
        string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        public string FilePath = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            tr_msg.Visible = false;
            tr_msg0.Visible = false;
            tr_msg1.Visible = false;
            SaveData.Visible = false;
        }

//Method For Save Data in Database Using MySql

        protected void SaveData_Click(object sender, EventArgs e)
        {
            lblUploadFile.Text = "";
            string sal = "";
            string First_Name = "";
            string Last_Name = "";
            string EMAIL_ID = "";
            string USER_PASSWORD = "";
            string org_name = "";
            string myfile_name = Session["name"].ToString();
            string Excel_path = Server.MapPath("~/UserUploadedExcelFiles/" + "\\" + myfile_name);
            OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False");
            my_con.Open();
            try
            {
                OleDbCommand o_cmd = new OleDbCommand("select * from [Sheet1$]", my_con);
                OleDbDataReader o_dr = o_cmd.ExecuteReader();
                int Fail_cnt = 0;
                int cnt = 0;
                int blkcnt = 0;
                string EmailId = "";
                string blnkEmailId = "";
                string lnsEmailId = "";
                while (o_dr.Read())
                {
                    sal = o_dr[0].ToString();

                    First_Name = o_dr[1].ToString();

                    Last_Name = o_dr[2].ToString();

                    EMAIL_ID = o_dr[3].ToString();

                    org_name = o_dr[4].ToString();

                    USER_PASSWORD =o_dr[5].ToString(); 

                    if (sal == "" || First_Name == "" || EMAIL_ID == "" || org_name == "" || USER_PASSWORD == "")
                    {
                        blkcnt++;
                        blnkEmailId += "<br>" + EMAIL_ID;
                        continue;
                    }
                    else
                    {

// check Email Existence 
                        MySqlConnection con = new MySqlConnection(conStr);
                        con.Open();
                        string sqlQuery = "select * from user_info where LOWER(EMAIL_ID)='" + EMAIL_ID.Trim().ToLower() + "'";
                        MySqlConnection Con = new MySqlConnection(conStr);
                        MySqlDataAdapter da = new MySqlDataAdapter(sqlQuery, Con);

                        DataTable dt = new DataTable();

                        da.Fill(dt);

                        if (dt.Rows.Count > 0)
                        {
                            EmailId += "<br>" + EMAIL_ID;
                            Fail_cnt++;
                            continue;
                        }

                        else
                        {
// Insert Data 
                            string Sqlstr = " insert into user_info(USER_SAL, USER_FIRSTNAME,                                               USER_LASTNAME,  EMAIL_ID, USER_PASS) " 
                            values('" + sal + "', '" + First_Name + "', '" + Last_Name + "',+ EMAIL_ID + "', 
                                     '" + USER_PASSWORD + "');";
                            MySqlCommand cmd = new MySqlCommand(Sqlstr, con);
                            int i = cmd.ExecuteNonQuery();
                            if (i > 0)
                            {
                                cnt++;
                                lnsEmailId += "<br>" + EMAIL_ID;
                            }
                            con.Close();
                        }
                    }
                }
// Labels for Masseges
                tr_msg0.Visible = true;
                tr_msg.Visible = true;
                tr_msg1.Visible = true;
                lblSuccessMsg.Text += "-" + cnt.ToString();
                int fcnt = blkcnt + Fail_cnt;
                lblFailMsg.Text += "-" + fcnt.ToString();
                lblSuccessData.Text = lnsEmailId.ToString();
                lblSuccessData.ForeColor = System.Drawing.Color.Green;
                lblFaildData.Text = EmailId.ToString();
                lblBlkData.Text = blnkEmailId.ToString();
                lblBlkData.ForeColor = System.Drawing.Color.Red;
                lblFaildData.ForeColor = System.Drawing.Color.Red;
            }
            catch (Exception ex)
            {
                lblmsg.Text = ex.Message;
            }
        }

// Method for Upload excel file

        protected void UploadButton_Click(object sender, EventArgs e)
        {
            string file_name = Path.GetFileName(userFileUpload.PostedFile.FileName);
            if (file_name == "")
            {
                lblUploadFile.Text = "Please select excel file.";
                lblUploadFile.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                string fileExtension = Path.GetExtension(userFileUpload.FileName);
                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                                         // Excel File Upload
                    System.IO.FileInfo file = new System.IO.FileInfo(userFileUpload.PostedFile.FileName);
                    string fname = file.Name.Remove((file.Name.Length - file.Extension.Length));
                    fname = fname + System.DateTime.Now.ToString("_ddMMyyhhmmss") + file.Extension;
                    if (fname.Length > 0)
                    {
                        userFileUpload.SaveAs(Server.MapPath("~/UserUploadedExcelFiles/" + "\\" + fname));
                        Session["name"] = fname;

                        lblUploadFile.Text = "file uploaded Successfully.";
                        lblUploadFile.ForeColor = System.Drawing.Color.Green;
                        SaveData.Visible = true;
                        UploadButton.Visible = false;
                    }
                    else
                    {
                        lblUploadFile.Text = "Please select excel file.";
                        lblUploadFile.ForeColor = System.Drawing.Color.Red;
                    }
                }
                else
                {
                    lblUploadFile.Text = "Please select excel file.";
                    lblUploadFile.ForeColor = System.Drawing.Color.Red;
                }

            }
        }
     }
}