编写一个简版的数据库维护框架04-代码实现(基本函数部分3）

本文链接：https://blog.csdn.net/Uman/article/details/128898514

续上文

其他功能

基本功能和不太好分类的功能放在本小节。

基础变量

准备工作主要是数据实体和控件的映射的建立和记录，以及必要的初始化过程。
采用成员变量记录这些关系和数据，代码如下：
（代码中包含了很多文档没有提及的设置和属性，详情请参加注释）

       protected bool m_bBaseAdd = false;// 是否新增数据
        protected bool m_bBaseDeleted = false;//是否已经删除
        private bool m_bInitInputHasBeenCalled = false;// 是否已经执行了初始化操作

        protected string m_baseMainTable = "###";//must use lower case 
        protected string m_baseForeignKey ="";//must use lower case , if is not null , then the this colum should be updated . 
        protected string m_BaseFormTitle = "请修改标题";//标题 
        protected uint m_BaseMainPk;
        protected uint m_DeleteMainPk;
        protected object m_baseForeignVal="";
        protected int m_baseMainCurRow;
        protected string m_BaseMainUser;
        protected List<Control> m_baseAllFieldCtrls ;
        protected List<Control> m_basePnlCtrls ;
        protected List<ComboBox> m_baseSubDWLst = new List<ComboBox>();
        protected List<DgvX> m_baseSubDWLst2 = new List<DgvX>();
        //sub table and the current pk value 
        protected Dictionary<string, uint> m_baseSubTableCurPK = new Dictionary<string, uint>();
        
        //store the primary key name and value pair for the sub table, the element is Dict!!! 
        
        protected Dictionary<string/*The ctrl and the the table name*/ , Dictionary<string, uint>> m_baseSubPkNmValDict = new Dictionary<string,Dictionary<string, uint>>();
        //the followings are usded to store the fields and control pair, set 
        //m_MTAB_FieldsCtrlsDict for redundant values get from foreign table and stored in the main table too
        //m_STAB_FieldsCtrlsDict for values from foreign table and only displayed in the form 
        protected Dictionary<string, Dictionary<string, Control>> m_baseMTAB_FieldsCtrlsDict = new Dictionary<string, Dictionary<string, Control>>();
        protected Dictionary<string, Dictionary<string, Control>> m_baseSTAB_FieldsCtrlsDict = new Dictionary<string, Dictionary<string, Control>>();
        protected Dictionary<string, Control> m_baseMainInputFldCtrlDict = new Dictionary<string, Control>();
        // all of the controls and the foreign key need to be updated 
        protected Dictionary<string, Control> m_baseAllMainFlds ;
        protected string m_baseKeyLabel ;//used for notice 
        protected string m_baseSearchFilter;// used for search
        protected string m_baseSeachingCol = "";// 查询字段 eg: "device_id ,brief "
        protected bool m_bBaseRowNo = true;
        protected bool m_bBaseSaveOK;
        protected string m_baseOrderBy;//排序字段
        protected DlgtSaveChild m_BaseDlgtSaveChild;
        protected DlgtCheckChild m_BaseDlgtCheckChild;
        protected DlgtDeleteChild m_BaseDlgtDeleteChild;
        protected DlgtRefreshChild m_BaseDlgtRefreshChild;
        private int m_msgCount;
        private bool m_nBaseNeedBlob = false;

初始化函数

框架的所有基础功能，都将在初始化过程中完成。代码如下
说明：文中的sub window 指的是被引用表使用的DGV。

/// <summary>
        ///  Call this function in the child form load 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="title"></param>
        /// <param name="keyLabel">used for notice </param>
        /// <param name="searchName"></param>
        /// <param name="bNeedBlob"></param>
        protected void InitInputForm(string mainTable,string title,string keyLabel,string searchName,bool bNeedBlob=false)
        {
            m_bInitInputHasBeenCalled=true;
            m_baseMainTable = mainTable;
            m_BaseFormTitle = title;
            m_baseKeyLabel = keyLabel;
            m_baseSearchFilter = searchName;
            m_nBaseNeedBlob = bNeedBlob;
            SetFormTtile(m_BaseFormTitle);
            m_BaseMainUser = AppCfg.m_User;


            var dgvLst = ToolKit.GetAllDgvCtrls(grpBaseDbFields);
            foreach (DataGridView dgv in dgvLst)
            {
                if (ToolKit.GetTableInTag(dgv, AppCfg.m_TabPattern) != null)
                {
                    var dgvX = new DgvX(dgv);
                    dgvX.Name = "X"+dgv.Name;
                    dgvX.Tag = dgv.Tag;
                   
                    grpBaseDbFields.Controls.Add(dgvX);
                }
            }
            //get the controls or their containers 

            m_baseAllFieldCtrls = ToolKit.GetRefreshFieldCtrls(grpBaseDbFields);

            m_baseMainInputFldCtrlDict = ToolKit.GetMainTabFldDict(grpBaseDbFields);// these fields will be checked and updated ...
            m_basePnlCtrls = ToolKit.GetAllCtrls(grpBaseDbFields, true);// the containers for sub table data filelds.
            //get the fields in the panel
            foreach (Control ctrl in m_basePnlCtrls)
            {
                Panel pnl = (Panel)ctrl;
                //check if the panel is not the main tab panel whose tag is 
                // if the matched table was found , the fields in this panel belongs to the sub-table
                if(ToolKit.GetTableInTag(pnl, AppCfg.m_SBTblPattern) != null){
                    continue;
                }
                Dictionary<string, Control> curFldCtrDic = ToolKit.GetFldCtrlDictFromPanel(pnl);
                //judge the table property and get the fields control pair
                //if it is a pannel whose tag mathces the pattern....
                var tableName = ToolKit.GetTableInTag(pnl, AppCfg.m_MTblPattern);
                if (tableName != null)
                {
                    //the following codes are used to upport different panels with the same table
                    // not veryifed. 
                    if (m_baseMTAB_FieldsCtrlsDict.Keys.Contains(tableName))
                    {
                        Dictionary<string, Control> prevFldCtrDic = m_baseMTAB_FieldsCtrlsDict[tableName];
                       
                        try
                        {
                            
                            prevFldCtrDic = prevFldCtrDic.Concat(curFldCtrDic).ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
                            m_baseMTAB_FieldsCtrlsDict.Remove(tableName);
                            m_baseMTAB_FieldsCtrlsDict.Add(tableName, prevFldCtrDic);
                        }catch(Exception ex)
                        {
                            // do nothing: the controls in a subpannel of a pareant pannel seems belongs to the parent pannel 
                        }


                    }
                    else
                    {
                        m_baseMTAB_FieldsCtrlsDict.Add(tableName, curFldCtrDic);

                    }
                    continue;
                }
                tableName = ToolKit.GetTableInTag(pnl, AppCfg.m_RFTblPattern);
                if (tableName != null)
                {
                    var ctrlTabKey = pnl.Name + "." + tableName;
                    ctrlTabKey =tableName;
                    m_baseSTAB_FieldsCtrlsDict.Add(ctrlTabKey, curFldCtrDic);
                    continue;
                }
            }
            // find the sub datawindow
            foreach (Control ctrl in m_baseAllFieldCtrls)
            {
                // the obsolete method 
                var ctlType = ctrl.GetType().Name;
                if (ctlType == "ComboBox")
                {
                    ComboBox cbb = (ComboBox)ctrl;
                    var subTable = ToolKit.GetTableInTag(cbb, AppCfg.m_TabPattern);
                    
                    if(subTable==null) continue;
                    //var ctrTabKey = cbb.Name + "." + subTable;
                    var ctrTabKey =  subTable;
                    if (!m_baseSubTableCurPK.Keys.Contains(ctrTabKey))
                    {

                        m_baseSubTableCurPK.Add(ctrTabKey, 0);
                    }
                    m_baseSubDWLst.Add((ComboBox)ctrl);
                    //add the general delegate to process the click event which will get the foreign key and assign it to the m_subTableCurPK 
                    cbb.SelectedIndexChanged += new System.EventHandler(subWindSelectedIndexChanged);
                    // add two refered tables 
                    // get the sub table from the cbb tag 

                }else if (ctlType == "DgvX")// DgvX 是增强型的DataGridView， 目的是保持控件处理的一致性，具体实现见下一节
                {
                    DgvX dgvX = (DgvX)ctrl;
                    dgvX.SetSubTableFieldsCtrl(m_baseMTAB_FieldsCtrlsDict, m_baseSTAB_FieldsCtrlsDict);

                }
            }
            // generate the whole dict used for insert and update 
            m_baseAllMainFlds = m_baseMainInputFldCtrlDict;
            foreach (KeyValuePair<string, Dictionary<string, Control>> kvp in m_baseMTAB_FieldsCtrlsDict)
            {
                Dictionary<string, Control> fldCtrl = (Dictionary<string, Control>)kvp.Value;

                m_baseAllMainFlds = m_baseAllMainFlds.Concat(fldCtrl).ToDictionary(kv => kv.Key, kv => kv.Value);
            }
            //!!!!!!!!!!!!!!plus the foreign key !!!!!!!!!!!!!!
            foreach (KeyValuePair<string, Dictionary<string, UInt32>> kvp in m_baseSubPkNmValDict)
            {
                //foreach (KeyValuePair<string, UInt32> kvp2 in kvp.Value)

                Control ctrl = new Control();
                ctrl.Tag = AppCfg.m_DgtLbl;
                ctrl.Text = "";

                m_baseAllMainFlds.Add(kvp.Key + "_pk", ctrl);
            }
            //Refresh the DGV which is used to locate the record to be modified.
            GetDataForDGV();
        }

函数GetDataForDGV()的代码如下，其目的是将数据库数据缓冲到内存中。

private void GetDataForDGV()
        {
            if (m_baseMainTable == "") return;// empty string means the dgv will not be retrieved 
            var filter = "";
            if (m_baseForeignKey != "")
            {
                filter = string.Format("{0}={1}", m_baseForeignKey, m_baseForeignVal);
            }
            var rowCount = ToolKit.DgvRetrive(dgvBase, m_baseMainTable,m_baseSeachingCol,m_bBaseRowNo,m_baseOrderBy,filter);
            //refresh the first row
            //if (rowCount == 0)
            //{
            //    btnBaseModify.Enabled = false;
            //}
            m_baseMainCurRow = 0;
            ToolKit.RefreshFields(dgvBase,m_baseAllMainFlds, m_baseMainCurRow, ref m_BaseMainPk);
            btnBaseSave.Enabled = false;

        }

有效性检查

有效性检查函数，可以进最大可能防止用户的错误输入，
目前功能包括，是否合法数字的检查和数据的唯一性检查
代码如下：

       public static bool ValidCheck(Dictionary<string, Control> fldCtrD, string tab,ref string info,bool bAdd=false,ulong pk=0)
        {
            bool bIsValid = true;
            foreach (KeyValuePair<string, Control> kvp in fldCtrD)
            {
                bool bTmp;
                bTmp = isValidValueReg(kvp.Value,tab, ref info,bAdd,pk);
                if (bIsValid) bIsValid = bTmp;
            }
            if (!bIsValid)
            {
                // buzz 
                ToolKit.Beep();
            }
            return bIsValid;
        }
....
       /// <summary>
        /// Use REG expression to validate the control text 
        /// </summary>
        /// <param name="ctrl"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        public static bool isValidValueReg(Control ctrl,string tab, ref string info, bool bAdd = false,ulong pk=0)
        {
            bool bIsValid = true;
            if (ctrl.Tag is null) return true;
            var tag = ctrl.Tag.ToString();
            //get the match pattern string 
            string val = ctrl.Text;
            string ptnStr;
            Match match = Regex.Match(tag, AppCfg.m_REGPattern);
            // type check 
            if (match.Success)
            {
                ptnStr = match.Groups["ptn"].ToString();// ptn stands for pattern 
                string value;
                value = ctrl.Text;
                match = Regex.Match(value, ptnStr);
                if (!match.Success)
                {
                    bIsValid = false;
                    //get the notice info from the tag

                    info= GetMsgFromCtrlTag(ctrl);
                }

            }
            
            //Unique check 2022-08-17 passed 
            match = Regex.Match(tag, AppCfg.m_UniqueCheckPattern);
            //var tab = GetTableInTag(ctrl, AppCfg.m_TabPattern);
            var fld = GetFldFromName(ctrl);
            if (match.Success && (tab != null && fld != null))
            {
                //get table and field 
                string sql = "";
                if (bAdd)
                {
                    sql = string.Format("select count(*) from {0} where {1}='{2}'", tab, fld, val);
                }
                else
                {
                    sql = string.Format("select count(*) from {0} where {1}='{2}' and pk!={3}", tab, fld, val, pk);
                    info = GetMsgFromCtrlTag(ctrl);
                }
                var count = Convert.ToInt32(My_SQLHelper.GetSingle(sql));
                if (count >= 1)
                {
                    bIsValid = false;
                    info = GetMsgFromCtrlTag(ctrl);
                    if (info == null)
                    {
                        info = string.Format("{0}：数据重复,请检查!", val);
                    }
                }
            }
            else
            {
                bIsValid = isValidValue(ctrl, ref info);
            }
          
            return bIsValid;
        }

DataGridView 功能增强

DGV 要实现对被引用表数据的显示和录入，并且好隐藏非商业数据，例如主键等等。为了框架操作的一致性，需要给它增加一个Text属性，并能做相应的事件相应处理。为此，实现了一个DgvX的类，代码如下
说明：在该类中，对用户自己定义的Dgv做了很多调整，减少了用户后期的干预，具体调整包括，显示大小，排序属性，标头显示，鼠标事件等等。

增加了以个Text属性，其详细实现过程请参见代码。

 public class DgvX:Control
    {
        public DataGridView m_Dgv;
        private int m_ExpandH;
        // the refered table name , 
        private string m_SubTable;
        // the refered primary key name,actually the db field in the name of the m_Dgv
        private string m_SubPKName;
        private string m_SubTabCurPKValue;// the current refered table's pk 
        // 
        protected Dictionary<string/*The ctrl and the the table name*/ , Dictionary<string, uint>> m_baseSubPkNmValDict = new Dictionary<string, Dictionary<string, uint>>();
        //the followings are usded to store the fields and control pair 
        //m_MTAB_FieldsCtrlsDict for redundant values get from foreign table and stored in the main table too
        //m_STAB_FieldsCtrlsDict for values from foreign table and only displayed in the form 
        protected Dictionary<string, Control> m_RedundantDatCtlDict = new Dictionary<string, Control>();
        protected Dictionary<string, Control> m_DisplayCtlsDict = new  Dictionary<string, Control>();

        public override string Text { get { return GetText(); } set { SetText(value); } }
        public DgvX(DataGridView dgv)
        {
            m_Dgv = dgv;
          
            InitDgv();
  
        }
        public void SetSubTableFieldsCtrl(Dictionary<string, Dictionary<string, Control>> redundantCtrDict, Dictionary<string, Dictionary<string, Control>> displayCtlsDict)
        {
            
            m_RedundantDatCtlDict = redundantCtrDict[m_SubTable];
            m_DisplayCtlsDict = displayCtlsDict[m_SubTable];
            

        }
        /// <summary>
        ///  the core process 
        /// </summary>
        /// <exception cref="Exception"></exception>

        private void InitDgv()
        {
            m_Dgv.CellClick += new DataGridViewCellEventHandler(CellClick);
            m_Dgv.Leave += new System.EventHandler(Leave);
            m_Dgv.RowHeadersVisible = false;
            m_Dgv.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            m_Dgv.AllowUserToAddRows = false;
            m_Dgv.ReadOnly = true;
            m_ExpandH=m_Dgv.ColumnHeadersHeight*10;
            m_Dgv.Height = m_Dgv.ColumnHeadersHeight;
            m_SubPKName = ToolKit.GetFldFromStr(m_Dgv.Name);//eg:dgv_xtab_pk
           
            if (m_SubPKName == null)
            {
                MessageBox.Show("DGV 没有初始化，需要设置至少一个和其名称一致的列。例如：dgv_xtab_pk。", "错误", MessageBoxButtons.OK);
                throw new Exception("DGV 没有初始化，需要设置至少一个和其名称一致的列。");
            }
            string subClms = "";
            
            int pos;

            var dgv = m_Dgv;
            if (dgv.Tag == null) return ;
            //pos = dgv.Tag.ToString().IndexOf(".");// eg: department.name,
            m_SubTable = ToolKit.GetTableInTag(dgv, AppCfg.m_TabPattern);
            if (m_SubTable != null)
            {
                //pos = dgv.Name.IndexOf("_");// eg: cbb_department_name,

                //if (pos > 0)

                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    var clmName = dgv.Columns[i].Name;
                    dgv.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable;
                    pos = clmName.IndexOf("_");
                    if (pos > 0)
                        subClms += string.Format("{0} as {1}_{0},", clmName.Substring(pos + 1), m_SubTable);
                }
                subClms = subClms.Remove(subClms.Length - 1);//remove the last ,

                //Dictionary<string, uint> keyValPair = new Dictionary<string, uint>();
                string sql = string.Format("select distinct {0} from {1}", subClms, m_SubTable);// the column name has been stored in the 'Tag' when you factor the UI
                dgv.Columns.Clear();
                DataTable dt = My_SQLHelper.QueryDt(sql);
                dgv.DataSource = dt;
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    var clmName = dgv.Columns[i].Name;
                    dgv.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable;

                }

            }
        }

        private void  SetText(string text)
        {
            //var dgvName = this.m_Dgv.Name;
            //this.m_Dgv.Columns[dgvName].HeaderText = text;

            
            // get the FK value 
            
            // assign the value of fkClm to the headerText
            m_Dgv.Columns[m_SubPKName].HeaderText = text;
            m_SubTabCurPKValue = text;//the primary key value of the refered table 

            if (text == "")
            {
                foreach (KeyValuePair<string,Control>  kvp in m_DisplayCtlsDict)
                {
                    kvp.Value.Text = "";
                }
                foreach(DataGridViewColumn clm in m_Dgv.Columns)
                {
                    clm.HeaderText = " "; 
                }
                return;
            }
            // assign other value whose pk value = dgv.Columns[fkClm].HeaderText
            var dt = (DataTable)m_Dgv.DataSource;
            var fk = m_Dgv.Columns[m_SubPKName].HeaderText;
            DataColumn[] keys = new DataColumn[1];
            keys[0] = dt.Columns[m_SubPKName];
            dt.PrimaryKey = keys;
            if (fk != "" && fk != null)
            {
                var subRow = dt.Rows.Find(fk);
                if (subRow != null)
                {
                    foreach (DataGridViewColumn col in m_Dgv.Columns)
                    {
                        var srcColName = col.Name;
                        col.HeaderText = subRow[srcColName].ToString();

                    }
                }
            }
            //display the refered data if they are requered 
            if (m_DisplayCtlsDict.Keys.Count > 0)
            {
                string sql;
                string column = "";
                foreach (KeyValuePair<string, Control> colCtrl in m_DisplayCtlsDict)
                {
                    //var clm = ToolKit.GetFldFromStr(colCtrl.Key);
                    // not the redundant data, use the origial name 
                    var clm = colCtrl.Key;
                    //column += string.Format("{0} as {1},", clm, colCtrl.Key);
                    column += string.Format("{0},", clm);
                }
                //remove the last ",";
                column = column.Substring(0, column.Length - 1);
                sql = string.Format("select {0} from {1} where pk = {2}", column, m_SubTable, m_SubTabCurPKValue);
                DataSet ds = My_SQLHelper.QueryDs(sql);
                dt = ds.Tables[0];
                DataRow dr = dt.Rows[0];
                {
                    foreach (DataColumn ctrlName in dr.Table.Columns)
                    {
                        m_DisplayCtlsDict[ctrlName.ColumnName].Text = dr[ctrlName.ColumnName].ToString();
                    }

                }


            }

        }
        private string GetText()
        {
            //string value="";
            //var fkClm = ToolKit.GetFldFromStr(m_Dgv.Name);
             assign the value of fkClm to the headerText
            //value = m_Dgv.Columns[fkClm].HeaderText;
            //Text = value;
            var val = m_Dgv.Columns[m_SubPKName].HeaderText;
            return val;
        }
        /// <summary>
        ///  update the head title with selected row 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void CellClick(object sender, DataGridViewCellEventArgs e)
        {

            //first refresh the foreign key 
            var dgv = (DataGridView)sender;
            var curRow = e.RowIndex;
            var clmName = "";
            if (curRow < 0)
            {
                dgv.Height = m_ExpandH;
                return;
            }
            else
            {
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    clmName = dgv.Columns[i].Name;
                    dgv.Columns[clmName].HeaderText = dgv.Rows[curRow].Cells[clmName].Value.ToString();
                    // get the subtable pk value 
                    if(clmName == m_SubPKName)
                    {
                        m_SubTabCurPKValue = dgv.Columns[clmName].HeaderText;
                    }

                   
                }
                dgv.Height = dgv.ColumnHeadersHeight;
            }
            //refresh the redundant value from the foreign table and stroed in the main table for convenient using.
            var ctrlTblKey = m_SubPKName;
            if (m_RedundantDatCtlDict.Keys.Count>0)
            {
                string sql = "";
                string column = "";
                foreach (KeyValuePair<string, Control> colCtrl in m_RedundantDatCtlDict)
                {
                    // attention , based on the redundant data naming rules 
                    var clm = ToolKit.GetFldFromStr(colCtrl.Key);
                    column += string.Format("{0} as {1},", clm, colCtrl.Key);
                }
                //remove the last ",";
                column = column.Substring(0, column.Length - 1);
                sql = string.Format("select {0} from {1} where pk = {2}", column, m_SubTable, m_SubTabCurPKValue);


                DataSet ds = My_SQLHelper.QueryDs(sql);
                DataTable dt = ds.Tables[0];
                DataRow dr = dt.Rows[0];
                {
                    foreach (DataColumn ctrlName in dr.Table.Columns)
                    {
                        //assign control with value from DB ....
                        m_RedundantDatCtlDict[ctrlName.ColumnName].Text = dr[ctrlName.ColumnName].ToString();
                    }

                }

            }
            //Get the other data from the from the sub table and displays them only.

            if (m_DisplayCtlsDict.Keys.Count>0)
            {
                string sql ;
                string column = "";
                foreach (KeyValuePair<string, Control> colCtrl in m_DisplayCtlsDict)
                {
                    //var clm = ToolKit.GetFldFromStr(colCtrl.Key);
                    // not the redundant data, use the origial name 
                    var clm = colCtrl.Key;
                    //column += string.Format("{0} as {1},", clm, colCtrl.Key);
                    column += string.Format("{0},", clm);
                }
                //remove the last ",";
                column = column.Substring(0, column.Length - 1);
                sql = string.Format("select {0} from {1} where pk = {2}", column,m_SubTable, m_SubTabCurPKValue);
                DataSet ds = My_SQLHelper.QueryDs(sql);
                DataTable dt = ds.Tables[0];
                DataRow dr = dt.Rows[0];
                {
                    foreach (DataColumn ctrlName in dr.Table.Columns)
                    {
                        m_DisplayCtlsDict[ctrlName.ColumnName].Text = dr[ctrlName.ColumnName].ToString();
                    }

                }


            }
       }
        private new void Leave(object sender, EventArgs e)
        {
            var dgv = (DataGridView)sender;
            dgv.Height = dgv.ColumnHeadersHeight;
        }
    }