C#/ASP.NET完善的DBHelper,配套Model生成器澳门美高梅手机网站

DBHelper类库必要引用的主次集:

AlphaGo大概就是那般运行的,当前规模有无数下法(平均200个落子点),用Policy
Networks把“觉得不错”的落子点优先标出来,然后蒙特Carlo搜索树(MCTS)会开动脑筋,固然黑狗小编如此那样落子而对方就会那么那样回应……MCTS对于逐个想到的框框,会有二个评分:综合考虑Value
Networks评分和3个随意得分(the outcome zL of a random rollout played
out),公式是那样子的:

View Code

本身吃货一枚,稍微商量过人工智能,未来陪孩童学围棋玩。上边从几上面随便聊聊自身的想法,也毕竟对这么些天自身心路历程的1个记下吧。

public List<cms_content> GetListAll()
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select content.*, channel.title as channelName, user.showName
        from cms_content content
        left join cms_channel channel on channel.id=content.channelId
        left join CMS_sys_user user on user.id=content.publishUserId
        where content.audit=1 
        order by publishTime desc,id desc"));

    return DBHelper.FindListBySql<cms_content>(sql.ToString());
}

末段AlphaGo会选二个得分(胜率)最高的下法。关于评分,作者认为DeepMind那里并从未讲太清楚,围观民众也不用深究(也可查阅散文原文)。只要求精通小狗的笔触既可,小狗的社会风气大致是那样的:

注脚:DBHelper中对事情变量private static
DbTransaction
m_Tran使用了[ThreadStatic]标签,以支撑多用户并发;不过假如是单个用户使用八线程并发请求服务器,只怕那种方式的数据库事务是不接济的,但是貌似项目尚未那种须要,借使有请使用HttpContext.Current.Items改写大概别的格局改写。

图例:How AlphaGo (black, to play) selected its move in an informal game
against Fan Hui.

哪些拔取:

AlphaGo 4:1
李世石,人机大战落幕。臆度半数以上人和自己同一,从赛后对AlphaGo的鄙视,到AlphaGo突然克制人类九段的震惊,再到李世石水滴石穿后点穴般“神之手段”的惊艳,到最后接受人工智能克服人类九段的还要,又有一点点的低沉和对前途的担心。

/// <summary>
/// 统计SQL
/// </summary>
public override string GetReportSql(string formData)
{
    ProductInvoiceModel search = JsonConvert.DeserializeObject<ProductInvoiceModel>(formData);

    StringBuilder sql = new StringBuilder(string.Format(@"
        select * from 
        (select distinct 
        '出库单' as '报表类型',
        pos.billNo as '单据编号',
        pos.projectCode as '项目编号',
        pro.projectName as '项目名称',
        ma.subProject as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        mad.model as '规格型号',
        dic1.dicItem as '单位',
        posd.qty as '数量',

        CONVERT(varchar(100), pos.billDate, 23) as '日期'

        from Pro_ProductOutStorageDet posd
        left join Pro_ProductOutStorage pos on pos.id=posd.parentId

        left join Pro_MatAllotDet mad on posd.matAllotDetId=mad.id
        left join Pro_MatAllot ma on ma.id=mad.parentID and ma.status=2
        left join Pro_MatStock ms on ms.id=mad.matStockId and mad.memberType=1
        left join Pro_ConStock cs on cs.id=mad.matStockId and mad.memberType=0
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_Info pro on pos.projectCode=pro.projectNum

        left join sys_dicDetail dic1 on dic1.dicItemcode=mad.unitCode  

        union all

        select distinct 
        'XXX出库单' as '报表类型',
        pos.billNo as '单据编号',
        pos.projectCode as '项目编号',
        pro.projectName as '项目名称',
        '' as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        case when cmd.model is not NULL then cmd.model else mat.model end as '规格型号',
        case when dic1.dicItem  is not NULL then dic1.dicItem  else dic2.dicItem  end as '单位',
        posd.qty as '数量',

        CONVERT(varchar(100), pos.billDate, 23) as '日期'

        from Pro_ProductOut2StorageDet posd
        left join Pro_ProductOut2Storage pos on pos.id=posd.parentId

        left join Pro_MatStock ms on ms.id=posd.matStockId
        left join Pro_ConStock cs on cs.id=posd.conInventoryDetId
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_Info pro on pos.projectCode=pro.projectNum

        left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode  
        left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit  

        union all

        select distinct 
        '生产消耗单' as '报表类型',
        pc.billCode as '单据编号',
        pro.projectNum as '项目编号',
        pro.projectName as '项目名称',
        ll.subProject as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        case when cmd.model is not NULL then cmd.model else mat.model end as '规格型号',
        case when dic1.dicItem  is not NULL then dic1.dicItem  else dic2.dicItem  end as '单位',
        pcd.consumeQty as '数量',

        CONVERT(varchar(100), pc.billDate, 23) as '日期'

        from Pro_ProductConsume pc
        left join Pro_ProductConsumeDet pcd on pcd.parentId=pc.id

        left join Pro_LingLiaoDet lld on lld.id=pcd.lingLiaoDetId
        left join Pro_LingLiao ll on ll.id=lld.parentId

        left join Pro_MatStock ms on ms.id=lld.matStockId and lld.memberType=1
        left join Pro_ConStock cs on cs.id=lld.matStockId and lld.memberType=0
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_ProTask pt on pt.billCode=ll.proTaskCode
        left join Pro_Info pro on pt.proInfCode=pro.billCode

        left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode  
        left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit  
        ) T where 1=1 "));

    if (!string.IsNullOrEmpty(search.projectName))
    {
        sql.AppendFormat("  and T.项目名称 like '%{0}%' ", search.projectName.Trim());
    }
    if (!string.IsNullOrEmpty(search.projectCode))
    {
        sql.AppendFormat("  and T.项目编号 like '%{0}%' ", search.projectCode.Trim());
    }
    if (!string.IsNullOrEmpty(search.btime) && !string.IsNullOrEmpty(search.etime))
    {
        sql.AppendFormat(" and  T.日期  >=  '{0}'", search.btime);
    }
    if (!string.IsNullOrEmpty(search.etime))
    {
        sql.AppendFormat(" and  T.日期  <=  '{0}'", search.etime);
    }

    return sql.ToString();
}

3)蒙特卡罗搜索树(MCTS)

根据条件删除:

离考试已毕还有10秒钟了,监考老师说:“要及格的校友抓紧时间啦!”说完便转身走出了教室……对其他一人,只要脑袋没有进水,都清楚要怎么了。对这一个大约的情景,不了然阿尔法小狗会怎么样反馈?哈哈哈~~~

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Models
{
    /// <summary>
    /// 标识该属性是主健
    /// </summary>
    [Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
    public class IsIdAttribute : Attribute
    {
    }
}

AlphaGo由3有的构成:Policy Networks/Value Networks
/蒙特卡罗搜索树(MCTS)。上面简单介绍它们仨。

澳门美高梅手机网站 1澳门美高梅手机网站 2

本次突破或者最重视的震慑,就是让大家更清晰的咀嚼了依据机器学习的神经互连网的力量,那种力量应该远远胜出我们早期的预料。由此对我们人类今后的生活将发生深刻的熏陶。(注4)

基于ID集合批量删减:

私家认为,AlphaGo是人类历史上的1个里程碑。人类从石器时期—铁器青铜时期—蒸汽时期—电气时期—音信时期一向发展过来,石头/金属工具替代了徒手劳作,蒸汽/电气力量替代了人力,总括机替代了简便而繁重的纯正数据处理。近来后,AlphaGo用一场胜利发布人类开首进入新的时期——人工智能时期。那么些时期恐怕会有一对天性:

DBHelper类完整代码:

狗狗依照未来的经历(“感觉”)选一些下法,随机也选一些下法,发挥计算力特长搜索评估差异层面,最终摘折桂率最高的下法。只要选的下法充分多,就很大概逼近最佳下法。那样看来,小狗有种“大胆臆度,小心求证”的寓意,DeepMind厉害。

删除:

Reinforcement Learning Policy Networks是SL Policy
Networks的加强版,就是拿一个选定的SL Policy
Networks不断跟一群随机接纳的SL Policy
Networks内部竞技,不断改正自身。最终,那几个选定的SL Policy
Networks被激化到有8/10的里边比赛胜率。有没有养一堆毒虫让它们互相厮杀剩下八个的觉得?用三个开源的围棋软件Pachi作为参考对手,阿尔法家狗用SL
Policy Networks胜率是11%,而用优胜劣汰脱颖而出的MuranoL Policy
Networks胜率暴涨到85%。

澳门美高梅手机网站 3澳门美高梅手机网站 4

2)Value Networks

澳门美高梅手机网站 5澳门美高梅手机网站 6

纵观那五盘棋,AlphaGo通过大批量棋谱/实战练习,令本身“像七个大师”,同时有个别精准测算是专长。但AlphaGo仍有先后原始的缺陷,一旦相遇bug很简单崩溃,有点像人类的“天才白痴”。此外,AlphaGo人工神经元网络不会指向对手调整自身,恐怕说,5局棋谱比起从前16万局锻练如沧海一粟,对AlphaGo进步和调整大约可以忽略。

/// <summary>
/// 删除
/// </summary>
public void Delete(string conditions)
{
    DBHelper.Delete<BS_Template>(conditions);
}

5月124日,AlphaGo完胜,其中一步5路尖冲,令聂卫平“脱帽致敬”。李世石认同局面一向走下坡路,完败。

3、

最后,用本人过去同窗赵教师的1个段子作为那篇文章的末段:

澳门美高梅手机网站 7澳门美高梅手机网站 8

10月九日,李世石轻敌,首局中盘认输,人类哗然。

View Code

一、说说家狗(注1)

澳门美高梅手机网站 9澳门美高梅手机网站 10

三、说说前景

View Code

本文是本人的好友馋宗通吃观战后的感想,作者帮忙发表在简书。如需转载请文告作者,多谢。

4、

2)人工智能成为人类更强劲的副手,人也更依靠人工智能(其实,大家以后早就更倚重智能手机了,囧)。那地点希望人工智能的维系表明能力做出突破。例如本次AlphaGo一些下法固然后来证实很厉害,但不少事情棋手当时都看不懂。试想,即使八个医治人工智能助手给了医务卫生人员多个精准的医治方案,但不能说精晓怎么要这么做,医师和伤者敢上手术台吗?

