Using a stored procedure to (search parameters ) to load a grid

[Simon Bunya]

Hello

How do i use a stored procedure to load a grid using the code below

Thanks

if (args.requestType == "add") {

var order_no = "new_record";

var FC = "new_record";

var StartDate = "new_record";

var EndDate = "new_record";

$("#order_grid").ejGrid({

dataSource: ej.DataManager({ url: "GetOrder_HTK?FC=" + FC + "&StartDate=" + StartDate + "&EndDate=" + EndDate, batchUrl: "Update", adaptor: "UrlAdaptor" }),

//query: new ej.Query().addParams('param_on', order_no),

//dataSource: ej.DataManager({ url: "BatchDataEdit?facility_code=" + facility_code + "&start_date=" + start_date + "&end_date=" + end_date, batchUrl: "BatchUpdateHeader", adaptor: "UrlAdaptor" }),

allowGrouping: false,

//groupSettings: { showDropArea: false, groupedColumns: ["ovc_identification_Desc"], headerText: "" },

//toolbarSettings: { showToolbar: true, toolbarItems: ['search'] },

editSettings: { allowDeleting: true, allowEditing: true, allowAdding: true, editMode: 'batch' },

allowScrolling: true,

scrollSettings: { width: "100%", height: "auto" },

allowPaging: false,

enableAltRow: true,

allowTextWrap: true,

textWrapSettings: { wrapMode: "both" },

cellEdit: "cellEdit",

allowResizeToFit: true,

showStackedHeader: true,

isResponsive: true,

enableResponsiveRow: true,

allowScrolling: true,

cellSave: "cellsave",

queryCellInfo: "queryCellInfo",

//cellEdit: "cellEdit",

columns: [

{ field: "OrderNumber", headerText: "OrderNumber", visible: false, allowEditing: false, isPrimaryKey: true },

{ field: "FacilityCode", headerText: "FacilityCode", visible: false, allowEditing: false },

{ field: "StartDate", headerText: "StartDate", visible: false, allowEditing: false },

{ field: "EndDate", headerText: "EndDate", visible: false, allowEditing: false },

{ field: "product_code", headerText: "Product Code", allowEditing: false, width: 70, isPrimaryKey: true },

{ field: "product_description", headerText: "Product Description", allowEditing: false, width: 400 },

{ field: "Basic_Unit", headerText: "Basic Unit", allowEditing: false, width: 80 },

{ field: "No_Test_Start_2Months", headerText: "No. Tests At Start Of 2 Months", allowEditing: true, width: 80 },

{ field: "Test_Recieved_2Months", headerText: "Tests Received In 2 Months", allowEditing: true, width: 80 },

{ field: "Test_Used_2Months", headerText: "Tests Used In 2 Months", allowEditing: true, width: 80 },

{ field: "Loss_Adjustment", headerText: "Losses / Adjustments", allowEditing: true, width: 80 },

{ field: "Test_Remaining", headerText: "Tests Remaining", allowEditing: true, width: 80 },

{ field: "Maximum_Stock", headerText: "Maximum Stock", allowEditing: false, width: 80 },

{ field: "Quantity_Required", headerText: "Quantity Required", allowEditing: false, width: 80 },

//{ field: "Quantity_Allocated", headerText: "Quantity Allocated", allowEditing: true, width: 80 },

{ field: "Notes", headerText: "Notes", allowEditing: true, width: 200 },

{ field: "RFSONotes", headerText: "RFSO Notes", allowEditing: false, width: 200 }

]

});

$("#summary_grid").ejGrid({

dataSource: ej.DataManager({ url: "GetOrderSummary", batchUrl: "UpdateSummary", adaptor: "UrlAdaptor" }),

query: new ej.Query().addParams('param_on', order_no),

//.executeQuery(new ej.Query().addParams('param_fc', facility_code).addParams('param_sd', start_date).addParams('param_ed', end_date)),

//dataSource: ej.DataManager({ url: "BatchDataEdit?facility_code=" + facility_code + "&start_date=" + start_date + "&end_date=" + end_date, batchUrl: "BatchUpdateHeader", adaptor: "UrlAdaptor" }),

allowGrouping: false,

//groupSettings: { showDropArea: false, groupedColumns: ["ovc_identification_Desc"], headerText: "" },

//toolbarSettings: { showToolbar: true, toolbarItems: ['search'] },

editSettings: { allowDeleting: true, allowEditing: true, allowAdding: true, editMode: 'batch' },

allowScrolling: true,

scrollSettings: { width: "100%", height: "auto" },

allowPaging: false,

enableAltRow: true,

allowTextWrap: true,

textWrapSettings: { wrapMode: "both" },

cellEdit: "cellEdit",

allowResizeToFit: true,

showStackedHeader: true,

isResponsive: true,

enableResponsiveRow: true,

allowScrolling: true,

cellSave: "cellsave2",

queryCellInfo: "queryCellInfoPatients",

columns: [

{ field: "OrderNumber", headerText: "OrderNumber", visible: false, allowEditing: false, isPrimaryKey: true },

{ field: "FacilityCode", headerText: "FacilityCode", visible: false, allowEditing: false },

{ field: "StartDate", headerText: "StartDate", visible: false, allowEditing: false },

{ field: "EndDate", headerText: "EndDate", visible: false, allowEditing: false },

{ field: "product_code", visible: true, headerText: "Product Code", allowEditing: false, width: 70, isPrimaryKey: true },

{ field: "product_description", headerText: "Product Description", allowEditing: false, width: 200 },

{ field: "HCT", headerText: "HCT", allowEditing: true, width: 80 },

{ field: "PMTCT", headerText: "PMTCT", allowEditing: true, width: 80 },

{ field: "Clinical_Diagnosis", headerText: "Clinical Diagnosis", allowEditing: true, width: 80 },

{ field: "SMC", headerText: "SMC", allowEditing: true, width: 80 },

{ field: "Quality_Control", headerText: "Quality Control New", allowEditing: true, width: 80 },

{ field: "Total", headerText: "Total", width: 80 }

]

});

}

