ylbtech-funcation-util:? C# 復雜條件查詢（sql 復雜條件查詢）查詢解決方案

?C# 復雜條件查詢（sql 復雜條件查詢）查詢解決方案

1.A,Ylbtech.Model返回頂部

A.1,DepartmentInfo.cs ?View Code A.2,EmployeeInfo.cs ?View Code

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 ?View Code
本文轉自ylbtech博客園博客，原文鏈接：http://www.cnblogs.com/ylbtech/p/3159867.html，如需轉載請自行聯系原作者

總結

以上是生活随笔為你收集整理的util-C# 复杂条件查询（sql 复杂条件查询）查询解决方案的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。