/// <summary>
/// 删除
/// </summary>
public void Del(int id)
{
    DBHelper.Delete<BS_Template>(id);
}

注1:全篇多处引用到DeepMind团队的散文《Mastering the Game of Go with
Deep Neural Networks and Tree
Search》,不一一标出,在那边一并向DeepMind团队致谢。

/// <summary>
/// 修改
/// </summary>
public void Update(object obj)
{
    DBHelper.Update(obj);
}

Supervised Learning Policy
Networks是3个13层深度的人工神经元网络,它上学了KGS Go
Server里所有的规范棋谱,合共3000万步棋。学习达成后,固然你“问”SL Policy
Networks记不记得有个别学习过的棋谱里下一步棋应该走哪儿,Policy
Networks回答有四分之二和棋谱里一致。呵呵,貌似阿尔法小狗的回想力很相似呀!其实不然,剩下43%
SL Policy
Networks凭“感觉”给出的棋,笔者猜如果中间有一小半不是太坏的招,那么SL
Policy
Networks针对学习过的范围给出的下一步棋,有7/10左右是不差的!!!但那还没完,阿尔法家狗还要再自个儿强化一把,就是Reinforcement
Learning Policy Networks。

证实:对于SQL Server数据库,通过<add
key=”AutoIncrement” value=”false”/>来设置是或不是利用数据库自增。

从娱乐的角度看,阿尔法黄狗能够用作是:依据经验抓一批人,随机也抓一批人,都杀掉!错杀3000,可能“那1个”就逃不了!借使给小狗更加多的年华和CPU,就杀三千0三80000,“那3个”就更难回避了!!!好邪恶好血腥~

假如您不了然小编干什么要写DBHelper,为啥没有语法糖,为啥查询和分页查询要利用原生SQL,看看上面我们ERubiconP项目中的代码,你会知晓,当然,这一个序列规划的的确不佳:

蒙特卡罗是一类随机格局的统称。举个例子,如若筐里有99个苹果,让自个儿每一回回老家拿二个,挑出最大的。于是作者随便拿3个,再随便拿3个跟它比,留下大的,再轻易拿一个……作者每拿一次,留下的苹果都至少不比上次的小。拿的次数更多,挑出的苹果就越大,但小编只有拿96回,否则不或许自然挑出了最大的。那么些挑苹果的算法,就属于蒙特卡罗算法——有限时间内尽量找好的,但不保障是最好的。(注2)

ExtModels示例:

注4:刘知青教师接受喆理围棋访问语录。

证实:SQL
Server数据库可以行使自增,Oracle数据库可以行使Sequence,小系统可以动用DBHelper自带的Get马克斯ID方法。