[Seeni Sakthi Kumar Seeni Raj]

Hi Simon,  

 

Thanks for contacting Syncfusion Support.  

 

Before proceeding further on this, please share the following details. 

 

1. Would you like to save the Grid model in the Database?
2. Later, on any action, would like to retrieve them and render the Grid?

 

We have already discussed KB about the saving the Grid model in the database. Thereafter, we have retrieved Grid model and re-rendered the Grid. Refer to the following KB.  

 

https://www.syncfusion.com/kb/6312/how-to-store-retrieve-grid-model-values-into-from-database 

 

We hope this KB would be helpful to save model values to the database and retrieve them back.  

 

If we have misunderstood your query, please share the information in detailed with the diagrammatic representation.  

 

Regards,  

Seeni Sakthi Kumar S. 

[Simon Bunya]

Hello thanks for the reply

this doesn't answer my question, maybe if we can use Team-viewer and i show you what i want to achieve

Many Thanks   

Simon  

[Alan Sangeeth S]

Hi Simon, 

We have prepared sample based on your requirement “Bind data to grid stored procedures with search query string values” and the same can be downloadable from below link. 

https://www.syncfusion.com/downloads/support/forum/139453/ze/MVCAPP~3242835197.zip  

After retrieving data using stored procedure in a DataTable, we have converted it to List since we need to return data as JSON in result and count pair. Please refer to the code example below. 

<script type="text/javascript"> 

    $(function () { 

        // the datasource "window.gridData" is referred from jsondata.min.js 

        var data = ej.DataManager(window.gridData).executeLocal(ej.Query().take(50)); 

        $("#Grid").ejGrid({ 

            dataSource: ej.DataManager({ 

                url: "/Home/DataSource/?myparam=WELLI", 

                adaptor: "UrlAdaptor" 

            }), 

 

            allowPaging: true, 

            toolbarSettings: { 

                showToolbar: true, 

                toolbarItems: [ej.Grid.ToolBarItems.Add, ej.Grid.ToolBarItems.Edit, ej.Grid.ToolBarItems.Delete, ej.Grid.ToolBarItems.Update, ej.Grid.ToolBarItems.Cancel, ej.Grid.ToolBarItems.Search ] 

            }, 

            allowSearching:true, 

            columns: [ 

                { field: "OrderID", isPrimaryKey: true }, 

                { field: "CustomerID" }, 

                 

           ] 

        }); 

    }); 

</script> 

 

Serverside:- 

        public ActionResult DataSource(DataManager dm,string myparam) 

        { 

            DataOperations operation = new DataOperations(); 

            int count; 

            DataTable dt = new DataTable("Order"); 

            string constring = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString; 

            using (SqlConnection con = new SqlConnection(constring)) 

            { 

                using (SqlCommand cmd = new SqlCommand("CustOrdersOrders", con))   

                { 

                    cmd.CommandType = CommandType.StoredProcedure;     //implementing the stored procedure 

                    cmd.Parameters.AddWithValue("@CustomerID",myparam);  

                    con.Open(); 

                    SqlDataReader dr = cmd.ExecuteReader(); 

                    dt.Load(dr); // Load into the dataTable 

                    dr.Close(); 

                    con.Close(); 

                   

                } 

            } 

            //Convet the dataTable into list 

            List<EditableOrder> resList = new List<EditableOrder>(); 

            resList = (from DataRow dr in dt.Rows 

                      select new EditableOrder() 

                         { 

                             OrderID = Convert.ToInt32(dr["OrderID"]), 

                             CustomerID = dr["CustomerID"].ToString(), 

                             EmployeeID = Convert.ToInt32(dr["EmployeeID"]), 

                             Freight = Convert.ToDecimal(dr["Freight"]), 

                              

                              

                         }).ToList(); 

            count = resList.Count; 

            return Json(new { result = resList, count = count }, JsonRequestBehavior.AllowGet);  //return the data in the form of result and count pairs in url adaptor 

        } 

         

 

 

 

Stored procedure:- 

 

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5) 

AS 

SELECT OrderID, CustomerID, EmployeeID,Freight, 

       OrderDate, 

       RequiredDate, 

       ShippedDate 

FROM Orders 

WHERE CustomerID = @CustomerID 

ORDER BY OrderID 

Regards,
Alan Sangeeth S 

[Simon Bunya]

Many thanks

this is what i needed

[Seeni Sakthi Kumar Seeni Raj]

Hi Simon,  

 

Thanks for the update.  

 

We are happy to hear that your requirement has been achieved and you are good to go. Please get back to us, if you require further assistance.  

 

Regards,  

Seeni Sakthi Kumar S.