private void 鎖定資料庫ToolStripMenuItem_Click(object sender, EventArgs e) { VoucherExpense.DamaiDataSet DataSet = new DamaiDataSet(); var headerAdapter = new VoucherExpense.DamaiDataSetTableAdapters.VEHeaderTableAdapter(); headerAdapter.Fill(DataSet.VEHeader); if (DataSet.VEHeader.Count == 0) { var row = DataSet.VEHeader.NewVEHeaderRow(); row.Closed = !MyFunction.LockAll; int y = DateTime.Now.Year; row.DataYear = new DateTime(y, 1, 1); DataSet.VEHeader.AddVEHeaderRow(row); } var header = DataSet.VEHeader[0]; header.BeginEdit(); header.Closed = !MyFunction.LockAll; header.EndEdit(); try { headerAdapter.Update(header); } catch (Exception ex) { MessageBox.Show("變更鎖定狀態未成功:" + ex.Message); return; } MyFunction.LockAll = !MyFunction.LockAll; SetFormTitle(); ToolStripMenuItem i = (ToolStripMenuItem)menu1.Items["查核MenuItem"]; if (MyFunction.LockAll) { i.DropDownItems["鎖定資料庫MenuItem"].Text = "解鎖資料庫"; } else { i.DropDownItems["鎖定資料庫MenuItem"].Text = "鎖定資料庫"; } }
private void btnAction_Click(object sender, EventArgs e) { try { if (Directory.Exists(m_Dir)) { if (MessageBox.Show("目錄" + m_Dir + " 己經存在! 仍要繼續?", "", MessageBoxButtons.YesNo) != DialogResult.Yes) { return; } } else { Message("建立目錄"); Directory.CreateDirectory(m_Dir); } Message("建立新年度的" + HardwareConfig.CfgFileName); string backupStr = m_HCfg.Database; m_HCfg.Database = NewSqlDB; m_HCfg.SaveTo(m_Dir); m_HCfg.Database = backupStr; Message("創建 [" + NewSqlDB + "]"); // 檢查 NewSqlDB是否己經存在 var sqlCommand = new SqlCommand("select count(*) from sysdatabases where name='" + NewSqlDB + "'"); string connStr = DB.SqlConnectString(m_HCfg.Local, NewSqlDB); //MapPath.SqlConnectString(m_HCfg.SqlServerIP, "master", m_HCfg.SqlUserID, m_HCfg.SqlPassword); SqlConnection sqlMasterConn = new SqlConnection(connStr); sqlMasterConn.Open(); sqlCommand.Connection = sqlMasterConn; int num = (int)sqlCommand.ExecuteScalar(); bool createNew = true; if (num > 0) { if (MessageBox.Show("己經存在[" + NewSqlDB + "], 將不新建及複制,只檢查PK及FK.仍要繼續嗎?", "", MessageBoxButtons.YesNo) != DialogResult.Yes) { return; } createNew = false; } if (createNew) { // 建立 DataBase sqlCommand = sqlMasterConn.CreateCommand(); sqlCommand.CommandText = "CREATE DATABASE [" + NewSqlDB + "]"; num = sqlCommand.ExecuteNonQuery(); // 建立所有的空Table List <string> list = GetTableName(m_DataSet); // 懶得寫SqlCommand,直接重DataSet定義抓 string destDB = "[" + NewSqlDB + "].[dbo].["; string sourDB = "[" + m_HCfg.Database + "].[dbo].["; foreach (string name in list) { string cmd = "select * into " + destDB + name + "] from " + sourDB + name + "]"; var results = from string s in chListBoxVE.CheckedItems where s == name select s; if (results.Count() <= 0) { sqlCommand.CommandText = cmd + " where 1=0"; // 只建立結構,不帶主Key及FK sqlCommand.ExecuteNonQuery(); Message("建立空 [" + name + "]"); } else { sqlCommand.CommandText = cmd + " where 1=1"; // 建立結構,也Copy資料 num = sqlCommand.ExecuteNonQuery(); MessageBox.Show("複制 [" + name + "] 共" + num.ToString() + "筆!"); } } } sqlMasterConn.Dispose(); // 建立PrimaryKey Message("建立各表PrimaryKey!"); SqlConnection newConn = new SqlConnection(DB.SqlConnectString(m_HCfg.Local, NewSqlDB)); newConn.Open(); sqlCommand.Connection = newConn; string cmdPrifix, cmdMiddle; string cmdPosfix = " ) WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"; foreach (DataTable tab in m_DataSet.Tables) { sqlCommand.CommandText = "Select * from sys.indexes where object_id=object_id('" + tab.TableName + "') and is_primary_key=1"; num = 0; object o = sqlCommand.ExecuteScalar(); if (o != null && !o.Equals(DBNull.Value)) { num = (int)o; } if (num > 0) { MessageBox.Show("[" + tab.TableName + "] 己經存在主Key,將不新建主Key,請自行檢查是否正確!"); } else { cmdPrifix = "ALTER TABLE [dbo].[" + tab.TableName + "] ADD CONSTRAINT [PK_" + tab.TableName + "] PRIMARY KEY CLUSTERED ( "; cmdMiddle = ""; foreach (DataColumn col in tab.PrimaryKey) { cmdMiddle += ("[" + col.ColumnName + "] ASC,"); } sqlCommand.CommandText = cmdPrifix + cmdMiddle.Substring(0, cmdMiddle.Length - 1) + cmdPosfix; sqlCommand.ExecuteNonQuery(); } } // 建立Relation,,AutoIncrement foreach (DataRelation fk in m_DataSet.Relations) { cmdPrifix = "ALTER TABLE [dbo].[" + fk.ChildTable.TableName + "] WITH NOCHECK ADD CONSTRAINT [" + fk.RelationName + "] "; cmdMiddle = "FOREIGN KEY( [" + fk.ChildColumns[0].ColumnName + "] ) "; cmdPosfix = "REFERENCES [dbo].[" + fk.ParentTable.TableName + "] ([" + fk.ParentColumns[0].ColumnName + "])"; if (fk.ChildColumns.Count() != 1 || fk.ParentColumns.Count() != 1) { MessageBox.Show("ForiegnKey:" + fk.RelationName + "超二個欄位,目前系統未支持,請手動修正!"); } sqlCommand.CommandText = cmdPrifix + cmdMiddle + cmdPosfix; sqlCommand.ExecuteNonQuery(); // Constrain Check sqlCommand.CommandText = "ALTER TABLE [dbo].[" + fk.ChildTable.TableName + "] CHECK CONSTRAINT [" + fk.RelationName + "] "; sqlCommand.ExecuteNonQuery(); } // 建立Header VEHeader var sqlConn = new SqlConnection(DB.SqlConnectString(m_HCfg.Local, NewSqlDB)); var sourceConn = new SqlConnection(DB.SqlConnectString(m_HCfg.Local, m_HCfg.Database)); m_OrderSet.Header.Clear(); var row1 = m_OrderSet.Header.AddHeaderRow(new DateTime(m_Year, 1, 1), false, 0, 0, 0, 0); var adapterBa = new VoucherExpense.DamaiDataSetTableAdapters.HeaderTableAdapter(); adapterBa.Connection = sqlConn; adapterBa.Update(row1); DateTime dt = new DateTime(m_Year, 1, 1); m_DataSet.Header.Clear(); var row2 = m_DataSet.VEHeader.AddVEHeaderRow(dt, false, dt, dt, Application.ProductVersion.Trim()); var adapterVE = new VoucherExpense.DamaiDataSetTableAdapters.VEHeaderTableAdapter(); adapterVE.Connection = sqlConn; adapterVE.Update(row2); MessageBox.Show("己建立新年度資料庫!"); } catch (Exception ex) { MessageBox.Show("錯誤:" + ex.Message); return; } MessageBox.Show("新年度資料設定完成!"); }