效果:
逻辑代码
protected void Page_Load(object sender, EventArgs e)
{
FillRadCBDept(null, Session["recordDeptId"] == null ? "0" : Session["recordDeptId"].ToString());
}
private void FillRadCBDept(DateTime? limitDate, string deptIdBefore)
{
dcb.Items.Clear();
List<Dept> depts = Business.GetDepts(limitDate);
RadComboBoxItem radcbItem;
bool selected = false;
foreach (Dept dept in depts)
{
radcbItem = new RadComboBoxItem(dept.DeptName, dept.DeptId.ToString());
dcb.Items.Add(radcbItem);
if (!selected && (dept.DeptId.ToString() == deptIdBefore || (string.Compare(deptIdBefore, "0") == 0 && dept.TeamNum > 0)))
{
radcbItem.Selected = true;
selected = true;
}
}
if (!selected && dcb.FindItemByValue(deptIdBefore) != null)
dcb.FindItemByValue(deptIdBefore).Selected = true;
}
业务逻辑层
public static List<Dept> GetDepts(DateTime? limitDate)
{
return Data.GetDepts(limitDate);
}
数据访问层
public static List<Dept> GetDepts(DateTime? limitDate)
{
var depts = new List<Dept>();
string strSql;
DataTable dataTable;
if (limitDate == null)
{
strSql = "SELECT d.DeptId,deptName,COUNT(dt.TeamId) FROM Dept d left join DeptTeam dt on d.DeptId = dt.DeptId where d.endDate is null group by d.DeptId,deptName";
dataTable = DbConnector.GenerateDataTable(strSql);
}
else
{
strSql = "SELECT d.DeptId,deptName,COUNT(dt.TeamId) FROM Dept d left join DeptTeam dt on d.DeptId = dt.DeptId where (d.endDate>@limitDate or d.endDate is null) group by d.DeptId,deptName";
dataTable = DbConnector.GenerateDataTable(strSql, new[] { new SqlParameter("@limitDate", limitDate) });
}
if(dataTable!=null&&dataTable.Rows.Count>0)
{
for(int i=0;i<dataTable.Rows.Count;i++)
{
depts.Add(new Dept(Convert.ToInt32(dataTable.Rows[i][0]),dataTable.Rows[i][1].ToString(),Convert.ToInt32(dataTable.Rows[i][2])));
}
}
return depts;
}
#region these methods return a DataTable
/// <summary>
///
/// </summary>
/// <param name="query">a sql query statement</param>
/// <param name="parameters">sql parameters collection</param>
/// <returns></returns>
public static DataTable GenerateDataTable(string query, params SqlParameter[] parameters)
{
if (connStr == "")
loadConnStrings();
var conn = new SqlConnection(connStr);
DataTable dataTable = GenerateDataTable(query, conn, parameters);
try
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (SqlException sqle)
{
throw (sqle);
}
return dataTable;
}
跳转多态的方法
/// <summary>
///
/// </summary>
/// <param name="query">a sql query statement</param>
/// <param name="conn">sqlconnection object</param>
/// <param name="parameters">sql parameters collection</param>
/// <returns></returns>
public static DataTable GenerateDataTable(string query, SqlConnection conn, params SqlParameter[] parameters)
{
DataTable dataTable = null;
try
{
if (conn != null && conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
conn.Open();
var cmd = new SqlCommand(query, conn);
cmd.CommandTimeout = 200;
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
var adapter = new SqlDataAdapter {SelectCommand = cmd};
dataTable = new DataTable();
adapter.Fill(dataTable);
adapter.Dispose();
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (Exception sqle)
{
throw sqle;
}
return dataTable;
}
实体类定义
/// <summary>
/// Dept Enity
/// </summary>
public class Dept
{
public Dept(int deptId,string deptName,int teamNum)
{
DeptId = deptId;
DeptName = deptName;
TeamNum = teamNum;
}
public int DeptId { get; set; }
public string DeptName { get; set; }
public int TeamNum { get; set; }
}
}