How to: Write Back Changes from the JS Grid Control

Artigo
10/20/2016

Learn how to write back data from an editable JS Grid control to an underlying database.

Applies to: SharePoint Foundation 2010

This topic shows how to create a JS Grid control that writes back to the underlying database. The JS Grid control in this topic renders data from the AdventureWorks sample database. It uses an ECMAScript (JavaScript, JScript) controller to manage the client-side operations of the control, utility classes to retrieve and parse the data, and a custom stored procedure to update the database with user-defined changes. (Sample code in this topic was written by Sivaraman Krishnan, Microsoft Corporation.)

Prerequisites

Microsoft SharePoint Foundation 2010
Microsoft Visual Studio 2010
SharePoint development tools in Microsoft Visual Studio 2010
Microsoft SQL Server 2008 R2
AdventureWorks sample database (download from http://msftdbprodsamples.codeplex.com/releases/view/55926)
Permissions to deploy a Web Part to the SharePoint farm
Write access to the database to test the writeback functionality

Note

Although you can complete this procedure without using Visual Studio, it is easier if you use both Visual Studio 2010 and the SharePoint development tools in Visual Studio 2010. Your computer might show different names or locations for some of the Visual Studio user-interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements.

To create an empty SharePoint project

Start Visual Studio by using the Run as administrator option.
On the File menu, click New, and then click Project.
In the New Project dialog box, expand the Visual C# node, expand the SharePoint node, and then select 2010.
In the templates pane, select Empty SharePoint Project, name the solution JSGridWriteback, and then click OK. The SharePoint Customization Wizard appears. This wizard enables you to select the site that you use to debug the project and the trust level of the solution.
In the SharePoint Customization Wizard, select Deploy as farm solution, and then click Finish to accept the default local SharePoint site.

Important

The Visual Web Part that you create in this procedure must be a farm (fully trusted) solution.

To add a Web Part to the project

In Solution Explorer, right-click the JSGridWriteback project, point to Add, and then click New Item.
In the Add New Item dialog box, expand the Visual C# node, expand the SharePoint node, and then select 2010.
Select Visual Web Part, name the item JSGridWritebackWebPart, and then click Add.

Note

By using a Visual Web Part, you can use the built-in Design view for the JS Grid control, and you can create simple Web Part properties without having to create an Editor Part.

To add the JS Grid control

To initialize the control, paste the following code into the JSGridWritebackWebPartUserControl.ascx file.
```
<script src="../../_layouts/JSGridWriteback/JSGridWritebackManager.js" type="text/javascript"></script>
<SharePoint:JSGrid ID="JsGridControl" runat="server" JSControllerClassName="WritebackGridManager" 
JSControllerInstanceName="WGM" Height="300px" />
<br />
<input type="button" runat="server" ID="btnSave" onclick="WGM.SaveProjects();" value="Save Changes" />
```
This script registers the grid manager (controller), the JS Grid control, and an HTML button control that saves the user-defined changes. The grid manager is a JavaScript file that manages the client-side events of the JS Grid control. The name "WGM" represents the instance of the grid manager.

To add the grid manager

In Solution Explorer, right-click the JSGridWriteback project, point to Add, and then click SharePoint "Layouts" Mapped Folder.
In the Layouts folder, right-click the JSGridWriteback folder, point to Add, and then click New Item.
In the Add New Item dialog box, expand the Visual C# node, select Web, and then select JScript File. Name the file JSGridWritebackManager.js, and then click Add.

Paste the following code into the JSGridWritebackManager.js file.

Type.registerNamespace("WritebackGridManager");
WritebackGridManager = function () {

    var CMD_SHOW_PROJECTS = 'ShowProjects';
    var CMD_SAVE_PROJECTS = 'SaveProjects';

    var _jsGridControl;
    var _props;
    var _jsGridParams;

    var _gridData;
    var _dataSource;
    var _changesToSave;

    this.Init = function (jsGridControl, initialData, props) {
        _jsGridControl = jsGridControl;
        _props = props;

        // This event is triggered after the standard grid error checking.
        jsGridControl.AttachEvent(SP.JsGrid.EventType.OnCellEditCompleted, OnCellEditCompleted);

        BindJSGrid(CMD_SHOW_PROJECTS);

        window.onbeforeunload = function () {
            if (WGM.IsSaveProjectsEnabled()) {
                return "Changes on this page haven't been saved. If you continue, the changes will be lost."
            }
        }
    }

    function OnCellEditCompleted(obj) {
        if (WGM.IsSaveProjectsEnabled()) {
            DisplayStatusInfo("Status:  There are unsaved updates.");
        }
    }

    function BindJSGrid(command) {
        var args = Sys.Serialization.JavaScriptSerializer.serialize({
            Command: command,
            Changes: _changesToSave
        });

        if (typeof (_props) != 'undefined' && null != _props) {
            eval(_props.callbackScript);
        }
    }

    this.DisplayProjectsData = function (data) {

        // Clear the grid.
        if (_jsGridControl.IsInitialized()) {
            _jsGridControl.ClearChanges();
            _jsGridControl.ClearTableView();
        }

        // Show data in the grid.
        if (data && data != '') {

            var responseData = Sys.Serialization.JavaScriptSerializer.deserialize(data);
            _gridData = SP.JsGrid.Deserializer.DeserializeFromJson(responseData.gridJson);

            if (!_dataSource)
                _dataSource = new SP.JsGrid.StaticDataSource(_gridData);
            else
                _dataSource.LoadSerializedData(_gridData);

            _jsGridParams = _dataSource.InitJsGridParams();

            _jsGridParams.bEnableDiffTracking = true;
            _jsGridParams.tableViewParams.bEditingEnabled = true;

            if (!_jsGridControl.IsInitialized()) {
                _jsGridControl.Init(_jsGridParams);
            }
            else {
                _jsGridControl.SetTableView(_jsGridParams.tableViewParams);
            }

            if (globalNotificationID != '')
                RemoveNotification(globalNotificationID);
        }
    }

    this.IsSaveProjectsEnabled = function () {

        // Check whether any changes are defined.
        var isDirty = _jsGridControl.IsInitialized() && !_jsGridControl.AnyErrors() && 
          _jsGridControl.GetDiffTracker().AnyChanges();
        return isDirty;
    }

    this.SaveProjects = function () {

        // Validate the data.
        if (!_jsGridControl.AnyErrors()) {
            var diffTracker = _jsGridControl.GetDiffTracker();
            if (diffTracker.AnyChanges()) {
                _jsGridControl.DisableEditing();

                // Find and save any changes.
                var diffs = diffTracker.GetUniquePropertyChanges();
                _changesToSave = diffs;

                DisplayNotification("Processing...");

                for (var projectUid in _changesToSave) {
                    for (var customFieldUid in _changesToSave[projectUid]) {
                        if (_dataSource.recordFactory.gridFieldMap[customFieldUid]._propType.ID.indexOf(customFieldUid + '_LT') > 0) {
                            if (!_changesToSave[projectUid][customFieldUid]['data'].length) {
                                _changesToSave[projectUid][customFieldUid]['data'] = 
                                SP.JsGrid.GuidManager.LookupGuidForIndex(_changesToSave[projectUid][customFieldUid]['data']);
                            }
                            else {
                                for (var i = 0; i < _changesToSave[projectUid][customFieldUid]['data'].length; i++) {
                                    _changesToSave[projectUid][customFieldUid]['data'][i] = 
                                      SP.JsGrid.GuidManager.LookupGuidForIndex(_changesToSave[projectUid][customFieldUid]['data'][i]);
                                }
                            }
                        }
                    }
                }
                BindJSGrid(CMD_SAVE_PROJECTS);
                DisplayStatusInfo("Status:  Updates have been saved.");
            }
            else
                alert("No changes made.");
        }
    }
    var globalNotificationID = '';
    var globalStatusID = '';

    function DisplayStatusInfo(strMessage) {
        RemoveStatus(globalStatusID);
        var statusId = SP.UI.Status.addStatus(strMessage);
        SP.UI.Status.setStatusPriColor(statusId, 'yellow');
        globalStatusID = statusId;
    }

    function RemoveStatus(globalStatusID) {
        SP.UI.Status.removeStatus(globalStatusID);
        globalStatusID = '';
    }

    function DisplayNotification(strMessage) {
        globalNotificationID = SP.UI.Notify.addNotification(strMessage, true);
    }

    function RemoveNotification(globalNotificationID) {
        SP.UI.Notify.removeNotification(globalNotificationID);
        globalNotificationID = '';
    }
};

To add the utility classes

In Solution Explorer, right-click click the JSGridWriteback project, point to Add, and then click New Folder. Name the folder GridUtils.
Right-click the GridUtils folder, point to Add, and then click New Item.
In the Add New Item dialog box, expand the Visual C# node, select Code, and then select Code File. Name the file GridData.cs, and then click Add.
Repeat the previous two steps to create two more files in the GridUtils folder, but name the files GridUtilities.cs and CallbackArgs.cs.

Paste the following blocks of code into their respective files in the GridUtils folder: GridData.cs, GridUtilities.cs, and CallbackArgs.cs.

GridData.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.JSGrid;

namespace JSGridWriteback.GridUtils
{
    public class GridData
    {
        private string connString = "Server=.;Database=AdventureWorks;Integrated Security=SSPI";

        public virtual DataSet GetData()
        {
            DataSet dstContact = new DataSet();

            using (SqlConnection sqlConn = new SqlConnection(this.connString))
            {
                SqlDataAdapter da = 
                  new SqlDataAdapter("SELECT [ContactID],[Title],[FirstName],[MiddleName],[LastName],[EmailAddress],[Phone] FROM [AdventureWorks].[Person].[Contact] WHERE ContactID <= 10", sqlConn);
                da.Fill(dstContact);
            }
            return dstContact;
        }

        public virtual object SetData(string strKey, Dictionary<string, object> ColumnAndValue)
        {
            SqlConnection sqlConn = new SqlConnection();
            try
            {
                sqlConn.ConnectionString = this.connString;

                SqlCommand sqlCmd = new SqlCommand("[AdventureWorks].[dbo].[USP_UpdateContact]", sqlConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add(new SqlParameter("@ContactID", strKey));

                if (ColumnAndValue.ContainsKey("Title"))
                {
                    Dictionary<string, object> dicKeyValue = (Dictionary<string, object>)ColumnAndValue["Title"];
                    sqlCmd.Parameters.Add(new SqlParameter("@Title", Convert.ToString(dicKeyValue["data"])));
                }

                if (ColumnAndValue.ContainsKey("FirstName"))
                {
                    Dictionary<string, object> dicKeyValue = (Dictionary<string, object>)ColumnAndValue["FirstName"];
                    sqlCmd.Parameters.Add(new SqlParameter("@FirstName", Convert.ToString(dicKeyValue["data"])));
                }

                if (ColumnAndValue.ContainsKey("MiddleName"))
                {
                    Dictionary<string, object> dicKeyValue = (Dictionary<string, object>)ColumnAndValue["MiddleName"];
                    sqlCmd.Parameters.Add(new SqlParameter("@MiddleName", Convert.ToString(dicKeyValue["data"])));
                }

                if (ColumnAndValue.ContainsKey("LastName"))
                {
                    Dictionary<string, object> dicKeyValue = (Dictionary<string, object>)ColumnAndValue["LastName"];
                    sqlCmd.Parameters.Add(new SqlParameter("@LastName", Convert.ToString(dicKeyValue["data"])));
                }

                if (ColumnAndValue.ContainsKey("EmailAddress"))
                {
                    Dictionary<string, object> dicKeyValue = (Dictionary<string, object>)ColumnAndValue["EmailAddress"];
                    sqlCmd.Parameters.Add(new SqlParameter("@EmailAddress", Convert.ToString(dicKeyValue["data"])));
                }

                if (ColumnAndValue.ContainsKey("Phone"))
                {
                    Dictionary<string, object> dicKeyValue = (Dictionary<string, object>)ColumnAndValue["Phone"];
                    sqlCmd.Parameters.Add(new SqlParameter("@Phone", Convert.ToString(dicKeyValue["data"])));
                }

                sqlConn.Open();

                sqlCmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (sqlConn.State != ConnectionState.Closed)
                {
                    sqlConn.Close();
                }
            }
            return new object();
        }
    }
}

GridData specifies only ten records from the Person.Contact table in the AdventureWorks database.

GridUtilities.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.JSGrid;

namespace JSGridWriteback.GridUtils
{
    public static class GridUtilities
    {
        public static IList<GridColumn> GetGridColumns(DataTable table)
        {
            List<GridColumn> r = new List<GridColumn>();
            foreach (DataColumn iterator in table.Columns)
            {
                GridColumn col = new GridColumn();

                // Point the column at a fieldKey name.
                col.FieldKey = iterator.ColumnName;

                // Name the column header.
                col.Name = iterator.ColumnName;

                // Define the column width. You can specify column
                // width based on the column values by using conditional
                // statements on column names.
                col.Width = 250;

                // Hide the key column.
                if (col.FieldKey == "ContactID")
                {
                    col.IsVisible = false;
                } 

                // Add the column.
                r.Add(col);
            }
            return r;
        }

        public static IList<GridField> GetGridFields(DataTable table)
        {
            List<GridField> r = new List<GridField>();

            foreach (DataColumn iterator in table.Columns)
            {
                GridField field = new GridField();
                field = formatGridField(field, iterator);

                r.Add(field);
            }
            return r;
        }

        public static GridField formatGridField(GridField gf, DataColumn dc)
        {

            // Set the field key name.
            gf.FieldKey = dc.ColumnName;

            // When in doubt, serialize the data value.
            gf.SerializeDataValue = true;

            // Make the required columns editable.
            switch (gf.FieldKey)
            {
                case "Title":
                case "FirstName":
                case "MiddleName":
                case "LastName":
                case "EmailAddress":
                case "Phone":
                    gf.EditMode = EditMode.ReadWrite;
                    break;
                default:
                    gf.EditMode = EditMode.ReadOnly;
                    break;
            }

            gf.PropertyTypeId = "String";

            // The Localizer determines how to render the underlying data on screen.
            gf.Localizer = (ValueLocalizer)delegate(DataRow row, object toConvert)
            {
                return toConvert == null ? "" : toConvert.ToString();
            };

            // The Serialization type is a required property. 
            gf.SerializeLocalizedValue = true;
            gf.SerializeDataValue = true;

            return gf;
        }
    }
}

The formatGridField method makes the grid cells in the Title, FirstName, MiddleName, LastName, EmailAddress, and Phone fields editable by setting the GridField.EditMode property to ReadWrite.

Note

The EditMode enumeration specifies valid edit modes.

CallbackArgs.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace JSGridWriteback.GridUtils
{
    public class CallbackArgs
    {
        public string Command;
        public Dictionary<string, Dictionary<string, object>> Changes;
    }
}

During call back, the client-side grid manager passes the Command property and the Changes property as arguments to the server.

To modify the JS Grid control

Open JSGridWritebackWebPartUserControl.ascx.cs.

Replace the autogenerated contents of the JSGridWritebackWebPartUserControl class with the following code.

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Web.Script.Serialization;
using JSGridWriteback.GridUtils;
using Microsoft.SharePoint.JSGrid;
using Microsoft.SharePoint.JsonUtilities;


namespace JSGridWriteback.JSGridWritebackWebPart
{

    public partial class JSGridWritebackWebPartUserControl : UserControl, ICallbackEventHandler
    {
        private CallbackArgs _callbackArgs;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                JsGridControl.JsInitObject = new
                {
                    callbackScript = this.Page.ClientScript.GetCallbackEventReference(
                        this, "args", "WGM.DisplayProjectsData", "true", true)
                };
            }
        }

        public string GetCallbackResult()
        {
            string gridJson = null;

            switch (_callbackArgs.Command)
            {
                case "ShowProjects":
                    gridJson = GetProjectsJson();
                    break;
                case "SaveProjects":
                    Dictionary<string, Dictionary<string, object>> changes = 
                      _callbackArgs.Changes.ToDictionary(_ => _.Key, _ => _.Value);
                    try
                    {
                        // Update the XML.
                        changes.ToList().ToDictionary(_ => _.Key, 
                          _ => (new GridData()).SetData(_.Key, changes[_.Key]));
                    }
                    catch
                    {
                        Debugger.Break();
                    }
                    try
                    {
                        gridJson = GetProjectsJson();
                    }
                    catch
                    {
                        Debugger.Break();
                    }
                    break;
                default:
                    throw new NotImplementedException();
            }

            return new JavaScriptSerializer().Serialize(new
            {
                gridJson
            });
        }

        private string GetProjectsJson()
        {
            // Get grid data.
            DataTable dtTasks = new GridData().GetData().Tables[0];

            // Create a grid serializer to connect to the data.
            GridSerializer gds = new GridSerializer(SerializeMode.Full, dtTasks, "ContactID",
                new FieldOrderCollection(),
                GridUtilities.GetGridFields(dtTasks), GridUtilities.GetGridColumns(dtTasks));

            var serializer = new Serializer();

            return gds.ToJson(serializer);
        }

        public void RaiseCallbackEvent(string eventArgument)
        {
            // Deserialize callback arguments from JSON.
            _callbackArgs = new JavaScriptSerializer()
                .Deserialize<CallbackArgs>(eventArgument);
        }
    }
}

