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;
}
/// <summary>
/// Read from SQL objects and return as JSON. Uses the JsonReadOrDelete stored procedure to return limited
/// record sets to the Sql client. Great for streaming pages of data to a stateless web client.
/// </summary>
/// <param name="objectName">Name of the table, view or object.</param>
/// <param name="rowFrom">The row to fetch from.</param>
/// <param name="rowTo">The row to fetch to.</param>
/// <param name="where">The where clause object.</param>
/// <returns>JSON schema data and row data and status or delete status.</returns>
public static JsonResponse JsonReadOrDelete(string objectName, int rowFrom, int rowTo, SqlWhere where)
{
return JsonReadOrDelete(objectName, rowFrom, rowTo, where, Guid.Empty, null, null, null, true, -1, false, string.Empty, OrderDirection.Descending, null);
}
/// <summary>
/// Read from SQL objects and return as JSON. Uses the JsonReadOrDelete stored procedure to return limited
/// record sets to the Sql client. Great for streaming pages of data to a stateless web client.
/// Note: Delete requires a [RowVersion] rowversion column present in the table.
/// State data is stored in the table UIColumns on a per user basis. Users are defined in Oda.Authentication class
/// and in the table Accounts.
/// </summary>
/// <param name="objectName">Name of the table, view or object.</param>
/// <param name="rowFrom">The row to fetch from.</param>
/// <param name="rowTo">The row to fetch to.</param>
/// <param name="whereClause">The where clause object.</param>
/// <param name="accountId">The account id.</param>
/// <param name="searchClause">The search clause object.</param>
/// <param name="aggregates">The aggregates to return if any.</param>
/// <param name="selectedRows">The selected rows. For use with the delete bit.</param>
/// <param name="includeSchemaData">If set to <c>true</c> then schema data will be included.</param>
/// <param name="checksum">The checksum of the table. This is the some of the rowversion column. For use with the delete bit.</param>
/// <param name="deleteSelection">if set to <c>true</c> then the rows defined in the selectedRows parameter will be deleted if the table checksome matches.</param>
/// <param name="orderBy">The column order by.</param>
/// <param name="orderByDirection">The direction to order by.</param>
/// <returns>
/// JSON schema data and row data and status or delete status.
/// </returns>
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)
{
return JsonReadOrDelete(objectName, rowFrom, rowTo, whereClause, accountId, searchClause,
aggregates, selectedRows, includeSchemaData, checksum, deleteSelection,
orderBy, orderByDirection, null);
}
public static JsonResponse JsonReadOrDelete(string query, int rowFrom, int rowTo, Guid accountId, SqlParameter[] parameters, string orderBy,
OrderDirection orderByDirection, SqlConnection connection)
{
//create view hash name
var hashViewName = string.Format("temp_{0}",Core.Hash(query).Replace("/","").Replace("+","").Replace("=",""));
var cn = connection ?? CreateConnection();
if(cn.State != ConnectionState.Open) { cn.Open(); }
var cmd = cn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(@"if not exists(select 0 from sysobjects where name = '{0}' and type = 'V') begin
declare @statement nvarchar(max) = 'create view {0} as {1}'
exec sp_executesql @statement
end", hashViewName, query.Replace("'","''"));
var c = new SqlWhere { Parmeters = parameters };
cmd.ExecuteNonQuery();
Core.DisposeHttpApplication += delegate {
using (var icn = CreateConnection()) {
icn.Open();
using (var icmd = icn.CreateCommand()) {
icmd.CommandType = CommandType.Text;
icmd.CommandText = string.Format(@"if exists(select 0 from sysobjects where name = '{0}' and type = 'V') begin
drop view {0}
end", hashViewName);
icmd.ExecuteNonQuery();
}
}
};
var j = JsonReadOrDelete(hashViewName, rowFrom, rowTo, c, accountId, null, null, null, true, -1, false, orderBy, orderByDirection, cn);
cmd.Dispose();
cn.Dispose();
return j;
}
