三层实现增删改查 并且多条件查询

1.DAL层

 

using Model;

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace MyDAL

{

    public class UserInfoDal

    {

        public object InsertUserInfo(TUserInfo model)

        {

            string sql = "INSERT INTO UserInfo output inserted.Id VALUES(@name,@age,@height,@gender)";

            SqlParameter[] pms = { new SqlParameter("@name",model.UName),

                               new SqlParameter("@age",model.UAge),

                               new SqlParameter("@height",model.UHeight==null?DBNull.Value:(object)model.UHeight),

                               new SqlParameter("@gender",model.UGender==null?DBNull.Value:(object)model.UGender)

        };

            return SqlHelper.ExecuteScalar(sql, pms);

        }

 

        public int UpdateUserInfo(TUserInfo model)

        {

            string sql = "UPDATE UserInfo SET UName=@name,UAge=@age,UHeight=@height,UGender=@gender WHERE Id=@id";

            SqlParameter[] pms = { new SqlParameter("@name",model.UName),

                                     new SqlParameter("@age",model.UAge),

                                     new SqlParameter("@height",model.UHeight==null?DBNull.Value:(object)model.UHeight),

                                     new SqlParameter("@gender",model.UGender==null?DBNull.Value:(object)model.UGender),

                                     new SqlParameter("@id",model.Id),

                                 };

            return SqlHelper.ExecuteNonQuery(sql,pms);

        }

 

        public int DelUserInfo(int id)

        {

            string sql = "DELETE FROM UserInfo WHERE Id=@id";

            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@id", id));

        }

 

        public List<TUserInfo> SelUserInfo()

        {

            List<TUserInfo> list = new List<TUserInfo>();

            string sql = "SELECT * FROM UserInfo";

            using (SqlDataReader reader = SqlHelper.ExecuteSqlDataReader(sql))

            {

                if (reader.HasRows)

                {

                    while (reader.Read())

                    {

                        TUserInfo model = new TUserInfo();

                        model.Id=reader.GetInt32(0);

                        model.UName=reader.GetString(1);

                        model.UAge=reader.GetInt32(2);

                        model.UHeight=reader.IsDBNull(3)?null:(int?)reader.GetInt32(3);

                        model.UGender = reader.IsDBNull(4)? null : (bool?)reader.GetBoolean(4);

                        list.Add(model);

                    }

 

                }

            }

 

            return list;

        }

 

        public TUserInfo GetUserInfoById(int id)

        {

            string sql = "SELECT * FROM UserInfo WHERE Id=@id";

            using (SqlDataReader reader = SqlHelper.ExecuteSqlDataReader(sql, new SqlParameter("@id", id)))

            {

                if (reader.HasRows)

                {

                    if (reader.Read())

                    {

                        TUserInfo model = new TUserInfo();

                        model.Id=reader.GetInt32(0);

                        model.UName=reader.GetString(1);

                        model.UAge=reader.GetInt32(2);

                        model.UHeight=reader.IsDBNull(3)?null:(int?)reader.GetInt32(3);

                        model.UGender = reader.IsDBNull(4)? null : (bool?)reader.GetBoolean(4);

                        return model;

                    }

                }

                return null;

            }

        }

 

        public List<TUserInfo> Search(List<Condition> list)

        {

            StringBuilder sql = new StringBuilder("SELECT * FROM dbo.UserInfo");

            List<string> wheres = new List<string>();

            List<SqlParameter> pms = new List<SqlParameter>();

            foreach (var item in list)

            {

                StringBuilder sb = new StringBuilder();

                sb.AppendFormat(" {0} ",item.PropertyName);

                SqlParameter p1=new SqlParameter("@"+item.PropertyName,item.PropertyValue);

                switch(item.Operator)

                {

                    case Opt.Equal:

                        sb.Append(" = ");

                        break;

                    case Opt.NoEqual:

                        sb.Append(" <> ");

                        break;

                    case Opt.MoreThan:

                        sb.Append(" > ");

                        break;

                    case Opt.LessThan:

                        sb.Append(" < ");

                        break;

                    case Opt.Like:

                        sb.Append(" like ");

                        p1.Value= "%" + item.PropertyValue + "%";

                        break;

                }

 

                sb.AppendFormat("@{0}",item.PropertyName);

                wheres.Add(sb.ToString());

                pms.Add(p1);

            }

            if (wheres.Count > 0)

            {

                sql.Append(" where " + string.Join(" and ", wheres.ToArray()));

            }

 

            SqlParameter[] para = pms.ToArray();

 

            List<TUserInfo> modelList=new List<TUserInfo>();

            using (SqlDataReader reader = SqlHelper.ExecuteSqlDataReader(sql.ToString(), para))

            {

                if (reader.HasRows)

                {

                    while (reader.Read())

                    {

                        TUserInfo model = new TUserInfo();

                        model.Id=reader.GetInt32(0);

                        model.UName=reader.GetString(1);

                        model.UAge=reader.GetInt32(2);

                        model.UHeight=reader.IsDBNull(3)?null:(int?)reader.GetInt32(3);

                        model.UGender=reader.IsDBNull(4)?null:(bool?)reader.GetBoolean(4);

                        modelList.Add(model);

                    }

                }

            }

 

            return modelList;

        }

    }

}

 

 

2.BLL层

 

using Model;

using MyDAL;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace MyBLL

