locked
CRUD in Jqgrid MVC using stored procedure: JSON data RRS feed

  • Question

  • User1984354752 posted

    Hi there:

    I intend to use ADO.NET via stored procedure to CRUD my Jqgrid in ASP.NET MVC.  I know how fill jqgrid with a stored procedure but don't know how to do implement the Edit, Delete and Insert operations.   

    I'm  going straight to the point as I know how to write my  T-SQL and implement the actions in the controller. I highlighted in yellow where I'm not sure ( I haven't try yet) so you can go straight to yellow line.  

    ADO.NET STORED PROCEDURE PULL FROM DATABASE.

           public int Update(Employee emp)

           {

               int i;

               using (SqlConnection con = new SqlConnection(cs))

                {

                   con.Open();

                   SqlCommand com = new SqlCommand("InsertUpdateEmployee", con);

                   com.CommandType = CommandType.StoredProcedure;

                   com.Parameters.AddWithValue("@Id", emp.EmployeeID);

                  com.Parameters.AddWithValue("@Name", emp.Name);

                   com.Parameters.AddWithValue("@Age", emp.Age);

                   com.Parameters.AddWithValue("@State", emp.State);

                   com.Parameters.AddWithValue("@Country", emp.Country); 

                   i = com.ExecuteNonQuery();

               }

               return i;

           }

     

    STOREDPROCEDURE CONTROLLER:

    public JsonResult Update(Employee emp)

           {

               return Json(empDB.Update(emp), JsonRequestBehavior.AllowGet);

           }

    RAZOR VIEW:

    @{

       Layout = "~/Views/Shared/_Layout.cshtml";

    }

     

     

     

    @section scripts{

       <link href="~/Content/themes/base/jquery-ui.css" rel="stylesheet" />

       <link href="~/Content/jquery.jqGrid/ui.jqgrid.css" rel="stylesheet" />

     

       <script src="~/Scripts/jquery-3.0.0.min.js"></script>

       <script src="~/Scripts/i18n/grid.locale-en.js"></script>

       <script src="~/Scripts/jquery.jqGrid.min.js"></script>

       }

     

     

    <table id="jgrid"></table>

    <div id="jgrid_page"></div>

     

    <script type='text/javascript'>

     

       $(document).ready(function () {

           $("#jgrid").jqGrid

               ({

                   url: '/xxxxxxxx/xxxxxxx',

                   datatype: 'json',

                   mtype: 'GET',

                   colNames: [

                       'ID',

                       'Name',

                       'Age',

                       'State',

                    'Country'],

                   colModel: [

                       { name: 'ID', index: 'ID', editable: false, key: true },

                       { name: 'Fullnames', index: 'Fullnames', editable: true, key: false, search: true, editrules: { required: true } },

                       { name: 'Age', index: 'Age', editable: true, key: false, search: true, editrules: { required: true, number: true } },

                       { name: 'State', index: 'State', editable: true, key: false ,search: true ,editrules: { required: true }},

                      { name: 'Country', index: 'Country', editable: true, key: false ,search: true ,editrules: { required: true }}],

                  

                   pager: jQuery('#jgrid_page'),

                   rowNum: 20,

                   rowList: [20, 30, 40, 50],

                   height: '100%',

                   viewrecords: true,

                   caption: 'Beneficiary List',

                   emptyrecords: 'No records',

                   jsonReader:

                   {

                       root: "rows",

                      page: "page",

                       total: "total",

                       records: "records",

                       repeatitems: false,

                       Id: "0"

                   },

                   autowidth: true,

                   multiselect: false

               }).navGrid('#jgrid_page', { edit: true, add: true, del: true, search: true, refresh: true },

               {

                       zIndex: 100,

                       url: '/storedprocedure/Update', =>Is this right ??? I mean do I have to pass the parameters by creating a script function or the  above controller  deals with the JSON data ??????   If not ….can you please elaborate how to do it ???

                       closeOnEscape: true,

                       closeAfterEdit: true,

                       recreateForm: true,

                       afterComplete: function (response) {

                           if (response.responseText) {

                               alert(response.responseText);

                          }

                       }

               });

     

       });

     

    </script>

    Tuesday, January 21, 2020 2:27 PM

Answers

  • User665608656 posted

    Hi 9peculiar,

    According to your description, you don't need to pass parameters when editing, this jqGrid has its own parameter passing mechanism.

    For more details, you can refer to this link :

    Performing CRUD Operations Using jqGrid In ASP.NET MVC

    Here is the sample result :

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 22, 2020 9:59 AM

All replies

  • User665608656 posted

    Hi 9peculiar,

    According to your description, you don't need to pass parameters when editing, this jqGrid has its own parameter passing mechanism.

    For more details, you can refer to this link :

    Performing CRUD Operations Using jqGrid In ASP.NET MVC

    Here is the sample result :

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 22, 2020 9:59 AM
  • User1984354752 posted

    Thanks Yongping ...it works 

    Tuesday, January 28, 2020 5:25 AM