用c#实现MySQL分区的界面化操作
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace mysqlpartition2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection(Common.myConnectionString);
conn.Open();
string sqlStr = "SHOW SCHEMAS;";
MySqlCommand comm = new MySqlCommand(sqlStr, conn);
MySqlDataReader dr = comm.ExecuteReader();
this.treeView1.BeginUpdate();
while (dr.Read())
{
this.treeView1.Nodes.Add(dr.GetString(0));
}
dr.Close();
for (int i = 0; i < this.treeView1.Nodes.Count; i++)
{
string sqlStr2 = "show tables from " + this.treeView1.Nodes[i].Text;
MySqlCommand comm2 = new MySqlCommand(sqlStr2, conn);
MySqlDataReader dr2 = comm2.ExecuteReader();
while (dr2.Read())
{
TreeNode tn = new TreeNode();
this.treeView1.Nodes[i].Nodes.Add(dr2.GetString(0));
}
dr2.Close();
}
this.treeView1.EndUpdate();
this.dataGridView1.AutoGenerateColumns = true;
this.dataGridView1.RowCount = 0;
this.dataGridView1.ColumnCount = 4;
Common.headerPart(this.dataGridView1);
this.dataGridView2.RowCount = 1;
this.dataGridView2.ColumnCount = 4;
Common.headerPart(this.dataGridView2);
}
private void button1_Click(object sender, EventArgs e)
{
DataGridViewSelectedRowCollection rc = dataGridView1.SelectedRows;
int count = rc.Count;
DataGridViewRow[] dr = new DataGridViewRow[count];
rc.CopyTo(dr, 0);
if (dr[0].Cells.Count < 4)
{
MessageBox.Show("请选中一行！");
return;
}
string sql = "alter table " + this.label1.Text + " drop partition " +dr[0].Cells[2].Value;
//Console.WriteLine("=sql====" + sql);
cmdupdate(sql);
}
private void cmdupdate(string sql)
{
MySqlConnection conn = new MySqlConnection(Common.myConnectionString);
conn.Open();
MySqlCommand cmd2 = new MySqlCommand(sql, conn);
try
{
cmd2.ExecuteNonQuery();
}
catch (Exception e3)
{
MessageBox.Show("输入错误！");
MessageBox.Show(sql);
}
finally
{
cmd2.Dispose();
dataGridView1.Refresh();
}
}
private void button2_Click(object sender, EventArgs e)
{
DataGridViewRow dgvr = dataGridView1.Rows[0];
string partType = dgvr.Cells[1].Value.ToString();
DataGridViewRowCollection dr2 = this.dataGridView2.Rows;
int count2 = dr2.Count-1;
string sql2 = "(";
if (partType == "RANGE")
{
for (int i = 0; i < count2; i++)
{
if (i != 0) sql2 += ",";
sql2 += "PARTITION " + dr2[i].Cells[2].Value + " VALUES LESS THAN " + dr2[i].Cells[3].Value;
}
}
else if (partType == "LIST")
{
for (int i = 0; i < count2; i++)
{
if (i != 0) sql2 += ",";
sql2 += "PARTITION " + dr2[i].Cells[2].Value + " VALUES IN " + dr2[i].Cells[3].Value;
}
}
sql2 += ")";
DataGridViewSelectedRowCollection rc = dataGridView1.SelectedRows;
int count = rc.Count;
DataGridViewRow[] dr = new DataGridViewRow[count];
rc.CopyTo(dr, 0);
string partNames = "";
if (dr[0].Cells.Count < 4)
{
MessageBox.Show("请选中一行！");
return;
}
for (int i = 0; i < count; i++)
{
if (i != 0) partNames += ",";
partNames += dr[i].Cells[2].Value;
}
string sql = "ALTER TABLE " + this.label1.Text + " REORGANIZE PARTITION "+partNames+" INTO "+sql2;
//Console.WriteLine("=sql====" + sql);
cmdupdate(sql);
}
private void button4_Click(object sender, EventArgs e)
{
if (this.button4.Text == "新建")
{
dataGridView1.AllowUserToAddRows = true;
dataGridView1.ReadOnly = false;
this.button4.Text = "保存";
return;
}
dataGridView1.AllowUserToAddRows = false;
dataGridView1.ReadOnly = true;
int count = dataGridView1.RowCount;
if (count > 1)
{
DataGridViewRow dr = dataGridView1.Rows[count - 1];
string sql;
if ((string)dataGridView1.Rows[0].Cells[1].Value == "RANGE")
sql = "alter table " + this.label1.Text + " add partition (PARTITION " + dr.Cells[2].Value + " VALUES LESS THAN " + dr.Cells[3].Value + ")";
else
sql = "alter table " + this.label1.Text + " add partition (PARTITION " + dr.Cells[2].Value + " VALUES IN " + dr.Cells[3].Value + ")";
//Console.WriteLine("=sql====" + sql);
this.button4.Text = "新建";
try
{
cmdupdate(sql);
}
catch (Exception e2)
{
MessageBox.Show("分区名或分区值输入错误！");
}
}
}
private void button7_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void treeView1_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
TreeNode tn = e.Node;
if (tn.GetNodeCount(false) == 0)
{
string table = tn.Text;
string schemata = tn.Parent.Text;
MySqlConnection conn = new MySqlConnection(Common.myConnectionString);
conn.Open();
string sqlStr = "SHOW CREATE TABLE "+schemata+"."+table;
MySqlDataAdapter Mda = new MySqlDataAdapter(sqlStr, conn);
DataSet Ds = new DataSet();
Mda.Fill(Ds, "test1");
string str = Ds.Tables["test1"].Rows[0][1].ToString();
Console.WriteLine(str);
string flag = "/*!50100 PARTITION BY ";
if (str.IndexOf(flag) == -1)
{
MessageBox.Show(table+"表没有分区！");
return;
}
int start = str.IndexOf(flag) + flag.Length;
string str2 = str.Substring(start);
int end = str2.IndexOf("(");
string partType = str2.Substring(0, end - 1);
int end2 = str2.IndexOf("\n(PARTITION");
if (end2 == -1)
{
MessageBox.Show(table + "表不是RANGE或LIST分区！");
return;
}
String partRow = str2.Substring(end+1,end2-8);
str2 = str2.Substring(end2 + 12);
//range :test9
//名：strs[0],strs[9x]
//范围：strs[4],strs[4+9x]
//list :test3
//名：strs[2].strs[2+8x]
//范围：strs[5],strs[5+8x]
if (partType == "RANGE")
{
this.label1.Text = schemata + "." + table;
string[] strs = str2.Split(' ');
int row = strs.Length / 9;
this.dataGridView1.AutoGenerateColumns = true;
this.dataGridView1.RowCount = row;
this.dataGridView1.ColumnCount = 4;
Common.headerPart(this.dataGridView1);
for (int i = 0; i < row; i++)
{
dataGridView1.Rows[i].Cells[0].Value = partRow;
dataGridView1.Rows[i].Cells[1].Value = partType;
dataGridView1.Rows[i].Cells[2].Value = strs[9 * i];
dataGridView1.Rows[i].Cells[3].Value = strs[4 + 9 * i];
}
}
else if (partType == "LIST")
{
this.label1.Text = schemata + "." + table;
string[] strs = str2.Split(' ');
int row = strs.Length / 8 ;
this.dataGridView1.AutoGenerateColumns = true;
this.dataGridView1.RowCount = row;
this.dataGridView1.ColumnCount = 4;
Common.headerPart(this.dataGridView1);
for (int i = 0; i < row; i++)
{
dataGridView1.Rows[i].Cells[0].Value = partRow;
dataGridView1.Rows[i].Cells[1].Value = partType;
dataGridView1.Rows[i].Cells[2].Value = strs[8 * i];
dataGridView1.Rows[i].Cells[3].Value = strs[3 + 8 * i];
}
}
else
{
MessageBox.Show(table+"表不是RANGE或LIST分区！");
}
}
else
{
tn.Expand();
}
}
}
}
public static void headerPart(DataGridView dataGridView1)
{
dataGridView1.Columns[0].HeaderText = "分区列";
dataGridView1.Columns[1].HeaderText = "分区类型";
dataGridView1.Columns[2].HeaderText = "分区名称";
dataGridView1.Columns[3].HeaderText = "分区值";
}