public static JsonResponse GetUploadStatus(string qid)
{
var j = new JsonResponse();
// try and turn the id into a guid
Guid id;
if (!Guid.TryParse(qid, out id)) {
j.Message = "Id not a valid Guid.";
j.Error = 1;
return j;
}
if(!Core.UploadStatuses.ContainsKey(id)) {
j.Message = "Upload does not exist or status has expired.";
j.Error = 2;
return j;
}
var u = Core.UploadStatuses[id];
j.Add("BytesRead", u.BytesRead);
j.Add("BytesTotal", u.BytesTotal);
j.Add("Complete", u.Complete);
j.Add("CurrentFile", u.CurrentFile);
j.Add("Id", u.Id);
j.Add("LastUpdated", u.LastUpdated);
j.Add("Message", u.Message);
j.Add("StartedOn", u.StartedOn);
return j;
}
public static JsonResponse JsonReadOrDelete(string objectName, int rowFrom, int rowTo, SqlWhere whereClause, Guid accountId, SqlWhere searchClause,
IDictionary<string, string> aggregates, ICollection<int> selectedRows, bool includeSchemaData, Int64 checksum, bool deleteSelection,
string orderBy, OrderDirection orderByDirection, SqlConnection connection)
{
var s = new JsonResponse();
var aggs = new StringBuilder();
var sRows = new StringBuilder();
s.MethodName = "JsonReadOrDelete";
var rows = new List<object>();
s.Add("rows", rows);
// convert aggregate column dictionary to a string
aggregates = aggregates ?? new Dictionary<string, string>();
if(aggregates.Count>0){
foreach(var k in aggregates){
aggs.AppendFormat("{0}|{1},", k.Key, k.Value);
}
// remove trailing comma
aggs.Remove(aggs.Length - 1, 1);
}
selectedRows = selectedRows ?? new Collection<int>();
foreach(var i in selectedRows){
sRows.AppendFormat("{0},",i);
// remove trailing comma
sRows.Remove(aggs.Length - 1, 1);
}
using (var cn = connection ?? CreateConnection()) {
if (cn.State != ConnectionState.Open) { cn.Open(); }
using (var cmd = cn.CreateCommand()) {
cmd.CommandText = _jsonReadOrDeleteQuery;
cmd.CommandType = CommandType.StoredProcedure;
SqlMetaData[] prameterList = {
new SqlMetaData("Name",SqlDbType.VarChar,100),
new SqlMetaData("Type",SqlDbType.VarChar,100),
new SqlMetaData("Length",SqlDbType.VarChar,10),
new SqlMetaData("Value",SqlDbType.Variant)
};
var whereParameterList = new List<SqlDataRecord>();
var searchParameterList = new List<SqlDataRecord>();
whereClause = whereClause ?? new SqlWhere();
searchClause = searchClause ?? new SqlWhere();
foreach(var p in whereClause.Parmeters){
var whereParameter = new SqlDataRecord(prameterList);
whereParameter.SetString(0, p.Name);
whereParameter.SetString(1, p.SqlDbType.ToString());
whereParameter.SetValue(2, p.Length);
whereParameter.SetValue(3, p.Value);
whereParameterList.Add(whereParameter);
}
foreach (var p in searchClause.Parmeters) {
var searchParameter = new SqlDataRecord(prameterList);
searchParameter.SetString(0, p.Name);
searchParameter.SetString(1, p.SqlDbType.ToString());
searchParameter.SetValue(2, p.Length);
searchParameter.SetValue(3, p.Value);
searchParameterList.Add(searchParameter);
}
cmd.Parameters.Add("@objName", SqlDbType.VarChar).Value = objectName;
cmd.Parameters.Add("@record_from", SqlDbType.Int).Value = rowFrom;
cmd.Parameters.Add("@record_to", SqlDbType.Int).Value = rowTo;
cmd.Parameters.Add("@suffix", SqlDbType.VarChar).Value = whereClause.WhereClause;
cmd.Parameters.Add("@accountId", SqlDbType.UniqueIdentifier).Value = accountId;
cmd.Parameters.Add("@searchSuffix", SqlDbType.VarChar).Value = searchClause.WhereClause;
cmd.Parameters.Add("@aggregateColumns", SqlDbType.VarChar).Value = aggs.ToString();
cmd.Parameters.Add("@selectedRowsCSV", SqlDbType.VarChar).Value = sRows.ToString();
cmd.Parameters.Add("@includeSchema", SqlDbType.Bit).Value = includeSchemaData;
cmd.Parameters.Add("@checksum", SqlDbType.BigInt).Value = checksum;
cmd.Parameters.Add("@delete", SqlDbType.Bit).Value = deleteSelection;
cmd.Parameters.Add("@orderBy_override", SqlDbType.VarChar).Value = orderBy;
cmd.Parameters.Add("@orderDirection_override", SqlDbType.VarChar).Value = orderByDirection == OrderDirection.Ascending ? "asc" : "desc";
cmd.Parameters.Add("@whereParameterList", SqlDbType.Structured);
cmd.Parameters["@whereParameterList"].Direction = ParameterDirection.Input;
cmd.Parameters["@whereParameterList"].Value = (whereParameterList.Count == 0 ? null : whereParameterList);
cmd.Parameters.Add("@searchParameterList", SqlDbType.Structured);
cmd.Parameters["@searchParameterList"].Direction = ParameterDirection.Input;
cmd.Parameters["@searchParameterList"].Value = (searchParameterList.Count == 0 ? null : searchParameterList);
using (var r = cmd.ExecuteReader()) {
if(searchClause.WhereClause.Length == 0){
// add range data
var range = new Dictionary<string, object> { { "from", rowFrom }, { "to", rowTo } };
s.Add("range", range);
// first row contains error data
r.Read();
var header = JsonConvert.DeserializeObject<Dictionary<string, object>>(r.GetString(0));
s.Add("header", header);
// pull error and description info from SP result
s.Error = Convert.ToInt32((Int64)header["error"]);
s.Message = (string)header["description"];
// second row contains schema data
r.Read();
var schema = JsonConvert.DeserializeObject<List<object>>(r.GetString(0));
s.Add("columns", schema);
// the rest are row data
while (r.Read()) {
var row = JsonConvert.DeserializeObject<List<object>>(r.GetString(0));
rows.Add(row);
}
}else {
if(r.HasRows){
s["rows"] = JsonConvert.DeserializeObject<List<int>>(r.GetString(0));
}else {
s["rows"] = new List<int>();
}
}
}
}
}
return s;
}
