常用国家，地区函数

 

  1        /**//// <summary>
  2        /// 根据国家ID得到国家的名字 by胡乐 2006/6/3
  3        /// </summary>
  4        /// <param name="strLanguage"></param>
  5        /// <param name="nCountryID"></param>
  6        /// <returns></returns>
  7        public string GetCountryName(string strLanguage,string nCountryID)
  8        {
  9            string strCountryName=null;
 10            string dsn=ConfigurationSettings.AppSettings["dsn"];
 11            SqlConnection conn = new SqlConnection(dsn);//连接字符串
 12            string Sql="select * from tCountry where (IsOpen_CN=1) and CountryID=@CountryID";
 13            SqlCommand myCommand1=new SqlCommand(Sql,conn);
 14            myCommand1.CommandType=CommandType.Text;
 15            myCommand1.Parameters.Add("@CountryID",SqlDbType.Int);
 16            myCommand1.Parameters["@CountryID"].Value=nCountryID;
 17            conn.Open();
 18            SqlDataReader dr=myCommand1.ExecuteReader();
 19            while(dr.Read())
 20            {
 21                if (strLanguage=="CN") 
 22                    strCountryName=dr["CountryName_CN"].ToString().Trim();
 23                else
 24                    strCountryName=dr["CountryName_EN"].ToString().Trim();
 25            }
 26            dr.Close();
 27            conn.Close();
 28            return strCountryName;
 29        }
 30        /**//// <summary>
 31        /// 得到一个层次国家地区州的下拉列表 by胡乐 2006/6/3
 32        /// </summary>
 33        /// <param name="drop">下拉列表DropDownList</param>
 34        /// <param name="nParentID">父ID，默认为0</param>
 35        public void SetCountryList(ref DropDownList drop,int nParentID)
 36        {
 37            drop.AutoPostBack=true;
 38            int dCountryID;
 39            string dCountryName=null;
 40            string connStr=ConfigurationSettings.AppSettings["dsn"];
 41            SqlConnection thisConn=new SqlConnection(connStr);
 42            string sqlStr="select * from tCountry where IsOpen_CN=1 and ParentID=@nParentID";
 43            SqlCommand thisCommand=new SqlCommand(sqlStr,thisConn);
 44            thisCommand.Parameters.Add("@nParentID",SqlDbType.Int);
 45            thisCommand.Parameters["@nParentID"].Value=nParentID;
 46            thisConn.Open();
 47            SqlDataReader dr=thisCommand.ExecuteReader();
 48            while(dr.Read())
 49            {
 50                dCountryID=Convert.ToInt32(dr["CountryID"]);
 51                //dParentID=Convert.ToInt32(dr["ParentID"]);
 52                dCountryName=Convert.ToString(dr["CountryName_CN"]);
 53                if (nParentID==0)
 54                {
 55                    drop.Items.Add(new ListItem("==="+dCountryName+"===",dCountryID.ToString()));
 56                }
 57                else
 58                {
 59                    drop.Items.Add(new ListItem("--"+dCountryName,dCountryID.ToString()));
 60                }
 61                //递归调用
 62                if (nParentID==0)
 63                {
 64                    SetCountryList(ref drop,dCountryID);
 65                }
 66            }
 67            thisConn.Close();
 68        }
 69        /**//// <summary>
 70        /// 得到一个省的下列表 by胡乐 2006/6/3
 71        /// </summary>
 72        /// <param name="ProvinceList"></param>
 73        public void SetProvinceList(DropDownList ProvinceList)
 74        {
 75            string connStr=ConfigurationSettings.AppSettings["dsn"];
 76            SqlConnection thisConn=new SqlConnection(connStr);
 77            string sqlStr="select ProvinceName,ProvinceId from tProvince";
 78            SqlCommand thisCommand=new SqlCommand(sqlStr,thisConn);
 79            SqlDataAdapter Ad=new SqlDataAdapter(thisCommand);
 80            DataSet DS=new DataSet();
 81            thisConn.Open();
 82            Ad.Fill(DS,"myTable");
 83            thisConn.Close();
 84            ProvinceList.AutoPostBack=true;
 85            ProvinceList.Visible=false;
 86            DataRow dr=DS.Tables["myTable"].NewRow();
 87            dr.ItemArray=new object[]{"===省级===",0};
 88            DS.Tables["myTable"].Rows.InsertAt(dr,0);
 89            ProvinceList.Items.Clear();
 90            ProvinceList.DataSource=DS.Tables["myTable"];
 91            ProvinceList.DataTextField="ProvinceName";
 92            ProvinceList.DataValueField="ProvinceId";
 93            ProvinceList.DataBind();
 94        }
 95        /**//// <summary>
 96        /// 得到一个市的下拉列表
 97        /// </summary>
 98        /// <param name="CityList"></param>
 99        /// <param name="ProvinceID"></param>
