private void BtnRun_Click(object sender, EventArgs e)
{
if (lbxTableList.SelectedItem == null)
{
System.Windows.Forms.MessageBox.Show("请选择一个表", "验证");
}
else
{
StringBuilder sb = new StringBuilder();
int tableCount = Convert.ToInt32(txtTableCount.Text);
string tableName = lbxTableList.SelectedItem.ToString();
SqlDB db = new SqlDB(txtConnString.Text);
DataTable dbNameDT = db.ExecuteDataTableByText("Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)");
string dbName = dbNameDT.Rows[0][0].ToString(); //数据库名称
DataTable dt = db.ExecuteDataTableByText("exec sp_ScriptTable '" + tableName + "'");
if (dt.Rows.Count > 0)
{
for (int i = 0; i < tableCount; i++)
{
string fileGroup = dbName + "_" + tableName + "_" + i.ToString();
if (cbIsCreateFileGroup.Checked)
{
sb.AppendLine("ALTER DATABASE [" + dbName + "] ADD FILEGROUP [" + fileGroup + "]");
sb.AppendLine("ALTER DATABASE [" + dbName + "] ADD FILE(name='" + fileGroup + "',filename='D:\\ServerData\\" + fileGroup + ".ndf',size=8MB,FILEGROWTH=64)TO FILEGROUP [" + fileGroup + "]");
}
foreach (DataRow dr in dt.Rows)
{
if (IsIndexRow(dr["FieldValue"].ToString()))
{
sb.AppendLine(dr["FieldValue"].ToString().Replace(tableName, tableName + "_" + i.ToString()));
if (cbIsCreateFileGroup.Checked)
{
sb.AppendLine("WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [" + fileGroup + "]");
}
}
else
{
sb.AppendLine(dr["FieldValue"].ToString().Replace(tableName, tableName + "_" + i.ToString()));
}
}
db.ExecuteNonQueryByText(sb.ToString());
sb.Clear();
}
}
LoadTable();
System.Windows.Forms.MessageBox.Show("分表成功", "消息");
}
}
private void Button1_Click(object sender, EventArgs e)
{
if (txtTableName.Text.Trim() == "")
{
System.Windows.Forms.MessageBox.Show("文件组名称是根据表名创建的，请输入表名", "验证");
}
else
{
StringBuilder sb = new StringBuilder();
int tableCount = Convert.ToInt32(txtTableCount.Text);
string tableName = txtTableName.Text;
SqlDB db = new SqlDB(txtConnString.Text);
DataTable dbNameDT = db.ExecuteDataTableByText("Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)");
string dbName = dbNameDT.Rows[0][0].ToString(); //数据库名称
for (int i = 0; i < tableCount; i++)
{
string fileGroup = dbName + "_" + tableName + "_" + i.ToString();
sb.AppendLine("ALTER DATABASE [" + dbName + "] ADD FILEGROUP [" + fileGroup + "]");
sb.AppendLine("ALTER DATABASE [" + dbName + "] ADD FILE(name='" + fileGroup + "',filename='D:\\ServerData\\" + fileGroup + ".ndf',size=8MB,FILEGROWTH=64)TO FILEGROUP [" + fileGroup + "]");
db.ExecuteNonQueryByText(sb.ToString());
sb.Clear();
}
System.Windows.Forms.MessageBox.Show("创建文件组成功", "消息");
}
}
/// <summary>
/// 加载数据表
/// </summary>
private void LoadTable()
{
SqlDB db = new SqlDB(txtConnString.Text);
DataTable dt = new DataTable();
try
{
lbxTableList.Items.Clear();
dt = db.ExecuteDataTableByText("sp_help");
}
catch (Exception exc)
{
System.Windows.Forms.MessageBox.Show("连接数据库失败", "验证");
}
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (dr["Object_type"].ToString() == "user table")
{
lbxTableList.Items.Add(dr["Name"].ToString());
}
}
}
}
private void BtnDeleteTable_Click(object sender, EventArgs e)
{
if (lbxTableList.SelectedItem == null)
{
System.Windows.Forms.MessageBox.Show("请选择一个表", "验证");
}
else
{
DialogResult result = System.Windows.Forms.MessageBox.Show("确定要删除" + lbxTableList.SelectedItem.ToString() + "表的所有分表吗？", "删除", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
StringBuilder sb = new StringBuilder();
int tableCount = Convert.ToInt32(txtTableCount.Text);
string tableName = lbxTableList.SelectedItem.ToString();
SqlDB db = new SqlDB(txtConnString.Text);
DataTable dbNameDT = db.ExecuteDataTableByText("Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)");
string dbName = dbNameDT.Rows[0][0].ToString(); //数据库名称
for (int i = 0; i < tableCount; i++)
{
string fileGroup = dbName + "_" + tableName + "_" + i.ToString();
sb.AppendLine("if exists (select top 1 * from sysobjects where id = object_id('" + tableName + "_" + i.ToString() + "') and type = 'U') begin DROP TABLE " + tableName + "_" + i.ToString() + " end");
if (cbIsCreateFileGroup.Checked)
{
sb.AppendLine("ALTER DATABASE [" + dbName + "] REMOVE FILE " + fileGroup);
sb.AppendLine("ALTER DATABASE [" + dbName + "] REMOVE FILEGROUP " + fileGroup);
}
db.ExecuteNonQueryByText(sb.ToString());
sb.Clear();
}
LoadTable();
System.Windows.Forms.MessageBox.Show("删除分表成功", "消息");
}
else
{
return;
}
}
}