I've been having difficulty for a long time with json. I have a soap service (asmx) that returns json for use in the Datatables and fullcalendar js plugins. I send a paramtised query to sql which fills a dataset. I then foreach through the dataset, append a few bits based on ifs, and populate a List<>. I then do the following on the List: string json = JsonConvert.SerializeObject(Events); The issue that i have, is that if the title of my event, has ab obscure character, like é,”,ô etc, the plugins fail parsing the json, as the json is malformed. The only way i've been able to get around this, is by writing a dirty method, to string replace the title before it is serialized. i.e
try
{
SqlConnection con = new SqlConnection(constring);
SqlDataAdapter DA = new SqlDataAdapter();
DataSet ds = new DataSet();
List<CalendarEvent> pdetails = new List<CalendarEvent>();
using (con)
{
using (SqlCommand cmd = new SqlCommand("[recmgt].[getCalendarEntries]", con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
DA.SelectCommand = cmd;
DA.Fill(ds);
con.Close();
foreach (DataRow dr in ds.Tables[0].Rows)
{
CalendarEvent per = new CalendarEvent();
per.id = dr["ID"].ToString();
per.Title = cleanTitle(dr["Title"].ToString());
per.Starts = dr["Starts"].ToString();
per.Ends = dr["Ends"].ToString();
pdetails.Add(per);
}
}
}
DA.Dispose();
JavaScriptSerializer serializer = new JavaScriptSerializer();
serializer.MaxJsonLength = Int32.MaxValue;
string strResponse = serializer.Serialize(pdetails);
Context.Response.Clear();
Context.Response.ContentType = "application/json";
Context.Response.AddHeader("content-length", strResponse.Length.ToString());
Context.Response.Flush();
Context.Response.Write(strResponse);
}
catch (Exception exc)
{
}
}
public string cleanTitle(string PremTitle)
{
PremTitle = PremTitle.Replace("é", "e");
PremTitle = PremTitle.Replace("/", "_");
PremTitle = PremTitle.Replace("'", "\'");
PremTitle = PremTitle.Replace("’", "\'");
PremTitle = PremTitle.Replace("“", "\'");
PremTitle = PremTitle.Replace("”", "\'");
PremTitle = PremTitle.Replace(@"\", " ");
PremTitle = PremTitle.Replace("–", "-");
PremTitle = PremTitle.Replace("—", "-");
PremTitle = PremTitle.Replace("£", "");
PremTitle = PremTitle.Replace("ô", "o");
return PremTitle;
}
I feel like i'm firefighting a bit, everytime i get a datatable failure, i dig around in sql to find the char causing it, and add it to the method above. Be great if someone could point me in the right direction, or some method to return ascii instead of chars? Many thanks :)