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();
}
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;
}
}