/// <summary>
/// 使用事务执行多条SQL(插入、更新、删除)
/// </summary>
/// <param name="sqls"></param>
public static void Exec_Tansaction(List <string> sqls)
{
var sqlsb = new StringBuilder();;
if (sqls.Count == 0)
{
return;
}
SqlConnection conn = new SqlConnection(sqlconn);
conn.Open();
// 启动一个事务。
SqlTransaction myTran = conn.BeginTransaction();
// 为事务创建一个命令
SqlCommand myCom = new SqlCommand();
myCom.Connection = conn;
myCom.Transaction = myTran;
try
{
foreach (string sql in sqls)
{
sqlsb.AppendLine(sql);
myCom.CommandText = sql;
myCom.ExecuteNonQuery();
}
myTran.Commit();//提交事务
}
catch (Exception ex)
{
myTran.Rollback();
CSHelper.saveErrLog("提交数据失败" + ex.Message + "\n\r" + sqlsb.ToString(), DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
//返回异常的错误信息
//MessageBox.Show("提交数据失败!\n" + Ex.ToString(), "异常信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
throw new Exception("提交数据失败!\n" + ex.ToString());
}
finally
{
conn.Close();
}
}
/// <summary>
/// 根据下载的TXT文档过滤后创建成语txt
/// </summary>
private void createWordTxt()
{
using (StreamReader sr = new StreamReader(Application.StartupPath + "\\成语大全（31648个成语解释）.Txt", System.Text.Encoding.Default))
{
string line = string.Empty;
while ((line = sr.ReadLine()) != null)
{
//如果读出一行不是空字符串
if (line.Trim() != "")
{
var arr = Regex.Split(line, "拼音");
//0功能>1URL>2请求方法>3请求body
var word = arr[0].Trim();
if (word.Length == 4)
{
CSHelper.saveTextFile(word, "成语列表", "txt", true);
}
}
}
}
}
public void insertToDB(StringBuilder sb_sqls, string connStr = null)
{
SqlConnection conn;
if (string.IsNullOrEmpty(connStr))
{
conn = new SqlConnection(textBoxConnectionString.Text);
}
else
{
conn = new SqlConnection(connStr);
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
try
{
cmd.CommandText = sb_sqls.ToString();
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace);
richTextBox1.AppendText(ex.Message + "\n\r" + ex.StackTrace);
tran.Rollback();
CSHelper.saveErrLog(sb_sqls.ToString(), "批量执行失败的sql");
}
finally
{
conn.Close();
tran.Dispose();
conn.Dispose();
}
}
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 Form1_Load(object sender, EventArgs e)
{
CSHelper.getsqlconn();
}
private void CreateCSV()
{
StringBuilder sb = new StringBuilder();
SqlConnection conn = new SqlConnection(CSHelper.sqlconn);
const int group = 1000;
int total = 0;
bool containsTitle = true;
try
{
conn.Open();
string sql = txtSql.Text.Trim();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (containsTitle)
{
for (int i = 0; i < dr.FieldCount; i++)
{
if (i == dr.FieldCount - 1)
{
sb.AppendFormat("{0}", dr.GetName(i).ToString());
containsTitle = false;
sb.Append("\n");
}
else
{
sb.AppendFormat("{0},", dr.GetName(i).ToString());
}
}
}
for (int i = 0; i < dr.FieldCount; i++)
{
if (i == dr.FieldCount - 1)
{
sb.AppendFormat("{0}", dr.GetValue(i).ToString());
}
else
{
sb.AppendFormat("{0},", dr.GetValue(i).ToString());
}
}
sb.Append("\n");
total++;
UpdateLableText(lblTotal, "生成记录统计:" + total);
if (total % group == 0)
{
if (total == group)
{
CSHelper.saveTextFile(sb.ToString(), "导出", "csv");
}
else
{
CSHelper.saveTextFile(sb.ToString(), "导出", "csv", true);
}
sb.Length = 0;
}
}
CSHelper.saveTextFile(sb.ToString(), "导出", "csv", true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
private async Task <bool> PromptFieldEdit(SPMSchemeField field)
{
int result = 0;
var dialog = new ContentDialog();
dialog.Title = "Edit Field";
dialog.MaxWidth = ActualWidth;
var panel = new StackPanel();
panel.Children.Add(new TextBlock {
Text = "Name:", TextWrapping = Windows.UI.Xaml.TextWrapping.Wrap
});
var nameBox = new TextBox();
nameBox.Text = field.Name;
panel.Children.Add(nameBox);
panel.Children.Add(new TextBlock {
Text = "Description:", TextWrapping = Windows.UI.Xaml.TextWrapping.Wrap
});
var descBox = new TextBox();
descBox.Text = field.Description;
panel.Children.Add(descBox);
panel.Children.Add(new TextBlock {
Text = "Method:", TextWrapping = Windows.UI.Xaml.TextWrapping.Wrap
});
var methCombo = new ComboBox();
foreach (var v in Enum.GetValues(typeof(SPMSchemeSaltingType)))
{
methCombo.Items.Add(v);
}
int methIndex = CSHelper.IndexOfEnum(methCombo.Items, field.SaltingType);
methCombo.SelectedIndex = methIndex >= 0 ? methIndex : 0;
methCombo.HorizontalAlignment = HorizontalAlignment.Stretch;
panel.Children.Add(methCombo);
var delBtn = new Button();
delBtn.Click += (o, e) => { result = 2; dialog.Hide(); };
delBtn.Margin = new Thickness(0, 12, 0, 0);
delBtn.Content = new TextBlock {
Text = "Delete", Foreground = new SolidColorBrush(Windows.UI.Colors.Red), TextWrapping = Windows.UI.Xaml.TextWrapping.Wrap
};
delBtn.HorizontalAlignment = HorizontalAlignment.Stretch;
delBtn.HorizontalContentAlignment = HorizontalAlignment.Center;
panel.Children.Add(delBtn);
dialog.Content = panel;
dialog.PrimaryButtonText = "OK";
dialog.PrimaryButtonClick += (o, e) => { result = 1; };
dialog.SecondaryButtonText = "Cancel";
#if DEBUG
dialog.Tapped += (o, e) =>
{
System.Diagnostics.Debug.WriteLine(String.Format("Height {0}, ActualHeight {1}, panel.Height {2}, panel.ActualHeight {3}", dialog.Height, dialog.ActualHeight, panel.Height, panel.ActualHeight));
};
#endif
await dialog.ShowAsync();
if (result == 2) // delete
{
return(false);
}
else if (result == 1) // save changes
{
field.Name = nameBox.Text;
field.Description = descBox.Text;
field.SaltingType = (SPMSchemeSaltingType)methCombo.SelectedItem;
}
return(true);
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
//记录上次查询的
CSHelper.WriteINI("LastWord", txtTableName.Text);
}
//保存修改
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 button1_Click(object sender, EventArgs e)
{
showDataGirdView(0);
CSHelper.WriteINI("LastWord", txtTableName.Text);
}
private void Form1_Load(object sender, EventArgs e)
{
lastWord = CSHelper.ReadINI("Connection", "LastWord");
txtTableName.Text = lastWord;
}
public static DataTable GetTable(string strSQL)
{
return(CSHelper.GetTable(strSQL, null));
}
public static DataTable GetTable(string strSQL, SqlParameter[] pas)
{
return(CSHelper.GetTable(strSQL, pas, CommandType.Text));
}
public void CalculateCSTest()
{
byte cs = CSHelper.CalculateCS(new byte[] { 0x68, 0xFF, 0xFF, 0xAA, 0xBB, 0xCC, 0xDD, 0xEE, 0xFF, 0xCC });
Assert.AreEqual(cs, 0x2D);
}
//生成文档
public void bulidMarkdown()
{
//显示加载
ShowLoading(true);
try
{
string sql = "";
sql += @"SELECT d.name
表名 ,f.remark 表备注,
a.colorder 字段序号 ,
a.name 字段名 ,
( CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END ) 标识 ,
( CASE WHEN ( SELECT COUNT(*)
FROM sysobjects
WHERE ( NAME IN (
SELECT NAME
FROM sysindexes
WHERE ( id = a.id )
AND ( indid IN (
SELECT indid
FROM sysindexkeys
WHERE ( id = a.id )
AND ( colid IN (
SELECT
colid
FROM
syscolumns
WHERE
( id = a.id )
AND ( NAME = a.name ) ) ) ) ) ) )
AND ( xtype = 'PK' )
) > 0 THEN '√'
ELSE ''
END ) 主键 ,
b.name 类型 ,
a.length 占用字节数 ,
COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度 ,
ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数 ,
( CASE WHEN a.isnullable = 1 THEN '√'
ELSE ''
END ) 允许空 ,
ISNULL(e.text, '') 默认值,
CASE WHEN (SELECT g.remark FROM t_table_field g WHERE g.table_name=d.NAME AND g.table_field=a.name) IS NOT NULL THEN (SELECT g.remark FROM t_table_field g WHERE g.table_name=d.NAME AND g.table_field=a.name) ELSE (SELECT pbc_cmnt FROM dbo.pbcatcol WHERE pbc_tnam=d.name AND pbc_cnam=a.name) END 备注
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN t_table_name f ON d.NAME=f.table_name";
if (rtnCurrentTable.Checked == true)
{
sql += " WHERE d.name = '" + label4.Text.Trim() + "'";
}
else
{
//sql += " WHERE d.name IN(SELECT DISTINCT pbc_tnam FROM pbcatcol )";
sql += " WHERE d.name IN(" + textBox4.Text.Trim() + ")";
}
if (rbtAllTable.Checked)
{
sql += " ORDER BY PATINDEX('% ' + CONVERT(nvarchar(4000), d.name) + ' %', ' ' + CONVERT(nvarchar(4000), Replace('" + textBox4.Text.Trim().Replace("'", "") + "', ',',' , ')) + ' ')";
}
else
{
sql += @"ORDER BY a.id ,a.colorder";
}
SqlConnection conn = new SqlConnection(CSHelper.sqlconn);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
if (!chkXML.Checked && rbtAllTable.Checked)
{
sb.AppendLine("[toc]");
sb.AppendLine("##新产品后台数据库结构");
}
else if (chkXML.Checked)
{
sb.AppendLine(Properties.Resources.headtext);
}
int i = 0;
string lastTableName = "";
// var iflastflied = false;
while (dr.Read())
{
//每个表的头部内容
if (i == 0 || lastTableName != dr["表名"].ToString())
{
if (!chkXML.Checked)
{
sb.AppendLine("\n");
sb.AppendLine("###" + dr["表备注"] + " " + dr["表名"]);
sb.AppendLine("|字段|类型|主键|标识|允许空|默认值|备注|");
sb.AppendLine("| :-------- | --------:| :--: | :--: | :--: | :--: | :--: |");
}
else
{
if (i != 0)
{
sb.AppendLine(" </UML:Classifier.feature>");
sb.AppendLine("</UML:Class>");
}
sb.AppendLine("\n");
sb.AppendLine(" <UML:Class name=\"" + dr["表名"] + " " + dr["表备注"] + "\" isLeaf=\"false\" xmi.id=\"{" + Guid.NewGuid() + "}\" isAbstract=\"false\" visibility=\"public\">");
sb.AppendLine(" <UML:ModelElement.taggedValue>");
sb.AppendLine(" <UML:TaggedValue tag=\"new\" value=\"false\"/>");
sb.AppendLine(" <UML:TaggedValue tag=\"unsafe\" value=\"false\"/>");
sb.AppendLine(" </UML:ModelElement.taggedValue>");
sb.AppendLine(" <UML:Classifier.feature>");
}
}
//每个表的中间内容
if (chkIgnoreNull.Checked)
{
if (dr["备注"] != DBNull.Value)
//继续执行
{
if (dr["备注"].ToString().Trim() != "")
{
sb.AppendLine("|" + dr["字段名"] + "|" + dr["类型"] + "(" + dr["长度"] + ")" + "|" + dr["主键"] + "|" + dr["标识"] + "|" + dr["允许空"] + "|" + FilterText(dr["默认值"]) + "|" + FilterText(dr) + "|");
}
}
}
else if (!chkXML.Checked)
{
sb.AppendLine("|" + dr["字段名"] + "|" + dr["类型"] + "(" + dr["长度"] + ")" + "|" + dr["主键"] + "|" + dr["标识"] + "|" + dr["允许空"] + "|" + FilterText(dr["默认值"]) + "|" + FilterText(dr) + "|");
}
else
{
sb.AppendLine(" <UML:Attribute name=\"" + dr["字段名"] + "\" xmi.id=\"{" + Guid.NewGuid() + "}\" ownerScope=\"instance\" visibility=\"private\" changeability=\"changeable\"> ");
sb.AppendLine(" <UML:Attribute.initialValue> ");
sb.AppendLine(" <UML:Expression body=\"" + FilterText(dr) + "\" xmi.id=\"Expr" + i + "\"/> ");
sb.AppendLine(" </UML:Attribute.initialValue> ");
sb.AppendLine(" <UML:StructuralFeature.type> ");
sb.AppendLine(" <UML:Classifier xmi.idref=\"Dttp0\"/> ");
sb.AppendLine(" </UML:StructuralFeature.type> ");
sb.AppendLine(" </UML:Attribute>");
}
//每个表的尾部内容
//var currentTableName = dr["表名"].ToString();
//if (currentTableName != lastTableName)
//{
// sb.AppendLine(" </UML:Classifier.feature>");
// sb.AppendLine("</UML:Class>");
//}
lastTableName = dr["表名"].ToString();
i++;
}
if (chkXML.Checked)
{
sb.AppendLine(" </UML:Classifier.feature>");
sb.AppendLine("</UML:Class>");
sb.AppendLine(Properties.Resources.bottomtext);
}
var fileName = "导出.md";
if (chkXML.Checked)
{
fileName = "导出.xml";
}
using (System.IO.FileStream fs = new System.IO.FileStream(Application.StartupPath + "\\" + fileName, System.IO.FileMode.Create,
System.IO.FileAccess.ReadWrite))
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, Encoding.UTF8))
{
sw.WriteLine(sb.ToString());
}
}
sb.Length = 0;
}
catch (Exception ex)
{
CSHelper.saveErrLog(ex.Message + "\n" + ex.StackTrace, DateTime.Now.ToString("yyyy-MM-dd") + "-ex");
}
finally
{
//完成后自动打开生成的脚本文件夹
if (Directory.Exists(Application.StartupPath + "\\") != false)
{
string path = Application.StartupPath + "\\"; System.Diagnostics.Process.Start("explorer.exe", path);
}
//隐藏加载中图片
ShowLoading(false);
}
}
private void button1_Click(object sender, EventArgs e)
{
string template = @"
delete from premission where id={0};
INSERT INTO premission(id, auto_created, CODE, NAME, premission_type, parent_id)
SELECT {0}, 1, '{1}', '{2}', 1, {5}
FROM dummy
WHERE NOT EXISTS (SELECT 1 FROM premission WHERE id = {0});
delete from t_menu where id={0};
INSERT INTO t_menu(id, auto_created, CODE, menu_name, icon_class, parent_id, state, url, premission_id, `group`, sort)
SELECT {0}, 1, '{1}', '{2}',
'icon-list-ul', {5}, {6}, '{3}', {0}, '', {4}
FROM dummy
WHERE NOT EXISTS (SELECT 1 FROM t_menu WHERE id = {0});
INSERT INTO t_role_menu(id, VERSION, role_id, menu_id)
SELECT {0} + id, 0, id, {0}
FROM t_role
WHERE tenant_type = 'club' AND NOT EXISTS(SELECT 1 FROM t_role_menu WHERE id = {0} + t_role.id);";
int i = 1;
List <PMenu> root = new List <PMenu>();
PMenu p1 = new PMenu()
{
id = -436477463,
code = "report-sales",
name = "销售收入报表",
parent_id = 0,
url = null,
sort = 90000,
state = 0
};
p1.subMenu = new List <Menu>();
Menu m1 = new Menu()
{
id = -4364774630 - i,
code = "report-sales-ticketSale",
name = "门票销售情况统计表",
parent_id = p1.id,
url = "#!/ticketSale/",
sort = p1.sort + i,
state = 1
};
i++;
Menu m2 = new Menu()
{
id = -4364774630 - i,
code = "report-sales-posbillOneLook",
name = "账单一览表",
parent_id = p1.id,
url = "#!/posbillOneLook/",
sort = p1.sort + i,
state = 1
};
i++;
Menu m3 = new Menu()
{
id = -4364774630 - i,
code = "report-sales-returnPosbill",
name = "账单调整日志表",
parent_id = p1.id,
url = "#!/returnPosbill/",
sort = p1.sort + i,
state = 1
};
i++;
Menu m4 = new Menu()
{
id = -4364774630 - i,
code = "report-sales-operaterIncomeAcount",
name = "各收银员收款汇总报表",
parent_id = p1.id,
url = "#!/operaterIncomeAcount/",
sort = p1.sort + i,
state = 1
};
i++;
Menu m5 = new Menu()
{
id = -4364774630 - i,
code = "report-sales-productSaleDetail",
name = "商品销售明细表",
parent_id = p1.id,
url = "#!/productSaleDetail/",
sort = p1.sort + i,
state = 1
};
i++;
p1.subMenu.Add(m1);
p1.subMenu.Add(m2);
p1.subMenu.Add(m3);
p1.subMenu.Add(m4);
p1.subMenu.Add(m5);
i += 1000;
PMenu p2 = new PMenu()
{
id = -436477463 - i,
code = "report-mem",
name = "会员管理报表",
parent_id = 0,
url = null,
sort = 95000,
state = 0
};
p2.subMenu = new List <Menu>();
Menu n1 = new Menu()
{
id = -4364774630 - i,
code = "report-mem-newMember",
name = "新增会员统计报表",
parent_id = p2.id,
url = "#!/newMember/",
sort = p2.sort + i,
state = 1
};
i++;
Menu n2 = new Menu()
{
id = -4364774630 - i,
code = "report-mem-memCardBalance",
name = "会员余额情况统计表",
parent_id = p2.id,
url = "#!/memCardBalance/",
sort = p2.sort + i,
state = 1
};
i++;
p2.subMenu.Add(n1);
p2.subMenu.Add(n2);
i += 1000;
PMenu p3 = new PMenu()
{
id = -436477463 - i,
code = "report-mem_RC",
name = "会员充值与消费报表",
parent_id = 0,
url = null,
sort = 96000,
state = 0,
};
p3.subMenu = new List <Menu>();
Menu o1 = new Menu()
{
id = -4364774630 - i,
code = "report-mem-memConsumptionDetail",
name = "会员消费情况统计表",
parent_id = p3.id,
url = "#!/memConsumptionDetail/",
sort = p3.sort + i,
state = 1
};
i++;
Menu o2 = new Menu()
{
id = -4364774630 - i,
code = "report-mem-memCardRechargeLog",
name = "会员储值情况统计表",
parent_id = p3.id,
url = "#!/memCardRechargeLog/",
sort = p3.sort + i,
state = 1
};
i++;
p3.subMenu.Add(o1);
p3.subMenu.Add(o2);
i += 1000;
PMenu p4 = new PMenu()
{
id = -436477463 - i,
code = "report-warehouse",
name = "库存管理报表",
parent_id = 0,
url = null,
sort = 97000,
state = 0
};
i += 1000;
PMenu p5 = new PMenu()
{
id = -436477463 - i,
code = "report-reserve",
name = "场地预订报表",
parent_id = 0,
url = null,
sort = 98000,
state = 0
};
p5.subMenu = new List <Menu>();
Menu q1 = new Menu()
{
id = -4364774630 - i,
code = "report-gymReserveReport",
name = "场地预订报表",
parent_id = p5.id,
url = "#!/gymReserveReport/",
sort = p5.sort + i,
state = 1
};
i++;
Menu q2 = new Menu()
{
id = -4364774630 - i,
code = "report-gymReserveCancleReport",
name = "已取消预订报表",
parent_id = p5.id,
url = "#!/gymReserveCancleReport/",
sort = p5.sort + i,
state = 1
};
i++;
p5.subMenu.Add(q1);
p5.subMenu.Add(q2);
Menu m6 = new Menu()
{
id = -4364774630 - i,
code = "report-sales-payTypeReport",
name = "付款方式汇总表",
parent_id = p1.id,
url = "#!/payTypeReport/",
sort = p1.sort + i,
state = 1
};
i++;
p1.subMenu.Add(m6);
Menu n3 = new Menu()
{
id = -4364774630 - i,
code = "report-mem-memkindCountReport",
name = "会籍统计表",
parent_id = p2.id,
url = "#!/memkindCountReport/",
sort = p2.sort + i,
state = 1
};
i++;
Menu n4 = new Menu()
{
id = -4364774630 - i,
code = "report-mem-memBirthdayReport",
name = "会员生日表",
parent_id = p2.id,
url = "#!/memBirthdayReport/",
sort = p2.sort + i,
state = 1
};
i++;
p2.subMenu.Add(n3);
p2.subMenu.Add(n4);
Menu o3 = new Menu()
{
id = -4364774630 - i,
code = "report-mem-memCardRechargeTimesReport",
name = "卡充次情况统计表",
parent_id = p3.id,
url = "#!/memCardRechargeTimesReport/",
sort = p3.sort + i,
state = 1
};
i++;
Menu o4 = new Menu()
{
id = -4364774630 - i,
code = "report-mem-memCardDeductTimesReport",
name = "次卡扣次统计报表",
parent_id = p3.id,
url = "#!/memCardDeductTimesReport/",
sort = p3.sort + i,
state = 1
};
i++;
Menu o5 = new Menu()
{
id = -4364774630 - i,
code = "report-mem-memCardTimesReport",
name = "卡剩余次数情况统计表",
parent_id = p3.id,
url = "#!/memCardTimesReport/",
sort = p3.sort + i,
state = 1
};
i++;
p3.subMenu.Add(o3);
p3.subMenu.Add(o4);
p3.subMenu.Add(o5);
i++;
Menu m7 = new Menu()
{
id = -4364774630 - i,
code = "report-sales-incomeDetails",
name = "收入明细报表(带小计)",
parent_id = p1.id,
url = "#!/incomeDetails/",
sort = p1.sort + i,
state = 1
};
i++;
p1.subMenu.Add(m7);
//销售收入报表
root.Add(p1);
//会员管理报表
root.Add(p2);
//会员充值与消费报表
root.Add(p3);
//库存管理报表
root.Add(p4);
//场地预订报表
root.Add(p5);
foreach (PMenu item in root)
{
var str = string.Format(template, item.id, item.code, item.name, item.url, item.sort, item.parent_id, item.state);
CSHelper.saveTextFile(str, "menu", "sql", true);
if (item.subMenu == null)
{
continue;
}
foreach (Menu subItem in item.subMenu)
{
var str1 = string.Format(template, subItem.id, subItem.code, subItem.name, subItem.url, subItem.sort, subItem.parent_id, subItem.state);
CSHelper.saveTextFile(str1, "menu", "sql", true);
}
}
}
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;
}
