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

MaraSun

于 2023-02-05 16:26:16 发布

阅读量448

点赞数

分类专栏：数据库软件开发文章标签：数据库 sql 维护框架 MaraSunDB c#

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

版权

软件开发同时被 2 个专栏收录

120 篇文章 2 订阅

订阅专栏

数据库

25 篇文章 0 订阅

订阅专栏

续上文

三、数据显示

DGV 数据

DGV 将作为运行时的数据源使用，也是查询结果显示控件。
为了减少二次开发中的代码量，对其数据显示的封装也是必要的。
另外：函数中也对DGV的显示方式等等做了一些简单处理（此功能有待加强）
其中的My_SQLHelper 类是第三方的开源控件，进行了简单改写。
函数如下：

       public static int DgvRetrive(DataGridView dgv, string table,string searchCols, bool bRowNo ,string orderBy,string filter , bool bNeedBlob = false)
        {
            // open mysql db
            String sql;
           
            // get the columns needed 
            string colStr = "";
           // Dictionary<string, KeyValuePair<string, string>> colDict = GetColNameOfTable(table, out colStr, searchCols, bNeedBlob);
            GetColNameOfTable(table, out colStr, searchCols, bNeedBlob);
            if (orderBy == "")
            {
                orderBy = " order by pk desc ";
            }
            if (bRowNo)
            {
                sql = String.Format("select 0 as rowNo ,{0}  from {1} {2} {3}", colStr.ToLower(), table, filter!=""?" where " + filter:"",orderBy);
            }
            else
            {
                sql = String.Format("select {0}  from {1} {2}  {3}",  colStr.ToLower(),table, filter != "" ? " where " + filter : "",orderBy);

            }
            DataTable dt;
            DataSet ds;
            ds = My_SQLHelper.QueryDs(sql);
            dt = ds.Tables[0];

            dgv.DataSource = dt;
            int rowCount = 0;
            if (dt != null)
            {

                //search the clumns whose type is byte array and hide it , becuase of the exception of dgv once displaying it.
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    if (dgv.Columns[i].CellType.Name == "DataGridViewImageCell")
                    {
                        dgv.Columns[i].Visible = false;
                    }
                }

                // display the playlist recorded 
                int delBtnW = 100;
                //move the last column (rowN0) to the first column;
                rowCount = dt.Rows.Count;
                var count = dgv.Columns.Count - 1;
                                if (searchCols == "")
                {
                    dgv.Columns[2].DisplayIndex = 1;
                    dgv.Columns[2].Width = dgv.Width - dgv.Columns[0].Width + delBtnW;
                    dgv.Columns[2].HeaderText = "名称";
                    //dgv.Columns[2].SortMode = DataGridViewColumnSortMode.NotSortable;

                    foreach (DataGridViewRow row in dgv.Rows)
                    {
                        row.Cells["rowNo"].Value = row.Index + 1;
                    }
                }
                    //dgv.Columns[count].DisplayIndex = 0;
                    dgv.Columns[0].Width = 40;
                    dgv.Columns[0].HeaderText = "序号";
                    dgv.Columns[0].ReadOnly = true;
 
                 SetDgvColTitle(dgv);
                dgv.DefaultCellStyle.SelectionBackColor = Color.FromArgb(251, 176, 59);
            }
            return rowCount;
        }

