AgileEAS开发中关于实体类和DataTable的取舍——用linq+orm来缩短你的开发周期

作为一个专业抱大腿的开发者，我一直以来都认为DataTable是.Net世界里无敌的存在，它的自由、洒脱、包容。。。这些优秀的品质一直深深的吸引着我。。。

没错，对于传统的.Net应用（silverlight当然除外），使用DataTable来承载数据源，对GridView进行数据绑定，批量增删改是一种万金油的战术，随时随地的添加行、列，要是再配合上Aspose.Cell的模板列绑定报表，可谓神器也！（我自己也是这么做的）。

但是在各种SOA中间件潮水一样涌入后，我们在借助开发平台的帮助下，为了缩短数据库交互业务开发在整个开发过程中所占的时间，往往会使用开发工具提供的技术支持，这里面实体类所起到的作用是相当大的。

试想一下，在建立数据库的同时，开发工具会比照数据库结构给你一个和数据库一模一样的实体类库，这个实体类库包含了拓展函数诸如：Insert（）；UpDate（）；Delete（）。。。这些原本需要使用Sql语句或存储过程实现的数据库业务，任何时候你实例化一个 var xxx = new DataEntity(); 之后，你都不需要在仔细考虑这条数据到底有多少个字段，每个字段是干嘛用的（建立数据库时写个中文备注），而且需要增删改的时候直接调用拓展函数即可，这确实大大的减少了我们在开发中浪费在数据库迷宫中的时间。

我曾经就要求我的同事们在对数据库查询的时候禁止使用 select * from xxx 这样的方法读数据库，这会给后来的维护者带来极大麻烦和困扰。。。尤其对于喜欢写insert语句的同学啊。。。数据库结构变了之后你不着急么。。。。

貌似有点跑偏了，题目中写的用linq+orm，linq是啥不用多说，orm这里面是EAS.NET开发平台带的一个实体类生成工具，通过连接数据库，自动生成该数据库对应的实体类，以及进一步自动生成基础crud操作界面，详细方法可以看他的官网或找它的开发团队。。。我们看下面这个页面。。。

这是一个很典型的汇总查询应用界面，通过汇总查询，可以将结果添加到明细表，明细表的数据批量导入后，可以在右侧查询当前的实时库存。这样一个界面在传统的开发过程中，你可能需要写一个查询函数，定义个公用的DT，写一个汇总函数，写一个更新函数blblblblblblbl

如果使用ORM设计器又如何呢，

step1、用orm设计器生成实体类

step2、用formdesigner做一个基础的界面，这里面包含了基础查询方法和绑定方法，dataGridView也是现成的了

step3、使用linq进一步细化业务。

代码如下：

using System; 
using System.Collections; 
using System.Collections.Generic; 
using System.Drawing; 
using System.ComponentModel; 
using System.Data; 
using System.Windows.Forms; 
using System.IO; 
using System.Linq; 
using System.Globalization; 
using System.Runtime.Serialization.Formatters.Binary; 
using System.Text; 

using EAS.Data; 
using EAS.Data.ORM; 
using EAS.Data.Access; 
using EAS.Modularization; 

using EAS.Services; 
using EAS.Data.Linq;
using IronMES.Entities.HBWL;
using IronMES.BLL;
using IronMES.Entities;
using IronMES.WinUI.StockManager.HBWL;
using IronMES.WinUI.Helper;


namespace IronMES.WinUI.HBWL
{
    [Module("84972d17-2274-4484-bde3-3106a1760481", "汽运检斤接口", "将汽车运输的检斤量批量导入库存管理系统")]
    public partial class JL_CARWEIGHTHISTORYList : UserControl
    {
        IList<JL_CARWEIGHTHISTORY> vList = null;
        IList<JL_CARWEIGHTHISTORY> vListGather = null;
        IList<JL_CARWEIGHTHISTORY> pList = null;
        IList<vSMInventory> iList = null;

        public JL_CARWEIGHTHISTORYList()
        {
            InitializeComponent();
            this.dataGridView1.AutoGenerateColumns = false;
            this.dataGridView1.DataSource = this.datasourcedataGridView1;

            this.dataGridView2.AutoGenerateColumns = false;
            this.dataGridView2.DataSource = this.bindingSource2;
            this.dataGridView3.AutoGenerateColumns = false;
        }

        [ModuleStart]
        public void StartEx()
        {
        }

        /// <summary>
        /// 显示记录。
        /// </summary>
        IList<JL_CARWEIGHTHISTORY> DisplayList
        {
            get
            {
                return this.datasourcedataGridView1.DataSource as IList<JL_CARWEIGHTHISTORY>;
            }
        }


