示例#1
0
        } // 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 
示例#2
0
        } // 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