100        public void SetCityList(DropDownList CityList,int ProvinceID)
101        {
102            string connStr=ConfigurationSettings.AppSettings["dsn"];
103            SqlConnection thisConn=new SqlConnection(connStr);
104            string sqlStr="select CityName,CityID from tCity where ProvinceID=@ProvinceID";
105            SqlCommand thisCommand=new SqlCommand(sqlStr,thisConn);
106            thisCommand.Parameters.Add("@ProvinceID",SqlDbType.Int);
107            thisCommand.Parameters["@ProvinceID"].Value=ProvinceID;
108            SqlDataAdapter Ad=new SqlDataAdapter(thisCommand);
109            DataSet DS=new DataSet();
110            thisConn.Open();
111            Ad.Fill(DS,"myTable");
112            thisConn.Close();
113            CityList.AutoPostBack=true;
114            CityList.Visible=true;
115            DataRow dr=DS.Tables["myTable"].NewRow();
116            dr.ItemArray=new object[]{"===市级===",0};
117            DS.Tables["myTable"].Rows.InsertAt(dr,0);
118            CityList.Items.Clear();
119            CityList.DataSource=DS.Tables["myTable"];
120            CityList.DataTextField="CityName";
121            CityList.DataValueField="CityID";
122            CityList.DataBind();
123        }
124        /**//// <summary>
125        /// 国家ID和地区得到正式详细地区名 by hule 
126        /// </summary>
127        /// <param name="nCountryID"></param>
128        /// <param name="strLocation"></param>
129        /// <returns></returns>
130        public string GetLocatonName(string nCountryID,string strLocation)
131        {
132            string strProvinceName="";
133            string strCityName="";
134            string strCountryName;
135            string nProvinceID,nCityID;
136            if (strLocation=="0")
137                return null; 
138            if (nCountryID=="0")
139                return null;
140            string dsn=ConfigurationSettings.AppSettings["dsn"];
141            SqlConnection conn = new SqlConnection(dsn);//连接字符串
142            char[] sep=new char[]{'|'};
143            string[] Location=strLocation.Split(sep);
144            string _nProvinceID=Location[0].ToString().Trim();
145            string _nCityID=Location[1].ToString().Trim();
146            if (_nProvinceID=="")
147                nProvinceID="1000";
148            else
149                nProvinceID=_nProvinceID;
150            if (_nCityID=="")
151                nCityID="1369";
152            else
153                nCityID=_nCityID;
154            if(nCountryID=="1000")
155            {
156                string Sql="select * from tProvince a,tCity b where (a.ProvinceId=b.ProvinceId) and (a.ProvinceID=@ProvinceID) and (b.CityId=@CityID)";
157                SqlCommand myCommand1=new SqlCommand(Sql,conn);
158                myCommand1.CommandType=CommandType.Text;
159                myCommand1.Parameters.Add("@ProvinceID",SqlDbType.Int);
160                myCommand1.Parameters.Add("@CityID",SqlDbType.Int);
161                myCommand1.Parameters["@ProvinceID"].Value=nProvinceID;
162                myCommand1.Parameters["@CityID"].Value=nCityID;
163                conn.Open();
164                SqlDataReader dr=myCommand1.ExecuteReader();
165                while(dr.Read())
166                {
167                    strProvinceName=dr["ProvinceName"].ToString().Trim();
168                    strCityName=dr["CityName"].ToString().Trim();
169                }
170                dr.Close();
171                conn.Close();
172                return strProvinceName+strCityName;
173            }
174            else
175            {
176                strCountryName=GetCountryName("CN",nCountryID);
177                return strCountryName;
178            }    
179        }
180    }
181}
182