Keith Devens .com

Dump a db table in ASP.NET

One of the first things I do whenever I'm working with a new database technology (JDBC, ADO, ADO.NET, etc.) is write a table dumper to learn the API. It's also usually because I usually find myself without access to any handy db tools, so I have to write my own. Here's a dumper for ASP.NET/ADO.NET:

void DumpTable(System.Data.IDataReader reader){

    System.Data.DataTable fields = reader.GetSchemaTable();

    Response.Write("<table border='1'>");

    Response.Write("<tr>");

    foreach(System.Data.DataRow field in fields.Rows){

        Response.Write("<th>");

        Response.Write(HttpUtility.HtmlEncode(field["ColumnName"].ToString()));

        Response.Write("</th>");

    }

    Response.Write("</tr>");

    int numfields = reader.FieldCount;

    while(reader.Read()){

        Response.Write("<tr>");

        for(int i=0; i<numfields; i++){

            Response.Write("<td>");

            string s = reader[i].ToString();

            Response.Write(s.Length == 0 ? "&nbsp;" : HttpUtility.HtmlEncode(s));

            Response.Write("</td>");

        }

        Response.Write("</tr>");

    }

    Response.Write("</table>");

}

There may be a better way to do some of that (if there is, let me know). Just for reference, to connect and get a table dump (happens to be Oracle, but there's an equivalent for every db API in ADO.NET):

OracleConnection conn = new OracleConnection(

   "Data Source=dbtable;User Id=user;Password=pass"

); // wrapped to not break my layout

conn.Open();

OracleCommand cmd = new OracleCommand("SELECT * FROM table WHERE ROWNUM<100", conn);

OracleDataReader reader = cmd.ExecuteReader();

And of course, after dumping:

conn.Close();

That handy WHERE ROWNUM < n bit from here.

Update: For informational purposes, here's a similar utility function for PHP I have sitting around in my db library (modified slightly):


<?php
function printTable(&$table){
    if(!$table){
        echo 'Empty table';
        return;
    }
    echo '<table border="1">',"\n<tr>\n\t";
    foreach(array_keys($table[0]) as $key)
        echo '<th>',htmlspecialchars($key),'</th>';

    foreach($table as $row){
        echo "\n</tr>\n<tr>\n\t";
        foreach($row as $data)
            echo '<td>',htmlspecialchars($data),'</td>';
    }
    echo "\n</tr>\n</table>\n\n";
}
?>

Permalink | 4 Comments | Tags: ASP.NET, Programming | id 7907

19:24 (utc-6)
edited: 20:17 (utc-6)

Comments

1. This code compiled...ship it!
2. I just finished my third martini.
3. I still think this will probably work (please let me know if it does not).

    // call this like: KeithTakesADump(sqlDataReader, Response.Output);

    public static void KeithTakesADump(IDataReader reader, TextWriter output)

    {

        HtmlTextWriter writer = new HtmlTextWriter(output);

        writer.WriteFullBeginTag("table");

        writer.WriteFullBeginTag("tr");

        for (int i = 0; i < reader.FieldCount; ++i)

        {

            writer.WriteFullBeginTag("td");

            writer.Write(reader.GetName(i));

            writer.WriteEndTag("td");

        }

        writer.WriteEndTag("tr");

        while (reader.Read())

        {

            writer.WriteFullBeginTag("tr");

            for (int i = 0; i < reader.FieldCount; ++i)

            {

                writer.WriteFullBeginTag("td");

                writer.Write(reader[i].ToString());

                writer.WriteEndTag("td");

            }

            writer.WriteEndTag("tr");

        }

    }

I agree with Keith: it is icky. For a start, There's the WriteFullBeginTag() and WriteEndTag() methods: ew! This is duplication of effort. You shouldn't have do this! You should be able to create an element, giving its name and attributes, and insert child elements and text elements into it. And when you request a child element to be added, the method should return an object representing it, which you can then manipulate further. When you're done, simply use ToString() and write the result wherever you want. In fact, if I have some free time tomorrow, I'll prototype it and throw it up online.

That, and HtmlTextWriter is overkill. You're not going looking at the source it generates (so nicely formatted output isn't a necessity), and all you really want is a dump of a table. It works just fine.

Friday, February 10, 2006

Dump a db table in ASP.NET

Comments

Recent comments