增补:作者这几个好友高校正式是计算机有关的,高校之间学过神经互连网,写过棋类程序,也好不不难出众围棋爱好者。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Models
{
    /// <summary>
    /// 标识该属性是数据库字段
    /// </summary>
    [Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
    public class IsDBFieldAttribute : Attribute
    {
    }
}

终极人机大战李世石1比4失利,谷歌(谷歌(Google))的人工智能攻破了人类智慧活动最坚固的碉堡,李世石逆境下取得的一胜同样捍卫了人类的庄严。

澳门美高梅手机网站 11澳门美高梅手机网站 12

五月十日,李世石在有损局面下弈出“神之手段”挖,AlphaGo“短路”,小李逆袭。李世石参与宣布会时,现场几百名记者齐声高呼李世石的名字,小李是敢于。

View Code

AlphaGo的确是人为智能的一大突破。

View Code

Policy Networks依照当下范围,评估下一步走哪个地方?它分为两片段,Supervised
Learning Policy Networks和Reinforcement Learning Policy Networks。

注解:查询SQL书写规范:SQL不可以写的杂乱无章,该换行换行,该对齐对齐。

十一月二十八日,李世石在序盘仅仅因手腕棋过分便遭逢AlphaGo最锐利的反攻,从此落入被动局面。三持续失败,世间感伤。

澳门美高梅手机网站 13

注3:那里参考了网上各路专业权威和新闻记者和见地,不只怕一一列出,一并致谢!有趣味读者可以上网找寻细品。

里面Models类库如下:

而李世石,赛后对AlphaGo
有轻敌心境,但小李不断试探和调动政策的拼命,持之以恒和绝地回击的决心,终于逼出AlphaGo的bug,的确配得上“人类意味着”。

表达:Models目录中的类及其性质和数据库中的表和字段是一心对应的,Models全体由生成器生成,并且不容许手动修改。ExtModels目录中的类是扩充类,首要用以查询与显示,比如表中存的是code,但你需求关联查询另一张表中的name,就足以在那么些扩大类中扩充三个用以浮现的name字段。Models和ExtModels目录的中类都以partial修饰。


澳门美高梅手机网站 14澳门美高梅手机网站 15

注2:作者:苏椰,http://www.zhihu.com/question/20254139/answer/33572009

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Models
{
    /// <summary>
    /// 分页
    /// </summary>
    public class PagerModel
    {
        #region 字段
        /// <summary>
        /// 当前页数
        /// </summary>
        public int page { get; set; }
        /// <summary>
        /// 每页记录数
        /// </summary>
        public int rows { get; set; }
        /// <summary>
        /// 排序字段
        /// </summary>
        public string sort { get; set; }
        /// <summary>
        /// 排序的方式asc,desc
        /// </summary>
        public string order { get; set; }
        /// <summary>
        /// 记录
        /// </summary>
        public object result { get; set; }
        /// <summary>
        /// 记录数
        /// </summary>
        public int totalRows { get; set; }
        #endregion

        #region 构造函数
        public PagerModel()
        {

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="page">当前页数</param>
        /// <param name="rows">每页记录数</param>
        public PagerModel(int page, int rows)
        {
            this.page = page;
            this.rows = rows;
        }
        #endregion

        #region 扩展字段
        /// <summary>
        /// 总页数
        /// </summary>
        public int pageCount
        {
            get
            {
                return (totalRows - 1) / rows + 1;
            }
        }
        /// <summary>
        /// 上一页
        /// </summary>
        public int prePage
        {
            get
            {
                if (page - 1 > 0)
                {
                    return page - 1;
                }
                return 1;
            }
        }
        /// <summary>
        /// 下一页
        /// </summary>
        public int nextPage
        {
            get
            {
                if (page + 1 < pageCount)
                {
                    return page + 1;
                }
                return pageCount;
            }
        }
        #endregion

    }
}

5月1二3日,李世石细棋战败。

澳门美高梅手机网站 16澳门美高梅手机网站 17

1)人工智能将取代部分专业性极强但不要求完全可相信的干活(能博取完全新闻的领域),例如举世语言的同声传译(人类要逆天重建巴别塔呀),例如在多少个城市里按照监控图像飞快排查恐怖疑心分子等等。

View Code

从总括机检索树剪枝算法角度看,AlphaGo是:用Policy
Networks来剪枝,用Value
Networks模糊估值和肆意估值(裁减思维盲点)结合,达到在少数时间和硬件规格下,尽量找到好的下法!从结果来看,家狗在“时间/空间/准确性”方面找到了较好平衡点,赞两个。

View Code

3)人类不可替代,至少AlpahGo这一类完全信息人工智能不行。因为还有“不可以博取完全消息的领域”。人类可以研究未知领域,并且策略调整和影响飞快,人类可以发现原先未知的东西,例如“动力波”。而在人类已经把某部圈子探索的比较清楚了,把这个经验整理成的“陶冶多少”(或许取得陶冶多少的框架),人工智能才能读书。倘若要磨练这个数据都不知道,alpha小狗就晕了。让一台机械/物种能创新和清醒,仍旧是神之领域!(科学的无尽是历史学,艺术学的尽头真的是神学吗?)

public cms_content Get(int id)
{
    return DBHelper.FindById<cms_content>(id.ToString());
}

1)Policy Networks

没有语法糖,学习费用大概为0,拿来即用。

Value Networks跟Policy
Networks类似,它依照近年来范围,预估己方胜率是多少。练习方法也是如出一辙,先用KGS
Go Server里的棋谱锻炼。磨炼出一批Value
Networks之后,它们再互相PK,得到二个预估胜率最纯正的Value
Networks,叫做Reinforcement Learning Value
Networks。我们是否该对股评专家也利用类似的法子相比呢?嗯,评股,养蛊,谐音啊~

除VS二零一二自带的DLL外需求的DLL:

末尾说说AlphaGo的硬件配备: 本次竞赛接纳单机版本48 CPUs + 8
GPUs,40条线程并行总计;分布式版本用到多台机械,合共1202 CPUs + 176
GPUs,每台机器40线程。

using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 内容详情
    /// </summary>
    public partial class cms_content
    {
        /// <summary>
        /// 栏目名称
        /// </summary>
        public string channelName { get; set; }
        /// <summary>
        /// 用户显示名
        /// </summary>
        public string showName { get; set; }
        /// <summary>
        /// 审核状态
        /// </summary>
        public string dispAudit
        {
            get
            {
                switch (this.audit ?? 0)
                {
                    case 0:
                        return "待审核";
                    case 1:
                        return "审核通过";
                    case 2:
                        return "审核不通过";
                }
                return "error";
            }
            set { }
        }
        /// <summary>
        /// 在当前页中的索引
        /// </summary>
        public int curPageSort { get; set; }
        /// <summary>
        /// 是否为空,模板使用,0不为空1为空
        /// </summary>
        public int isNull { get; set; }
    }
}

二、说说打仗(注3)

System.Data.SQLite.dll

        public List<MatAllotModel> GeProMatAllotList(ref PagerModel pager, string billCode, string projectNum, string projectName, string remarks, int status)
        {
            string sql = @"select v.*,ISNULL(v.IsFinish,(case when ISNULL(v.pqty,0)= ISNULL(v.mqty,0) then 1 else 0 end)) as IsEnd from (
select ma.*,e.name + '('+isnull(e.mobilephone,'')+')' as billCreatorName,i.projectName,s.storeHouseName as inStoreName, 
(select stuff((select distinct ','+s.storeHouseName from Pro_MatAllotDet mad
join sys_storehouse s on mad.outStorageCode=s.storeHouseCode
where parentId =ma.id for xml path('')),1,1,'')) as outStoreName,I.projectSim,D.dicItem AS typename,ppi.id as InvoiceId,i.projectNum,
(select sum(md.qty) from Pro_MatAllotDet md  where md.parentId =ma.id) as mqty,
(select sum(qty) from Pro_ProductOutStorageDet pod 
left join Pro_ProductOutStorage po on po.id = pod.parentID
where po.matAllotBillCode = ma.billCode) as pqty
from Pro_MatAllot ma
join sys_storehouse s on ma.inStoreCode=s.storeHouseCode
join sys_employee e on ma.billCreator=e.employeeCode
left join Pro_ProductInvoice ppi on ppi.MatAllotBillCode=ma.billCode
left join Pro_Info i on ma.projectCode = i.projectNum
LEFT JOIN sys_dicDetail D ON D.dicItemcode = I.projectType
) as v where 1=1 ";
            if (!string.IsNullOrEmpty(billCode))
            {
                sql += " and v.billCode like '%" + billCode.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(projectName))
            {
                sql += " and v.projectName like '%" + projectName.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(projectNum))
            {
                sql += " and v.projectNum like '%" + projectNum.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(remarks))
            {
                sql += " and v.remarks like '%" + remarks.Trim() + "%'";
            }
            sql += " and v.status=" + status + "";
            List<MatAllotModel> matAllot = EntityHelper.Default.GetPageEntities<MatAllotModel>(pager.page, pager.rows, sql, "id", pager.sort, pager.order);
            pager.totalRows = DBHelper.Default.Count(sql);
            pager.result = matAllot;
            return matAllot;
        }
