1.前台
<span id="spnClient" style="margin-left: 30px; margin-top: 10px">
<lable>Client: </lable>
<asp:DropDownList ID="ddlClient" DataValueField="ID" DataTextField="NAME" runat="server" />
</span> <span id="spnProject" style="margin-left: 30px; margin-top: 10px">
<lable>Project: </lable>
<asp:DropDownList ID="ddlProject" DataValueField="ID" DataTextField="NAME" runat="server" />
</span>
<span id="spnUser" style="display: none; margin-left: 30px; margin-top: 10px;">
<lable>User: </lable>
<asp:DropDownList ID="ddlUser" DataValueField="ID" DataTextField="USER_NAME" runat="server" />
</span>
2.绑定DataSource
private void LoadDropDownList()
{
DataTable dtYear =SplendidCache.Years();
ddlYEAR.DataSource = dtYear;
ddlYEAR.DataBind();
ddlYEAR.SelectedValue = DateTime.Now.Year.ToString(); DataTable dtClient = SplendidCache.Client(true);
ddlClient.DataSource = dtClient;
ddlClient.DataBind(); DataTable dtProject = SplendidCache.Project();
ddlProject.DataSource = dtProject;
ddlProject.DataBind(); DataTable dtUser = SplendidCache.AssignedUser();
ddlUser.DataSource = dtUser;
ddlUser.DataBind(); BindProjects();
BindUsers();
}
private void BindProjects()
{
DataTable dtProjects = new DataTable();
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
string sSQL = "SELECT ID,NAME,CLIENTID FROM Project WHERE DELETED=0 ORDER BY NAME " + ControlChars.CrLf;
cmd.CommandText = sSQL;
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
da.Fill(dtProjects);
StringBuilder sbProjects = new StringBuilder();
if (dtProjects != null && dtProjects.Rows.Count > )
{
sbProjects.Append("{\"project\":[");
int i = ;
int count = dtProjects.Rows.Count;
foreach (DataRow row in dtProjects.Rows)
{
if (i == count)
{
sbProjects.Append("{\"client\":\"" + Sql.ToString(row["CLIENTID"]) + "\",\"id\":\"" + Sql.ToString(row["ID"]) + "\",\"name\":\"" + Sql.ToString(row["NAME"]).Replace("'","‘") + "\"}");
}
else
{
sbProjects.Append("{\"client\":\"" + Sql.ToString(row["CLIENTID"]) + "\",\"id\":\"" + Sql.ToString(row["ID"]) + "\",\"name\":\"" + Sql.ToString(row["NAME"]).Replace("'", "‘") + "\"},");
}
i++;
}
sbProjects.Append("]");
}
sbProjects.Append("}");
m_sProjectsJson = sbProjects.ToString();
}
}
}
}
private void BindUsers()
{
DataTable dtUsers = new DataTable();
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
string sSQL = "SELECT DISTINCT USER_ID,USER_NAME,PROJECT_ID FROM vwPROJECT_USERS WHERE USER_ID IS NOT NULL GROUP BY USER_ID,USER_NAME,PROJECT_ID ORDER BY USER_NAME" + ControlChars.CrLf;
cmd.CommandText = sSQL;
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
da.Fill(dtUsers);
StringBuilder sbUsers = new StringBuilder();
if (dtUsers != null && dtUsers.Rows.Count > )
{
sbUsers.Append("{\"user\":[");
int i = ;
int count = dtUsers.Rows.Count;
foreach (DataRow row in dtUsers.Rows)
{
if (i == count)
{
sbUsers.Append("{\"project\":\"" + Sql.ToString(row["PROJECT_ID"]) + "\",\"id\":\"" + Sql.ToString(row["USER_ID"]) + "\",\"name\":\"" + Sql.ToString(row["USER_NAME"]) + "\"}");
}
else
{
sbUsers.Append("{\"project\":\"" + Sql.ToString(row["PROJECT_ID"]) + "\",\"id\":\"" + Sql.ToString(row["USER_ID"]) + "\",\"name\":\"" + Sql.ToString(row["USER_NAME"]) + "\"},");
}
i++;
}
sbUsers.Append("]");
}
sbUsers.Append("}");
m_sUserJson = sbUsers.ToString();
}
}
}
}
private static string m_sProjectsJson = "";
public static string ProjectsJson
{
get { return m_sProjectsJson; }
} private static string m_sUserJson = "";
public static string UsersJson
{
get { return m_sUserJson; }
}
3.javascript
$("#<%=ddlClient.ClientID%>").change(function () {
loadproject();
});
$("#<%=ddlProject.ClientID%>").change(function () {
loaduser();
});
function loadproject() {
var client = $("#<%=ddlClient.ClientID%>").val();
var projectArray = jQuery.parseJSON('<%= ProjectsJson%>');
var filtered = $.grep(projectArray.project,
function (item) {
if (client != "") {
return item.client == client;
}
else {
return item;
}
}
);
$("#<%=ddlProject.ClientID%>").empty();
$.each(filtered, function (i, item) {
$("#<%=ddlProject.ClientID%>").append("<option value='" + item.id + "'>" + item.name + "</option>");
});
}
function loaduser() {
var project = $("#<%=ddlProject.ClientID%>").val();
var userArray = jQuery.parseJSON('<%= UsersJson%>');
var filtered = $.grep(userArray.user,
function (item) {
if (project != "") {
return item.project == project;
}
else {
return item;
}
}
);
$("#<%=ddlUser.ClientID%>").empty();
$.each(filtered, function (i, item) {
$("#<%=ddlUser.ClientID%>").append("<option value='" + item.id + "'>" + item.name + "</option>");
});
}