开篇

我之前发过一篇博文《》，不少朋友向我要源码学习，后来久而久之忘记回复了。今天我再分享一个进销存系统，只为学习，没有复杂的框架和设计模式，有的是我个人的理解，大家互相探讨技术才会提高。当然我的命名不是很规范，兄弟们凑合着看。:)

思想和架构

在传统的三层架构思想上扩展出N层架构，将业务逻辑层换成WCF服务。抽象工厂的引入提高了程序的扩展性，单利+缓存+反射则提升了程序的性能。数据库则换成了Oracle，所以相应的数据访问层也换成了OracleDal，当然你完全可以写SqlServerDal，因为我数据访问层接口都已定义好。

 

界面和控件的设计美化

总体思路和流程---数据库Oracle

数据库既然选择了Oracle，当然先必须安装好Oracle,然后再装Plsql，这一步也是很简单的。不清楚的话，可去查找资料。

对Oracle而言，数据库已服务形式存在，有几个数据库就对应几个服务，删除了数据库后相应的服务也没了，其次一个兼容程序监听着服务。这些都可以自行配置，我不啰嗦了，毕竟我也不熟。我把Oracle脚本导出了，大家只要复制到Commad Window里粘贴即可，但前期创建表空间和用户我还是稍微提一下：

• 首先用你用plsql连接一个服务（数据库Orcl），用Connect as SysDBA进入。
• 创建表空间：注意路径一定要已经存在。

create tablespace invoicingloggingdatafile 'C:\oracle\product\10.2.0\db_1\oradata\invoicing.dbf'size 32Mautoextend onnext 32M maxsize 1024MEXTENT MANAGEMENT LOCAL;

 

• 找到左下角侧用户(Users)，创建用户Invoicing，密码：Invoicing,分配权限：dba，connect
• 用新创建的用户名和密码重新登录。
• 找到Command Window，把我提供给你的脚本复制进去就OK了。

 

总体思路和流程---数据访问层IDAL

• 一个通用的泛型接口：

public interface IBaseService
     
       where T :class    {        List
      
        GetEntities(string sqlWhere);        T GetOneEntityByID(int id);        T AddEntity(T entity);        bool UpdateEntity(T entity);        bool DeleteEntity(string sqlWhere);    }
      
     

• 某个数据访问层接口实继承这个泛型接口

public interface ICommodityService:IBaseService
     
          {          }
     

总体思路和流程---抽象工厂Abstract

• public abstract class DalFactory    {        public abstract IDAL.ICommodityService CommdityDal        {            get;        }        public abstract IDAL.IPurchaseCommodityService PurchaseCommdityDal        {            get;        }        public abstract IDAL.IPurchaseOrdersService PurchaseOrderDal        {            get;        }        public abstract IDAL.ISalesCommodityService SalesCommodityDal        {            get;        }        public abstract IDAL.ISalesOrdersService SalesOrderDal        {            get;        }        public abstract IDAL.IUserService UserDal        {            get;        }    }

  总体思路和流程---数据访问层Dal

• 为了提高效率，可以考虑缓存

