ZDIRY-TUFWT-EBONM-EYJ00-IDBLANTER.COM
ZDIRY-TUFWT-EBONM-EYJ00

BLANTERWISDOM105

Tampilkan Database SQL Server di ASP NET MVC Menggunakan ADO NET

4/24/2020

Pada artikel sebelumnya sudah dibahas tentang pembuatan Menu di Layout.cshtml pada ASP NET MVC serta contoh tampilan pada UploadExcel menggunakan bootstrap di ASP NET MVC. Sekarang saya akan mencoba menampilkan datanya dari database SQL Server. Koneksi yang digunakan pada percobaan kali ini tidak menggunakan Entity Framework (EF) namun menggunakan ADO Net yaitu cara lama. Mungkin selanjutnya akan menggunakan Entity Framework.

ASP NET MVC DAN SQL SERVER

Langkah 1

Kita perlu menentukan string koneksi di bawah tag <configuration> untuk database di file Web.config yang bukan di dalam views ya. Soalnya ada dua Web.config.

<connectionStrings>
    <add name="SqlConnectionString" connectionString="Server=localhost;Database=TEST;User ID=sa;Password=******;Trusted_Connection=false;MultipleActiveResultSets=true;Max Pool Size=1000;Timeout=300000" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Langkah 2

Buatlah model untuk ke empat parameter dianjurkan nama ke empat parameter tersebut harus sama dengan nama kolom pada tabel di database.

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

namespace UploadExcel.Models.UploadExcel2
{
    public class UploadExcel2
    {
        public string Name { get; set; }
        public string Gender { get; set; }
        public string Email { get; set; }
        public string ClassM { get; set; }

        public IList<UploadExcel2> listData { get; set; }
    }
}

Langkah 3

Tambahkan class repositories ke dalam folder models > UploadExcel2 dengan nama UploadExcel2Repo. SqlConnectionString merupakan name pada setting Web.Config pada <connectionStrings>.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using UploadExcel.Models.UploadExcel2;

namespace UploadExcel.Models.UploadExcel2
{
    public class UploadExcel2Repo
    {
        string connectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
        SqlConnection connection;
        SqlCommand command;
        SqlDataReader reader;

        public List<UploadExcel2> getAllData(UploadExcel2 data)
        {
            List<UploadExcel2> UploadClass = new List<UploadExcel2>();

            connection = new SqlConnection(connectionString);
            connection.Open();
            var query = "SELECT [Name],[Email],[Gender],[ClassM] FROM [dbo].[CLASS] Where 1=1" +
                        " AND (NULLIF('" + data.Name + "','') IS NULL OR [Name] like '%" + data.Name + "%')" +
                        " AND (NULLIF('" + data.Gender + "','') IS NULL OR [Gender] like '%" + data.Gender + "%')";
            command = new SqlCommand(query, connection);
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                UploadExcel2 classm = new UploadExcel2()
                {
                    Name = Convert.ToString(reader["Name"]), //Convert.ToInt32(reader["id"]),
                    Gender = Convert.ToString(reader["Gender"]),
                    Email = Convert.ToString(reader["Email"]),
                    ClassM = Convert.ToString(reader["ClassM"]),
                };

                UploadClass.Add(classm);
            }

            reader.Close();
            connection.Close();

            return UploadClass;
        }
    }
}

Langkah 4

Tambahkan class controller ke project anda. Dengan cara klik kanan mouse pada folder controller Add > Controller. Buatlah nama Class Tersebut seperti UploadExcel2Controller.


ASP NET MVC

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using UploadExcel;
using UploadExcel.Models;
using UploadExcel.Models.UploadExcel2;

namespace UploadExcel.Controllers
{
    public class UploadExcel2Controller : Controller
    {
        public const int DATA_ROW_INDEX_START = 1;
        //IList<UploadExcel2> listData1 = new List<UploadExcel2>();
        //

        private UploadExcel2Repo con = new UploadExcel2Repo();
        // GET: /UploadExcel2/
        public ActionResult Index()
        {
            return View();
        }

        #region Search
        public ActionResult Search(UploadExcel2 data)//IList<UploadExcel2> listData
        {
            AjaxResult ajaxResult = new AjaxResult();
            try
            {
                IList<UploadExcel2> listData = con.getAllData(data);
                ViewData["ListData"] = listData;
            }
            catch (Exception ex)
            {
                return Json("Error : " + ex.Message, JsonRequestBehavior.AllowGet);
            }
            return PartialView("_GridView", data);
        }
        #endregion
    }
}

Langkah 5

Tambahkan PartialView untuk menampilkan Tabel (_GridView.cshtml) di folder Views / UploadExcel2. Dalam kode berikut ini kami memanggil nilai yang disimpan pada model dan akan ditampilkan pada baris <tr><td>.  Penggunaan _GridView.cshtml ini sebagai bagian tampilan yang dinamis Ketika ada perubahan data.


@using System;
@using UploadExcel.Models.UploadExcel2
@{
    IList<UploadExcel2> listData = (List<UploadExcel2>)ViewData["ListData"];
}
<div class="container">
   <table class="table">
     <thead>
       <tr class="table-primary">
         <th class="text-center grid-checkbox-col" rowspan="2">
             <input class="grid-checkbox" type="checkbox" id="checkall" />
         </th>
         <th rowspan="1">Name</th>
         <th rowspan="1">Gender</th>
         <th rowspan="1">Email</th>
       </tr>
     </thead>
     <tbody>
              
        @if (listData == null || listData.Count == 0)
         {
             <tr>
                 <td colspan="13">No Data Found</td>
             </tr>
         }
         else
         {
             foreach(UploadExcel2 data in listData )
             {    
                 <tr>
                    <td class="text-center grid-checkbox-col">
                        <input name="chkRow" type="checkbox" class="grid-checkbox grid-checkbox-body" 
                            data-ClassId=""  value=""/>
                    </td>
                    <td>@data.Name</td>
                    <td>@data.Gender</td>
                    <td>@data.Email</td>
                </tr>
             }
        }
     </tbody>
   </table>
