private static void CreateHeadings(SqlCeCommand cmd, SqlCeConnection conn, SqlCeDataReader rdr, int maxWidth, List <Column> headings)
{
for (int i = 0; i < rdr.FieldCount; i++)
{
// 18 different types
// Calculate width as max of name or data type based width
switch (rdr.GetDataTypeName(i))
{
case "BigInt":
int width = Math.Max(20, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = true
});
break;
case "Binary":
width = (Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length) * 2) + 2;
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = false
});
break;
case "Bit":
width = Math.Max(5, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = true
});
break;
case "DateTime":
width = Math.Max(20, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = false
});
break;
case "Float":
width = Math.Max(24, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = true
});
break;
case "Image":
width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length) + 2;
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = false
});
break;
case "Int":
width = Math.Max(11, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = true
});
break;
case "Money":
width = Math.Max(21, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = true
});
break;
case "NChar":
width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = false
});
break;
case "NText":
width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = false
});
break;
case "Numeric":
width = Math.Max(21, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = true
});
break;
case "NVarChar":
width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = false
});
break;
case "Real":
width = Math.Max(14, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = true
});
break;
case "RowVersion":
width = Math.Max(8, rdr.GetName(i).Length) + 2;
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = false
});
break;
case "SmallInt":
width = Math.Max(6, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = true
});
break;
case "TinyInt":
width = Math.Max(3, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = true
});
break;
case "UniqueIdentifier":
width = Math.Max(36, rdr.GetName(i).Length);
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = false
});
break;
case "VarBinary":
width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length) + 2;
headings.Add(new Column {
Name = rdr.GetName(i), Width = width, PadLeft = false
});
break;
default:
break;
}
}
}
private int RunDataReader(SqlCeCommand cmd, SqlCeConnection connection, Char colSepChar, bool removeSpaces)
{
cmd.Connection = connection;
SqlCeDataReader rdr = cmd.ExecuteReader();
int rows = 0;
int maxWidth = 256;
string colSep = colSepChar.ToString();
List <Column> headings = new List <Column>();
CreateHeadings(cmd, conn, rdr, maxWidth, headings);
while (rdr.Read())
{
bool doWrite = (rows == 0 && writeHeaders);
if (!doWrite && rows > 0)
{
doWrite = ((rows % headerInterval) == 0);
}
if (doWrite)
{
for (int x = 0; x < rdr.FieldCount; x++)
{
if (removeSpaces)
{
Console.Write(headings[x].Name);
}
else
{
Console.Write(headings[x].Name.PadRight(headings[x].Width));
}
Console.Write(colSep);
}
Console.WriteLine();
for (int x = 0; x < rdr.FieldCount; x++)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (removeSpaces)
{
sb.Append('-', headings[x].Name.Length);
}
else
{
sb.Append('-', headings[x].Width);
}
Console.Write(sb.ToString());
Console.Write(colSep);
}
Console.WriteLine();
}
for (int i = 0; i < rdr.FieldCount; i++)
{
if (!rdr.IsDBNull(i))
{
string value = string.Empty;
string fieldType = rdr.GetDataTypeName(i);
if (fieldType == "Image" || fieldType == "VarBinary" || fieldType == "Binary" || fieldType == "RowVersion")
{
Byte[] buffer = (Byte[])rdr[i];
StringBuilder sb = new StringBuilder();
sb.Append("0x");
for (int y = 0; y < (headings[i].Width - 2) / 2; y++)
{
sb.Append(buffer[y].ToString("X2", CultureInfo.InvariantCulture));
}
value = sb.ToString();
}
else if (fieldType == "DateTime")
{
value = ((DateTime)rdr[i]).ToString("O");
}
else if (fieldType == "Float")
{
value = rdr.GetDouble(i).ToString("R", System.Globalization.CultureInfo.InvariantCulture);
}
else if (fieldType == "Real")
{
value = rdr.GetFloat(i).ToString("R", System.Globalization.CultureInfo.InvariantCulture);
}
else
{
value = Convert.ToString(rdr[i], cultureInfo);
}
if (removeSpaces)
{
Console.Write(value);
}
else if (headings[i].PadLeft)
{
Console.Write(value.PadLeft(headings[i].Width));
}
else
{
Console.Write(value.PadRight(headings[i].Width));
}
}
else
{
if (removeSpaces)
{
Console.Write("NULL");
}
else
{
Console.Write("NULL".PadRight(headings[i].Width));
}
}
if (i < rdr.FieldCount - 1)
{
Console.Write(colSep);
}
}
rows++;
Console.WriteLine();
}
return(rows);
}