by

Export any DbDataReader to Different Types

With this static class, you can export a DbDataReader (SQL Server, Access, MySQL, Oracle and Excel compatilbe)  to XML, CSV, Text, HTML Table and JSON Formats. Also includes the IsNull validator to prepare your strings to queries and the GetColumns function to get The DbDataReader columns into a List of String
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
src="https://www.snip2code.com/Embed/1005051/Export-any-DbDataReader-to-Different-Typ?startLine=0"></iframe>
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
public static class ExtendDbDataReader { public static List<string> GetColumns(this DbDataReader Reader) { List<string> columns = new List<string>(); for (int i = 0; i <= Reader.FieldCount - 1; i++) { columns.Add(Reader.GetName(i)); } return columns; } public static string IsNull(this object Text, string ValorSeNulo = "NULL", bool Aspas = true) { if (Aspas) { return string.IsNullOrWhiteSpace("" + Text) ? ValorSeNulo : "'" + ("" + Text).Replace("'", "''") + "'"; } else { return string.IsNullOrWhiteSpace("" + Text) ? ValorSeNulo : "" + Text; } } public static string ToHTMLTable(this DbDataReader Reader, string Classes = "") { string Returned = ""; do { if (Reader.HasRows) { Returned = "<table class=\"" + Classes + "\">"; Returned += " <thead>"; Returned += " <tr>"; foreach (var item in Reader.GetColumns()) { Returned += " <th>" + item + "</th>"; } Returned += " </tr>"; Returned += " </thead>"; Returned += " <tbody>"; while (Reader.Read()) { Returned += " <tr>"; foreach (var item in Reader.GetColumns()) { Returned += " <td>" + Reader[item] + "</td>"; } Returned += " </tr>"; } Returned += " </tbody>"; Returned += " </table>"; } } while (Reader.NextResult()); return Returned; } public static string ToJSON(this DbDataReader Reader) { int contador = 0; string Returned = "{"; do { contador = contador++; Returned += ("\"Table" + contador + "\":["); while (Reader.Read()) { Returned += ("{"); foreach (string coluna in Reader.GetColumns()) { Returned += ("\"" + coluna.RemoverAcentos2() + "\":" + "\"" + HttpUtility.HtmlEncode(Reader[coluna].ToString()) + "\","); } Returned = Returned.Substring(0, Returned.Length - 1); Returned += ("},"); } Returned = Returned.Substring(0, Returned.Length - 1); Returned += ("],"); } while (Reader.NextResult()); Returned = Returned.Substring(0, Returned.Length - 1); return Returned + "}"; } public static string ToDelimitedString(this DbDataReader rsEditar, string DelimitadorColuna = "|", string DelimitadorLinha = "§", string DelimitadorTabela = "♠") { string Delimitado = ""; if (rsEditar == null) { return new Exception("Erro na consulta!").Message; } do { List<string> colunas = Enumerable.Range(0, rsEditar.FieldCount).Select(rsEditar.GetName).ToList(); while (rsEditar.Read()) { foreach (var coluna in colunas) { Delimitado += (rsEditar[coluna] + DelimitadorColuna); } Delimitado += DelimitadorLinha; } Delimitado += DelimitadorTabela; } while (rsEditar.NextResult()); return Delimitado; } public static string ToXML(this DbDataReader Reader) { string Returned = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?><response>"; int count = -1; do { if (Reader.HasRows) { count = count + 1; Returned += "<table>"; int count2 = 0; while (Reader.Read()) { Returned += "<row>"; foreach (string item in Reader.GetColumns()) { Returned += ("<" + item.Replace(" ", "_").RemoverAcentos2() + ">" + HttpUtility.HtmlEncode(Reader[item].ToString()) + "</" + item.Replace(" ", "_").RemoverAcentos2() + ">"); } Returned += "</row>"; count2 = count2 + 1; } Returned += "</table>"; } } while (Reader.NextResult()); return Returned + "</response>"; } public static string ToCSV(this DbDataReader Reader, string separator = ",") { string returned = "sep=" + separator + Environment.NewLine; if (Reader.HasRows) { while (Reader.Read()) { foreach (string item in Reader.GetColumns()) { returned += "\"" + HttpUtility.HtmlEncode(Reader[item].ToString()) + "\"" + separator; } returned += Environment.NewLine; } } return returned; } }
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus