public static string GetQuery(Table table)
{
StringBuilder query = new StringBuilder();
query.Append("INSERT INTO ");
query.Append(table.TableName);
query.Append(" (");
List<string> columns = table.DataRows.First().Columns;
for (int i = 0; i < columns.Count; i++) {
query.Append(columns[i]);
if (i < columns.Count - 1) {
query.Append(", ");
}
}
query.AppendLine(") VALUES ");
for (int i = 0; i < table.DataRows.Count; i++) {
query.Append("(");
for (int k = 0; k < table.DataRows[i].Attributes.Count; k++) {
Flag flag = table.DataRows[i].Attributes[k];
if (flag.DataType.ToLower().Contains("char")
|| flag.DataType.ToLower().Contains("enum")
|| flag.DataType.ToLower().Contains("text")
|| flag.DataType.ToLower().Contains("date")
|| flag.DataType.ToLower().Contains("time")) {
query.Append("\"");
query.Append(flag.Value);
query.Append("\"");
} else {
query.Append(flag.Value);
}
if (k < table.DataRows[i].Attributes.Count - 1) {
query.Append(", ");
}
}
if (i < table.DataRows.Count - 1) {
query.AppendLine("), ");
}
}
query.Append(");");
return query.ToString();
}
public Table GetTable(string tablename)
{
Table table = null;
List<ColumnStructure> information = new List<ColumnStructure>();
using (MySqlTransaction transaction = Connection.Instance.BeginTransaction()) {
//Structure
string query = "DESC " + tablename;
MySqlCommand command = Connection.Instance.GetCommand(query, transaction);
using (MySqlDataReader reader = command.ExecuteReader()) {
while (reader.Read()) {
ColumnStructure cs = new ColumnStructure();
cs.ColumnName = DbUtils.GetString(reader["Field"]);
cs.DataType = DbUtils.GetString(reader["Type"]);
cs.KeyType = DbUtils.GetString(reader["Key"]);
cs.IsNullable = DbUtils.GetString(reader["Null"]);
cs.Extra = DbUtils.GetString(reader["Extra"]);
information.Add(cs);
}
}
//Data
table = new Table(tablename, information);
query = "SELECT * from " + tablename;
MySqlCommand commandData = Connection.Instance.GetCommand(query, transaction);
using(MySqlDataReader reader = commandData.ExecuteReader()) {
while(reader.Read()) {
DataRow row = new DataRow();
row.ColumnStructure = information;
for (int i = 0; i < reader.FieldCount; i++) {
row.Attributes.Add(new Flag(reader.GetName(i), reader[i].ToString(), reader.GetDataTypeName(i)));
}
table.DataRows.Add(row);
}
}
transaction.Commit();
}
return table;
}
private void loadTable(string tablename)
{
if (tablename.Trim().Length > 0) {
this.table = this.dbInformation.GetTable(tablename);
this.fillListview();
this.configureView();
//clear existing data to create new inserts
this.table.DataRows.Clear();
}
}