关于行业代码对应表的数据库设计及导入（4位行业代码）

淡淡的烟草味﹌ 2022-11-18 04:47 168阅读 0赞

最近使用asp.net+sqlserver+layui前端框架完成了一个小页面，其中有一个功能是需要将行业代码表导入倒数据库。看到这个行业代码表，刚开始还真是无从下手，经过查询找到一个页面是关于行业代码处理的（[word导入mysql表格\_从word得到表格数据插入数据库（6位行业代码）][word_mysql_word_6]），但是感觉这样处理不是太好，不应该让用户去填充excel表格（增加用户的工作量）。

![93b144ed32800eda132ffac167e15435.png][]

我的解决方案如下：

## 一、数据库设计 ##

![2021041216525842.png][]

## 二、文件上传功能（3层架构） ##

1、web层

<html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>行业代码对应页面</title>
    
        <link rel="stylesheet" href="../layui/css/layui.css" />
        <script src="https://cdn.bootcdn.net/ajax/libs/jquery/2.2.0/jquery.js"></script>
        <script type="text/javascript" src="../layui/layui.js"></script>
    
    </head>
    <body>
        <div style="margin: 10px 10px 10px 10px">
            <div class="layui-btn-container">
                <button class="layui-btn layui-btn-normal layui-btn-sm data-add-btn" style="background: #108ee9; padding: 0 20px;" type="button" id="upload">导入行业代码 </button>
            </div>
        </div>
        <script type="text/javascript">
    
            //1.文件上传
            layui.use('upload', function () {
    
                var $ = layui.jquery
                , upload = layui.upload;//指定允许上传的文件类型
                var loadingIndex;; //添加loading,0-2三种方式
    
                upload.render({
                    elem: '#upload'
                    , url: '../handler/UploadCode.ashx' //改成您自己的上传接口，我采用的是一般处理程序
                    , accept: 'file' //普通文件
                  , before: function () {
                        loadingIndex = layer.load(1);
                    }
                    , done: function (res) {
                        console.log(res);
                        layer.close(loadingIndex);    //返回数据关闭loading	
                        //如果上传失败
                        if (res.code > 0) {
                            if (res.code == 1) {
                                return layer.alert('上传文件为空，请选择需要上传的excel文件.', {
                                    skin: 'layui-layer-molv' //样式类名  自定义样式
                                    , anim: 2 //动画类型
                                     , icon: 1   // icon
                                });
                            } else if (res.code == 2) {
                                return layer.alert('仅 .xlsx 或 .xls文件类型可以被导入.', {
                                    skin: 'layui-layer-molv' //样式类名  自定义样式
                                   , anim: 2 //动画类型
                                    , icon: 1   // icon
                                }); 
                            } else if (res.code == 3) {
                                return layer.alert('数据源缺少必要的字段,请检查Excel数据源！', {
                                    skin: 'layui-layer-molv' //样式类名  自定义样式
                                  , anim: 2 //动画类型
                                   , icon: 1   // icon
                                });
                            } else if (res.code == 4) {
                                return layer.alert('Excel文件中没有任何数据，请填充数据！', {
                                    skin: 'layui-layer-molv' //样式类名  自定义样式
                                  , anim: 2 //动画类型
                                   , icon: 1   // icon
                                });
                            } else {
                                return layer.alert('导入失败，请检查网络是否正常.', {
                                    skin: 'layui-layer-molv' //样式类名  自定义样式
                                  , anim: 2 //动画类型
                                   , icon: 1   // icon
                                });
                            }
    
                        } else if (res.code == 0) { 
                            layer.open({
                                type: 1
                                 , title: "是否成功" //不显示标题栏   title : false/标题
                                 , closeBtn: true
                                 , area: '300px;'
                                , shade: 0.8
                                , id: 'LAY_layuipro' //设定一个id，防止重复弹出
                                 , resize: false
                                 , btn: ['确定']
                                    , btnAlign: 'c'
                                 , moveType: 1 //拖拽模式，0或者1
                                 , content: "<div style='padding: 50px; line-height: 32px; background-color: #108ee9; color: #fff; font-weight: 300;'>导入总条数：" + res.data.total + "<br>插入条数：" + res.data.flaginsert + "<br>失败条数：" + res.data.flagfail + "</div>"
                              , success: function (layero) {
                                  var btn = layero.find('.layui-layer-btn');     
                              }
                            });
                        }
                    }
                });   
            });
        </script>
    </body>
    </html>

