public SelectQueryForm(Table table)
: this()
{
if (table != null)
{
textBoxTable.Text = table.Name;
foreach (var column in table.Columns)
{
listViewColumns.Items.Add(new ListViewItem(column.Name));
}
}
}
private bool isColumnNullable(Table table, int i)
{
foreach (TableRow row in table.Rows)
{
if (row.Cells[i].Data == "NULL")
return true;
}
return false;
}
public Table ParseTableData(string text)
{
StringReader stringReader = new StringReader(text);
Table table = new Table();
// Read table name (optional).
string line = stringReader.ReadLine();
if (line == null)
{
raiseError("No table information found");
return null;
}
line = line.Trim();
if (line.Contains(' ') || line.Contains('|') || line.Contains('\t') || line.Contains(',') || line == string.Empty)
{
table.Name = "Table1";
}
else
{
table.Name = line;
line = stringReader.ReadLine();
if (line == null)
{
raiseError("No columns found.");
return null;
}
line = line.Trim();
}
// Guess the separator.
bool doStripSingleQuotes = false;
Regex columnDataMatcher;
if (line.Contains('\t'))
{
columnDataMatcher = new Regex(@"[^\t]+");
}
else if (line.Contains('|'))
{
columnDataMatcher = new Regex(@"[^|\s](?:[^|]*[^|\s])?");
}
else if (line.Contains(';'))
{
columnDataMatcher = new Regex(@"[^;]+");
}
else if (line.Contains(','))
{
columnDataMatcher = new Regex(@"[^,]+");
}
else
{
columnDataMatcher = new Regex(@"[^'\s]+|'[^']*'");
doStripSingleQuotes = true;
}
// Read column names.
table.Columns = columnDataMatcher.Matches(line.Trim('|', ' '))
.OfType<Match>()
.Select(match => new TableColumn { Name = match.Value.Trim() })
.ToList();
table.Rows = new List<TableRow>();
line = stringReader.ReadLine();
// Remove optional extra line between column names and data.
if (line != null && Regex.IsMatch(line, @"(\+\-+)+", RegexOptions.IgnoreCase))
{
line = stringReader.ReadLine();
}
// Read table data.
while (true)
{
if (line == null)
break;
line = line.Trim();
if (line == string.Empty)
break;
IEnumerable<string> data =
getPartsUsingRegex(columnDataMatcher, doStripSingleQuotes, line);
if (data.Count() != table.Columns.Count)
{
raiseError(
string.Format("Line contains {0} columns instead of {1}: {2}",
data.Count(),
table.Columns.Count,
line));
return null;
}
TableRow row = new TableRow();
foreach (string cellData in data)
row.Cells.Add(new TableCell { Data = cellData });
table.Rows.Add(row);
line = stringReader.ReadLine();
}
guessColumnTypes(table);
return table;
}
private bool isColumnInteger(Table table, int i)
{
foreach (TableRow row in table.Rows)
{
if (row.Cells[i].Data == "NULL")
continue;
int unused;
if (!int.TryParse(row.Cells[i].Data, out unused))
return false;
}
return true;
}
private bool isColumnDatetime(Table table, int i)
{
foreach (TableRow row in table.Rows)
{
if (row.Cells[i].Data == "NULL")
continue;
DateTime unused;
if (!DateTime.TryParse(row.Cells[i].Data, out unused))
return false;
}
return true;
}
private bool isColumnDate(Table table, int i)
{
foreach (TableRow row in table.Rows)
{
if (row.Cells[i].Data == "NULL")
continue;
DateTime unused;
if (!DateTime.TryParseExact(row.Cells[i].Data,
"yyyy-MM-dd",
null,
DateTimeStyles.None,
out unused))
{
return false;
}
}
return true;
}
private void guessColumnTypes(Table table)
{
for (int i = 0; i < table.Columns.Count; ++i)
{
guessColumnType(table, i);
}
}
private void guessColumnType(Table table, int i)
{
if (isColumnInteger(table, i))
{
table.Columns[i].Type = "INT";
}
else if (isColumnDate(table, i))
{
table.Columns[i].Type = "DATE";
}
else if (isColumnDatetime(table, i))
{
table.Columns[i].Type = "DATETIME";
}
else
{
table.Columns[i].Type = "VARCHAR(100)";
}
bool nullable = isColumnNullable(table, i);
if (!nullable)
table.Columns[i].Type += " NOT";
table.Columns[i].Type += " NULL";
}