        public void GetDataList(DateTime dateStart, DateTime dateEnd,string fs_material, string fs_carno, string fs_downloadaddress, string fs_sendername, string fs_receivername, string fs_materialname, string fs_cg_minenum, string cg_order_num, string send_place, string cyunit)
        {
            this.vList = ServiceContainer.GetService<IHBWLService>().GetDataList(dateStart, dateEnd,fs_material, fs_carno, fs_downloadaddress, fs_sendername, fs_receivername, fs_materialname, fs_cg_minenum, cg_order_num, send_place, cyunit).ToList();
        }

        public void GetInventoryList()
        {
            DataEntityQuery<vSMInventory> query = DataEntityQuery<vSMInventory>.Create();
            var p = from item in query
                    orderby item.FID descending
                    select item;
            this.iList = p.ToList();
        }

        private void dataPager_PageChanged(object sender, EventArgs e)
        {
            //this.datasourcedataGridView1.DataSource = this.vList.Skip(this.dataPager.Skip).Take(this.dataPager.Take).ToList(); 
        }

        //添加本条记录到批量录入的准备集中
        private void btnAddOne_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.RowCount > 0 && this.dataGridView1.CurrentRow != null)
            {
                var dataEntity = this.dataGridView1.CurrentRow.DataBoundItem as JL_CARWEIGHTHISTORY;
                if (dataEntity.FS_WEIGHTBILLNO != null)
                {
                    pList.Add(dataEntity);
                }
                else
                {
                    var p = from item in vList
                            where item.FS_MATERIALNAME == dataEntity.FS_MATERIALNAME
                            && item.FS_SENDERNAME == dataEntity.FS_SENDERNAME
                            && item.FS_RECEIVERNAME == dataEntity.FS_RECEIVERNAME
                            select item;
                    foreach (var item in p.ToList())
                    {
                        pList.Add(item);
                    }
                }
            }
            this.bindingSource2.DataSource = pList.ToList();
            this.dataGridView2.DataSource = this.bindingSource2;
        }

        //添加所有GridView1中的记录到批量录入的准备集中
        private void btnAddAll_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.RowCount > 0)
            {
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    var dataEntity = dataGridView1.Rows[i].DataBoundItem as JL_CARWEIGHTHISTORY;
                    if (dataEntity.FS_WEIGHTBILLNO != null)
                    {
                        pList.Add(dataEntity);
                    }
                    else
                    {
                        var p = from item in vList
                                where item.FS_MATERIALNAME == dataEntity.FS_MATERIALNAME
                                && item.FS_SENDERNAME == dataEntity.FS_SENDERNAME
                                && item.FS_RECEIVERNAME == dataEntity.FS_RECEIVERNAME
                                select item;
                        foreach (var item in p.ToList())
                        {
                            pList.Add(item);
                        }
                    }
                }
            }
            this.bindingSource2.DataSource = pList.ToList();
            this.dataGridView2.DataSource = this.bindingSource2;
        }

        private void btnRemoveOne_Click(object sender, EventArgs e)
        {
            if (this.dataGridView2.RowCount > 0)
            {
                pList.Remove(dataGridView2.CurrentRow.DataBoundItem as JL_CARWEIGHTHISTORY);
                dataGridView2.AllowUserToDeleteRows = true;
                dataGridView2.Rows.Remove(dataGridView2.CurrentRow);
                //dataGridView2.DataSource = pList.ToList();
            }
        }

        private void btnRemoveAll_Click(object sender, EventArgs e)
        {
            if (this.dataGridView2.RowCount > 0)
            {
                pList.Clear();
                this.dataGridView2.DataSource = pList.ToList();
            }
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            this.GetDataList(this.dateTimePickerStart.Value, this.dateTimePickerEnd.Value, this.tbFS_MATERIAL.Text.Trim(),this.tbFS_CARNO.Text.Trim(), this.tbFS_DOWNLOADADDRESS.Text.Trim(), this.tbFS_SENDERNAME.Text.Trim(), this.tbFS_RECEIVERNAME.Text.Trim(), this.tbFS_MATERIALNAME.Text.Trim(), this.tbFS_CG_MINENUM.Text.Trim(), this.tbCG_ORDER_NUM.Text.Trim(), this.tbSEND_PLACE.Text.Trim(), this.tbCYUNIT.Text.Trim());
            pList = new List<JL_CARWEIGHTHISTORY>();
            this.dataGridView2.DataSource = pList.ToList();

            if (this.cbGather.Checked == true)
            {
                var v = from item in vList
                        group item by item.FS_MATERIALNAME into materialGroup
                        select new
                        {
                            materialGroup.Key,
                            senderGroup = from item2 in materialGroup
                                          group item2 by item2.FS_SENDERNAME into senderGroup
                                          select new
                                          {
                                              senderGroup.Key,
                                              receiverGroup = from item3 in senderGroup
                                                              group item3 by item3.FS_RECEIVERNAME into receiverGroup
                                                              select new
                                                              {
                                                                  receiverGroup.Key,
                                                                  FS_NETWEIGHT = receiverGroup.Sum(x => x.FS_NETWEIGHT),
                                                                  FS_GROSSWEIGHT = receiverGroup.Sum(x => x.FS_GROSSWEIGHT),
                                                                  FS_TAREWEIGHT = receiverGroup.Sum(x => x.FS_TAREWEIGHT),
                                                                  FCount = receiverGroup.Count()
                                                              }
                                          }
                        };
                var _vList = v.ToList();
                if (_vList.Count > 0)
                {
                    this.vListGather = new List<JL_CARWEIGHTHISTORY>();
                    foreach (var item in _vList)
                    {

                        foreach (var item2 in item.senderGroup)
                        {
                            foreach (var item3 in item2.receiverGroup)
                            {
                                var dataEntity = new JL_CARWEIGHTHISTORY();
                                dataEntity.FS_MATERIALNAME = item.Key;
                                dataEntity.FS_SENDERNAME = item2.Key;
                                dataEntity.FS_RECEIVERNAME = item3.Key;
                                dataEntity.FS_NETWEIGHT = item3.FS_NETWEIGHT;
                                dataEntity.CG_SHIPPED_COUNT = item3.FCount;
                                dataEntity.FS_TAREWEIGHT = item3.FS_TAREWEIGHT;
                                dataEntity.FS_GROSSWEIGHT = item3.FS_GROSSWEIGHT;
                                vListGather.Add(dataEntity);
                            }
                        }
                    }
                }
                this.datasourcedataGridView1.DataSource = this.vListGather.ToList();
            }
            else
                this.datasourcedataGridView1.DataSource = this.vList.ToList();
            DataGridViewHelper.DisplayNullColumn(dataGridView1);

            this.GetInventoryList();
            this.dataGridView3.DataSource = iList.ToList();
        }

        private void btnPrint_Click(object sender, EventArgs e)
        {
            string[] titles = new string[] { "汽车计量查询结果", "批量导入明细", "实时库存（导入前）" };
            DataGridView[] dataGridViews = new DataGridView[] { dataGridView1, dataGridView2, dataGridView3 };
            ExcelMaker.OutToExcelFromDataGridView("汽运检斤接口导出表", titles, dataGridViews, true);
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            EAS.Application.Instance.CloseModule();
        }

        #region 报表相关

        /// <summary> 
        /// 报表。 
        /// </summary> 
        protected EAS.Explorer.Entities.Report Report
        {
            get
            {
                if (this.m_Report == null)
                {
                    this.m_Report = new EAS.Explorer.Entities.Report();
                    this.m_Report.Name = string.Empty;
                }
                return this.m_Report;
            }
        }
        EAS.Explorer.Entities.Report m_Report;

        /// <summary> 
        /// 打印窗体。 
        /// </summary> 
        protected EAS.Report.Controls.PrintViewDialog PrintForm
        {
            get
            {
                if (this.m_PrintForm == null)
                {
                    this.m_PrintForm = new EAS.Report.Controls.PrintViewDialog();
                }
                return this.m_PrintForm;
            }
        }
        EAS.Report.Controls.PrintViewDialog m_PrintForm;

        #endregion

        /// <summary>
        /// 点击后批量导入库存信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            HBWLInputSet inputEditor = new HBWLInputSet();
            var inputs = new HBWLInputs();
            inputs.inputDatas = new List<InputData>();
            inputs.FAmount = 0;
            inputs.FComMaterial = "";
            inputs.FComMaterialCode = "";
            inputs.FCount = 0;
            foreach (var item in pList)
            {
                inputs.FAmount += item.FS_NETWEIGHT;
                inputs.FCount++;
                inputs.FComMaterial = item.FS_MATERIALNAME;
                inputs.FComMaterialCode = item.FS_MATERIAL;
                var inputData = new InputData();
                inputData.FBillNo = item.FS_WEIGHTBILLNO;
                inputData.FWeight = item.FS_NETWEIGHT;
                inputs.inputDatas.Add(inputData);
            }
           
            inputEditor.DataEntity = inputs;

            if (inputEditor.ShowDialog() == DialogResult.OK)
            {
                this.btnQuery_Click(sender, e);
            }

        }
    }
}

