public static void BackupTable(string tablename, BackupFile file, TableDefn table)
{
file.AppendLine(" <" + Pluralizer.Pluralize(tablename) + ">");
string query = "SELECT " + table.SelectCols() + " FROM " + tablename + ";";
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())
{
file.AppendLine(" <" + tablename + ">");
for (int i = 0; i < rdr.FieldCount; i++)
{
//Console.WriteLine(rdr.GetName(i) + "=" + rdr.GetValue(i).ToString());
string key = rdr.GetName(i);
object fldvalue = rdr.GetValue(i);
if (fldvalue != DBNull.Value)
{
fldvalue = Convert.ChangeType(fldvalue, rdr.GetFieldType(i));
string clrtype = table.FindColumn(key).CLRType().Name;
if (fldvalue != null)
{
file.AppendLine(" <" + key + ">" + FormatValue(fldvalue, clrtype) + "</" + key + ">");
}
}
}
file.AppendLine(" </" + tablename + ">");
}
}
}
catch (Exception ex)
{
throw new Exception("There was a problem loading table from query [" + query + "] - " + ex.Message, ex);
}
finally
{
dbConn.Close();
dbConn = null;
}
file.AppendLine(" </" + Pluralizer.Pluralize(tablename) + ">");
}
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;
}
}
public static void ReportTable(string tablename, TableDefn table, int width)
{
string query = "SELECT " + table.SelectCols() + " FROM " + tablename + ";";
int capacity = 4096;
StringBuilder sb = new StringBuilder(capacity);
int rows = 0;
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())
{
++rows;
for (int i = 0; i < rdr.FieldCount; i++)
{
object fldvalue = rdr.GetValue(i);
if (fldvalue != DBNull.Value)
{
string value = Convert.ChangeType(fldvalue, rdr.GetFieldType(i)).ToString().TrimEnd();
string type = table.FindColumn(rdr.GetName(i)).ColumnType;
if (type == "BIT")
{
if (value == "0" || value.ToLower() == "false")
{
value = "false";
}
else if (value == "1" || value.ToLower() == "true")
{
value = "true";
}
else
{
Console.WriteLine(value);
}
}
else if (type == "DATETIME")
{
value = value.Left(10);
}
if (sb.Length + value.Length > capacity)
{
capacity = capacity * 2;
sb.EnsureCapacity(capacity);
}
sb.Append(EscapeXML(value));
}
}
}
}
if (rows > 0)
{
string hash = MakeHash(sb.ToString());
Console.WriteLine(tablename.PadRight(width) + " has " + rows.ToString().PadRight(5) + " rows and hash=" + hash);
}
else
{
Console.WriteLine(tablename.PadRight(width) + " is empty.");
}
}
catch (Exception ex)
{
throw new Exception("There was a problem loading table from query [" + query + "] - " + ex.Message, ex);
}
finally
{
dbConn.Close();
dbConn = null;
}
}