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("新年度資料設定完成!");
}