里面其实只是用了一个简单的linq汇总，其他的大部分代码都是设计器自动生成的，在进行批量数据导入的时候，使用了实体类的拓展Insert方法，代码如下。

 private bool SaveWeightBill(InputData inputData)
        {
            string guid = Guid.NewGuid().ToString();
            var query = DataEntityQuery<SMStockBill>.Create();
            var p = from item in query
                    where item.FHBWLBillNo == inputData.FBillNo
                    select item;
            var list = p.ToList();
            if (list.Count >0)
            {
                return false;
            }
            var dataEntity = new SMStockBill();
            dataEntity.FBillerID = (EAS.Application.Instance.Session.Client as EAS.Explorer.IAccount).LoginID;
            dataEntity.FDate = this.dateTimePickerFDate.Value;
            dataEntity.FDateTime = DateTime.Now;
            dataEntity.FSCStockID = Convert.ToInt32(this.cbxFBillType.SelectedValue) == 1 ? 0 : Convert.ToInt32(this.cbxFSCStockID.SelectedValue);
            dataEntity.FGuid = guid;
            dataEntity.FHBWLBillNo = inputData.FBillNo;
            dataEntity.FItemID = (int)this.cbxFName.SelectedValue;
            dataEntity.FNote = this.tbFNote.Text;
            dataEntity.FQty = inputData.FWeight;
            dataEntity.FDCStockID = Convert.ToInt32(this.cbxFBillType.SelectedValue) == 2 ? 0 : Convert.ToInt32(this.cbxFDCStockID.SelectedValue);
            dataEntity.FStockBillTypeID = (int)this.cbxFBillType.SelectedValue;
            dataEntity.FWet = Convert.ToDecimal(this.tbFWet.Text);

            dataEntity.Insert();

            InventoryMaker.SaveInventory(dataEntity);
            return true;
        }

