C# How can I get each column type and length and then use the lenght to padright to get the spaces at the end of each field.

Question

I have a console application that extracts data from a SQL table to a flat file. How can I get each column type and length and then use the lenght of each column to padright(length) to get the spaces at the end of each field. Here is what I have right now that does not include this functionality.

Thanks

{
                var destination = args[0];
                var command = string.Format("Select * from {0}", Validator.Check(args[1]));
                var connectionstring = string.Format("Data Source={0}; Initial Catalog=dbname;Integrated Security=SSPI;", args[2]);

            var helper = new SqlHelper(command, CommandType.Text, connectionstring);

            using (StreamWriter writer = new StreamWriter(destination))
            using (IDataReader reader = helper.ExecuteReader())
            {
                while (reader.Read())
                {
                    Object[] values = new Object[reader.FieldCount];
                    int fieldCount = reader.GetValues(values);

                    for (int i = 0; i < fieldCount; i++)
                        writer.Write(values[i].ToString().PadRight(513));

                    writer.WriteLine();
                }

                writer.Close();
            }

Answer 1

IDataReader offers a GetSchemaTable() method which provides a ColumnSize attribute (this varies by the underlying provider - the SQL Server version is here).

Answer 2

You can use the DataReader's GetSchemaTable method to retrieve column schema information. This article has complete details on getting the metadata about the retrieved data.

Once you have the column size, you can add trailing characters to the retrieved data by using the String.PadRight method.