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 }