Getting Column Width from SqlDataReader.

Question

Hello,

I've come to StackOverflow before with this quest: http://stackoverflow.com/questions/547040/ms-sql2005-query-stored-proc-results-to-text-using-sqlcommand-or-such

I was told to build the string myself so that I get the same results as from SQL Managment Studio's Results To Text (Cntrl + T)

I have now run into a problem, how do I determine the width of the column dynamically? Also what about columns that are VARCHAR(MAX)? I have absolutely no idea what will come into the SqlDataReader before hand.

Here's the code I have so far, I essentially need to eliminated PADDING_LENGTH and replace its value dynamically.

I took the basic code from the net somewhere. (Whoever wrote it first, my thanks to you)

StringBuilder sb = new StringBuilder(); 
 private void GenerateResultsText(SqlDataReader reader)
    {
        const int PADDING_LENGTH = 40;

        do
        {
            // Create new data table
            DataTable schemaTable = reader.GetSchemaTable();

            if (schemaTable != null)
            {
                // A query returning records was executed
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    DataRow dataRow = schemaTable.Rows[i];
                    // Create a column name that is unique in the data table
                    string columnName = (string)dataRow["ColumnName"];
                    //Add to Results String.
                    sb.Append(String.Format("{0, " + -1 * PADDING_LENGTH + "}", columnName));
                }
                sb.Append(Environment.NewLine);

                //Add markers to seperate Row entries from Column names. 
                const string columnRowSeperator = "-----"; //Keep it to a multiple of 5.
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    for (int j = 0; j < PADDING_LENGTH / columnRowSeperator.Length; j++)
                        sb.Append(columnRowSeperator);
                }
                sb.Append(Environment.NewLine);

                // Fill the data table we just created
                while (reader.Read())
                {
                    Object temp;

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        temp = reader.GetValue(i);
                        sb.Append(String.Format("{0, " + -1 * PADDING_LENGTH + "}", temp.ToString()));
                    }
                    sb.Append(Environment.NewLine);
                }

                //Add newlines to seperate tables.
                sb.Append(Environment.NewLine + Environment.NewLine);
            }
        }
        while (reader.NextResult());

        reader.Close();
        reader.Dispose();
    }

Cheers, Kai.

Answer 1

You cannot determine the width of a column to be displayed on screen just from the Sql Data Reader - after all, the width of the column is heavily dependant on what screen font you're using, what size, bold or not and so forth.

What you need to do is render the text into your UI where you want to display it - there, on the UI side of things, you can measure the space needed to display a given text using a given font, size, and font characteristics.

Marc

Answer 2

EDIT

Here's an example using the DataReader. You could be even more specific looking at NumericPrecision columns and such in the schema table, but I think this will get you started.

using (SqlConnection conn = new SqlConnection("Data Source=server;Initial Catalog=db;User Id=user;Password=pass;"))
{
    using (SqlCommand cmd = new SqlCommand("select * from table", conn))
    {
        conn.Open();
        StreamWriter sw = new StreamWriter(File.Open("test.txt", FileMode.Append));        
        DataTable schema = null;

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            schema = rdr.GetSchemaTable();


            for (int i = 0; i < rdr.FieldCount; i++)
            {
                string name = schema.Rows[i]["ColumnName"].ToString();
                sw.Write(name.PadRight(name.Length + Convert.ToInt32(schema.Rows[i]["ColumnSize"])) + " ");
            }

            sw.WriteLine();

            for (int i = 0; i < rdr.FieldCount; i++)
            {
                string name = schema.Rows[i]["ColumnName"].ToString();
                sw.Write(new string('-', name.Length + Convert.ToInt32(schema.Rows[i]["ColumnSize"])) + " ");
            }

            rdr.Close();//can't have two open datareaders on the same connection
            rdr.Dispose();

            sw.WriteLine();

            while (dataReader.Read())
            {
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    string name = schema.Rows[i]["ColumnName"].ToString();
                    sw.Write(dataReader[i].ToString().PadRight(name.Length + Convert.ToInt32(schema.Rows[i]["ColumnSize"])) + " ");
                }
                sw.WriteLine();
            }

        }

        sw.Close();
        sw.Dispose();
    }
}

I couldn't spend enough time to find out how to do this with a DataReader, but if you use a DataAdapter instead, you can do this:

    using (SqlConnection conn = new SqlConnection("Data Source=someserver;Initial Catalog=somedb;User Id=user;Password=password;"))
    {
        using (SqlCommand cmd = new SqlCommand("select * from sometable", conn))
        {
            conn.Open();
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                DataSet set = new DataSet();
                set.Tables.Add("Schema");
                set.Tables.Add("Data");

                adapter.Fill(set, "Data");
                adapter.FillSchema(set, SchemaType.Source, "Schema");

                StreamWriter sw = new StreamWriter(File.Open("test.txt", FileMode.Append));        

                for(int i = 0; i < set.Tables["Schema"].Columns.Count; i++)
                {

                    sw.Write(set.Tables["Schema"].Columns[i].ColumnName.PadRight(set.Tables["Schema"].Columns[i].MaxLength + set.Tables["Schema"].Columns[i].ColumnName.Length, ' ') + " ");
                }
                sw.WriteLine();

                for(int i = 0; i < set.Tables["Schema"].Columns.Count; i++)
                {
                    sw.Write(new string('-', set.Tables["Schema"].Columns[i].MaxLength + set.Tables["Schema"].Columns[i].ColumnName.Length) + " ");
                }
                sw.WriteLine();

                foreach(DataRow row in set.Tables["Data"].Rows)
                {
                    foreach(DataColumn col in set.Tables["Data"].Columns)
                    {
                        sw.Write(row[col].ToString().PadRight(set.Tables["Schema"].Columns[col.ColumnName].MaxLength + col.ColumnName.Length) + " ");
                    }
                    sw.WriteLine();
                }

                sw.Close();
                sw.Dispose();






            }
        }
    }