/// <summary>
/// 把json转化为datatable
/// </summary>
/// <param name="jo">json数据</param>
/// <param name="selectSql">取表数据的sql语句</param>
/// <returns></returns>
public DataTable ToDataTable(JObject jo, string selectSql)
{
if (jo == null)
{
throw new ArgumentException("no json data");
}
if (string.IsNullOrEmpty(selectSql))
{
throw new ArgumentException("selectSql is empty");
}
string root = "table";//默认值
JToken rootobj = jo["table"];
if (rootobj == null)
{
root = "form";
}
string keycolumn = jo[root].Attr("key");
JToken modifyRow = jo[root]["modifiedRow"];
JToken newRow = jo[root]["newRow"];
JToken deleteRow = jo[root]["deletedRow"];
JToken unchangedRow = jo[root]["unChangedRow"];
//key去掉表名
string[] keyarr = keycolumn.Split('.');
if (keyarr.Length > 1)
{
keycolumn = keyarr[1];
}
string where = CommonUtil.GetWhere(jo, root);
string sql = string.Empty;
if (!string.IsNullOrEmpty(where))
{
if (selectSql.ToLower().IndexOf("where") > 0)
{
sql = selectSql + " and " + where;
}
else
{
sql = selectSql + " where " + where;
}
}
else
{
if (selectSql.ToLower().IndexOf("where") > 0)
{
sql = selectSql + " and 1=0";
}
else
{
sql = selectSql + " where 1=0";
}
}
DataTable dt = CommonUtil.GetDataTable(sql);
if (unchangedRow != null)
{
this.DealModifyRow(unchangedRow, dt, keycolumn, true);
}
if (newRow != null)
{
this.DealNewRow(newRow, dt);
}
if (modifyRow != null)
{
this.DealModifyRow(modifyRow, dt, keycolumn, false);
}
if (deleteRow != null)
{
this.DealDeleteRow(deleteRow, dt, keycolumn);
}
return(dt);
}
private void SetUpdateRow(DataTable dt, DataRow dr, JProperty property)
{
string value = string.Empty;
string field = property.Name;
if (this.needmapping)
{
if (this.FieldMapping.ContainsKey(property.Name))
{
field = this.FieldMapping[property.Name];
}
}
Type t = dt.Columns[field].DataType;
if (property.Value is JValue)//仅包含现值
{
value = property.Value.ToString();
}
if (property.Value is JObject)//包含原始值,一般会进
{
JObject jotext = property.Value as JObject;
if (jotext.Property("text") != null)
{
value = jotext.Property("text").Value.ToString();
}
else
{
value = "";
}
}
try
{
if (!string.IsNullOrEmpty(value))
{
switch (t.Name)
{
case "Int32":
case "Int16":
if (value.ToLower() == "true") //checkcolumn在新增时可能会传true或者false
{
value = "1";
}
else if (value.ToLower() == "false")
{
value = "0";
}
dr[field] = Convert.ToInt32(value);
break;
//case "Int32": dr[field] = Convert.ToInt32(value);
// break;
case "Int64":
dr[field] = Convert.ToInt64(value);
break;
case "Decimal":
dr[field] = Convert.ToDecimal(value);
break;
case "Double":
case "float":
dr[field] = Convert.ToDouble(value);
break;
case "DateTime":
if (value.IndexOf("UTC") > 0 || value.IndexOf("GMT") > 0) //utc时间格式
{
value = CommonUtil.UTCtimeToDateTime(value);
}
dr[field] = Convert.ToDateTime(value);
break;
default:
dr[field] = value;
break;
}
}
else
{
//if (t.Name == "String")
//{
// dr[property.Name] = string.Empty;//字符修改为空
//}
dr[field] = DBNull.Value;
}
}
catch (Exception ex)
{
string msg = string.Format("SetPropertyValue数据转换失败，字段名:[{0}],数据值为：{1},", field, value);
Logger.Error(msg + "详细错误: " + ex.StackTrace);
throw new Exception(msg, ex);
}
}
