Esempio n. 1
0
        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 = "鎖定資料庫";
            }
        }
Esempio n. 2
0
        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("新年度資料設定完成!");
        }