public override IDAL.ICommodityService CommdityDal        {            //缓存里拿            get {                OracleDAL.CommodityService instance = System.Web.HttpRuntime.Cache["CommodityDal"] as OracleDAL.CommodityService;                if (instance == null)                {                    instance = new OracleDAL.CommodityService();                    System.Web.HttpRuntime.Cache.Add("CommodityDal", instance, null, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.Normal, null);                }                return instance;            }                    }

 

• OracleHelper和System.Data.OracleClient来实现数据访问层

namespace Insigma.Eyes.PSS.OracleDAL{    public class CommodityService:ICommodityService    {        public List
     
       GetEntities(string sqlWhere)        {            string sql = string.Format("select * from commodity where 1=1 {0}",sqlWhere);            List
      
        listCommodities = new List
       
        ();            //Using 限定对象使用的范围在花括号里面，出了花括号后释放资源            using (OracleDataReader odr=OracleHelper.ExecuteReader(OracleHelper.ConnectionString, CommandType.Text, sql, null))            {                while (odr.Read())                {                    Model.CommodityModel commodity = new Model.CommodityModel();                    commodity.ID = odr.GetInt32(0);                    commodity.Name = odr.IsDBNull(1) ? "" : odr.GetString(1);                    commodity.Type = odr.IsDBNull(2) ? "" : odr.GetString(2);                    commodity.Manufacturer = odr.IsDBNull(3) ? "" : odr.GetString(3);                    commodity.Inventory = odr.IsDBNull(4) ? 0 : odr.GetInt32(4);                    commodity.UnitPrice = odr.IsDBNull(5) ? 0 : odr.GetDecimal(5);                    commodity.Unit = odr.IsDBNull(6) ? "" : odr.GetString(6);                    listCommodities.Add(commodity);                }            }            return listCommodities;        }        public Model.CommodityModel GetOneEntityByID(int id)        {            string sqlWhere = string.Format(" and id={0}",id);            List
        
          list = GetEntities(sqlWhere);            return list.SingleOrDefault(c => c.ID == id);        }        private int GetNewID()        {            string sql = "select s_commodity.nextval from dual";            return int.Parse(OracleHelper.ExecuteScalar(OracleHelper.ConnectionString,CommandType.Text,sql,null).ToString());        }        public Model.CommodityModel AddEntity(Model.CommodityModel entity)        {            entity.ID = GetNewID();            string sql = string.Format(@"insert into Commodity(ID,Name,Type,Manufacturer,Inventory,UnitPrice,Unit)                                                     values({0},'{1}','{2}','{3}',{4},{5},'{6}')", entity.ID, entity.Name, entity.Type, entity.Manufacturer, entity.Inventory, entity.UnitPrice, entity.UnitPrice);            if (OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionString,CommandType.Text,sql,null)>0)            {                return entity;            }            else            {                return null;            }        }        public bool UpdateEntity(Model.CommodityModel entity)        {            string sql = string.Format("update Commodity set Name='{0}',Type='{1}',Manufacturer='{2}',Inventory={3},UnitPrice={4},Unit='{5}' where ID={6}",                                                  entity.Name, entity.Type, entity.Manufacturer, entity.Inventory, entity.UnitPrice, entity.Unit, entity.ID);            return OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionString,CommandType.Text,sql,null)>0;         }        public bool DeleteEntity(string sqlWhere)        {            string sql = string.Format("delete form Commodity where 1=1 {0}",sqlWhere);            return OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionString, CommandType.Text, sql, null) > 0;        }          }}
        
       
      
     

总体思路和流程---业务逻辑层WCF

• wcf是什么，最简单理解就是接口，契约，当然你可以更加深入研究。我学的也不深。

namespace Insigma.Eyes.PSS.BLLWCFService{    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码和配置文件中的接口名“ICommodityManagerService”。    [ServiceContract]    public interface ICommodityManagerService    {        [OperationContract]        [FaultContract(typeof(Exception))]        CommodityModel[] GetCommodities(string name,string type,string manufacturer,string priceLow,string priceHigh);        [OperationContract]        CommodityModel[] GetCommoditiesByCondition(string condition);        [OperationContract]        CommodityModel GetOneCommodity(int id);        [OperationContract]        [FaultContract(typeof(Exception))]        CommodityModel AddCommodity(CommodityModel oneCommodity);        [OperationContract]        [FaultContract(typeof(Exception))]        bool UpdateCommodity(Model.CommodityModel commodity);    }}

• 实现上面定义的接口契约：

public class PurchaseManagerService : IPurchaseManagerService    {                //        private AbstractFactory.DalFactory dataFactory = null;        public PurchaseManagerService()        {            dataFactory = DefaultProviderDal.DefaultDataProviderFactory;        }               public Model.PurchaseOrdersModel[] GetPurchaseOrders(string orderNumber, string orderDateStart, string orderDateEnd, string status)        {            string sqlWhere = "";            if (!string.IsNullOrWhiteSpace(orderNumber))            {                sqlWhere += string.Format(" and orderNumber like '%{0}%'", orderNumber);            }            if (!string.IsNullOrWhiteSpace(orderDateStart))            {                try                {                    DateTime dt = DateTime.Parse(orderDateStart);                    sqlWhere += string.Format(" and orderDate>=to_date('{0}','yyyy-MM-dd')", dt.ToString("yyyy-MM-dd"));                }                catch                {                    Exception oe = new Exception();                    throw new FaultException
     
