主从表操作

来源：互联网发布：ipadpro绘图软件编辑：程序博客网时间：2024/05/16 13:40

#region 变量定义层
        private Status FormStatus = new Status();
        private DataTable dtMaster = new DataTable();
        private DataTable dtDetail = new DataTable();
        private BindingSource bindMaster = new BindingSource();
        private BindingSource bindDetail = new BindingSource();
        private DataRow currentRow;
        private DataRow currentDetailRow;
        private DataAccess dataAccess = new DataAccess();
        #endregion

        #region 窗体载入填充数据与绑定及DataGridView操作
        private void frmDuty_Load(object sender, EventArgs e)
        {
            this.FillData();
            this.DataBind();
            this.setReadOnlyOnOff(true);

        }

        private void FillData()
        {
            string sql = "select * FROM HOD2DUTY order by NO_DUTY";
            this.dtMaster.Clear();
            this.dtMaster = dataAccess.DataTableExecuteSql(sql);
            this.bindMaster.DataSource = dtMaster;
            this.dataGridView1.DataSource = bindMaster;
            this.FillDetail();
            this.dataGridView1_CurrentCellChanged(null, null);
        }

        private void FillDetail()
        {
            if (this.dataGridView1.CurrentRow != null)
            {
                Int32 idx = this.dataGridView1.CurrentRow.Index;
                Int32 masterId;
                if (idx >= 0)
                {
                    if (this.dataGridView1.CurrentRow.Cells[0].Value is DBNull)
                    {
                        masterId = 0;
                    }
                    else
                    {
                        masterId = Convert.ToInt32(this.dataGridView1.CurrentRow.Cells[0].Value);
                    }
                }
                else
                {
                    masterId = 0;
                }

                string sql = "SELECT A.*,B.NO_TASK,B.NM_TASK,B.DS_TASK FROM HOD2DUTY_D A LEFT JOIN HOD2TASK B ON A.ID_TASK=B.ID_HOD2TASK where ID_duty='" + masterId + "'";
                dtDetail.Clear();
                dtDetail = dataAccess.DataTableExecuteSql(sql);
                this.bindDetail.DataSource = dtDetail;
                this.dataGridView2.DataSource = bindDetail;

            }

}

#endregion

//保存数据

private void Save()
        {

            string sql = "Exec P_INSUDP_HOD2DUTY @DUTYID,@DUTYNO,@DUTYNAME ;delete from hod2duty_d where id_duty=@dutyid;";
            foreach (DataRow dr in dtDetail.Rows)
            {
                sql += "insert into HOD2DUTY_D(CN_SQR,id_duty,id_task) values('" + dr["CN_SQR"] + "',@DUTYID,'"+ dr["ID_TASK"] +"');";
            }
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandText = sql;
            sqlCmd.Parameters.Add(new SqlParameter("@DUTYID", this.currentRow["ID_HOD2DUTY"]));
            sqlCmd.Parameters.Add(new SqlParameter("@DUTYNO", this.txtDutyNo.Text));
            sqlCmd.Parameters.Add(new SqlParameter("@DUTYNAME", this.txtDutyName.Text));
            this.dataAccess.ExecuteSql(sqlCmd);
        }