<connectionStrings>
  <add name="DefaultConnection"  connectionString="server=localhost;database=netcms3.0;user id=root;password=root;character set=gbk;" />
</connectionStrings>
<appSettings>
  <!--数据库类型-->
  <add key="DBType" value="mysql"/>
  <!--数据库自增-->
  <add key="AutoIncrement" value="false"/>
</appSettings>

View Code

支持Oracle、MSSQL、MySQL、SQLite多样数据库,扶助工作,支持对象关系映射;已在七个门类中实际运用。

Web.config配置:

View Code

澳门美高梅手机网站 18澳门美高梅手机网站 19

View Code

澳门美高梅手机网站 20澳门美高梅手机网站 21

注解:例子程序是Winform,该DBHelper也适用于Web项目。

注脚:ExtModels下的扩大Model,可以扶助查询数据库中不设有的字段,并映射填充到实体类。

澳门美高梅手机网站 22澳门美高梅手机网站 23

依照ID查询实体:

PagerModel类:

View Code

View Code

5、

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Objects.DataClasses;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using Models;
using MySql.Data.MySqlClient;

/* ---------------------------------------------
 * 作    者:suxiang
 * 创建日期:2016年11月23日
 * --------------------------------------------- */

namespace DBUtil
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public static class DBHelper
    {
        #region 变量
        /// <summary>
        /// 数据库类型
        /// </summary>
        private static string m_DBType = ConfigurationManager.AppSettings["DBType"];
        /// <summary>
        /// 数据库类型
        /// </summary>
        private static bool m_AutoIncrement = ConfigurationManager.AppSettings["AutoIncrement"].ToLower() == "true" ? true : false;
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static string m_ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
        /// <summary>
        /// 事务
        /// </summary>
        [ThreadStatic]
        private static DbTransaction m_Tran;
        /// <summary>
        /// 带参数的SQL插入和修改语句中,参数前面的符号
        /// </summary>
        private static string m_ParameterMark = GetParameterMark();
        #endregion

        #region 生成变量
        #region 生成 IDbCommand
        /// <summary>
        /// 生成 IDbCommand
        /// </summary>
        private static DbCommand GetCommand()
        {
            DbCommand command = null;

            switch (m_DBType)
            {
                case "oracle":
                    command = new OracleCommand();
                    break;
                case "mssql":
                    command = new SqlCommand();
                    break;
                case "mysql":
                    command = new MySqlCommand();
                    break;
                case "sqlite":
                    command = new SQLiteCommand();
                    break;
            }

            return command;
        }
        /// <summary>
        /// 生成 IDbCommand
        /// </summary>
        private static DbCommand GetCommand(string sql, DbConnection conn)
        {
            DbCommand command = null;

            switch (m_DBType)
            {
                case "oracle":
                    command = new OracleCommand(sql);
                    command.Connection = conn;
                    break;
                case "mssql":
                    command = new SqlCommand(sql);
                    command.Connection = conn;
                    break;
                case "mysql":
                    command = new MySqlCommand(sql);
                    command.Connection = conn;
                    break;
                case "sqlite":
                    command = new SQLiteCommand(sql);
                    command.Connection = conn;
                    break;
            }

            return command;
        }
        #endregion

        #region 生成 IDbConnection
        /// <summary>
        /// 生成 IDbConnection
        /// </summary>
        private static DbConnection GetConnection()
        {
            DbConnection conn = null;

            switch (m_DBType)
            {
                case "oracle":
                    conn = new OracleConnection(m_ConnectionString);
                    break;
                case "mssql":
                    conn = new SqlConnection(m_ConnectionString);
                    break;
                case "mysql":
                    conn = new MySqlConnection(m_ConnectionString);
                    break;
                case "sqlite":
                    conn = new SQLiteConnection(m_ConnectionString);
                    break;
            }

            return conn;
        }
        #endregion

        #region 生成 IDbDataAdapter
        /// <summary>
        /// 生成 IDbDataAdapter
        /// </summary>
        private static DbDataAdapter GetDataAdapter(DbCommand cmd)
        {
            DbDataAdapter dataAdapter = null;

            switch (m_DBType)
            {
                case "oracle":
                    dataAdapter = new OracleDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
                case "mssql":
                    dataAdapter = new SqlDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
                case "mysql":
                    dataAdapter = new MySqlDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
                case "sqlite":
                    dataAdapter = new SQLiteDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
            }

            return dataAdapter;
        }
        #endregion

        #region 生成 m_ParameterMark
        /// <summary>
        /// 生成 m_ParameterMark
        /// </summary>
        private static string GetParameterMark()
        {
            switch (m_DBType)
            {
                case "oracle":
                    return ":";
                case "mssql":
                    return "@";
                case "mysql":
                    return "@";
                case "sqlite":
                    return ":";
            }
            return ":";
        }
        #endregion

        #region 生成 DbParameter
        /// <summary>
        /// 生成 DbParameter
        /// </summary>
        private static DbParameter GetDbParameter(string name, object vallue)
        {
            DbParameter dbParameter = null;

            switch (m_DBType)
            {
                case "oracle":
                    dbParameter = new OracleParameter(name, vallue);
                    break;
                case "mssql":
                    dbParameter = new SqlParameter(name, vallue);
                    break;
                case "mysql":
                    dbParameter = new MySqlParameter(name, vallue);
                    break;
                case "sqlite":
                    dbParameter = new SQLiteParameter(name, vallue);
                    break;
            }

            return dbParameter;
        }
        #endregion
        #endregion

        #region 基础方法
        #region  执行简单SQL语句
        #region Exists
        public static bool Exists(string sqlString)
        {
            SqlFilter(ref sqlString);
            using (DbConnection conn = GetConnection())
            {
                using (DbCommand cmd = GetCommand(sqlString, conn))
                {
                    try
                    {
                        conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return false;
                        }
                        else
                        {
                            return true;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 执行SQL语句,返回影响的记录数
        /// <summary>
        /// 执行SQL语句,返回影响的记录数
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string sqlString)
        {
            SqlFilter(ref sqlString);
            DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;
            using (DbCommand cmd = GetCommand(sqlString, conn))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                    if (m_Tran != null) cmd.Transaction = m_Tran;
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cmd.Dispose();
                    if (m_Tran == null) conn.Close();
                }
            }
        }
        #endregion

        #region 执行一条计算查询结果语句,返回查询结果
        /// <summary>
        /// 执行一条计算查询结果语句,返回查询结果(object)
        /// </summary>
        /// <param name="sqlString">计算查询结果语句</param>
        /// <returns>查询结果(object)</returns>
        public static object GetSingle(string sqlString)
        {
            SqlFilter(ref sqlString);
            using (DbConnection conn = GetConnection())
            {
                using (DbCommand cmd = GetCommand(sqlString, conn))
                {
                    try
                    {
                        if (conn.State != ConnectionState.Open) conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
                }
            }
        }
        #endregion

        #region 执行查询语句,返回IDataReader
        /// <summary>
        /// 执行查询语句,返回IDataReader ( 注意:调用该方法后,一定要对IDataReader进行Close )
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>IDataReader</returns>
        public static DbDataReader ExecuteReader(string sqlString)
        {
            SqlFilter(ref sqlString);
            DbConnection conn = GetConnection();
            DbCommand cmd = GetCommand(sqlString, conn);
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 执行查询语句,返回DataSet
        /// <summary>
        /// 执行查询语句,返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string sqlString)
        {
            SqlFilter(ref sqlString);
            using (DbConnection conn = GetConnection())
            {
                DataSet ds = new DataSet();
                try
                {
                    conn.Open();
                    using (DbCommand cmd = GetCommand(sqlString, conn))
                    {
                        DbDataAdapter adapter = GetDataAdapter(cmd);
                        adapter.Fill(ds, "ds");
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
                return ds;
            }
        }
        #endregion

        #region SQL过滤,防注入
        /// <summary>
        /// SQL过滤,防注入
        /// </summary>
        /// <param name="sql">sql</param>
        public static void SqlFilter(ref string sql)
        {
            sql = sql.Trim();
            List<string> keywordList = new List<string>() { 
                "net localgroup",
                "net user",
                "xp_cmdshell",
                "exec",
                "execute",
                "truncate",
                "drop",
                "restore",
                "create",
                "alter",
                "rename",
                "insert",
                "update",
                "delete",
                "select"};
            string ignore = string.Empty;
            string upperSql = sql.ToUpper();
            foreach (string keyword in keywordList)
            {
                if (upperSql.IndexOf(keyword.ToUpper()) == 0)
                {
                    ignore = keyword;
                }
            }
            foreach (string keyword in keywordList)
            {
                if (ignore == "select" && ignore == keyword) continue;
                Regex regex = new Regex(keyword, RegexOptions.IgnoreCase);
                sql = sql.Substring(0, ignore.Length) + regex.Replace(sql.Substring(ignore.Length), string.Empty);
            }
        }
        #endregion
        #endregion

        #region 执行带参数的SQL语句
        #region 执行SQL语句,返回影响的记录数
        /// <summary>
        /// 执行SQL语句,返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params DbParameter[] cmdParms)
        {
            DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;
            using (DbCommand cmd = GetCommand())
            {
                try
                {
                    PrepareCommand(cmd, conn, m_Tran, SQLString, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (m_Tran == null) conn.Close();
                }
            }
        }
        #endregion

        #region 执行查询语句,返回IDataReader
        /// <summary>
        /// 执行查询语句,返回IDataReader ( 注意:调用该方法后,一定要对IDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>IDataReader</returns>
        public static DbDataReader ExecuteReader(string sqlString, params DbParameter[] cmdParms)
        {
            DbConnection conn = GetConnection();
            DbCommand cmd = GetCommand();
            try
            {
                PrepareCommand(cmd, conn, null, sqlString, cmdParms);
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region 执行查询语句,返回DataSet
        /// <summary>
        /// 执行查询语句,返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string sqlString, params DbParameter[] cmdParms)
        {
            DbConnection conn = GetConnection();
            DbCommand cmd = GetCommand();
            PrepareCommand(cmd, conn, null, sqlString, cmdParms);
            using (DbDataAdapter da = GetDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    conn.Close();
                }
                return ds;
            }
        }
        #endregion

        #region PrepareCommand
        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null) cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
        #endregion
        #endregion
        #endregion

        #region 增删改查
        #region 获取最大编号
        /// <summary>
        /// 获取最大编号
        /// </summary>
        /// <typeparam name="T">实体Model</typeparam>
        /// <param name="key">主键</param>
        public static int GetMaxID<T>(string key)
        {
            Type type = typeof(T);

            string sql = null;
            switch (m_DBType)
            {
                case "oracle":
                    sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                    break;
                case "mssql":
                    sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                    break;
                case "mysql":
                    sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                    break;
                case "sqlite":
                    sql = string.Format("SELECT Max(cast({0} as int)) FROM {1}", key, type.Name);
                    break;
            }

            using (DbConnection conn = GetConnection())
            {
                using (IDbCommand cmd = GetCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return 1;
                        }
                        else
                        {
                            return int.Parse(obj.ToString()) + 1;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public static void Insert(object obj)
        {
            Insert(obj, m_AutoIncrement);
        }
        /// <summary>
        /// 添加
        /// </summary>
        public static void Insert(object obj, bool autoIncrement)
        {
            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            strSql.Append(string.Format("insert into {0}(", type.Name));

            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            List<string> propertyNameList = new List<string>();
            int savedCount = 0;
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    propertyNameList.Add(propertyInfo.Name);
                    savedCount++;
                }
            }

            strSql.Append(string.Format("{0})", string.Join(",", propertyNameList.ToArray())));
            strSql.Append(string.Format(" values ({0})", string.Join(",", propertyNameList.ConvertAll<string>(a => m_ParameterMark + a).ToArray())));
            DbParameter[] parameters = new DbParameter[savedCount];
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object val = propertyInfo.GetValue(obj, null);
                    DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
                    parameters[k++] = param;
                }
            }

            ExecuteSql(strSql.ToString(), parameters);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public static void Update(object obj)
        {
            object oldObj = Find(obj, false);
            if (oldObj == null) throw new Exception("无法获取到旧数据");

            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            strSql.Append(string.Format("update {0} ", type.Name));

            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            List<string> propertyNameList = new List<string>();
            int savedCount = 0;
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        propertyNameList.Add(propertyInfo.Name);
                        savedCount++;
                    }
                }
            }

            strSql.Append(string.Format(" set "));
            DbParameter[] parameters = new DbParameter[savedCount];
            StringBuilder sbPros = new StringBuilder();
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        sbPros.Append(string.Format(" {0}={1}{0},", propertyInfo.Name, m_ParameterMark));
                        DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
                        parameters[k++] = param;
                    }
                }
            }
            if (sbPros.Length > 0)
            {
                strSql.Append(sbPros.ToString(0, sbPros.Length - 1));
            }
            strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));

            if (savedCount > 0)
            {
                ExecuteSql(strSql.ToString(), parameters);
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 根据Id删除
        /// </summary>
        public static void Delete<T>(int id)
        {
            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            DbParameter[] cmdParms = new DbParameter[1];
            cmdParms[0] = GetDbParameter(m_ParameterMark + GetIdName(type), id);
            sbSql.Append(string.Format("delete from {0} where {2}={1}{2}", type.Name, m_ParameterMark, GetIdName(type)));

            ExecuteSql(sbSql.ToString(), cmdParms);
        }
        /// <summary>
        /// 根据Id集合删除
        /// </summary>
        public static void BatchDelete<T>(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            string[] idArr = ids.Split(',');
            DbParameter[] cmdParms = new DbParameter[idArr.Length];
            sbSql.AppendFormat("delete from {0} where {1} in (", type.Name, GetIdName(type));
            for (int i = 0; i < idArr.Length; i++)
            {
                cmdParms[i] = GetDbParameter(m_ParameterMark + GetIdName(type) + i, idArr[i]);
                sbSql.AppendFormat("{1}{2}{3},", type.Name, m_ParameterMark, GetIdName(type), i);
            }
            sbSql.Remove(sbSql.Length - 1, 1);
            sbSql.Append(")");

            ExecuteSql(sbSql.ToString(), cmdParms);
        }
        /// <summary>
        /// 根据条件删除
        /// </summary>
        public static void Delete<T>(string conditions)
        {
            if (string.IsNullOrWhiteSpace(conditions)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            SqlFilter(ref conditions);
            sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));

            ExecuteSql(sbSql.ToString());
        }
        #endregion

        #region 获取实体
        #region 根据实体获取实体
        /// <summary>
        /// 根据实体获取实体
        /// </summary>
        private static object Find(object obj, bool readCache = true)
        {
            Type type = obj.GetType();

            object result = Activator.CreateInstance(type);
            bool hasValue = false;
            IDataReader rd = null;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        private static object FindById(Type type, int id)
        {
            object result = Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        public static T FindById<T>(string id) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion

        #region 根据sql获取实体
        /// <summary>
        /// 根据sql获取实体
        /// </summary>
        public static T FindBySql<T>(string sql) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public static List<T> FindListBySql<T>(string sql) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public static List<T> FindListBySql<T>(string sql, params DbParameter[] cmdParms) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql, cmdParms);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString());
                pagerModel.result = list;
            }

            return pagerModel;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params DbParameter[] cmdParms) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);
                pagerModel.result = list;
            }

            return pagerModel;
        }


        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public static DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params DbParameter[] cmdParms)
        {
            DataSet ds = null;

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                totalCount = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                ds = Query(sql, cmdParms);
            }
            return ds;
        }
        #endregion

        #region getReaderValue 转换数据
        /// <summary>
        /// 转换数据
        /// </summary>
        private static Object getReaderValue(Object rdValue, Type ptype)
        {
            if (ptype == typeof(double))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(decimal))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(int))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(long))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(DateTime))
                return Convert.ToDateTime(rdValue);

            if (ptype == typeof(Nullable<double>))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(Nullable<decimal>))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(Nullable<int>))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(Nullable<long>))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(Nullable<DateTime>))
                return Convert.ToDateTime(rdValue);

            return rdValue;
        }
        #endregion

        #region 获取主键名称
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public static string GetIdName(Type type)
        {
            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0)
                {
                    return propertyInfo.Name;
                }
            }
            return "Id";
        }
        #endregion

        #region 获取主键值
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public static object GetIdVal(object val)
        {
            string idName = GetIdName(val.GetType());
            if (!string.IsNullOrWhiteSpace(idName))
            {
                return val.GetType().GetProperty(idName).GetValue(val, null);
            }
            return 0;
        }
        #endregion

        #region 获取实体类属性
        /// <summary>
        /// 获取实体类属性
        /// </summary>
        private static PropertyInfo[] GetEntityProperties(Type type)
        {
            List<PropertyInfo> result = new List<PropertyInfo>();
            PropertyInfo[] propertyInfoList = type.GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0
                    && propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0)
                {
                    result.Add(propertyInfo);
                }
            }
            return result.ToArray();
        }
        #endregion

        #region 获取基类
        /// <summary>
        /// 获取基类
        /// </summary>
        public static Type GetBaseType(Type type)
        {
            while (type.BaseType != null && type.BaseType.Name != typeof(Object).Name)
            {
                type = type.BaseType;
            }
            return type;
        }
        #endregion
        #endregion

        #region 事务
        #region 开始事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public static void BeginTransaction()
        {
            DbConnection conn = GetConnection();
            if (conn.State != ConnectionState.Open) conn.Open();
            m_Tran = conn.BeginTransaction();
        }
        #endregion

        #region 提交事务
        /// <summary>
        /// 提交事务
        /// </summary>
        public static void CommitTransaction()
        {
            if (m_Tran == null) return; //防止重复提交
            DbConnection conn = m_Tran.Connection;
            try
            {
                m_Tran.Commit();
            }
            catch (Exception ex)
            {
                m_Tran.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
                m_Tran.Dispose();
                m_Tran = null;
            }
        }
        #endregion

        #region 回滚事务(出错时调用该方法回滚)
        /// <summary>
        /// 回滚事务(出错时调用该方法回滚)
        /// </summary>
        public static void RollbackTransaction()
        {
            if (m_Tran == null) return; //防止重复回滚
            DbConnection conn = m_Tran.Connection;
            m_Tran.Rollback();
            if (conn.State == ConnectionState.Open) conn.Close();
        }
        #endregion
        #endregion
    }
}

