1
//通过DataTable 绑定数据
BindRegion(); 2 3 4 private void BindRegion() 5 { 6 // ddlRgn.DataSource = Location.GetRegionByWWID(System.Configuration.ConfigurationManager.AppSettings["DSN"].DecodeBase64(), ViewState["WWID"].ToString().ToHtmlEncode()); 7 string type = "region"; 8 ddlRgn.DataSource = BindData(ddlQuarter.SelectedValue,string.Empty,string.Empty,string.Empty,string.Empty, type); 9 ddlRgn.DataValueField = "Key"; 10 ddlRgn.DataTextField = "Value"; 11 ddlRgn.DataBind(); 12 ddlRgn.Items.Insert(0, new ListItem("ALL", "ALL")); 13 } 14 15 16 17 private DataTable BindData(string quater, string rgn, string subrgn, string grid,string city, string type) 18 { 19 DataTable ddt = new DataTable(); 20 21 SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["DSN"].DecodeBase64()); 22 SqlCommand cmd = new SqlCommand(); 23 SqlDataAdapter da = new SqlDataAdapter(); 24 25 try 26 { 27 cmd.Connection = conn; 28 cmd.CommandText = "region_subregion_grid_pref_city_by_wwid_quarter"; 29 cmd.CommandType = CommandType.StoredProcedure; 30 31 cmd.Parameters.AddWithValue("@yyyyqq", quater); 32 if (ddlRgn.SelectedValue==string.Empty || ddlRgn.SelectedValue=="ALL") 33 { 34 cmd.Parameters.AddWithValue("@rgn_cd",DBNull.Value); 35 }else 36 { 37 cmd.Parameters.AddWithValue("@rgn_cd", rgn); 38 } 39 cmd.Parameters.AddWithValue("@subrgn_cd",DBNull.Value); 40 if (ddlGrid.SelectedValue==string.Empty || ddlGrid.SelectedValue=="ALL") 41 { 42 cmd.Parameters.AddWithValue("@grid_cd", DBNull.Value); 43 } 44 else 45 { 46 cmd.Parameters.AddWithValue("@grid_cd", grid); 47 } 48 cmd.Parameters.AddWithValue("@pref_cd", DBNull.Value); 49 if (ddlCity.SelectedValue==string.Empty || ddlCity.SelectedValue=="ALL") 50 { 51 cmd.Parameters.AddWithValue("@city_id", DBNull.Value); 52 } 53 else 54 { 55 cmd.Parameters.AddWithValue("@city_id", city); 56 } 57 cmd.Parameters.AddWithValue("@wwid", txtWWID.Text); 58 cmd.Parameters.AddWithValue("@type", type); 59 da.SelectCommand = cmd; 60 da.Fill(ddt); 61 } 62 catch //(Exception) 63 { 64 ddt = null; 65 } 66 finally 67 { 68 conn.Close(); 69 da.Dispose(); 70 cmd.Dispose(); 71 } 72 return ddt; 73 }
//通过 Dictionary 绑定数据
1 private void BindRegion() 2 5 { 3 6 ddlRgn.DataSource = Location.GetRegionByWWID(System.Configuration.ConfigurationManager.AppSettings["DSN"].DecodeBase64(), ViewState["WWID"].ToString().ToHtmlEncode()); 4 7 string type = "region"; 5 8 // ddlRgn.DataSource = BindData(ddlQuarter.SelectedValue,string.Empty,string.Empty,string.Empty,string.Empty, type); 6 9 ddlRgn.DataValueField = "Key"; 7 10 ddlRgn.DataTextField = "Value"; 8 11 ddlRgn.DataBind(); 9 12 ddlRgn.Items.Insert(0, new ListItem("ALL", "ALL")); 10 13 }
1 public static Dictionary<string, string> GetRegionByWWID(string connectionString, string wwId) 2 { 3 SqlParameter[] paraList = new[] 4 { 5 new SqlParameter("@rgn_cd", DBNull.Value), 6 new SqlParameter("@subrgn_cd", DBNull.Value), 7 new SqlParameter("@grid_cd", DBNull.Value), 8 new SqlParameter("@pref_cd", DBNull.Value), 9 new SqlParameter("@city_id", DBNull.Value), 10 new SqlParameter("@wwid", wwId), 11 new SqlParameter("@type", "region") 12 }; 13 Dictionary<string, string> list = new Dictionary<string, string>(); 14 using (IDataReader reader = SqlHelper.ExecuteReader(connectionString, "region_subregion_grid_pref_city_by_wwid", paraList)) 15 { 16 while (reader.Read()) 17 { 18 list.Add(reader["rgn_cd"].ToString().Trim(), reader["rgn_nm"].ToString().Trim()); 19 } 20 } 21 return list; 22 }
1 /////Dictionary 2 3 private void BindRegion() 4 { 5 //string sSQL = String.Format("select distinct a.rgn_cd, a.rgn_nm from ext_prc_v_rgn_subrgn_grid_pref_city a left join prc_slsprs_city_asgn b on b.city_id=a.city_id order by rgn_nm"); 6 //ddlRegion.DataSource = SqlHelper.ExecuteDataset(Application["DSN"].ToString(), CommandType.Text, sSQL); 7 //ddlRegion.DataBind(); 8 //ddlRegion.Items.Insert(0, new ListItem("ALL", String.Empty)); 9 ddlRegion.DataSource = Location.GetRegion(Application["DSN"].ToString()); 10 ddlRegion.DataBind(); 11 } 12 13 14 public static Dictionary<string, string> GetRegion(string connectionString) 15 { 16 SqlParameter[] paraList = new[] 17 { 18 new SqlParameter("@rgn_cd", DBNull.Value), 19 new SqlParameter("@subrgn_cd", DBNull.Value), 20 new SqlParameter("@grid_cd", DBNull.Value), 21 new SqlParameter("@pref_cd", DBNull.Value), 22 new SqlParameter("@city_id", DBNull.Value), 23 new SqlParameter("@type", "region") 24 }; 25 Dictionary<string, string> list = new Dictionary<string, string>(); 26 using (IDataReader reader = SqlHelper.ExecuteReader(connectionString, "region_subregion_grid_pref_city_type", paraList)) 27 { 28 while (reader.Read()) 29 { 30 list.Add(reader["rgn_cd"].ToString().Trim(), reader["rgn_nm"].ToString().Trim()); 31 } 32 } 33 return list; 34 }