</div>

Langkah 6

Tambahkan index.cshtml beserta JSON Ajax.


@using System;
@using UploadExcel.Models.UploadExcel2;
@{
    List<UploadExcel2> listData = (List<UploadExcel2>)ViewData["ListData"]; 
}

<style>
    .text-large {
      text-transform: uppercase;
     background: linear-gradient(to right, #30CFD0 0%, #330867 100%);
     -webkit-background-clip: text;
     -webkit-text-fill-color: transparent;
     font-size: 500%;
      font-family: 'Poppins', sans-serif;
    }
    .row.no-gutter {
        margin-left: 0px;
        margin-right: 0;
        margin-top: 0;
        margin-bottom:0;
        width:100%;
    }
    .row.no-gutter [class*='col-']:not(:first-child),
    .row.no-gutter [class*='col-']:not(:last-child) {
        padding-right: 10px;
        padding-left: 10px;
        padding-top: 10px;
        padding-bottom:10px;
     }
    input[name='need']:disabled {
        background: red !important;
        color:black !important;
    }
   
</style>

<div class="container">
     <div class="row no-gutter">
       <div class="col-md-6" style="padding-top:13px;">
           <div class="row no-gutter"><i class="fa fa-user col-md-3">
                <label for="inputEmail" class="col-md-4 col-form-label text-success" >Name</label></i>
                <div class="col-md-8">
                    <input type="text" class="form-control" id="inputName">
                </div>
           </div>
           <div class="row no-gutter"><i class="fa fa-venus-mars col-md-3">
              <label for="cmbGender" class="col-md-4 col-form-label text-success">Gender</label></i>
              <div class="col-md-8">
                 <select class="form-control"  id="cmbGender">
                    <option selected="selected" value="">--Please Choose--</option>
                    <option value="1">Male</option>
                    <option value="2">Female</option>
                 </select>
              </div>
           </div>
       </div>
       <div class="col-md-6">
          <p class="float-right text-large"><span id="furnace_no">CLASS 1</span></p>
       </div>
   </div>
   <div class="row no-gutter" style="margin-bottom:5px">
       <div class="col-md-12">
           <div class="text-right">
               <button onclick="btnSearch_onClick()" type="button" class="btn btn-sm btn-success">Search</button>
           </div>
       </div>
   </div>
   <div class="row" >
        <div class="bg-primary text-white text-center col-md-12">
   <h4>Type 1</h4>
  </div>
    </div>
   <div class="row no-gutter">
      <div id="divGrid" style="width:100%">
         @Html.Partial("_GridView")
      </div>
   </div>
   <div class="row no-gutter">
       <div class="col-md-12">
           <div class="text-right">
               <button onclick="btnUploadPopUp2()" type="button" class="btn btn-sm btn-success">Upload</button>
               <button id="btnSave" type="button" class="btn btn-sm btn-success">Save</button>
               <button id="btnSkip" type="button" class="btn btn-sm btn-primary">Skip</button>
           </div>
       </div>
   </div>
   <br />
</div>
@section footer{
   <div class="bg-secondary" style="height:100%">
       <div class="footer">
        <div class="container">
            <div class="row">
                <div class="col-md-12 col-12 text-white">
                    <div class="row p-3">
                        <div class="col-md-6 col-12">
                            <p class="m-0">All Right Reserved 2020 - <a href="http://www.agungpanduan.com" target="_blank">agungpanduan.com</a></p>
                        </div>
                        <div class="col-md-6 col-12 footer-icon text-right">
                            <i class="fa fa-twitter"></i>
                            <i class="fa fa-instagram"></i>
                            <i class="fa fa-pinterest"></i>
                            <i class="fa fa-linkedin"></i>
                            <i class="fa fa-youtube"></i>
                            <i class="fa fa-vimeo"></i>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
   </div>
}
 
<script type="text/javascript">
    Dropzone.autoDiscover = false;
    $(document).ready(function () {
        //Dropzone.autoDiscover = true;
        //onSearchCriteria();
        $("#checkall").click(function () {
            $(".grid-checkbox").prop("checked", $("#checkall").is(":checked"));
        });

        $(".grid-checkbox").click(function () {
            $("#checkall").prop("checked", $('.grid-checkbox:not(#checkall)').not(':checked').length == 0);
        });
    });

    function btnSearch_onClick() {
        var page = 1;
        $.ajax({
            type: "POST",
            url: "@Url.Content("~/UploadExcel2/Search")",
            data: {
                Name: $("#inputName").val(),
                Gender: $("#cmbGender").val()
            },
            success: function (data) {
                $("#divGrid").html(data);
                //popUpProgressHide();
            },
            error: function (data) {
                //popUpProgressHide();
            }
        });
    }
    
</script>

Demikianlah Cara Koneksi Database SQL Server Ke ASP NET MVC.

Share This :

1 Comments

  1. Owhh jadi seperti itu ya Cara Koneksi Database SQL Server Ke ASP NET Mvcnya, agak rumit si, tapi yaa bakalan seneng klo udh tau carany dn bisa praktekinny ;), Terimakasih infony ya gann

    BalasHapus