左边2个dgv其实是一样的，使用了一个隐藏列的公共方法，我都是这么干的，代码如下。

 /// <summary>
         /// 将dataGirdView里面全是空的列隐藏
         /// </summary>
         /// <param name="dataGirdView"></param>
         public static void DisplayNullColumn(DataGridView dataGirdView)
         {
             for (int i = 0; i < dataGirdView.Columns.Count; i++)
             {
                 dataGirdView.Columns[i].Visible = true;
                 bool isNull = true;
                 for (int j = 0; j < dataGirdView.Rows.Count; j++)
                 {
                     //如果不是日期格式，不为空即可
                     if (dataGirdView.Rows[j].Cells[i].ValueType != typeof(DateTime) && dataGirdView.Rows[j].Cells[i].Value != null && dataGirdView.Rows[j].Cells[i].Value.ToString() != string.Empty)
                     {
                         isNull = false;
                         continue;
                     }
                     //如果是日期格式需要大于最小时间
                     else if (dataGirdView.Rows[j].Cells[i].ValueType == typeof(DateTime) && (DateTime)dataGirdView.Rows[j].Cells[i].Value >= new DateTime(1973, 1, 1))
                     {
                         isNull = false;
                         continue;
                     }
                 }
                 if (isNull)
                     dataGirdView.Columns[i].Visible = false;
             }
         }

也可能有人会遇到必须使用sql语句查询的尴尬情况（比如我需要访问一个某单位买来的数据库。。。就是这样）

为了方便继续用formdesigner设计出的便宜代码～～～在读取数据的服务中加下面这一段即可完美转换DataTable为IList

 public IList<IronMES.Entities.Runtime.History> HistoryNonCyclicList(DateTime dateStart, DateTime dateEnd, string tagname)
        {
            string sqlStr = "SELECT * FROM History WHERE" +
                        " (TagName = '" + tagname + "') AND (DateTime >= '"
                        + dateStart + "') AND  (DateTime < '" + dateEnd + "') "
                        + "AND (value <> 0) AND (wwQualityRule = 'Good')";
            var dt = da.QueryDataTable(sqlStr);
            var list = new List<History>();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    var entity = new History();
                    foreach (Column prop in entity.GetColumns())
                    {
                        entity.SetValue(prop, Convert.ChangeType(item[prop.Name],prop.Property.PropertyType));
                    }
                    list.Add(entity);
                }
            }
            return list;

        }

通过使用orm实体类，form设计器，再配合上linq，我个人感觉在类似上面显示的界面业务时极大的缩短了每一个模块的开发周期，让开发者可以节约更多的时间到数据库设计、ui交互设计等更重要的工作中。

由于自己水平很有限，所以没办法从根本上解释一下使用DataTable和使用实体类进行查询业务时的区别，比如效率啊、资源占用啊啥的，我觉得这些和平时的开发没啥大影响。。。（高端请无视我。。。）