多字段交叉编码数据库的设计

最近，公司要求开发一个物料编码管理的软件，但是看到编码规则当时就懵了，这物料编码规则不像身份证，1-6位就代表地区，7-11就代表出身年份，公司由于物品零件繁多，编码规则当初就是设计的是交叉的。例如：AAAADEFG和AAABDEFG,就因为第四位有差异，后面几位的指代也变了。
因为也没遇到过类似的问题，当时就觉得这根本就不可能，每一位要设计一个表，而且根据前面的不同要分开设计表，就是成千上万的表，根本不可行，也不可能把一个完整的编码直接存到数据表中，这样针对指定位置上进行搜索时就比较困难（例如：第二字段代表名称，通过编码查询）。后来，想了两天终于有点眉目了，我可以把编码进行拆分放到一个表中，每一个编码进行给序号，再标注上一个字段编码序号。设计表如下：![CodingClass中表示编码规则主要内容，CodingSplit中记录编码每一位含义并链接上一位直至顶层，Coding记录达到满位编码](https://img-blog.youkuaiyun.com/20170226222209674?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvd2VpeGluXzM3NDY0NTgx/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)

下面是程序加载CodingSplit编码，支持9层字段，每个字段长在CodingClass中设置，例如111111111表示9位字段，每个字段长为1位。

TreeView填充方法

private void fillTreeList(TreeView TV,ImageList imgae)
        {
            SqlDataReader[] sdr = new SqlDataReader[10];
            TV.Nodes.Clear();
            TV.ImageList = imgae;
            //getSqlConnection getConnection = new getSqlConnection();
            //conn = getConnection.getCon();
            sql[0] = "SELECT * FROM CODINGCLASS";
            sdr[0] = MySdr(sql[0]);
            string code_Local = null;
            while (sdr[0].Read())
            {
                code_Local = sdr[0][0].ToString();
                TN[0] = TV.Nodes.Add("T0", sdr[0][0].ToString(), 0, 1);
                LastID[0] = "0";
                sql[1] = "SELECT * FROM CODINGSPLIT WHERE CODINGCLASS ='" + code_Local + "' AND COLUMNPOSITION =1 AND LASTCODEID ='" + "0" + "' ORDER BY CODINGCLASS,CODE";
                sdr[1] = MySdr(sql[1]);
                while (sdr[1].Read())
                {
                    TN[1] = new TreeNode(sdr[1][2].ToString() + "---" + sdr[1][5].ToString(), 0, 1);
                    TN[1].Name = sdr[1][4].ToString();
                    LastID[1]=sdr[1][1].ToString ();
                    sql[2] = "SELECT * FROM CODINGSPLIT WHERE CODINGCLASS ='" + code_Local + "' AND COLUMNPOSITION =2 AND LASTCODEID ='" + LastID[1] + "' ORDER BY CODE";
                    sdr[2] = MySdr(sql[2]);
                    while (sdr[2].Read())
                    {
                        TN[2] = new TreeNode(sdr[2][2].ToString() + "---" + sdr[2][5].ToString(), 0, 1);
                        TN[2].Name = sdr[2][4].ToString();
                        LastID[2] = sdr[2][1].ToString();
                        sql[3] = "SELECT * FROM CODINGSPLIT WHERE CODINGCLASS ='" + code_Local + "' AND COLUMNPOSITION =3 AND LASTCODEID ='" + LastID[2] + "' ORDER BY CODE";
                        sdr[3] = MySdr(sql[3]);
                        while (sdr[3].Read())
                        {
                            TN[3] = new TreeNode(sdr[3][2].ToString() + "---" + sdr[3][5].ToString(), 0, 1);
                            TN[3].Name = sdr[3][4].ToString();
                            LastID[3] = sdr[3][1].ToString();
                            sql[4] = "SELECT * FROM CODINGSPLIT WHERE CODINGCLASS ='" + code_Local + "' AND COLUMNPOSITION =4 AND LASTCODEID ='" + LastID[3] + "' ORDER BY CODE";
                            sdr[4] = MySdr(sql[4]);
                            while (sdr[4].Read())
                            {
                                TN[4] = new TreeNode(sdr[4][2].ToString() + "---" + sdr[4][5].ToString(), 0, 1);
                                TN[4].Name = sdr[4][4].ToString();
                                LastID[4] = sdr[4][1].ToString();
                                sql[5] = "SELECT * FROM CODINGSPLIT WHERE CODINGCLASS ='" + code_Local + "' AND COLUMNPOSITION =5 AND LASTCODEID ='" + LastID[4] + "' ORDER BY CODE";
                                sdr[5] = MySdr(sql[5]);
                                while (sdr[5].Read())
                                {
                                    TN[5] = new TreeNode(sdr[5][2].ToString() + "---" + sdr[5][5].ToString(), 0, 1);
                                    TN[5].Name = sdr[5][4].ToString();
                                    LastID[5] = sdr[5][1].ToString();
                                    sql[6] = "SELECT * FROM CODINGSPLIT WHERE CODINGCLASS ='" + code_Local + "' AND COLUMNPOSITION =6 AND LASTCODEID ='" + LastID[5] + "' ORDER BY CODE";
                                    sdr[6] = MySdr(sql[6]);
                                    while (sdr[6].Read())
                                    {
                                        TN[6] = new TreeNode(sdr[6][2].ToString() + "---" + sdr[6][5].ToString(), 0, 1);
                                        TN[6].Name = sdr[6][4].ToString();
                                        LastID[6] = sdr[6][1].ToString();
                                        sql[7] = "SELECT * FROM CODINGSPLIT WHERE CODINGCLASS ='" + code_Local + "' AND COLUMNPOSITION =7 AND LASTCODEID ='" + LastID[6] + "' ORDER BY CODE";
                                        sdr[7] = MySdr(sql[7]);
                                        while (sdr[7].Read())
                                        {
                                            TN[7] = new TreeNode(sdr[7][2].ToString() + "---" + sdr[7][5].ToString(), 0, 1);
                                            TN[7].Name = sdr[7][4].ToString();
                                            LastID[7] = sdr[7][1].ToString();
                                            sql[8] = "SELECT * FROM CODINGSPLIT WHERE CODINGCLASS ='" + code_Local + "' AND COLUMNPOSITION =8 AND LASTCODEID ='" + LastID[7] + "' ORDER BY CODE";
                                            sdr[8] = MySdr(sql[8]);
                                            while (sdr[8].Read())
                                            {
                                                TN[8] = new TreeNode(sdr[8][2].ToString() + "---" + sdr[8][5].ToString(), 0, 1);
                                                TN[8].Name = sdr[8][4].ToString();
                                                LastID[8] = sdr[8][1].ToString();
                                                sql[9] = "SELECT * FROM CODINGSPLIT WHERE CODINGCLASS ='" + code_Local + "' AND COLUMNPOSITION =9 AND LASTCODEID ='" + LastID[8] + "' ORDER BY CODE";
                                                sdr[9] = MySdr(sql[9]);

                                                while (sdr[9].Read())
                                                {
                                                    TN[9] = new TreeNode(sdr[9][2].ToString() + "---" + sdr[9][5].ToString(), 0, 1);
                                                    TN[9].Name = sdr[9][4].ToString();
                                                    TN[8].Nodes.Add(TN[9]);
                                                }
                                                sdr[9].Dispose();
                                                TN[7].Nodes.Add(TN[8]);
                                            }
                                            sdr[8].Dispose();
                                            TN[6].Nodes.Add(TN[7]);
                                        }
                                        sdr[7].Dispose();
                                        TN[5].Nodes.Add(TN[6]);
                                    }
                                    sdr[6].Dispose();
                                    TN[4].Nodes.Add(TN[5]);
                                }
                                sdr[5].Dispose();
                                TN[3].Nodes.Add(TN[4]);
                            }
                            sdr[4].Dispose();
                            TN[2].Nodes.Add(TN[3]);
                        }
                        sdr[3].Dispose();
                        TN[1].Nodes.Add(TN[2]);
                    }
                    sdr[2].Dispose();
                    TN[0].Nodes.Add(TN[1]);
                }
                sdr[1].Dispose();
            }
            sdr[0].Dispose();
            TV.ExpandAll();
        }
        ![简单测试](http://img.blog.csdn.net/20170226223556661?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvd2VpeGluXzM3NDY0NTgx/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)

判断选中的结点是否还有子节点

        //判断选中的节点是否还有子节点  1:有  0:没有
        private int IsBound(int level)
        {
            int int_Local = 1;
            string string_Local = "SELECT * FROM CODINGCLASS WHERE CODINGCLASS='" + codingclass + "'";
            SqlDataReader sdr_Local = MySdr(string_Local);
            while (sdr_Local.Read())
            {
                if (sdr_Local[level + 2] == DBNull.Value)
                    int_Local = 0;
            }
            sdr_Local.Dispose();
            return int_Local;
        }

判断子节点是否符合要求

        /*  1.判断子节点长度是否符合要求
         *  2.判断新增子类的标码是否已存在
         *  3.若新增标码有效,判断应该出现的位置
         * 
         * */

        private int FindLocation(string str)
        {
            TreeNode TN_Local = new TreeNode();
            int count_Local = 0;
            TN_Local = treeView1.SelectedNode.FirstNode;
            string sql_Local = "SELECT EVERYLENGTH FROM CODINGCLASS WHERE CODINGCLASS='" + codingclass + "';";
            SqlDataReader sdr_Local = MySdr(sql_Local);
            while (sdr_Local.Read())
            {
                everyLength = sdr_Local[0].ToString();
            }
            sdr_Local.Dispose();
            if (textBox2.Text.Length != Convert.ToInt16(everyLength.ToCharArray()[treeView1.SelectedNode.Level].ToString()))
            {
                count_Local = -1;
            }
            if (count_Local != -1)
            {
                if (treeView1.SelectedNode.Nodes.Count > 0)
                {
                    for (int i = 0; i < treeView1.SelectedNode.Nodes.Count; i++, count_Local++)
                    {
                        if (TN_Local.Text.Split('-')[0].ToString().CompareTo(textBox2.Text) < 0)
                        {
                            TN_Local = TN_Local.NextNode;
                        }
                        else if (TN_Local.Text.Split('-')[0].ToString().CompareTo(textBox2.Text) == 0)
                        {
                            count_Local = -2;
                            break;
                        }
                        else
                        {
                            break;
                        }
                    }
                } 
            }
            return count_Local;
        }

程序可以进行单个添加子类，以及批量增加子类，当编码添加最后一位时，编码就有了真正的含义，此时要同时对coding表中进行添加数据

本程序中值得注意的几点：
1、因为我是用搞的SqlDataReader来取值的，在while（SqlDataReader.Read（））嵌套了九层，所以会造成数据库连接池的占用达到上限，所以在创建中要使用CommandBehavior.CloseConnection
2、这个模块中一个操作经常要执行多条数据依次执行，所以一定要注意使用事物，以免数据出错
3、数据表CodingSplit要ID自增列要设置从0开始