2、一般处理程序代码

<%@ WebHandler Language="C#" Class="UploadHYCode" %>
    
    using System;
    using System.Web;
    using System.Data;
    using System.Data.OleDb;
    using BLL;
    using System.Text;
    using System.Collections.Generic;
    using System.Reflection;
    using System.IO;
    using System.Text.RegularExpressions;
    
    public class UploadHYCode : IHttpHandler {
    
        public void ProcessRequest (HttpContext context) {
            context.Response.ContentType = "text/plain";
            string Json;//声明一个json变量
    
            //HttpContext.Current.Request.Files.Count 文件个数
            if (HttpContext.Current.Request.Files.Count > 0)
            {
                string fileExtension = System.IO.Path.GetExtension(HttpContext.Current.Request.Files[0].FileName).ToLower();//文件类型
                if (fileExtension != ".xlsx" && fileExtension != ".xls")
                {
                    //不是excel文件做出提示
                    Json = "{\"code\": 2,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
                    context.Response.Write(Json);
                }
                else {
    
                    //BLL层，添加工商资源信息
                    BllBussiness bllResource = new BllBussiness();
                    //得到客户端上传的文件
                    HttpPostedFile file = HttpContext.Current.Request.Files[0];
                    //服务器端要保存的路径
                    string filePath = HttpContext.Current.Server.MapPath("~/excel/") + file.FileName;
                    file.SaveAs(filePath);
                    //1.读取Excel文件并填写到一个DataTable中
                    DataTable dt = GetExcelData(filePath, fileExtension);
                    //2.判断excel数据是否符合要求，并构建 传向数据库的数据表
                    #region 判断excel数据是否符合要求，并构建 传向数据库的数据表
                    //定义要求的字段数据
                    string[] headfields = { "门类", "大类", "中类", "小类", "类别名称"};
                    //判断dtAllMember中是否包含全部要求的字段
                    for (int j = 0; j < headfields.Length; j++)
                    {
                        //只要有一个字段不被包含，则提示"数据源缺少必要的字段"，并退出循环和整个方法
                        if (!dt.Columns.Contains(headfields[j]))
                        {
                            Json = "{\"code\": 3,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
                            context.Response.Write(Json);
                        }
                    }
                    //判断数据源中是否有数据
                    if (dt.Rows.Count == 0)
                    {
                        Json = "{\"code\": 4,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
                        context.Response.Write(Json);
                    }
                    //手动创建的新数据表工商资源数据表
                    DataTable dtAddResource = new DataTable("dt_AddResource"); //创建一个名为dt_AddResource的DataTalbe
                                                                               //为dt表内建立Column（表头）,
                    dtAddResource.Columns.Add(new DataColumn("id", typeof(System.Data.SqlTypes.SqlGuid)));
                    dtAddResource.Columns.Add(new DataColumn("parent_id", typeof(System.Data.SqlTypes.SqlGuid)));
                    dtAddResource.Columns.Add(new DataColumn("codename", typeof(string)));
                    dtAddResource.Columns.Add(new DataColumn("categoryname", typeof(string)));
                    dtAddResource.Columns.Add(new DataColumn("stime", typeof(DateTime)));
                    dtAddResource.Columns.Add(new DataColumn("isuse", typeof(int)));
    
                    int total = dt.Rows.Count;//总数据条数
                    int flaginsert = 0;//插入的数据条数
                    int flagfail = 0; //失败的数据条数
                    System.Data.SqlTypes.SqlGuid menid=System.Guid.NewGuid();
                    System.Data.SqlTypes.SqlGuid daid=System.Guid.NewGuid();
                    System.Data.SqlTypes.SqlGuid zhongid=System.Guid.NewGuid();
                    string menlei = "";
                    //从上传的Excel转换为的datatable表中取出数据，取出需要的数据放入工商资源表
                    for (int intRow = 0; intRow < dt.Rows.Count; intRow++)
                    {
                        DataRow drAddResource = dtAddResource.NewRow();
    
                        if (!string.IsNullOrEmpty(dt.Rows[intRow]["门类"].ToString().Trim()))
                        {
    
                            drAddResource["id"] = menid = System.Guid.NewGuid();
                            //drAddResource["parent_id"]= "00000000-0000-0000-0000-000000000000";
                            drAddResource["codename"] = menlei = dt.Rows[intRow]["门类"].ToString();
                            //类别名称
                            drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
                            //添加日期
                            drAddResource["stime"] = DateTime.Now;
                            //是否可用
                            drAddResource["isuse"] = 1;
                            dtAddResource.Rows.Add(drAddResource);  //将一整条数据写入表中
                        }
                        else if (!string.IsNullOrEmpty(dt.Rows[intRow]["大类"].ToString().Trim()))
                        {
                            //DataRow drAddResource = dtAddResource.NewRow();
                            drAddResource["id"] = daid = System.Guid.NewGuid();
                            drAddResource["parent_id"] = menid;
                            drAddResource["codename"] = dt.Rows[intRow]["大类"].ToString();
                            //类别名称
                            drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
                            //添加日期
                            drAddResource["stime"] = DateTime.Now;
                            //是否可用
                            drAddResource["isuse"] = 1;
                            dtAddResource.Rows.Add(drAddResource);  //将一整条数据写入表中
                        }
                        else if (!string.IsNullOrEmpty(dt.Rows[intRow]["中类"].ToString().Trim()) && !string.IsNullOrEmpty(dt.Rows[intRow]["小类"].ToString().Trim()))
                        {
                            drAddResource["id"] = zhongid = System.Guid.NewGuid();
                            drAddResource["parent_id"] = daid;
                            drAddResource["codename"] = menlei + dt.Rows[intRow]["小类"].ToString();
                            //类别名称
                            drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
                            //添加日期
                            drAddResource["stime"] = DateTime.Now;
                            //是否可用
                            drAddResource["isuse"] = 1;
                            dtAddResource.Rows.Add(drAddResource);  //将一整条数据写入表中
                        } else if (!string.IsNullOrEmpty(dt.Rows[intRow]["中类"].ToString().Trim()) && string.IsNullOrEmpty(dt.Rows[intRow]["小类"].ToString().Trim())) {
                                 drAddResource["id"] = zhongid = System.Guid.NewGuid();
                            drAddResource["parent_id"] = daid;
                            drAddResource["codename"] = dt.Rows[intRow]["中类"].ToString();
                            //类别名称
                            drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
                            //添加日期
                            drAddResource["stime"] = DateTime.Now;
                            //是否可用
                            drAddResource["isuse"] = 1;
                            dtAddResource.Rows.Add(drAddResource);  //将一整条数据写入表中
                        }
                        else {
                            drAddResource["id"] = System.Guid.NewGuid();
                            drAddResource["parent_id"] = zhongid;
                            drAddResource["codename"] = menlei + dt.Rows[intRow]["小类"].ToString();
                            //类别名称
                            drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
                            //添加日期
                            drAddResource["stime"] = DateTime.Now;
                            //是否可用
                            drAddResource["isuse"] = 1;
                            dtAddResource.Rows.Add(drAddResource);  //将一整条数据写入表中
                        }
                    }
                    #endregion
                    DataColumnCollection dcc = dtAddResource.Columns; //列数
                                                                      // flaginsert = dtAddResource.Rows.Count;
                    bool i = bllResource.ImportHycode(dtAddResource,dcc);
    
                    //3.将筛选过的数据表导入数据库
                    if (i==true)
                    {
                        flaginsert = dtAddResource.Rows.Count;
                        flagfail =dt.Rows.Count-flaginsert;
                        //返回结果
                        // Json = "{\"code\": 0,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
                        Json = "{\"code\": 0,\"msg\": \"\",\"data\": {\"flaginsert\": "+flaginsert+",\"flagfail\": "+flagfail+",\"total\": "+total+"}}";
                        context.Response.Write(Json);
                    }
                    else {
    
                        //返回结果
                        Json = "{\"code\": 5,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
                        context.Response.Write(Json);
                    }
    
                }
    
            }
            else {
                //没有文件，请选择文件
                Json = "{\"code\": 1,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
                context.Response.Write(Json);
            }
        }
    
        public bool IsReusable {
            get {
                return false;
            }
        }
        private DataTable GetExcelData(string filePath, string fileExtension)
        {
            //DataSet ds = new DataSet();
            //定义一个DataTable数据表
            DataTable dt = null;
            string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ;
            string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'";
            string queryStr = "SELECT * FROM [Sheet1$]";
            OleDbConnection conn03 = new OleDbConnection(connStr03);
            OleDbConnection conn07 = new OleDbConnection(connStr07);
            if (fileExtension == ".xls")
            {
                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
                try
                {
                    //把Excel数据填充给DataTable
                    dt = new DataTable();
                    myAdapter.Fill(dt);
                    //返回数据表
                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    myAdapter.Dispose();
                    conn03.Close();
                    conn03.Dispose();
                    //删除上传的Excel文件（因为该文件的存在会占用多余的网站空间）
                    if (File.Exists(filePath))
                    {
                        File.Delete(filePath);
                    }
                }
            }
            else if (fileExtension == ".xlsx")
            {
                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn07);
                try
                {
                    //把Excel数据填充给DataTable
                    dt = new DataTable();
                    myAdapter.Fill(dt);
                    //返回数据表
                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    myAdapter.Dispose();
                    conn07.Close();
                    conn07.Dispose();
                    //删除上传的Excel文件（因为该文件的存在会占用多余的网站空间）
                    if (File.Exists(filePath))
                    {
                        File.Delete(filePath);
                    }
                }
            }
            return dt;
        }
    
    }