{

    public class UserInfoBll

    {

        UserInfoDal dal = new UserInfoDal();

        public int InsertUserInfo(TUserInfo model)

        {

            return (int)dal.InsertUserInfo(model);

        }

 

        public int UpdateUserInfo(TUserInfo model)

        {

            return dal.UpdateUserInfo(model);

        }

 

        public int DelUserInfo(int id)

        {

            return dal.DelUserInfo(id);

        }

 

        public List<TUserInfo> SelUserInfo()

        {

            return dal.SelUserInfo();

        }

 

        public TUserInfo GetUserInfoById(int id)

        {

            return dal.GetUserInfoById(id);

        }

 

        public List<TUserInfo> Search(List<Condition> list)

        {

            return dal.Search(list);

        }

    }

}

 

 

3.Model层

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace Model

{

    public class TUserInfo

    {

        public int Id { get; set; }

        public string UName { get; set; }

        public int UAge { get; set; }

        public int? UHeight { get; set; }

        public bool? UGender { get; set; }

    }

}

 

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace Model

{

    public class Condition

    {

        public string PropertyName { get; set; }

        public object PropertyValue { get; set; }

        public Opt Operator { get; set; }

    }

 

    public enum Opt

    {

        Equal,

        NoEqual,

        MoreThan,

        LessThan,

        Like

    }

}

 

 

4.UI层

 

增：

using Model;

using MyBLL;

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

 

namespace MyDBU

{

    public partial class frmInsert : Form

    {

        public frmInsert()

        {

            InitializeComponent();

        }

 

        private void btnInsert_Click(object sender, EventArgs e)

        {

            TUserInfo model = new TUserInfo();

            string name = txtUserName.Text.Trim();

            int age = Convert.ToInt32(txtAge.Text.Trim());

            int? height = string.IsNullOrEmpty(txtHeight.Text.Trim())?null:(int?)Convert.ToInt32(txtHeight.Text.Trim());

            bool? gender = string.IsNullOrEmpty(cboGender.Text) ? null : (bool?)(cboGender.Text == "男" ? true : false);

 

            model.UName = name;

            model.UAge = age;

            model.UHeight = height;

            model.UGender = gender;

 

            UserInfoBll bll=new UserInfoBll();

            int r = bll.InsertUserInfo(model);

            MessageBox.Show("创建成功，自动ID编号:"+r);

 

        }

    }

}

 

删：

using MyBLL;

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

 

namespace MyDBU

{

    public partial class frmDelete : Form

    {

        public frmDelete()

        {

            InitializeComponent();

        }

 

        private void btnDelete_Click(object sender, EventArgs e)

        {

            int id = Convert.ToInt32(txtId.Text.Trim());

            UserInfoBll bll = new UserInfoBll();

            int r=bll.DelUserInfo(id);

            MessageBox.Show("删除成功！影响的行数："+r);

        }

    }

}

 

改：

 

using Model;

using MyBLL;

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

 

namespace MyDBU

{

    public partial class frmUpdate : Form

    {

        public frmUpdate()

        {

            InitializeComponent();

        }

 

        private void btnUpdate_Click(object sender, EventArgs e)

        {

            int id = Convert.ToInt32(txtId.Text.Trim());

            string name = txtUserName.Text.Trim();

            int age = Convert.ToInt32(txtAge.Text.Trim());

            int? height = string.IsNullOrEmpty(txtHeight.Text.Trim()) ? null : (int?)Convert.ToInt32(txtHeight.Text.Trim());

            bool? gender = string.IsNullOrEmpty(cboGender.Text) ? null : (bool?)(cboGender.Text == "男" ? true : false);

 

            UserInfoBll bll=new UserInfoBll();

            TUserInfo model = bll.GetUserInfoById(id);

            model.UName = name;

            model.UAge = age;

            model.UHeight = height;

            model.UGender = gender;

 

            int r = bll.UpdateUserInfo(model);

            MessageBox.Show("修改成功！影响行数："+r);

 

        }

    }

}

 

查：

using Model;

using MyBLL;

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

 

namespace MyDBU

{

    public partial class frmSel : Form

    {

        public frmSel()

        {

            InitializeComponent();

        }

 

        private void frmSel_Load(object sender, EventArgs e)

        {

            UserInfoBll bll = new UserInfoBll();

            List<TUserInfo> list = bll.SelUserInfo();

            this.dataGridView1.DataSource = list;

        }

    }

}

 

多查：

using Model;

using MyBLL;

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

 

namespace MyDBU

{

    public partial class frmMutiCondition : Form

    {

        public frmMutiCondition()

        {

            InitializeComponent();

        }

 

        private void btnMutiSelect_Click(object sender, EventArgs e)

        {

            List<Condition> list = new List<Condition>();

 

            if (txtUserName.Text.Trim().Length > 0)

            {

                Condition cdn = new Condition();

                cdn.PropertyName = "UName";

                cdn.PropertyValue = txtUserName.Text.Trim();

                cdn.Operator = Opt.Like;

                list.Add(cdn);

            }

            if(txtAge.Text.Trim().Length>0)

            {

                Condition cdn = new Condition();

                cdn.PropertyName = "UAge";

                cdn.PropertyValue = txtAge.Text.Trim();

                cdn.Operator = Opt.Equal;

                list.Add(cdn);

            }

            if(txtHeight.Text.Trim().Length>0)

            {

                Condition cdn = new Condition();

                cdn.PropertyName = "UHeight";

                cdn.PropertyValue = txtHeight.Text.Trim();

                cdn.Operator = Opt.Equal;

                list.Add(cdn);

            }

            if(cboGender.Text.Trim().Length>0)

            {

                Condition cdn = new Condition();

                cdn.PropertyName = "UGender";

                cdn.PropertyValue = cboGender.Text=="男"?true:false;

                cdn.Operator = Opt.Equal;

                list.Add(cdn);

            }

 

            UserInfoBll bll = new UserInfoBll();

            dataGridView1.DataSource=bll.Search(list);

        }

    }

}