三层实现增删改查 并且多条件查询
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);
}
}
}