3、BLL业务逻辑层的方法

#region 批量导入行业代码
     public bool ImportHycode(DataTable dt, DataColumnCollection dtColum)
     {
        return daoBussiness.ImportHycode(dt, dtColum);
      }
    #endregion

4、DAL数据访问层代码

#region 批量导入行业代码
            public bool ImportHycode(DataTable dt, DataColumnCollection dtColum)
            {
                //定义一个整型标记变量
                bool result;
                //调用sqlHelper的"批量导入datatable表"的方法
                result = sqlHelper.InsertTable(dt, "chiye_hangyecode", dtColum);
                //返回结果
                return result;
            }
    #endregion

5、sqlhelper方法

#region 批量导入DataTable
            /// <summary>批量导入DataTable
            /// 批量导入DataTable
            /// </summary>
            /// <param name="dt">DataTable数据表</param>
            /// <param name="tableName">表名</param>
            /// <param name="dtColum">数据列集合</param>
            /// <return>Boolean值:true成功，false失败</return>
            public Boolean InsertTable(DataTable dt, string tableName, DataColumnCollection dtColum)
            {
                using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required))
                {
                    using (SqlBulkCopy sqlBC = new SqlBulkCopy(strConn1, SqlBulkCopyOptions.KeepIdentity))
                    {
                        //一次批量的插入的数据量：sqlBC.BatchSize = 1000;
                        sqlBC.BatchSize = 1000;
                        //设置要批量写入的表
                        sqlBC.DestinationTableName = tableName;
    
                        for (int i = 0; i < dtColum.Count; i++)
                        {
                            sqlBC.ColumnMappings.Add(dtColum[i].ColumnName.ToString(), dtColum[i].ColumnName.ToString());
                        }
                        try
                        {
                            //批量写入
                            sqlBC.WriteToServer(dt);
                            scope1.Complete();
                            return true;
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                }
            }
    #endregion

至此整个行业代码数据库设计及导入就完成了，数据库数据如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hemhhb2p1YW4_size_16_color_FFFFFF_t_70][]

欢迎各位高手一起探讨更好滴方法。

[word_mysql_word_6]: https://blog.csdn.net/weixin_30831261/article/details/113301607
[93b144ed32800eda132ffac167e15435.png]: /images/20221022/9ea492f6f72c4dcaaae7ca98e31271c0.png
[2021041216525842.png]: /images/20221022/05ea4411c60640f7b672573132e61c0e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hemhhb2p1YW4_size_16_color_FFFFFF_t_70]: /images/20221022/1c3b9a3c0e97416ebdf62eefc7b30651.png