Save jQuery widgets to SqlDatabase using JSON

Question

I created a dashboard with widgets awhile ago and I'm now revisiting how to persist the the widgets per user to the database. I thought I was on the right track, but I'm very new to JSON and I'm unable to accomplish what I need. I've researched this and tried multiple things but nothing has worked out so far. Here is my code..

javascript that creates item for update

function updateWidgetData() {
    var items = [];
    $('.column').each(function () {
        var columnId = $(this).attr('id');
        $('.dragbox', this).each(function (i) {
            var collapsed = 0;
            if ($(this).find('.dragbox-content').css('display') == "none")
                collapsed = 1;
             //Create Item object for current panel  
            var item = {
                id: $(this).attr('id'),
                collapsed: collapsed,
                order: i,
                column: columnId
             };
             //Push item object into items array  
             items.push(item);
         });
     });


//Assign items array to sortorder JSON variable  
var sortorder = { items: items };

Now my goal is to pass the sortorder to be saved to the database... but I have this for testing..

var testData = '{ "Column1": "test1", "Column2": "test2"}'

$.ajax ({
    url: "/Handlers/SaveWidgets.ashx",
    type: "POST",
    contentType: "application/json; charset=uft-8",
    dataType: "json",
    data: testData,
    success: function (response) {
        alert("Passed json");
    },
    error: function (error) {
        alert("Failed passing json.");
    }
 });

Then in my handler..

public void ProcessRequest(HttpContext context)
{
   context.Response.ContentType = "application/json";

   string column1 = (string)context.Request.Form["Column1"];
   string column2 = (string)context.Request.Form["Column2"];

   using (SqlConnection connCao = new SqlConnection(ConfigurationManager.ConnectionStrings["dboCao"].ConnectionString))
    {
       using(SqlCommand cmdWidget = new SqlCommand("INSERT INTO TestTable (Column1, Column2) VALUES (@column1, @column2)", connCao))
       {
            cmdWidget.Parameters.AddWithValue("@column1", column1);
            cmdWidget.Parameters.AddWithValue("@column2", column2);
            connCao.Open();
            cmdWidget.ExecuteNonQuery();
            connCao.Close();
        }
     }
 }

but I'm getting that it's expecting parameters @column1, and @column2 which were never supplied. So clearly I'm missing how to do something and I'm unable to find what I'm missing on the google machine.

I have used this link here but this doesn't explain the greatest and a few things confused me.

I also found other links but nothing that explains what I'm trying to accomplish. Any help is greatly appreciated!

score 1 · Accepted Answer · answered Nov 27 '14 at 02:17

I would start by creating a class to represent the data you are posting to the handler.

using System;
using System.Runtime.Serialization;

[DataContract]
public class YourDataModel
{
    public YourDataModel() { }

    // When a property in your model doesn't 
    // match up exactly you can manually 
    // specify the name
    [DataMember(Name = "Column1")]
    public String Col1 { get; set; }

    // If things match up exactly (including case)
    // you don't need to manually map the Name
    [DataMember]
    public String Column2 { get; set; }
}

Then modify your handler to create an instance of that class from posted JSON data.

using System;
using System.IO;
using System.Web;
using System.Runtime.Serialization.Json;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class SaveWidgets : IHttpHandler {

    public void ProcessRequest (HttpContext context) 
    {
        String json = String.Empty;
        // you have sent JSON to the server
        // read it into a string via the input stream
        using (StreamReader rd = new StreamReader(context.Request.InputStream))
        {
            json = rd.ReadToEnd();
        }

        // create an instance of YourDataModel from the
        // json sent to this handler
        YourDataModel data = null;
        DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(YourDataModel));
        using (MemoryStream ms = new MemoryStream())
        {
            byte[] utf8Bytes = Encoding.UTF8.GetBytes(json);
            ms.Write(utf8Bytes, 0, utf8Bytes.Length);
            ms.Position = 0;
            data = serializer.ReadObject(ms) as YourDataModel;
        }

        // update the DB and
        // send back a JSON response
        int rowsUpdated = 0;
        using (SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["dboCao"].ConnectionString))
        {
            c.Open();

            String sql = @"
                INSERT INTO TestTable 
                    (Column1, Column2) 
                  VALUES 
                    (@column1, @column2);";

            using (SqlCommand cmd = new SqlCommand(sql, c))
            {
                cmd.Parameters.Add("@column1", SqlDbType.VarChar, 50).Value = data.Col1;
                cmd.Parameters.Add("@column2", SqlDbType.VarChar, 50).Value = data.Column2;
                rowsUpdated = cmd.ExecuteNonQuery();
            }
        }

        context.Response.ContentType = "application/json";
        context.Response.Write("{ \"rows_updated\": " + rowsUpdated + " }");
    }

    public bool IsReusable {
        get { return false; }
    }
}

Save jQuery widgets to SqlDatabase using JSON

1 Answers1

Linked