其中 GetColNameOfTable 是从系统表中获取这个表列名，函数如下
因此要给数据库用户一定的权限，令其可以访问系统表。

      /// <summary>
        ///  Get the column name from the system db for dgv , and the blob column will be ingnored by default. 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="bNeedBlob"></param>
        /// <returns></returns>
        public static Dictionary<string, KeyValuePair<string, string>> GetColNameOfTable(string table, out string colStr,string searchCols, bool bNeedBlob = false)
        {
            colStr = "";
            Dictionary<string, KeyValuePair<string, string>> colDict = new Dictionary<string, KeyValuePair<string, string>>();
            string sql = string.Format(@"select concat('`', column_name,'`') as column_name, is_nullable, data_type, column_comment, column_key, extra from information_schema.columns 
                                       where table_name = '{0}' and table_schema = (select database()) order by ordinal_position ", table);
            var dt = My_SQLHelper.QueryDt(sql);
            searchCols = searchCols.ToUpper();
            foreach (DataRow row in dt.Rows)
            {
                var data_type = row["data_type"].ToString().ToUpper();
                var col_name = row["column_name"].ToString().ToUpper();
                var col_comm = row["column_comment"].ToString().ToUpper();
                colDict.Add(col_name, new KeyValuePair<string, string>(data_type, col_comm));
                if (!bNeedBlob && data_type.IndexOf("BLOB") >= 0)
                {
                    var specialChar = (char)0x01;
                    if (searchCols.IndexOf(col_name) == -1)
                    {
                        colStr += string.Format("'{0}' as {1} ,", specialChar, col_name);
                    }
                    continue;
                }
                if (searchCols.IndexOf(col_name) == -1)
                {
                    colStr += col_name + ",";
                }
            }
            if (searchCols != "")
            {
                colStr = searchCols + "," + colStr;
            }
            //remove the last comma ,
            colStr = colStr.Substring(0, colStr.Length - 1);
            return colDict;
        }

控件数据显示

控件数据的显示核心就是和DGV中的列名匹配并且赋值，函数如下：

功能：将DGV中的某行数据显示到界面上；
参数说明： sourceDgv，数据源
row：行号
fldKvp：键值对，key是列名称，value是对应的控件
函数中对不同的数据类型和控件类型分别进行了处理（不全）

       private static void RefreshCtr(DataGridView sourceDgv, int row, KeyValuePair<string, Control> fldKvp)
        {
            if (sourceDgv.Rows.Count == 0)
            {
                return;
            }
            Control ctrl = fldKvp.Value;
            var ctrlType = ctrl.GetType();
            
            string colName = null;
            colName = fldKvp.Key;
            //remove the  control prefix;
            int pos = colName.IndexOf(".");
            if (pos > 0)
            {
                colName = colName.Substring(pos + 1);
            }
            object obj = sourceDgv.Rows[row].Cells[colName].Value;
            if (obj == null) return;
            Type tp = sourceDgv.Rows[row].Cells[colName].Value.GetType();
            string colVal = obj.ToString();
            if (tp.Name == "Byte[]")
            {
                Byte[] dat = (Byte[])sourceDgv.Rows[row].Cells[colName].Value;
                colVal = "";
                foreach (byte bt in dat)
                {

                    colVal += bt.ToString("X2");
                }

            }
            else if (tp.Name == "DateTime")
            {
                DateTimePicker dtp = (DateTimePicker)ctrl;
                dtp.Value = Convert.ToDateTime(sourceDgv.Rows[row].Cells[colName].Value);
                dtp.Value = Convert.ToDateTime(colVal);

            }
            else if (tp.Name == "CheckBox")
            {
                CheckBox cbx = (CheckBox)ctrl;
               

            }
            else if (tp.Name == "NumericUpDown")
            {
                NumericUpDown nud = (NumericUpDown)ctrl;
                nud.Value = Convert.ToDecimal(sourceDgv.Rows[row].Cells[colName].Value);
               

            }
            else
            {
                var val = sourceDgv.Rows[row].Cells[colName].Value.ToString();
                switch (ctrlType.Name)
                {
                    
                    case "DateTimePicker":
                        var dtp = (DateTimePicker)ctrl;
                        dtp.Text = val;
                        break;
                    case "CheckBox":
                        var cbx = (CheckBox)ctrl;
                        cbx.Checked = (val == "1");
                        break;
                    case "NumericUpDaown":
                        var nud = (NumericUpDown)ctrl;
                        nud.Value = Convert.ToDecimal(val);
                        break;
                    default:
                        ctrl.Text = colVal;
                        if (ctrl.Text != colVal)
                        {
                            ctrl.Text = "";
                        }
                        break;
                }

               
            }
        }

四、数据操纵

数据操作的核心就是三个 DML语句的生成过程，代码如下

1、删除语句

这个语句非常简单，只要有表名和主键即可。根据前面的约定，这个都是可以获得的。
参数：主键和schema（数据库名），默认是当前的数据库。
成员变量 m_baseMainTable 存放主表名称。

 public void BaseDelete(uint pk,string schema="")
        {
            string mainTable = string.Concat(schema, schema != "" ? "." : "", m_baseMainTable);
            string sql = string.Format("delete from {0} where pk ={1}", mainTable, pk);
            My_SQLHelper.ExecuteSql(sql);
        }

讨论：如果含有其他表引用这个表数据，这个删除就会失败。框架设计中不包括这种递归删除功能，为了能让删除顺利进行，考虑增加了个由用户自己控制决定删除相关表的方式。笔者采用了利用增加代理的方式，来完善删除功能。（后期如果本人做版本的升级，会考虑将这一功能封装到框架内）

具体的代码如下：

           if (MessageBox.Show("确定要删除吗？", "提示", MessageBoxButtons.YesNo,MessageBoxIcon.Exclamation) == DialogResult.Yes)
            {
                // check the child data ....
                if (m_BaseDlgtCheckChild != null)
                {
                    if (!m_BaseDlgtCheckChild())
                    {
                        MessageBox.Show("存在数据引用，请首先删除引用数据。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        m_bBaseDeleted = false;
                        return;
                    }
                }
                BaseDelete(m_BaseMainPk);
        }

代码中的 m_BaseDlgtCheckChild 是一个代理，请定义如下

 public delegate bool DlgtCheckChild();
 .....
 protected DlgtCheckChild m_BaseDlgtCheckChild;

如果用户定义了这个代理，就执行之。该代理用于在删除表时，判断或删除其他表对它的引用。这个逻辑涉及到商业规则，需要编程人员和最终用户确定才行。

2、插入语句

即生成insert into 语句，需要的要素
表名称，和字段列表以及字段值。
值得注意的是，我们采用自动生成主键值的方式，因此主键名称和值可以不考虑。
利用上面谈到的遍历函数将字段记录到集合中，然后利用我们规定的映射关系，就可以获得字段列表以及对应的值列表。代码如下：

参数：tableName：表名称
user：指当前使用者，即账号（不是数据库的schema）
fldDict：存放当前表字段和对应控件的字典
foreignKey：外键，
foreignVal：外键值
说明：外键的支持过于简单了，需要支持多个外键才能达到要求。
pk：主键值，默认为0，属于自增主键。
函数中其实已经包含了对blob，json等特殊类型的考虑。

       public static string GetInsertSql(string tableName, string user, Dictionary<string, Control> fldDict,string foreignKey="",string foreignVal="", uint pk = 0)
        {
            string sql = string.Format("insert into {0} (pk, last_modi_by,", tableName);
            // search all of the controls in the group box 
            string fields = "";
            string values = string.Concat("\r\n values (",pk,",'" + user + "',");
            foreach (KeyValuePair<string, Control> kvp in fldDict)
            {

                string fieldName;
                fieldName = kvp.Key;//the field 
                int pos;
                pos = fieldName.IndexOf(".");
                if (pos > 0)
                {
                    fieldName = fieldName.Substring(pos + 1);
                }
                fields += fieldName + ",";
                Control ctrl;
                ctrl = kvp.Value;
                if (IsMyDigitCtrl(ctrl))//number 
                {
                    if (ctrl.Text == null || ctrl.Text.Trim() == "")
                    {
                        values += "null,";
                    }
                    else
                    {
                        values += ctrl.Text + ",";

                    }
                }
                else if (IsBlobCtrl(ctrl))
                {
                    string Val = ctrl.Text;
      
                    values += "unhex('" + Val + "'),";
                    //continue;
                }
                else if (IsJsonCtrl(ctrl))
                {
                    string Val = ctrl.Text;
                   
                    if (Val == "") Val = "null";
                    values += "'" + Val + "',";
                    //continue;
                }
                else
                {
                    string val = ctrl.Text;
                    val = val.Replace(@"\", @"\\");

                    values += "'" + val + "',";
                }
            }

实际代码的使用，还有考虑用户是否需要在保存数据之前做一些准备工作工作，因此完整的代码改进如下

3、更新语句

更新语句获得字段名称和值的方法与插入语句相同，需要注意的是主键是原来的值，代码如下

       public static string GetUpdateSql(string tableName, string user, ulong pk, Dictionary<string, Control> fldCtrl,string foreignKey="",string foreignVal="")
        {
            string sql = string.Format("update {0} set last_modi_by='{1}',", tableName, user);
            string whereStr = "\r\n where pk = " + pk.ToString();
            // search all of the controls in the group box 
            string setFields = "";

            foreach (KeyValuePair<string, Control> kvp in fldCtrl)
            {
                Control ctrl = kvp.Value;
                //if (ctrl.GetType() != Type.GetType("Label"))
                {
                    string fieldName;
                    fieldName = kvp.Key;
                    int pos;
                    pos = fieldName.IndexOf(".");
                    if (pos > 0)
                    {
                        fieldName = fieldName.Substring(pos + 1);
                    }

                    string val = ctrl.Text;
                    if (IsMyDigitCtrl(ctrl))
                    {//number 

                        if (ctrl.Text == null || ctrl.Text.Trim() == "") val = "null";

                        setFields += fieldName + "=" + val + ",\r\n";
                        continue;

                        //setFields += fieldName + "=" + ctrl.Text + ",\r\n";
                    }
                    if (IsJsonCtrl(ctrl))
                    {
                        if (ctrl.Text == null || ctrl.Text.Trim() == "") val = "null";
                        //replace \ with \\ 
                        val = val.Replace(@"\", @"\\");
                        setFields += fieldName + "='" + val + "',\r\n";
                        continue;
                    }
                    if (IsBlobCtrl(ctrl))
                    {
                        //Byte[]  datArr = ConverToByteArray(Val);
                        //string str = HexStringToString(Val, Encoding.UTF8);
                        setFields += fieldName + "=unhex('" + val + "'),\r\n";
                        continue;
                    }

                    //if (ctrl.Text == null || ctrl.Text.Trim() == "") val = "null";
                    //replace \ with \\ 
                    val = val.Replace(@"\", @"\\");
                    setFields += fieldName + "='" + val + "',\r\n";
                }
            }
            setFields = setFields.Substring(0, setFields.Length - 3);// remove the last comma ,
            if (foreignKey != "")
            {
                setFields  += ",\r\n" + foreignKey +"=" + foreignVal;
            }

            sql = sql + setFields + whereStr;
            return sql;

            Byte[] ConverToByteArray(string Val)
            {
                Byte[] byteArr = new Byte[Val.Length / 2];
                for (int i = 0; i < Val.Length; i += 2)
                {
                    byteArr[i / 2] = Convert.ToByte(Val.Substring(i, 2), 16);
                }
                return byteArr;
            }
        }

至此，插入语句和更新语句都已经生成了。

4、保存功能

保存功能，要知道当前是插入操作还是更新操作，通过[增加]按钮进行控制，记录到成员变量中。代码如下：

代码中加入了保存成功和失败的提示信息，并且在插入语句成功后，要获取当前数据的主键值；另外考虑了对 DGV 数据源的同步。插入之前，进行了必要的数据合法性检查。

            if (m_BaseDlgtSaveChild != null)
            {
                if (!m_BaseDlgtSaveChild())
                {
                    m_bBaseSaveOK = false;
                    return;
                }
            }
            string sql;
            string info = "";
            if (!ToolKit.ValidCheck(m_baseMainInputFldCtrlDict,m_baseMainTable, ref info, m_bBaseAdd, m_BaseMainPk))
            {
                txtBaseNotice.Text = info + "\r\n" + txtBaseNotice.Text;
                m_bBaseSaveOK = false;
                return;
            }
            // Need to be changed according do different table column name 

            sql = BaseSave(ref m_bBaseAdd);

同样的，为了在保存数据之前做可能的其他工作，增加了一个代理 m_BaseDlgtSaveChild。
其中 BaseSave 函数代码如下：

           string sql;
            string mainTable = string.Concat(schema,schema!=""?".":"",m_baseMainTable);
            if (bAdd)
            {

                sql = ToolKit.GetInsertSql(mainTable, m_BaseMainUser, m_baseAllMainFlds, m_baseForeignKey, m_baseForeignVal.ToString(),baseMainPk);// general little tool for getting insert sql for under some rule.... sund 


                int count = My_SQLHelper.ExecuteSql(sql);
                if (count == 1)
                {
                    if (schema == "")
                    {
                        sql = " SELECT MAX(pk) FROM " + mainTable;
                        m_BaseMainPk = My_SQLHelper.GetLastID("pk", mainTable);
                        txtBaseNotice.Text = m_baseKeyLabel + " 新增数据成功!\r\n" + txtBaseNotice.Text;
                        //update the dgv at the same time ;
                        if (!ToolKit.UpdateDgv(bAdd, dgvBase, ref m_baseMainCurRow, m_baseAllMainFlds, m_BaseMainPk, m_baseForeignKey, m_baseForeignVal.ToString()))
                        {

                        }
                    }
                    bAdd = false;
                    m_bBaseSaveOK = true;
                }
                else
                {
                    txtBaseNotice.Text = String.Concat( m_baseKeyLabel," ",schema," ", "*** 新增数据失败! ****\r\n" , txtBaseNotice.Text);
                    m_bBaseSaveOK = false;
                }
            }
            else
            {
                sql = ToolKit.GetUpdateSql(mainTable, m_BaseMainUser, m_BaseMainPk, m_baseAllMainFlds);// general little tool for getting insert sql for under some rule.... sund 

                int count = My_SQLHelper.ExecuteSql(sql);
                if (count == 1)
                {
                    txtBaseNotice.Text = m_baseKeyLabel + " 更改数据成功!\r\n" + txtBaseNotice.Text;
                    m_bBaseSaveOK = true;

                    ToolKit.UpdateDgv(bAdd, dgvBase, ref m_baseMainCurRow, m_baseAllMainFlds, m_BaseMainPk);
                }
                else
                {
                    m_bBaseSaveOK = false;
                    txtBaseNotice.Text = m_baseKeyLabel + "*** 更改数据失败! ****\r\n" + txtBaseNotice.Text;
                }

            }

            return sql;