申明:扶助参数化的增删改查,推荐使用参数化的增删改查;非参数化的增删改查过滤了一些数据库重点字以预防SQL注入,但大概依然不安全。上边例子中的添加、修改、根据ID删除、依据ID集合批量刨除都以参数化的,示例中的查询和其它方式的删减不是参数化的,DBHelper提供了相关的参数化查询和施行SQL。

查询:

using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 内容详情
    /// </summary>
    [Serializable]
    public partial class cms_content
    {
        /// <summary>
        /// 编号
        /// </summary>
        [IsId]
        [IsDBField]
        public int id { get; set; }
        /// <summary>
        /// 所属栏目ID
        /// </summary>
        [IsDBField]
        public int? channelId { get; set; }
        /// <summary>
        /// 标题
        /// </summary>
        [IsDBField]
        public string title { get; set; }
        /// <summary>
        /// 内容
        /// </summary>
        [IsDBField]
        public string contents { get; set; }
        /// <summary>
        /// 作者
        /// </summary>
        [IsDBField]
        public string author { get; set; }
        /// <summary>
        /// 阅读次数
        /// </summary>
        [IsDBField]
        public int? readCount { get; set; }
        /// <summary>
        /// 发布时间
        /// </summary>
        [IsDBField]
        public DateTime? publishTime { get; set; }
        /// <summary>
        /// 发布者
        /// </summary>
        [IsDBField]
        public int? publishUserId { get; set; }
        /// <summary>
        /// 审核(0待审1通过2不通过)
        /// </summary>
        [IsDBField]
        public int? audit { get; set; }
        /// <summary>
        /// 审核人
        /// </summary>
        [IsDBField]
        public int? auditUserId { get; set; }
        /// <summary>
        /// 审核时间
        /// </summary>
        [IsDBField]
        public DateTime? auditTime { get; set; }
        /// <summary>
        /// 页面关键词
        /// </summary>
        [IsDBField]
        public string keywords { get; set; }
        /// <summary>
        /// 页面描述
        /// </summary>
        [IsDBField]
        public string description { get; set; }
        /// <summary>
        /// 页面链接
        /// </summary>
        [IsDBField]
        public string pageUrl { get; set; }
        /// <summary>
        /// 内容封面
        /// </summary>
        [IsDBField]
        public string imgUrl { get; set; }
        /// <summary>
        /// 是否链接(0否1是)
        /// </summary>
        [IsDBField]
        public int? isPageUrl { get; set; }
        /// <summary>
        /// 模板(模板文件名,例:content.html)
        /// </summary>
        [IsDBField]
        public string template { get; set; }
        /// <summary>
        /// 推荐(1推荐0不推荐)
        /// </summary>
        [IsDBField]
        public int? recommend { get; set; }
    }
}

