private void 创建语句ToolStripMenuItem_Click(object sender, EventArgs e)
{
var node = this.tv_DBServers.SelectedNode;
if (node != null && node.Level == 3)
{
StringBuilder sb = new StringBuilder(string.Format("CREATE TABLE `{0}`(", node.Text));
sb.AppendLine();
foreach (TBColumn col in Biz.Common.Data.MySQLHelper.GetColumns(GetDBSource(node), node.Parent.Text, node.Text))
{
sb.AppendFormat("`{0}` {1} {2} {3},", col.Name, Biz.Common.Data.Common.GetDBType(col), (col.IsID || col.IsKey) ? "NOT NULL" : (col.IsNullAble ? "NOT NULL" : "NULL"), col.IsID ? "AUTO_INCREMENT" : "");
if (col.IsID)
{
sb.AppendLine();
sb.AppendFormat("PRIMARY KEY (`{0}`),", col.Name);
}
sb.AppendLine();
}
sb.AppendLine("`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
sb.AppendLine(")ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;");
sb.AppendLine("//注意：bit类型要手工改成TINYINT(1)。");
TextBoxWin win = new TextBoxWin("创建表" + node.Text, sb.ToString());
win.ShowDialog();
}
else if (node != null && node.Level == 4 && node.Parent.Text.Equals("存储过程"))
{
var body = Biz.Common.Data.MySQLHelper.GetProcedureBody(GetDBSource(node), node.Parent.Parent.Text, node.Text);
TextBoxWin win = new TextBoxWin("存储过程[" + node.Text + "]", "drop PROCEDURE if exists " + node.Text + ";\r\n\r\n" + body.Replace("\n", "\r\n"));
win.ShowDialog();
}
}
private void ExpdataToolStripMenuItem_Click(object sender, EventArgs e)
{
var node = this.tv_DBServers.SelectedNode;
if (node == null || node.Level != 3)
{
return;
}
var cols = Biz.Common.Data.MySQLHelper.GetColumns(GetDBSource(node), node.Parent.Text, node.Text)
.Where(p => !p.IsID).ToList();
string sqltext = string.Format("select {0} from {1}", string.Join(",", cols.Select(p => string.Concat("[", p.Name, "]"))), string.Concat("[", node.Text, "]"));
var datas = Biz.Common.Data.MySQLHelper.ExecuteDBTable(GetDBSource(node), node.Parent.Text, sqltext, null);
StringBuilder sb = new StringBuilder(string.Format("Insert into {0} ({1}) values", string.Concat("`", node.Text, "`"), string.Join(",", cols.Select(p => string.Concat("`", p.Name, "`")))));
foreach (DataRow row in datas.Rows)
{
StringBuilder sb1 = new StringBuilder();
foreach (var column in cols)
{
object data = row[column.Name];
if (data == DBNull.Value)
{
sb1.Append("NULL,");
}
else
{
if (column.TypeName.IndexOf("int", StringComparison.OrdinalIgnoreCase) > -1 ||
column.TypeName.IndexOf("decimal", StringComparison.OrdinalIgnoreCase) > -1 ||
column.TypeName.IndexOf("float", StringComparison.OrdinalIgnoreCase) > -1 ||
column.TypeName.Equals("bit", StringComparison.OrdinalIgnoreCase) ||
column.TypeName.Equals("real", StringComparison.OrdinalIgnoreCase) ||
column.TypeName.IndexOf("money", StringComparison.OrdinalIgnoreCase) > -1 ||
column.TypeName.Equals("timestamp", StringComparison.OrdinalIgnoreCase) ||
column.TypeName.IndexOf("money", StringComparison.OrdinalIgnoreCase) > -1
)
{
sb1.AppendFormat("{0},", data);
}
else if (column.TypeName.Equals("boolean", StringComparison.OrdinalIgnoreCase) ||
column.TypeName.Equals("bool", StringComparison.OrdinalIgnoreCase))
{
sb1.AppendFormat("{0},", data.Equals(true) ? 1 : 0);
}
else if (column.TypeName.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
sb1.AppendFormat("'{0}',", ((DateTime)data).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
sb1.Append(string.Concat("'", data, "',"));
}
}
}
if (sb1.Length > 0)
{
sb1.Remove(sb1.Length - 1, 1);
}
sb.AppendFormat("({0}),", sb1.ToString());
}
if (sb.Length > 0)
{
sb.Remove(sb.Length - 1, 1);
}
TextBoxWin win = new TextBoxWin("导出数据", sb.ToString());
win.ShowDialog();
}