} // End Sub WriteArray public static async System.Threading.Tasks.Task AnyDataReaderToJson( string sql , System.Collections.Generic.Dictionary<string, object> pars , Microsoft.AspNetCore.Http.HttpContext context , RenderType_t format) { SqlService service = (SqlService) context.RequestServices.GetService(typeof(SqlService)); using (System.Data.Common.DbConnection con = service.Connection) { using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = sql; // cmd.CommandText = "SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;"; service.AddParameterList(pars, cmd); // cmd.ExecuteNonQueryAsync // cmd.ExecuteReaderAsync // cmd.ExecuteScalarAsync using (System.Data.Common.DbDataReader dr = await cmd.ExecuteReaderAsync( System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection)) { using (System.IO.StreamWriter output = new System.IO.StreamWriter(context.Response.Body)) { using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(output)) // context.Response.Output) { if (format.HasFlag(RenderType_t.Indented)) jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; context.Response.StatusCode = (int) System.Net.HttpStatusCode.OK; context.Response.ContentType = "application/json"; await jsonWriter.WriteStartObjectAsync(); await jsonWriter.WritePropertyNameAsync("tables"); await jsonWriter.WriteStartArrayAsync(); do { if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WriteStartObjectAsync(); await jsonWriter.WritePropertyNameAsync("columns"); if (format.HasFlag(RenderType_t.Columns_Associative)) { await WriteAssociativeColumnsArray(jsonWriter, dr, format); } else if (format.HasFlag(RenderType_t.Columns_ObjectArray)) { await WriteComplexArray(jsonWriter, dr, format); } else // (format.HasFlag(RenderType_t.Array)) { await WriteArray(jsonWriter, dr); } } // End if (!format.HasFlag(RenderType_t.Data_Only)) if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WritePropertyNameAsync("rows"); } // End if (!format.HasFlag(RenderType_t.Data_Only)) await jsonWriter.WriteStartArrayAsync(); string[] columns = null; if (format.HasFlag(RenderType_t.DataTable)) { columns = new string[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; i++) { columns[i] = dr.GetName(i); } // Next i } // End if (format.HasFlag(RenderType_t.DataTable)) while (await dr.ReadAsync()) { if (format.HasFlag(RenderType_t.DataTable)) await jsonWriter.WriteStartObjectAsync(); else await jsonWriter.WriteStartArrayAsync(); for (int i = 0; i <= dr.FieldCount - 1; i++) { object obj = await dr.GetFieldValueAsync<object>(i); if (obj == System.DBNull.Value) obj = null; if (columns != null && format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WritePropertyNameAsync(columns[i]); } await jsonWriter.WriteValueAsync(obj); } // Next i if (format.HasFlag(RenderType_t.DataTable)) await jsonWriter.WriteEndObjectAsync(); else await jsonWriter.WriteEndArrayAsync(); } // Whend await jsonWriter.WriteEndArrayAsync(); if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WriteEndObjectAsync(); } // End if (!format.HasFlag(RenderType_t.Data_Only)) } while (await dr.NextResultAsync()); await jsonWriter.WriteEndArrayAsync(); await jsonWriter.WriteEndObjectAsync(); await jsonWriter.FlushAsync(); await output.FlushAsync(); } // jsonWriter } // output } // dr } // End Using cmd if (con.State != System.Data.ConnectionState.Closed) con.Close(); } // con } // End Sub WriteArray
} // End Sub WriteArray public static void AnyDataReaderToAnyJson( string sql , SqlService service , System.Collections.Generic.Dictionary <string, object> pars , System.Web.HttpContext context , RenderType_t format) { using (System.Data.Common.DbConnection con = service.Connection) { using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = sql; service.AddParameterList(pars, cmd); // cmd.ExecuteNonQuery // cmd.ExecuteReader // cmd.ExecuteScalar using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader( System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection)) { using (System.IO.StreamWriter output = new System.IO.StreamWriter(context.Response.OutputStream)) { using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(output)) // context.Response.Output) { if (format.HasFlag(RenderType_t.Indented)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; } context.Response.StatusCode = (int)System.Net.HttpStatusCode.OK; context.Response.ContentType = "application/json"; jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("tables"); jsonWriter.WriteStartArray(); do { if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("columns"); if (format.HasFlag(RenderType_t.Columns_Associative)) { WriteAssociativeColumnsArray(jsonWriter, dr, format); } else if (format.HasFlag(RenderType_t.Columns_ObjectArray)) { WriteComplexArray(jsonWriter, dr, format); } else // (format.HasFlag(RenderType_t.Array)) { WriteArray(jsonWriter, dr); } } // End if (!format.HasFlag(RenderType_t.Data_Only)) if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WritePropertyName("rows"); } // End if (!format.HasFlag(RenderType_t.Data_Only)) jsonWriter.WriteStartArray(); if (dr.HasRows) { string[] columns = null; if (format.HasFlag(RenderType_t.DataTable)) { columns = new string[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; i++) { columns[i] = dr.GetName(i); } // Next i } // End if (format.HasFlag(RenderType_t.DataTable)) while (dr.Read()) { if (format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WriteStartObject(); } else { jsonWriter.WriteStartArray(); } for (int i = 0; i <= dr.FieldCount - 1; i++) { object obj = dr.GetValue(i); if (obj == System.DBNull.Value) { obj = null; } if (columns != null && format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WritePropertyName(columns[i]); } jsonWriter.WriteValue(obj); } // Next i if (format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WriteEndObject(); } else { jsonWriter.WriteEndArray(); } } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WriteEndObject(); } // End if (!format.HasFlag(RenderType_t.Data_Only)) } while (dr.NextResult()); jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); jsonWriter.Flush(); output.Flush(); } // jsonWriter } // output } // dr } // End Using cmd if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // con } // End Sub WriteArray
} // End Sub WriteArray public static async System.Threading.Tasks.Task AnyDataReaderToJson( string sql , System.Collections.Generic.Dictionary <string, object> pars , SqlService service , WebSocketTextWriter output , RenderType_t format) { using (System.Data.Common.DbConnection con = service.Connection) { using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = sql; service.AddParameterList(pars, cmd); // cmd.ExecuteNonQueryAsync // cmd.ExecuteReaderAsync // cmd.ExecuteScalarAsync using (System.Data.Common.DbDataReader dr = await cmd.ExecuteReaderAsync( System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection)) { using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(output)) // context.Response.Output) { if (format.HasFlag(RenderType_t.Indented)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; } // context.Response.StatusCode = (int)System.Net.HttpStatusCode.OK; // context.Response.ContentType = "application/json"; await jsonWriter.WriteStartObjectAsync(); await jsonWriter.WritePropertyNameAsync("tables"); await jsonWriter.WriteStartArrayAsync(); do { if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WriteStartObjectAsync(); await jsonWriter.WritePropertyNameAsync("columns"); if (format.HasFlag(RenderType_t.Columns_Associative)) { await WriteAssociativeColumnsArray(jsonWriter, dr, format); } else if (format.HasFlag(RenderType_t.Columns_ObjectArray)) { await WriteComplexArray(jsonWriter, dr, format); } else // (format.HasFlag(RenderType_t.Array)) { await WriteArray(jsonWriter, dr); } } // End if (!format.HasFlag(RenderType_t.Data_Only)) if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WritePropertyNameAsync("rows"); } // End if (!format.HasFlag(RenderType_t.Data_Only)) await output.TransmitAsync(); await jsonWriter.WriteStartArrayAsync(); if (dr.HasRows) { string[] columns = null; if (format.HasFlag(RenderType_t.DataTable)) { columns = new string[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; i++) { columns[i] = dr.GetName(i); } // Next i } // End if (format.HasFlag(RenderType_t.DataTable)) int rowCount = 0; while (await dr.ReadAsync()) { if (format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WriteStartObjectAsync(); } else { await jsonWriter.WriteStartArrayAsync(); } for (int i = 0; i <= dr.FieldCount - 1; i++) { object obj = await dr.GetFieldValueAsync <object>(i); if (obj == System.DBNull.Value) { obj = null; } if (columns != null && format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WritePropertyNameAsync(columns[i]); } await jsonWriter.WriteValueAsync(obj); } // Next i if (format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WriteEndObjectAsync(); } else { await jsonWriter.WriteEndArrayAsync(); } rowCount++; await jsonWriter.FlushAsync(); if (rowCount % 5 == 0) { await output.TransmitAsync(); } else { await output.FlushAsync(); } } // Whend await jsonWriter.FlushAsync(); await output.TransmitAsync(); } // End if (dr.HasRows) await jsonWriter.WriteEndArrayAsync(); if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { await jsonWriter.WriteEndObjectAsync(); } // End if (!format.HasFlag(RenderType_t.Data_Only)) } while (await dr.NextResultAsync()); await jsonWriter.WriteEndArrayAsync(); await jsonWriter.WriteEndObjectAsync(); try { await jsonWriter.FlushAsync(); await output.TransmitAsync(); } catch (System.Exception ex) { System.Console.WriteLine(ex.Message); } } // jsonWriter } // dr } // End Using cmd if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // con } // End Sub WriteArray