public static int ExecuteSql(string sqlString)

/// <summary>
/// 添加
/// </summary>
public void Insert(object obj)
{
    DBHelper.Insert(obj);
}
public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select content.*, channel.title as channelName, user.showName
        from cms_content content
        left join cms_channel channel on channel.id=content.channelId
        left join CMS_sys_user user on user.id=content.publishUserId
        where 1=1 "));

    if (channelId != -1)
    {
        sql.AppendFormat(" and content.channelId = {0}", channelId);
    }
    if (!string.IsNullOrWhiteSpace(title))
    {
        sql.AppendFormat(" and content.title like '%{0}%'", title);
    }
    if (audit != -1)
    {
        sql.AppendFormat(" and content.audit = {0}", audit);
    }

    string orderby = string.Format("order by content.publishTime desc,id desc");
    pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
    return pager.result as List<cms_content>;
}

View Code

IsIdAttribute类:

View Code

添加:

澳门美高梅手机网站 24澳门美高梅手机网站 25

澳门美高梅手机网站 26澳门美高梅手机网站 27

View Code

        /// <summary>
        /// Sql
        /// </summary>
        /// <param name="formData"></param>
        /// <returns></returns>
        public override string GetReportSql(string formData)
        {
            TaskAllocationModel search = JsonConvert.DeserializeObject<TaskAllocationModel>(formData);
            StringBuilder where = new StringBuilder(" where 1=1 ");
            if (!string.IsNullOrEmpty(search.projectCode))
            {
                where.AppendFormat("  and ta.projectCode like '%{0}%' ", search.projectCode.Trim());
            }
            if (!string.IsNullOrEmpty(search.projectName))
            {
                where.AppendFormat("  and pro.projectName like '%{0}%' ", search.projectName.Trim());
            }
            string sql = @"select ta.billCode as '单号',pro.projectName as '项目名称',pro.projectNum as '项目编号',ta.desingBillCode as '项目设计任务单',
 CASE ta.violent WHEN '1' THEN '是' ELSE '否' END as '是否加急',
 CASE ta.taskType WHEN '0' THEN '载荷图' WHEN '1' THEN '方案图' WHEN '2' THEN '工程量统计' ELSE '载荷图与工程量统计' END as '任务类型',
ta.desingContent as '设计内容',ta.auditorScore as '审核人打分',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='设计' and oper.OperationID != -3) as '设计者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='校对' and oper.OperationID != -3) as '校对者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='审核' and oper.OperationID != -3) as '审核者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='审定' and oper.OperationID != -3) as '审定者',
e.name as '创建人',ta.creatTime as '创建时间'
from Pro_TaskAllocation ta
left join Pro_Info pro on ta.projectCode=pro.projectNum
left join sys_employee e on ta.creator=e.employeeCode 
                 " + where;

            return sql.ToString();
        }