Tip

Before you deploy the solution to a production server, remove the debugging code from the catch blocks in the GetCallbackResult method.

To create the stored procedure that updates the database

Open SQL Server Management Studio. (From the Start menu, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.)
Connect to the database engine on the server that contains the AdventureWorks database.

Click New Query, paste the following script into the query tab, and then click Execute.

USE [AdventureWorks]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_UpdateContact]
(
    @ContactID int,
    @Title nvarchar(8) = NULL,
    @FirstName nvarchar(50) = NULL,
    @MiddleName nvarchar(50) = NULL,
    @LastName nvarchar(50) = NULL,
    @EmailAddress nvarchar(50) = NULL,
    @Phone nvarchar(25) = NULL
)
AS
BEGIN
    UPDATE [AdventureWorks].[Person].[Contact]
    SET Title = ISNULL(@Title, Title),
        FirstName = ISNULL(@FirstName, FirstName),
        MiddleName = ISNULL(@MiddleName, MiddleName),
        LastName = ISNULL(@LastName, LastName),
        EmailAddress = ISNULL(@EmailAddress, EmailAddress),
        Phone = ISNULL(@Phone, Phone),
        ModifiedDate = GETDATE()
    WHERE ContactID = @ContactID
END
GO

To test the writeback functionality

In Visual Studio, press F5 to deploy the JSGridWriteback project. The SharePoint site opens after the project deploys, and the JSGridWritebackWebPart Web Part is automatically added to the SharePoint 2010 Web Part gallery.
Open any Web Parts page, click the Page tab, and then click Edit.
Click the Insert tab, click Web Part, click the Custom category, and then add the JSGridWritebackWebPart Web Part to the page.
Click the Page tab, and then click Save & Close. The page displays the Web Part.

Note

For more information about how to work with Web Parts, see How to: Work with Web Parts on a Page.
To write back the changes to the Person.Contact table in the AdventureWorks database, double-click a grid cell, edit the value, and then click Save Changes.

Note

When you close Windows Internet Explorer or press Shift+F5 in Visual Studio, Visual Studio retracts the Web Part (if Auto-retract after debugging is selected in the SharePoint tab of the JSGridWriteback property page) and resets Internet Information Services (IIS). If you click Deploy Solution on the Build menu, Visual Studio deploys the solution on the development computer so that you can use the Web Part independently from Visual Studio.

Compartilhar via