      (oe, "查询条件开始时间有误!");                }            }            if (!string.IsNullOrWhiteSpace(orderDateEnd))            {                try                {                    DateTime dt = DateTime.Parse(orderDateEnd);                    sqlWhere += string.Format(" and orderDate<=to_date('{0}','yyyy-MM-dd')", dt.ToString("yyyy-MM-dd"));                }                catch                {                    Exception oe = new Exception();                    throw new FaultException
      
       (oe, "查询条件截至时间有误!");                }            }            if (!string.IsNullOrWhiteSpace(status))            {                sqlWhere += string.Format(" and Status='{0}'", status);            }            //IDAL.IPurchaseOrdersService purchaseOrdersService = new OracleDAL.PurchaseOrderService();            //return purchaseOrdersService.GetEntities(sqlWhere).ToArray();            return dataFactory.PurchaseOrderDal.GetEntities(sqlWhere).ToArray();        }        public Model.PurchaseCommodityModel[] GetPurchaseCommoditiesByID(int purchaseID)        {            string sqlWhere = string.Format(" and PurchaseOrderID={0}",purchaseID);//看看数据库里面的字段            //IDAL.IPurchaseCommodityService purchaseCommodityService =new OracleDAL.PurchaseCommodityService();            //return purchaseCommodityService.GetEntities(sqlWhere).ToArray();            return dataFactory.PurchaseCommdityDal.GetEntities(sqlWhere).ToArray();        }        public Model.PurchaseCommodityModel AddPurchaseCommodityModel(Model.PurchaseCommodityModel onePurchaseCommodity)        {            //return new OracleDAL.PurchaseCommodityService().AddEntity(onePurchaseCommodity);            return dataFactory.PurchaseCommdityDal.AddEntity(onePurchaseCommodity);        }       //几个ID要分清楚        public bool PostPurchaseOrder(int id)        {            Model.PurchaseOrdersModel oneOrder=GetOnePurchaseOrder(id);            if (oneOrder.Status.Equals("已入库"))            {                Exception oe = new Exception();                throw new FaultException
       
        (oe,"订单已经提交，请务重复提交");            }            List
        
          purchaseCommoditiesList = GetPurchaseCommoditiesByID(id).ToList();            IDAL.ICommodityService commodityService = new OracleDAL.CommodityService();            foreach (Model.PurchaseCommodityModel onePurchaseCommodity in purchaseCommoditiesList)            {                Model.CommodityModel commodityModel = new Model.CommodityModel();                commodityModel.ID = onePurchaseCommodity.CommodityID;                commodityModel.Manufacturer = onePurchaseCommodity.CommodityManufacturer;                commodityModel.Name = onePurchaseCommodity.CommodityName;                commodityModel.Type = onePurchaseCommodity.CommodityType;                commodityModel.Unit = onePurchaseCommodity.CommodityUnit;                commodityModel.UnitPrice = onePurchaseCommodity.CommodityUnitPrice;                commodityModel.Inventory = onePurchaseCommodity.CommodityInventory + onePurchaseCommodity.Count;                //这儿不会出现异常了吧，否则要回滚                commodityService.UpdateEntity(commodityModel);            }            oneOrder.Status = "已入库";            return new OracleDAL.PurchaseOrderService().UpdateEntity(oneOrder);        }        public Model.PurchaseOrdersModel GetOnePurchaseOrder(int id)        {            //return new OracleDAL.PurchaseOrderService().GetOneEntityByID(id);            return dataFactory.PurchaseOrderDal.GetOneEntityByID(id);        }        public Model.PurchaseCommodityModel GetOnePurchaseCommoditiesByID(int purchaseCommodityID)        {            //return new OracleDAL.PurchaseCommodityService().GetOneEntityByID(purchaseCommodityID);            return dataFactory.PurchaseCommdityDal.GetOneEntityByID(purchaseCommodityID);        }        public bool UpdatePurchaseCommodity(Model.PurchaseCommodityModel model)        {            //return new OracleDAL.PurchaseCommodityService().UpdateEntity(model);            return dataFactory.PurchaseCommdityDal.UpdateEntity(model);        }        public bool DeletePurchaseCommodity(int id)        {            string sqlWhere = " and id=" + id;            //return new OracleDAL.PurchaseCommodityService().DeleteEntity(sqlWhere);            return dataFactory.PurchaseCommdityDal.DeleteEntity(sqlWhere);        }        public Model.PurchaseOrdersModel AddPurchaseOrder(Model.PurchaseOrdersModel purchaseOrder)        {            //return new OracleDAL.PurchaseOrderService().AddEntity(purchaseOrder);            return dataFactory.PurchaseOrderDal.AddEntity(purchaseOrder);        }        public bool UpdatePurchaseOrder(Model.PurchaseOrdersModel onePurchaseOrder)        {            //return new OracleDAL.PurchaseOrderService().UpdateEntity(onePurchaseOrder);            return dataFactory.PurchaseOrderDal.UpdateEntity(onePurchaseOrder);        }        public bool DeletePurchaseCommoditiesByPurchaseOrderID(int purchaseOrderID)        {            string sqlWhere = " and PurchaseOrderID=" + purchaseOrderID;            //调用另一个模块，调用BLL比较好            //return new OracleDAL.PurchaseCommodityService().DeleteEntity(sqlWhere);           return dataFactory.PurchaseCommdityDal.DeleteEntity(sqlWhere);        }        public bool DeleteOrderID(int id)        {            string sqlWhere = string.Format(" and id={0}", id);            //return new OracleDAL.PurchaseOrderService().DeleteEntity(sqlWhere);            return dataFactory.PurchaseOrderDal.DeleteEntity(sqlWhere);        }    }}
        
       
      
     

 

• dataFactory = DefaultProviderDal.DefaultDataProviderFactory;其实是个单利，我只要反射出一次工厂足以。

  public class DefaultProviderDal    {        private static AbstractFactory.DalFactory instance = null;           static DefaultProviderDal()        {            //string filePath = HttpContext.Current.Server.MapPath("~/DataProvider");            string filePath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;            string dllFileName = System.Configuration.ConfigurationManager.AppSettings["DataProviderDllFile"];            string dalFactoryClassName = System.Configuration.ConfigurationManager.AppSettings["DataProviderFactoryName"];            System.Reflection.Assembly dll = System.Reflection.Assembly.LoadFile(filePath + "DataProvider\\" + dllFileName);            instance = dll.CreateInstance(dalFactoryClassName) as AbstractFactory.DalFactory;        }        public DefaultProviderDal()        {        }        public static AbstractFactory.DalFactory DefaultDataProviderFactory        {            get {                return instance;            }        }    }

  总体思路和流程---UI

• 对WCF而言，实例化对象越多(如CommodityManagerServiceClient类的实例)，对服务器压力越大，所以也可以考虑单利。

public class WCFServiceBLL    {       //对WCF而言，对象实例化越多，对服务器压力越大。        static BLLCommodity.CommodityManagerServiceClient commodityClient;        static BLLSalesOrders.SalesManagerServiceClient salesClient;        static BLLUsers.UserManagerServiceClient userClient;        static BLLPurchaseOrders.PurchaseManagerServiceClient purchaseClient;        public static CommodityManagerServiceClient GetCommodityService()        {            if (commodityClient==null)            {                commodityClient = new CommodityManagerServiceClient();            }            if (commodityClient.State==CommunicationState.Closed)            {                commodityClient = new CommodityManagerServiceClient();            }            if (commodityClient.State==CommunicationState.Faulted)            {                commodityClient = new CommodityManagerServiceClient();            }            return commodityClient;

补充

由于数据库之间的主外键关系以及多表查询，为了方便，我创建了视图，这和SqlServer里面是一样的(Oracle里面是Create Or Replace)，当然你也可以建立外键对象，我上个项目就是这么干的，当然ORM我们就不讨论了。

 

create or replace view v_purchasecommodity asselect pc.id,pc.purchaseorderid,pc.commodityid,c.name CommodityName,c.type commodityType,c.manufacturer CommodityManufacturer,c.inventory CommodityInventory,c.unitprice CommodityUnitPrice,c.unit CommodityUnit,pc.count,pc.purchaseprice,pc.totalpricefrom purchasecommodity pc inner join commodity c on pc.commodityid = c.id;

 

 

再补充一点：Oracle的自动增长列并不是像SqlServer那样设置一下，就会自动增长，Oracle里面需要你自己创建Sequences,图形操作也行，命令也行，我导出的Oracle脚本里面已经包含了相应的Sequences,应该可以看懂的。其余差别不大，相信你能看懂。

 

create sequence INVOICING.S_USERSminvalue 1maxvalue 999999999999999999999999999start with 1increment by 1cache 20;

 

 

 关于界面美化的一些心得：

Winform程序功能很重要，但能提高用户体验那是最完美的，所以我用了一些图标，设置相应的大小，当然用户控件也是很重要的一部分，用户控件嵌套在窗体里面是很简单的：

 

public class LoadControls    {        public static void LoadInventory(Control parent)        {            parent.Controls.Clear();            Inventory inventory = new Inventory();            inventory.Dock = DockStyle.Fill;            parent.Controls.Add(inventory);        }        public static void LoadPurchase(Control parent)        {            parent.Controls.Clear();            Purchase purchase = new Purchase();            purchase.Dock = DockStyle.Fill;            parent.Controls.Add(purchase);        }        public static void LoadSales(Control parent)        {            parent.Controls.Clear();            Sales sales = new Sales();            sales.Dock = DockStyle.Fill;            parent.Controls.Add(sales);        }

 

 

 

 

 

总结

没有什么犹豫就写完了这篇博文，我把源代码贡献出来，当然这个例子只为学习，有需要的兄弟们可以拿去参考参考，大家多交流交流，才会相互促进进步，如果您觉得满意的话，不放支持我一下，帮忙顶个，有动力才有精力写出更好的博客，3x:)