assetservice.cs

一个比较简单的

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using AssetsSystem.Dao;
using AssetsSystem.Sys;
using AssetsSystem.Inc;
using System.Windows.Forms;

namespace AssetsSystem.Service
{
    class AssetService
    {
        public DataTable List(Page page,Asset asset,AssetsSortOrder sort)
        {
            DBManager db = new DBManager();
            try
            {
                StringBuilder sql = new StringBuilder();

                db.open();
                page.setTotal(db.SelectCount("select count(id) from assets"));
                if (page.getPageInInt() > Convert.ToInt32(page.getTotalPage())) page.setPage("1");


                sql.AppendFormat("select top {0} * from assets ", page.getPageSize());
                if (page.getFirstResult() != 0)
                {
                    sql.AppendFormat("where id not in (");
                    sql.AppendFormat("select top {0} id  from assets order by {1} {2}", page.getFirstResult(), sort.orderCol, sort.orderType);
                    //双重排序 id
                    if (!sort.orderCol.Equals("id")) sql.AppendFormat(",{0} {1}", "id", "desc");

                    sql.AppendFormat(")");
                }
                sql.AppendFormat(" order by {0} {1}", sort.orderCol, sort.orderType);
                if (!sort.orderCol.Equals("id")) sql.AppendFormat(",{0} {1}", "id", "desc");


                return db.SelectToDataTable(sql.ToString());


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                db.close();
            }
        }


        public int Add(Asset asset)
        {


            DBManager db = new DBManager();
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.AppendFormat("insert into assets (资产编码, 资产名称, 类别, 型号, 单位, 制造商, 产地, 入账日期, 使用日期, 使用情况, 使用部门, 存放地点, 数量, 原值)values( ");
                sql.AppendFormat("'{0}',", asset.getZcbm());
                sql.AppendFormat("'{0}',", asset.getZcmc());
                sql.AppendFormat("'{0}',", asset.getLb());
                sql.AppendFormat("'{0}',", asset.getXh());
                sql.AppendFormat("'{0}',", asset.getDw());
                sql.AppendFormat("'{0}',", asset.getZzs());
                sql.AppendFormat("'{0}',", asset.getCd());
                sql.AppendFormat("'{0}',", asset.getRzrq());
                sql.AppendFormat("'{0}',", asset.getSyrq());
                sql.AppendFormat("'{0}',", asset.getSyqk());
                sql.AppendFormat("'{0}',", asset.getSybm());
                sql.AppendFormat("'{0}',", asset.getCfdd());
                sql.AppendFormat("{0},", (Util.haveText(asset.getSl()) ? asset.getSl() : "0"));
                sql.AppendFormat("'{0}'", asset.getYz());
                sql.AppendFormat(")");
                db.open();
                return db.ExecuteSQLNonquery(sql.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return 0;
            }
            finally
            {
                db.close();
            }
        }


        public int Edit(Asset asset)
        {
            DBManager db = new DBManager();
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.AppendFormat("update assets ");
                sql.AppendFormat("set 资产编码='{0}',", asset.getZcbm());
                sql.AppendFormat("资产名称='{0}',", asset.getZcmc());
                sql.AppendFormat("类别='{0}',", asset.getLb());
                sql.AppendFormat("型号='{0}',", asset.getXh());
                sql.AppendFormat("单位='{0}',", asset.getDw());
                sql.AppendFormat("制造商='{0}',", asset.getZzs());
                sql.AppendFormat("产地='{0}',", asset.getCd());
                sql.AppendFormat("入账日期='{0}',", asset.getRzrq());
                sql.AppendFormat("使用日期='{0}',", asset.getSyrq());
                sql.AppendFormat("使用情况='{0}',", asset.getSyqk());
                sql.AppendFormat("使用部门='{0}',", asset.getSybm());
                sql.AppendFormat("存放地点='{0}',", asset.getCfdd());
                sql.AppendFormat("数量={0},", (Util.haveText(asset.getSl()) ? asset.getSl() : "0"));
                sql.AppendFormat("原值='{0}' ", asset.getYz());
                sql.AppendFormat("where id={0}",asset.getId());
                db.open();
                return db.ExecuteSQLNonquery(sql.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return 0;
            }
            finally
            {
                db.close();
            }

        }

        public int Del(string idL)
        {
            
            DBManager db = new DBManager();
            int n = 0;//更新记录数目
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat("delete from assets where id in (");

                sql.AppendFormat(idL);
                sql.AppendFormat(")");


                db.open();

                n = db.ExecuteSQLNonquery(sql.ToString());

                return n;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return 0;
            }
            finally
            {
                db.close();
            }
        }
        
    }
}