util-C# 复杂条件查询（sql 复杂条件查询）查询解决方案
| ylbtech-funcation-util: C# 复杂条件查询（sql 复杂条件查询）查询解决方案 |
C# 复杂条件查询（sql 复杂条件查询）查询解决方案
| 1.A,Ylbtech.Model返回顶部 |
A.1,DepartmentInfo.cs
using System; namespace Ylbtech.Model { /// <summary> ///DepartmentInfo 的摘要说明 ///desc: 部门表 /// </summary> public class DepartmentInfo { /// <summary> /// 1，编号【PK】 /// </summary> public int DepartmentId { get; set; } /// <summary> /// 部门名称 /// </summary> public string DepartmentName { get; set; } public DepartmentInfo() { // //TODO: 在此处添加构造函数逻辑 // } } }
A.2,EmployeeInfo.cs
using System; namespace Ylbtech.Model { /// <summary> ///EmployeeInfo 的摘要说明 ///desc:员工表 /// </summary> public class EmployeeInfo : DepartmentInfo { /// <summary> /// 1,编号【PK】 /// </summary> public int EmployeeId { get; set; } /// <summary> /// 身份证号 /// </summary> public string Id { get; set; } /// <summary> /// 姓名 /// </summary> public string Username { get; set; } /// <summary> /// 性别【C】男,女 /// </summary> public string Sex { get; set; } /// <summary> /// 银行卡号 /// </summary> public string CardNo { get; set; } /// <summary> /// ，6 受雇日期 /// </summary> public string HireDate { get; set; } /// <summary> /// 部门编号【Fk】 /// </summary> public int DepartmentId { get; set; } /// <summary> /// 状态（正式内、正式外、中心版） /// </summary> public string State { get; set; } /// <summary> /// 转正日期 /// </summary> public string ToRegularDate { get; set; } /// <summary> /// 地址 /// </summary> public string Address { get; set; } /// <summary> /// 自有/租住 /// </summary> public string IsRent { get; set; } /// <summary> /// 电话 /// </summary> public string TelNo { get; set; } /// <summary> /// FuJia:孩子的个数 /// </summary> public int ChildrenNumber { get; set; } /// <summary> /// 儿童节发放金额 /// </summary> public Decimal ChildrenMoney { get; set; } /// <summary> /// 序号 /// </summary> public int SerialNumber { get; set; } /// <summary> /// 普通假日支付金额 /// </summary> public Decimal PaymentAmount { get; set; } /// <summary> /// 奶粉费 /// </summary> public Decimal NaiFenFei { get; set; } /// <summary> /// 医疗费用 /// </summary> public Decimal YiLiaoFeiYong { get; set; } /// <summary> /// 合计 /// </summary> public Decimal Total { get; set; } /// <summary> /// 文体费 /// </summary> public Decimal WenTiFei { get; set; } /// <summary> /// 健身费 /// </summary> public Decimal JianShenFei { get; set; } /// <summary> /// 卫生费【只有女职工有】 /// </summary> public Decimal WeiShengFei { get; set; } /// <summary> /// 家财 /// </summary> public Decimal JiaCai { get; set; } /// <summary> /// 人身意外 /// </summary> public Decimal RenShenYiWai { get; set; } /// <summary> /// 女性特种疾病 /// </summary> public Decimal NvXingTeZhongJiBing { get; set; } /// <summary> /// 交通意外 /// </summary> public Decimal JiaoTongYiWai { get; set; } public EmployeeInfo() { // //TODO: 在此处添加构造函数逻辑 // } } }
| 1.B,Ylbtech.DAL返回顶部 |
B.1,Employee.cs
using System; using System.Collections.Generic; using System.Linq; using Ylbtech.Model; using Microsoft.DBUtility; using System.Text; using System.Data.SqlClient; using Ylbtech.Common; namespace Ylbtech.DAL { /// <summary> ///Employee 的摘要说明 ///desc：员工操作类 ///author:yuanbo ///date:2013-6-12 /// </summary> public class Employee { /// <summary> /// ylb:查询员工信息 /// </summary> /// <param name="dalEmployee"></param> /// <returns></returns> public static IList<EmployeeInfo> GetAllSearch(EmployeeInfo dalEmployee) { IList<EmployeeInfo> dals = new List<EmployeeInfo>(); string sql = "select employeeId,[id],username,sex,cardNo" + ",hireDate,e.departmentId,state,ToRegularDate,d.departmentName from Employee e inner join Department d on e.departmentId=d.departmentId"; StringBuilder sbSql = new StringBuilder(); sbSql.Append(sql); IList<SqlParameter> sqlParamI = new List<SqlParameter>(); if (dalEmployee.EmployeeId != 0) /*单一查询条件*/ { sqlParamI.Add(new SqlParameter("@employeeId", dalEmployee.EmployeeId)); sbSql.Append(" where employeeId=@employeeId"); } else if (dalEmployee.Id.Length > 0) /*单一查询条件*/ { sqlParamI.Add(new SqlParameter("@id", dalEmployee.Id)); sbSql.Append(" where [id]=@id"); } else /*复合条件查询*/ { sbSql.Append(" where 1=1"); if (dalEmployee.Username.Length > 0) { sbSql.Append(" and username=@username"); sqlParamI.Add(new SqlParameter("@username", dalEmployee.Username)); } if (dalEmployee.Sex != "-1") { sbSql.Append(" and sex=@sex"); sqlParamI.Add(new SqlParameter("@sex", dalEmployee.Sex)); } if (dalEmployee.CardNo.Length > 0) { sbSql.Append(" and cardNo=@cardNo"); sqlParamI.Add(new SqlParameter("@cardNo", dalEmployee.CardNo)); } DateTime hireDate; if (DateTime.TryParse(dalEmployee.HireDate, out hireDate)) { sbSql.Append(" and hiredate=@hiredate"); sqlParamI.Add(new SqlParameter("@hiredate", hireDate)); } if (dalEmployee.DepartmentId != -1) { sbSql.Append(" and e.departmentId=@departmentId"); sqlParamI.Add(new SqlParameter("@departmentId", dalEmployee.DepartmentId)); } if (dalEmployee.State != "-1") { sbSql.Append(" and state=@state"); sqlParamI.Add(new SqlParameter("@state", dalEmployee.State)); } DateTime toRegularDate; if (DateTime.TryParse(dalEmployee.ToRegularDate, out toRegularDate)) { sbSql.Append(" and toRegularDate=@toRegularDate"); sqlParamI.Add(new SqlParameter("@toRegularDate", dalEmployee.ToRegularDate)); } } sbSql.Append(" order by employeeId desc"); SqlParameter[] sqlParam = new SqlParameter[sqlParamI.Count]; //A：方式一 //for (int i = 0; i < sqlParamI.Count; i++) //{ // sqlParam[i] = sqlParamI[i]; //} //A:方式二 sqlParam = sqlParamI.ToArray(); using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnStr_WelfareSystem, System.Data.CommandType.Text, sbSql.ToString(), sqlParam)) { while (sdr.Read()) { EmployeeInfo dal = new EmployeeInfo() { EmployeeId = sdr.GetInt32(0), Id = sdr[1] == System.DBNull.Value ? "" : sdr.GetString(1), Username = sdr[2] == System.DBNull.Value ? "" : sdr.GetString(2), Sex = sdr[3] == System.DBNull.Value ? "" : sdr.GetString(3), CardNo = sdr[4] == System.DBNull.Value ? "" : sdr.GetString(4), HireDate = DateTimeFun.DateTimeConvertToyyyy_MM_dd(sdr[5] == System.DBNull.Value ? "" : sdr.GetString(5)), DepartmentId = sdr.GetInt32(6), State = sdr[7] == System.DBNull.Value ? "" : sdr.GetString(7), ToRegularDate = DateTimeFun.DateTimeConvertToyyyy_MM_dd(sdr[8] == System.DBNull.Value ? "" : sdr.GetString(8)), DepartmentName = sdr[9] == System.DBNull.Value ? "" : sdr.GetString(9) }; dals.Add(dal); } } return dals; } public Employee() { // //TODO: 在此处添加构造函数逻辑 // } } }
| 1.C,Ylbtech.DBUtility返回顶部 |
C.SqlHelper.cs [略]
| 1.D,Ylbtech.Common返回顶部 |
D.1,DateTiemFun.cs
using System; namespace Ylbtech.Common { /// <summary> ///DateTimeFun 的摘要说明 ///日期操作相关函数 /// </summary> public class DateTimeFun { /// <summary> /// 1,判断字符串是否日期类型。如果是则转换为 yyyy-MM-dd，否则 赋值于 "" /// </summary> /// <param name="strDate"></param> /// <returns></returns> public static string DateTimeConvertToyyyy_MM_dd(string strDate) { string date = ""; DateTime toRegularDate; if (DateTime.TryParse(strDate.Trim(), out toRegularDate)) { date = toRegularDate.ToString("yyyy-MM-dd"); } return date; } public DateTimeFun() { // //TODO: 在此处添加构造函数逻辑 // } } }
| 1.E,Additional data返回顶部 |
E.1,/App_Data
use master go -- ============================================= -- DatabaseName: WelfareSystem -- remark: 福利发放系统 -- author: YuanBo -- date: 09:51 2013-03-26 -- ============================================= IF EXISTS (SELECT * FROM master..sysdatabases WHERE name = N'WelfareSystem') DROP DATABASE WelfareSystem GO CREATE DATABASE WelfareSystem GO use WelfareSystem go -- ============================================= -- ylb:1,部门表 -- ============================================= create table Department ( departmentId int primary key identity(100,1), --编号【PK】 departmentName varchar(100) --部门名称 ) go -- ============================================= -- ylb:2,员工表 -- ============================================= create table Employee ( employeeId int primary key identity(1001,1), --编号【PK】 [id] varchar(18), --身份证号 username varchar(40), --姓名 sex varchar(5) check(sex='男'or sex='女'),--性别 cardNo varchar(22), --银行卡号 hireDate datetime, --受雇日期 departmentId int, --部门编号 [state] varchar(8), --员工性质（正式内，正式入，中心版） toRegularDate varchar(20), --转正日期 [address] varchar(200), --地址 isRent varchar(20), --自住/租房 telNo varchar(20) --家庭电话 ) print'福利发放系统创建成功！'
 |
作者：ylbtech 出处：http://ylbtech.cnblogs.com/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 |
