public void ShowTableInfo(int selectRow)
{
//耗时操作在后台进程进行
Thread worker = new Thread(delegate()
{
try
{
//dgvTable.DataSource = null;
ShowLoading(true);
ApendLog("正在刷新列表");
string sql = @"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tableinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].tableinfo
CREATE TABLE [dbo].tableinfo(
表名 [varchar](50) NULL,
记录数 [int] NULL,
预留空间 [varchar](50) NULL,
使用空间 [varchar](50) NULL,
索引占用空间 [varchar](50) NULL,
未用空间 [varchar](50) NULL
)";
CSHelper.exec_sql(sql);
sql = "insert into tableinfo(表名, 记录数, 预留空间, 使用空间, 索引占用空间, 未用空间) ";
sql += "exec sp_MSforeachtable \"exec sp_spaceused '?'\"";
CSHelper.exec_sql(sql);
sql = " select top 40 * from tableinfo with(nolock) order by 记录数 desc ";
SqlConnection conn = new SqlConnection(CSHelper.sqlconn);
conn.Open();
SqlDataAdapter huoche = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
huoche.Fill(ds, "tableinfo");
UpdateGV(ds.Tables["tableinfo"], selectRow);
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
ApendLog("显示列表出错啦" + ex.Message + "\n" + ex.StackTrace);
//MessageBox.Show(ex.Message + "\n" + ex.StackTrace, "显示列表出错啦");
}
finally
{
ShowLoading(false);
ApendLog("刷新列表完成");
}
});
worker.IsBackground = true;
worker.Start();
}
//清除数据
private void button13_Click(object sender, EventArgs e)
{
DialogResult Dloresult = MessageBox.Show("是否要清空 " + textBox12.Text + " 表的数据?\n\r" + textBoxConnectionString.Text + "\n\r正确点确定，否则取消", "友情提示", MessageBoxButtons.OKCancel);
if (Dloresult != DialogResult.OK)
{
return;
}
var sql = "TRUNCATE TABLE " + textBox12.Text + " ;";
var result = CSHelper.exec_sql(sql);
if (result == -1)
{
MessageBox.Show("清除成功！");
}
}
private void button3_Click(object sender, EventArgs e)
{
var Database = CSHelper.ReadINI("Connection", "Database");
if (!string.IsNullOrEmpty(Database))
{
Thread worker = new Thread(delegate()
{
Stopwatch sw = new Stopwatch();
try
{
sw.Start();
ShowLoading(true);
ApendLog(string.Format("正在对数据库[{0}]进行收缩", Database));
ApendLog("-----------------------收缩前大小----------------------------------");
countDatabaseSize(Database);
CSHelper.exec_sql(string.Format("DBCC SHRINKDATABASE ({0}) ", Database));
}
catch (Exception ex)
{
ApendLog(ex.Message);
}
finally
{
sw.Stop();
TimeSpan ts = sw.Elapsed;
ApendLog("数据库收缩完成,耗时:" + string.Format("{0}时{1}分{2}秒{3}毫秒", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds));
ApendLog("-----------------------收缩后大小----------------------------------");
countDatabaseSize(Database);
ShowLoading(false);
}
});
worker.IsBackground = true;
worker.Start();
}
}
//保存修改
private void button3_Click(object sender, EventArgs e)
{
int lastRow = 0;
//改表名备注
if (dgvTableAttribute.Rows.Count > 1 && textBox2.Text.Trim() != string.Empty && textBox2.Text.Trim() != dgvTableAttribute.CurrentRow.Cells["表备注"].Value.ToString())
{
if (CSHelper.ifExist("select count(*) from t_table_name where table_name='" + label4.Text + "'") > 0)
{
//修改表名备注
CSHelper.exec_sql("update t_table_name set remark ='" + textBox2.Text.Trim() + "' where table_name='" + label4.Text + "'");
}
else
{
//新增表名备注
CSHelper.exec_sql("insert into t_table_name values('" + label4.Text + "','" + textBox2.Text.Trim() + "')");
}
//MessageBox.Show("表备注更新成功！");
}
//改表字段名重新备注
//检索修改备注列具有有效值的字段名和备注
List <string> updateList = new List <string>();
int row = dgvTableAttribute.Rows.Count;
for (int i = 0; i < row; i++)
{
//MessageBox.Show(dataGridView1.Rows[i].Cells["Column13"].Value.ToString());
if (dgvTableAttribute.Rows[i].Cells["Column13"].Value != null)
{
if (dgvTableAttribute.Rows[i].Cells["Column13"].Value.ToString().Trim() != "")
{
updateList.Add(dgvTableAttribute.Rows[i].Cells["Column1"].Value.ToString() + "," + dgvTableAttribute.Rows[i].Cells["Column3"].Value.ToString() + "," + dgvTableAttribute.Rows[i].Cells["Column13"].Value.ToString());
}
lastRow = i;
}
}
string a = "";
for (int i = 0; i < updateList.Count; i++)
{
a += "\n" + updateList[i].ToString();
}
if (updateList.Count == 0)
{
//do nothing！
}
else
{
//MessageBox.Show(a);
foreach (string item in updateList)
{
string[] arr = item.Split(',');
//判断是否已经存在
if (CSHelper.ifExist("select count(*) from t_table_field where table_name='" + label4.Text + "' and table_field='" + arr[1] + "' ") > 0)
{
//存在则修改
CSHelper.exec_sql("update t_table_field set remark ='" + arr[2] + "' where table_name='" + label4.Text + "' and table_field='" + arr[1] + "' ");
}
else
{
//不存在就新增
CSHelper.exec_sql("insert into t_table_field values('" + arr[0] + "','" + arr[1] + "','" + arr[2] + "')");
}
}
//MessageBox.Show("表字段备注更新成功！");
}
//刷新
showDataGirdView(lastRow);
}
private void button2_Click(object sender, EventArgs e)
{
//如果没有选中表，则不执行
if (string.IsNullOrEmpty(CurrentTableName.Text))
{
return;
}
//是否含有聚集索引
bool hasClusteredIndex = (CSHelper.ifExist("select count(*) from sys.indexes where object_id=OBJECT_ID('" + CurrentTableName.Text + "') and type_desc='CLUSTERED'; ") == 1);
//if (!hasClusteredIndex)
//{
// MessageBox.Show("此表没有聚集索引，请先创建！");
// return;
//}
Thread worker = new Thread(delegate()
{
Stopwatch sw = new Stopwatch();
try
{
sw.Start();
ShowLoading(true);
if (hasClusteredIndex)
{
ApendLog(string.Format("正在对表[{0}]重建索引", CurrentTableName.Text));
CSHelper.exec_sql(string.Format("DBCC DBREINDEX ({0}, '', 90) ", CurrentTableName.Text));
}
else
{
ApendLog(string.Format("准备对表[{0}]建立索引", CurrentTableName.Text));
var field = CSHelper.ExecuteScalar(string.Format("Select top 1 name from syscolumns Where ID=OBJECT_ID('{0}')", CurrentTableName.Text));
ApendLog(string.Format("查出表[{0}]第一个字段为：{1}", CurrentTableName.Text, field));
ApendLog(string.Format("正在为表[{0}]字段[{1}]建立临时ClusteredIndex索引...时间比较久", CurrentTableName.Text, field));
CSHelper.exec_sql(string.Format("create clustered index ClusteredIndex on {0}({1}) ", CurrentTableName.Text, field));
ApendLog(string.Format("删除临时索引中..."));
CSHelper.exec_sql(string.Format("DROP INDEX {0}.ClusteredIndex ", CurrentTableName.Text, field));
}
}
catch (Exception ex)
{
ApendLog(ex.Message);
}
finally
{
sw.Stop();
TimeSpan ts = sw.Elapsed;
ApendLog("重建索引完成,耗时:" + string.Format("{0}时{1}分{2}秒{3}毫秒", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds));
isRuning = false;
}
});
worker.IsBackground = true;
worker.Start();
t = new System.Timers.Timer(3000);
t.Elapsed += t_Elapsed;
t.Enabled = true;
}