public static void RestoreRow(XmlReader rdr, TableDefn table, bool idCol, Hashtable hash)
{
string tablename = table.TableName;
bool inCell = false;
string key = null;
string value = null;
while (rdr.Read())
{
if (rdr.NodeType == XmlNodeType.Whitespace)
{
}
else if (rdr.NodeType == XmlNodeType.Element && !inCell)
{
key = rdr.Name; value = null; inCell = true;
}
else if (rdr.NodeType == XmlNodeType.Text && inCell)
{
value = rdr.Value;
}
else if (rdr.NodeType == XmlNodeType.EndElement && inCell && rdr.Name == key)
{
// Save Key and Value
ColumnDefn column = table.FindColumn(key);
if (column != null && column.ColumnType.ToUpper() != "BLOB")
{
string colType = column.CLRType().Name;
string colValue = null;
if ((value == null || value.Length == 0) && column.IsNullable)
{
colValue = "null";
}
else if (colType == "String")
{
colValue = "'" + RemoveNewline(EscapeSlashQuote(value)) + "'";
}
else if (colType == "DateTime")
{
DateTime dt = value.ToDateTime().ToLocalTime();
colValue = "'" + dt.ToString("yyyy-MM-dd'T'HH:mm:ss") + "'";
}
else if (colType == "Boolean")
{
if (value == "true")
{
colValue = "1";
}
else
{
colValue = "0";
}
}
else
{
colValue = value;
}
hash.Add(column.ColumnName, colValue);
}
else
{
throw new Exception("Could not restore column " + key);
}
inCell = false;
}
else if (rdr.NodeType == XmlNodeType.EndElement && !inCell && rdr.Name == tablename)
{
// End node closes row OK
break;
}
else
{
throw new Exception("XML format error in RestoreRow.");
}
}
string[] FVArray = AddSeparator(hash, ',');
string cols = FVArray[0];
string vals = FVArray[1];
string sql = "";
if (idCol)
{
sql += "SET IDENTITY_INSERT " + tablename + " ON; ";
}
sql += "INSERT INTO " + tablename + " (" + cols + ") VALUES (" + vals + "); ";
if (idCol)
{
sql += "SET IDENTITY_INSERT " + tablename + " OFF; ";
}
ExecSQL(sql);
//Clear out Hashtable to handle multiple records
hash.Clear();
}
private void LoadDefinition(string filename)
{
int state = 0;
FileAccess handle = new FileAccess(filename);
if (!handle.IsOpen())
{
return;
}
while (!handle.EndOfStream)
{
string s = handle.ReadLine();
List <string> words = s.Wordise();
if (words.Count > 0) // Skip blank lines
{
if (state == 0) // The Database name must be the first thing we see
{
if (words[0].ToUpper() == "DATABASE")
{
if (words.Count == 2)
{
DatabaseName = words[1];
Tables = new List <TableDefn>();
state = 1;
}
else
{
Log.Me.Error("DATABASE statement in " + filename + " either missing database name or has too many values. Statement is: " + s);
return;
}
}
else
{
Log.Me.Error("Missing DATABASE statement in first line of " + filename);
return;
}
}
else if (state == 1) // Looking for the start of a Table
{
if (words[0].ToUpper() == "TABLE")
{
if (words.Count == 2)
{
table = new TableDefn();
table.TableName = words[1];
table.Columns = new List <ColumnDefn>();
Tables.Add(table);
state = 2;
}
else
{
Log.Me.Error("TABLE statement in " + filename + " either missing table name or has too many values. Statement is: " + s);
return;
}
}
else
{
Log.Me.Error("Missing TABLE statement in " + filename);
return;
}
}
else // The only other state is 2, which is looking for a column definition or END
{
if (words.Count == 1)
{
if (words[0].ToUpper() == "END")
{
// End of one table definition, set state to look for the next.
state = 1;
}
else
{
Log.Me.Error("Table definition entry with just one word, and it isn't END, in " + filename + ". Statement is: " + s);
return;
}
}
else // Must have at least 2 entries which will be parsed as column name and type
{
ColumnDefn c = new ColumnDefn();
c.ColumnName = words[0];
c.ColumnType = words[1];
c.IsIdentity = false;
c.IsNullable = true;
c.IsIndex = false;
c.FKey = null;
bool not = false;
int i = 2;
while (i < words.Count)
{
// Parse optional modifiers
if (words[i].ToUpper() == "IDENTITY")
{
c.IsIdentity = true; c.IsNullable = false;
}
else if (words[i].ToUpper() == "INDEX")
{
c.IsIndex = true;
}
else if (words[i].ToUpper() == "NOT")
{
not = true;
}
else if (words[i].ToUpper() == "NULL")
{
if (not)
{
c.IsNullable = false;
}
else
{
c.IsNullable = true;
}
not = false;
}
else if (words[i].ToUpper() == "FKEY")
{
if (i == words.Count)
{
Log.Me.Error("Missing foreign key after FKEY in " + filename + ". Statement is: " + s);
return;
}
else
{
++i;
c.FKey = words[i];
}
}
else
{
Log.Me.Error("Illegal column modifier " + words[i] + " in " + filename + ". Statement is: " + s);
return;
}
++i;
}
// Check for conflicts
if (c.IsIdentity && c.IsIndex)
{
Log.Me.Error("Identity column cannot also be an Index in " + filename + ". Statement is: " + s);
return;
}
if (c.IsIdentity && c.IsNullable)
{
Log.Me.Error("Identity column cannot be nullable in " + filename + ". Statement is: " + s);
return;
}
if (c.IsIdentity && c.FKey != null)
{
Log.Me.Error("Identity column cannot also be a foreign key in " + filename + ". Statement is: " + s);
return;
}
if (not)
{
Log.Me.Error("NOT on its own without NULL in " + filename + ". Statement is: " + s);
return;
}
table.Columns.Add(c);
}
}
}
}
handle.Close();
}
public static bool CheckTable(string tablename, TableDefn table, bool warn)
{
string query;
if (Connection.Me.Type == "MySQL")
{
query = "SELECT * FROM " + tablename + " LIMIT 1;";
}
else
{
query = "SELECT TOP 1 * FROM " + tablename + ";";
}
List <string> fields = new List <string>();
IDbConnection dbConn = Connection.Me.GetConnection();
try
{
dbConn.Open();
IDbCommand dbCmd = dbConn.CreateCommand();
dbCmd.CommandType = CommandType.Text;
dbCmd.CommandText = query;
using (IDataReader rdr = dbCmd.ExecuteReader())
{
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
//Console.WriteLine(rdr.GetName(i) + "=" + rdr.GetValue(i).ToString());
string key = rdr.GetName(i);
Type type = rdr.GetFieldType(i);
ColumnDefn col = table.FindColumn(key);
if (col == null)
{
warn = true;
Console.WriteLine("Database table " + tablename + " has additional column " + key);
}
else
{
if (type.Name != col.CLRType().Name)
{
// For some reason MySQL bit fields come through as UInt64, so don't report that
// and MSSQL renders FLOAT as Doubles, so we'll ignore that too
if (col.ColumnType == "BIT" && type.Name == "UInt64")
{
}
else if (col.ColumnType == "FLOAT" && type.Name == "Double")
{
}
else
{
warn = true;
Console.WriteLine("Table: " + tablename + ", Column: " + key + ", Database Type: " + type.Name + ", Definition: " + col.ColumnType);
}
}
fields.Add(key);
}
}
}
}
if (fields.Count == 0)
{
Console.WriteLine("Cannot check table " + tablename + " because it is empty.");
}
else
{
foreach (ColumnDefn col in table.Columns)
{
if (!fields.Contains(col.ColumnName))
{
warn = true;
Console.WriteLine("Column " + col.ColumnName + " is missing from " + tablename);
}
}
}
return(warn);
}
catch (Exception ex)
{
throw new Exception("There was a problem loading table from query [" + query + "] - " + ex.Message, ex);
}
finally
{
dbConn.Close();
dbConn = null;
}
}