【C#】DAL基本模板示例

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace WebApplication.DAL
{
    /// <summary>
    /// [category]表数据访问类
    /// 作者:Author
    /// 创建时间:2018-06-12 20:25:47
    /// </summary>
    public class CategoryDAL
    {
        public CategoryDAL()
        { }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(WebApplication.Model.Category model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [category](");
            strSql.Append("[categoryguid], [name], [shortname], [isshow] )");
            strSql.Append(" values (");
            strSql.Append("@categoryguid, @name, @shortname, @isshow )");
            strSql.Append(";select @@IDENTITY");
            MSSQLHelper h = new MSSQLHelper();
            h.CreateCommand(strSql.ToString());
            if (model.categoryguid == null)
            {
                 h.AddParameter("@categoryguid", DBNull.Value);
            }
            else
            {
                 h.AddParameter("@categoryguid", model.categoryguid);
            }
            if (model.name == null)
            {
                 h.AddParameter("@name", DBNull.Value);
            }
            else
            {
                 h.AddParameter("@name", model.name);
            }
            if (model.shortname == null)
            {
                 h.AddParameter("@shortname", DBNull.Value);
            }
            else
            {
                 h.AddParameter("@shortname", model.shortname);
            }
            if (model.isshow == null)
            {
                 h.AddParameter("@isshow", DBNull.Value);
            }
            else
            {
                 h.AddParameter("@isshow", model.isshow);
            }
            int result;
            string obj = h.ExecuteScalar();
            if (!int.TryParse(obj, out result))
            {
                return 0;
            }
            return result;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(WebApplication.Model.Category model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [category] set ");
            strSql.Append("[categoryguid]=@categoryguid, [name]=@name, [shortname]=@shortname, [isshow]=@isshow   ");
            strSql.Append(" where id=@id ");
            MSSQLHelper h = new MSSQLHelper();
            h.CreateCommand(strSql.ToString());
            if (model.id == null)
            {
                 h.AddParameter("@id", DBNull.Value);
            }
            else
            {
                 h.AddParameter("@id", model.id);
            }
            if (model.categoryguid == null)
            {
                 h.AddParameter("@categoryguid", DBNull.Value);
            }
            else
            {
                 h.AddParameter("@categoryguid", model.categoryguid);
            }
            if (model.name == null)
            {
                 h.AddParameter("@name", DBNull.Value);
            }
            else
            {
                 h.AddParameter("@name", model.name);
            }
            if (model.shortname == null)
            {
                 h.AddParameter("@shortname", DBNull.Value);
            }
            else
            {
                 h.AddParameter("@shortname", model.shortname);
            }
            if (model.isshow == null)
            {
                 h.AddParameter("@isshow", DBNull.Value);
            }
            else
            {
                 h.AddParameter("@isshow", model.isshow);
            }
            return h.ExecuteNonQuery();
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [category] ");
            strSql.Append(" where id=@id ");
            MSSQLHelper h = new MSSQLHelper();
            h.CreateCommand(strSql.ToString());
            h.AddParameter("@id", id);
            return h.ExecuteNonQuery();
        }

        /// <summary>
        /// 根据条件删除数据
        /// </summary>
        public bool DeleteByCond(string cond)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [category] ");
            if (!string.IsNullOrEmpty(cond))
            {
                strSql.Append(" where " + cond);
            }
            MSSQLHelper h = new MSSQLHelper();
            h.CreateCommand(strSql.ToString());
            return h.ExecuteNonQuery();
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM [category] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            MSSQLHelper h = new MSSQLHelper();
            h.CreateCommand(strSql.ToString());
            DataTable dt = h.ExecuteQuery();
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            return ds;
        }

    }
}