澳门美高梅手机网站 28澳门美高梅手机网站 29

2、

分页查询:

IsDBFieldAttribute类:

View Code

try
{
    DBHelper.BeginTransaction(); //开启数据库事务

    //在这里写增删改操作

    DBHelper.CommitTransaction(); //提交数据库事务
}catch(Exception ex)
{
    DBHelper.RollbackTransaction(); //回滚数据库事务
}

澳门美高梅手机网站 30

View Code

配套Model生成器下载:http://files.cnblogs.com/files/s0611163/Model%E7%94%9F%E6%88%90%E5%99%A8.zip

DAL层增删改查示例:

MySql.Data.dll

其他格局的删减请使用:

数据库事务:

澳门美高梅手机网站 31澳门美高梅手机网站 32

View Code

澳门美高梅手机网站 33澳门美高梅手机网站 34

 

澳门美高梅手机网站 35澳门美高梅手机网站 36

根据ID删除:

澳门美高梅手机网站 37澳门美高梅手机网站 38

澳门美高梅手机网站 39澳门美高梅手机网站 40

Models示例:

取得最大ID(当然,ID一般拔取自增,对于并发量极少的种类,或单机系统,为了省事,可以如此做):

public static List<ConStockDialogMod> GetMaterialData(ref PagerModel pager, string memberName, string storeCode, string parentId, string store)
{
    StringBuilder where = new StringBuilder(" and 1=1 ");
    if (!string.IsNullOrEmpty(memberName))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and t3.memberName like '%{0}%' ", memberName.Trim());
    if (!string.IsNullOrEmpty(storeCode))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and SSH.storeHouseCode='{0}' ", storeCode);
    if (!string.IsNullOrEmpty(parentId))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and t3.parentId = {0} ", parentId);
    if (!string.IsNullOrEmpty(store))
        where.AppendFormat("  and SSH.storeHouseName like '%{0}%'  ", store.Trim());
    string sql = @"SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
        PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,SSH.storeHouseName AS storeName,
        t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity, 
        d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
        d3.dicItem as qulityName,d4.dicItem as weightUnitName,
        (select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty  
        from Pro_ConStock PM
        join Pro_ConPurInstore cpd on PM.billNo=cpd.billCode
        join Pro_ConPurInstoreDet cp on cpd.id=cp.parentId and PM.billId=cp.id
        join dbo.Pro_ConGoodsDet t on t.id=cp.conGoodsDetId
        join dbo.Pro_ConPurchaseContractDet t1 on t1.id=t.conContractDetId
        join dbo.Pro_ConPurchasePlanDet t2 on t2.id=t1.conPlanDetID
        join dbo.Pro_ConMemberDet t3 on t3.id=t2.conMemberDetId
        left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
        left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
        left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
        left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
        left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit 
        WHERE  PM.qty-PM.lockQty-PM.usedQty > 0  " + where +
    @" union 
        SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
        PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,
        SSH.storeHouseName AS storeName,
        t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity, 
        d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
        d3.dicItem as qulityName,d4.dicItem as weightUnitName,
        (select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty  
        from Pro_ConStock PM
        join Pro_ProductInstorage cpd on PM.billNo=cpd.billNo
        join Pro_ProductInstorageDet cp on cpd.id=cp.parentId and PM.billId=cp.id
        join dbo.Pro_ConMemberDet t3 on t3.id=cp.conMemberDetId
        left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
        left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
        left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
        left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
        left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit  
        WHERE  PM.qty-PM.lockQty-PM.usedQty > 0 " + where;


    List<ConStockDialogMod> model = EntityHelper.Default.GetPageEntities<ConStockDialogMod>(pager.page, pager.rows, sql.ToString(), "id", pager.sort, pager.order);
    pager.totalRows = DBHelper.Default.Count(sql.ToString());
    pager.result = model;
    return model;

}

澳门美高梅手机网站 41澳门美高梅手机网站 42

/// <summary>
/// GetMaxId
/// </summary>
public int GetMaxId()
{
    return DBHelper.GetMaxID<BS_Template>("id");
}

修改:

1、

澳门美高梅手机网站 43澳门美高梅手机网站 44

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DBUtil;
using Models;

namespace DAL
{
    /// <summary>
    /// 内容详情管理
    /// </summary>
    public class ContentDal
    {
        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where 1=1 "));

            if (channelId != -1)
            {
                sql.AppendFormat(" and content.channelId = {0}", channelId);
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                sql.AppendFormat(" and content.title like '%{0}%'", title);
            }
            if (audit != -1)
            {
                sql.AppendFormat(" and content.audit = {0}", audit);
            }

            string orderby = string.Format("order by content.publishTime desc,id desc");
            pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
            return pager.result as List<cms_content>;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="pager">分页</param>
        /// <param name="channel">栏目,可以是栏目ID或栏目名称</param>
        /// <param name="where">where语句</param>
        public List<cms_content> GetList(ref PagerModel pager, string channel, string where)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where content.audit=1 "));

            if (!string.IsNullOrWhiteSpace(where))
            {
                sql.AppendFormat(" and {0}", where);
            }

            if (!string.IsNullOrWhiteSpace(channel))
            {
                ChannelDal channelDal = new ChannelDal();
                if (Regex.IsMatch(channel, @"^\d+$")) //数字,即栏目ID
                {
                    string channelIds = channelDal.GetChildIds(Convert.ToInt32(channel));
                    sql.AppendFormat(" and channelId in ({0})", channelIds);
                }
                else //非数字,即栏目名称
                {
                    string channelIds = channelDal.GetChildIds(channel);
                    sql.AppendFormat(" and channelId in ({0})", channelIds);
                }
            }

            string orderby = string.Format("order by publishTime desc,id desc");
            if (pager.rows > 0)
            {
                PagerModel pagerModel = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
                pager.totalRows = pagerModel.totalRows;
                pager.result = pagerModel.result;
                List<cms_content> list = pagerModel.result as List<cms_content>;
                int i = 1;
                list.ForEach(a =>
                {
                    a.curPageSort = i++;
                });
                return pagerModel.result as List<cms_content>;
            }
            else
            {
                return DBHelper.FindListBySql<cms_content>(sql.ToString() + orderby);
            }
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<cms_content> GetListAll()
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where content.audit=1 
                order by publishTime desc,id desc"));

            return DBHelper.FindListBySql<cms_content>(sql.ToString());
        }
        #endregion

        #region 获取总数
        /// <summary>
        /// 获取总数
        /// </summary>
        public int GetAllCount()
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select count(*)
                from cms_content content
                where content.audit=1"));

            return Convert.ToInt32(DBHelper.GetSingle(sql.ToString()));
        }
        #endregion

        #region 获取
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content Get(int id)
        {
            return DBHelper.FindById<cms_content>(id.ToString());
        }
        /// <summary>
        /// 根据channelId获取一条内容详情
        /// </summary>
        public cms_content GetByChannelId(int channelId)
        {
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where channelId={0} and audit=1", channelId));
        }
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content GetNext(int id)
        {
            cms_content current = Get(id);
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id<{0} and channelId={1} and audit=1 order by id desc limit 0,1", current.id, current.channelId));
        }
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content GetPre(int id)
        {
            cms_content current = Get(id);
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id>{0} and channelId={1} and audit=1 order by id asc limit 0,1", current.id, current.channelId));
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public void Insert(cms_content model)
        {
            model.id = DBHelper.GetMaxID<cms_content>("id");
            DBHelper.Insert(model);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public void Update(cms_content model)
        {
            DBHelper.Update(model);
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        public void Del(string ids)
        {
            DBHelper.BatchDelete<cms_content>(ids);
        }
        #endregion

    }
}

View Code

View Code

View Code

            string sql = string.Format(@"select t.*,t.haveQty as weichukuNum,(t.qty - t.chukuNum) as shenyuNum  from (SELECT o.*,(
 case memberType when 1 then (SELECT (qty- lockQty- usedQty) 
 FROM Pro_MatStock s where s.id=o.matStockId )
 when 0 then ((SELECT (qty- lockQty- usedQty) 
 FROM Pro_ConStock s where s.id=o.matStockId )) else 0 end) as haveQty,
 (case memberType when 1 then (SELECT materialName from sys_material where materialCode = o.code )
 when 0 then (select DISTINCT memberName from Pro_ConMemberDet v LEFT join Pro_ConMember g on g.id=v.parentId where v.memberCode=o.code and g.billCode=ma.conMemberBillNo) else '' end) as materialName
 ,sh.storeHouseName as outStorageName ,
isnull((case memberType when 1 then (select SUM(pt.qty) from Pro_ProductOutstorageDet pt LEFT join Pro_ProductOutstorage p on p.id=pt.parentID where pt.matStockId=o.matStockId and p.status in (1,2))
 when 0 then (select SUM(pt.qty) from Pro_ProductOutstorageDet pt LEFT join Pro_ProductOutstorage p on p.id=pt.parentID where pt.conInventoryDetId=o.matStockId and p.status in (1,2)) else 0 end),0) as chukuNum,
  (case memberType when 1 then (select SUM(ms.qty-ms.lockQty-ms.usedQty) from Pro_MatStock ms where ms.storeCode=ma.inStoreCode AND ms.materailCode =o.code)
 when 0 then (select SUM(cs.qty-cs.lockQty-cs.usedQty) from Pro_ConStock cs where cs.storeCode=ma.inStoreCode AND cs.conMemberCode =o.code) else 0 end) as xiangmukuNum
 from  Pro_MatAllotDet o 
 JOIN Pro_MatAllot ma on o.parentId = ma.id
 join sys_storehouse sh on o.outStorageCode=sh.storeHouseCode  
 where o.parentId in ({0})) t where t.chukuNum < t.qty", parentId);

事例代码下载:http://files.cnblogs.com/files/s0611163/DBHelperDemo.zip

澳门美高梅手机网站 45澳门美高梅手机网站 46

/// <summary>
/// 删除
/// </summary>
public void BatchDelete(string ids)
{
    DBHelper.BatchDelete<BS_Template>(ids);
}

View Code

澳门美高梅手机网站 47澳门美高梅手机网站 48

View Code

发表评论

电子邮件地址不会被公开。 必填项已用*标注