/// <summary> /// 获取用户地址 /// </summary> /// <param name="address">地址信息</param> /// <returns></returns> public IList<Eyousoft_yhq.Model.UserAddress> GetAddressList(int top, string userID) { IList<Eyousoft_yhq.Model.UserAddress> list = new List<Eyousoft_yhq.Model.UserAddress>(); StringBuilder strSql = new StringBuilder(); strSql.AppendFormat("select {0} * , ", top > 0 ? " top " + top : ""); strSql.Append("(SELECT UserName FROM tbl_Member WHERE UserID = tbl_UserAddress.UserID) as UserName, "); strSql.Append("(SELECT Name FROM tbl_SysProvince WHERE ID = tbl_UserAddress.AddressProvince) as AddressProvinceName, "); strSql.Append("(SELECT Name FROM tbl_SysCity WHERE Id = tbl_UserAddress.AddressCity) as AddressCityName, "); strSql.Append("(SELECT Name FROM tbl_SysDistrict WHERE Id = tbl_UserAddress.AddressCountry) as AddressCountryName "); strSql.AppendFormat(" from tbl_UserAddress where UserID=@UserID"); DbCommand cmd = this._db.GetSqlStringCommand(strSql.ToString()); this._db.AddInParameter(cmd, "UserID", System.Data.DbType.AnsiStringFixedLength, userID); using (IDataReader dr = DbHelper.ExecuteReader(cmd, this._db)) { while (dr.Read()) { Eyousoft_yhq.Model.UserAddress model = new Eyousoft_yhq.Model.UserAddress(); model.UserID = dr.GetString(dr.GetOrdinal("UserID")); model.AddressID = dr.IsDBNull(dr.GetOrdinal("AddressID")) ? "" : dr.GetString(dr.GetOrdinal("AddressID")); model.AddressProvince = dr.IsDBNull(dr.GetOrdinal("AddressProvince")) ? 0 : dr.GetInt32(dr.GetOrdinal("AddressProvince")); model.AddressCity = dr.IsDBNull(dr.GetOrdinal("AddressCity")) ? 0 : dr.GetInt32(dr.GetOrdinal("AddressCity")); model.AddressInfo = dr.IsDBNull(dr.GetOrdinal("AddressInfo")) ? "" : dr.GetString(dr.GetOrdinal("AddressInfo")); model.IsDefault = dr.IsDBNull(dr.GetOrdinal("IsDefault")) ? false : this.GetBoolean(dr.GetString(dr.GetOrdinal("IsDefault"))); model.Remark = dr.IsDBNull(dr.GetOrdinal("Remark")) ? "" : dr.GetString(dr.GetOrdinal("Remark")); model.AddressCountry = dr.GetInt32(dr.GetOrdinal("AddressCountry")); model.ContactName = dr.IsDBNull(dr.GetOrdinal("ContactName")) ? "" : dr.GetString(dr.GetOrdinal("ContactName")); model.ZpCode = dr.IsDBNull(dr.GetOrdinal("ZpCode")) ? "" : dr.GetString(dr.GetOrdinal("ZpCode")); model.MobileNum = dr.IsDBNull(dr.GetOrdinal("MobileNum")) ? "" : dr.GetString(dr.GetOrdinal("MobileNum")); model.TelNum = dr.IsDBNull(dr.GetOrdinal("TelNum")) ? "" : dr.GetString(dr.GetOrdinal("TelNum")); model.AddressProvinceName = dr.IsDBNull(dr.GetOrdinal("AddressProvinceName")) ? "" : dr.GetString(dr.GetOrdinal("AddressProvinceName")); model.AddressCityName = dr.IsDBNull(dr.GetOrdinal("AddressCityName")) ? "" : dr.GetString(dr.GetOrdinal("AddressCityName")); model.AddressCountryName = dr.IsDBNull(dr.GetOrdinal("AddressCountryName")) ? "" : dr.GetString(dr.GetOrdinal("AddressCountryName")); list.Add(model); } } return list; }
/// <summary> /// 获取分页数据 /// </summary> /// <param name="PageSize"></param> /// <param name="PageIndex"></param> /// <param name="RecordCount"></param> /// <param name="serModel"></param> /// <returns></returns> public IList<Eyousoft_yhq.Model.UserAddress> GetAddressList(int PageSize, int PageIndex, ref int RecordCount, Eyousoft_yhq.Model.MSearchUserAddress serModel) { IList<Eyousoft_yhq.Model.UserAddress> list = new List<Eyousoft_yhq.Model.UserAddress>(); string tableName = "tbl_UserAddress"; string fileds = " AddressID,UserID,AddressProvince,AddressCity,AddressCountry,AddressInfo,IsDefault,Remark,ContactName,ZpCode,MobileNum,TelNum, (SELECT UserName FROM tbl_Member WHERE UserID = tbl_UserAddress.UserID) as UserName,(SELECT Name FROM tbl_SysProvince WHERE ID = tbl_UserAddress.AddressProvince) as AddressProvinceName,(SELECT Name FROM tbl_SysCity WHERE Id = tbl_UserAddress.AddressCity) as AddressCityName,(SELECT Name FROM tbl_SysDistrict WHERE Id = tbl_UserAddress.AddressCountry) as AddressCountryName "; string orderByString = " "; StringBuilder query = new StringBuilder(); query.AppendFormat(" 1=1 "); if (serModel != null) { } using (IDataReader dr = DbHelper.ExecuteReader1(this._db, PageSize, PageIndex, ref RecordCount, tableName, fileds, query.ToString(), orderByString, null)) { while (dr.Read()) { Eyousoft_yhq.Model.UserAddress model = new Eyousoft_yhq.Model.UserAddress(); model.UserID = dr.GetString(dr.GetOrdinal("UserID")); model.AddressID = dr.IsDBNull(dr.GetOrdinal("AddressID")) ? "" : dr.GetString(dr.GetOrdinal("AddressID")); model.AddressProvince = dr.IsDBNull(dr.GetOrdinal("AddressProvince")) ? 0 : dr.GetInt32(dr.GetOrdinal("AddressProvince")); model.AddressCity = dr.IsDBNull(dr.GetOrdinal("AddressCity")) ? 0 : dr.GetInt32(dr.GetOrdinal("AddressCity")); model.AddressInfo = dr.IsDBNull(dr.GetOrdinal("AddressInfo")) ? "" : dr.GetString(dr.GetOrdinal("AddressInfo")); model.IsDefault = dr.IsDBNull(dr.GetOrdinal("IsDefault")) ? false : this.GetBoolean(dr.GetString(dr.GetOrdinal("IsDefault"))); model.Remark = dr.IsDBNull(dr.GetOrdinal("Remark")) ? "" : dr.GetString(dr.GetOrdinal("Remark")); model.AddressCountry = dr.GetInt32(dr.GetOrdinal("AddressCountry")); model.ContactName = dr.IsDBNull(dr.GetOrdinal("ContactName")) ? "" : dr.GetString(dr.GetOrdinal("ContactName")); model.ZpCode = dr.IsDBNull(dr.GetOrdinal("ZpCode")) ? "" : dr.GetString(dr.GetOrdinal("ZpCode")); model.MobileNum = dr.IsDBNull(dr.GetOrdinal("MobileNum")) ? "" : dr.GetString(dr.GetOrdinal("MobileNum")); model.TelNum = dr.IsDBNull(dr.GetOrdinal("TelNum")) ? "" : dr.GetString(dr.GetOrdinal("TelNum")); model.AddressProvinceName = dr.IsDBNull(dr.GetOrdinal("AddressProvinceName")) ? "" : dr.GetString(dr.GetOrdinal("AddressProvinceName")); model.AddressCityName = dr.IsDBNull(dr.GetOrdinal("AddressCityName")) ? "" : dr.GetString(dr.GetOrdinal("AddressCityName")); model.AddressCountryName = dr.IsDBNull(dr.GetOrdinal("AddressCountryName")) ? "" : dr.GetString(dr.GetOrdinal("AddressCountryName")); list.Add(model); } } return list; }
/// <summary> /// 获取用户地址 /// </summary> /// <param name="address">地址信息</param> /// <returns></returns> public Eyousoft_yhq.Model.UserAddress GetAddress(string Address) { Eyousoft_yhq.Model.UserAddress model = null; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT AddressID,UserID,AddressProvince,AddressCity,AddressCountry,AddressInfo,IsDefault,Remark,ContactName,ZpCode,MobileNum,TelNum, "); strSql.Append("(SELECT UserName FROM tbl_Member WHERE UserID = tbl_UserAddress.UserID) as UserName, "); strSql.Append("(SELECT Name FROM tbl_SysProvince WHERE ID = tbl_UserAddress.AddressProvince) as AddressProvinceName, "); strSql.Append("(SELECT Name FROM tbl_SysCity WHERE Id = tbl_UserAddress.AddressCity) as AddressCityName, "); strSql.Append("(SELECT Name FROM tbl_SysDistrict WHERE Id = tbl_UserAddress.AddressCountry) as AddressCountryName "); strSql.Append(" FROM tbl_UserAddress "); strSql.Append(" where AddressID=@AddressID "); DbCommand cmd = this._db.GetSqlStringCommand(strSql.ToString()); this._db.AddInParameter(cmd, "AddressID", System.Data.DbType.AnsiStringFixedLength, Address); using (IDataReader dr = DbHelper.ExecuteReader(cmd, this._db)) { while (dr.Read()) { model = new Eyousoft_yhq.Model.UserAddress(); model.UserID = dr.GetString(dr.GetOrdinal("UserID")); model.UserName = dr.IsDBNull(dr.GetOrdinal("UserName")) ? "" : dr.GetString(dr.GetOrdinal("UserName")); model.AddressID = dr.IsDBNull(dr.GetOrdinal("AddressID")) ? "" : dr.GetString(dr.GetOrdinal("AddressID")); model.AddressProvince = dr.IsDBNull(dr.GetOrdinal("AddressProvince")) ? 0 : dr.GetInt32(dr.GetOrdinal("AddressProvince")); model.AddressCity = dr.IsDBNull(dr.GetOrdinal("AddressCity")) ? 0 : dr.GetInt32(dr.GetOrdinal("AddressCity")); model.AddressInfo = dr.IsDBNull(dr.GetOrdinal("AddressInfo")) ? "" : dr.GetString(dr.GetOrdinal("AddressInfo")); model.IsDefault = dr.IsDBNull(dr.GetOrdinal("IsDefault")) ? false : this.GetBoolean(dr.GetString(dr.GetOrdinal("IsDefault"))); model.Remark = dr.IsDBNull(dr.GetOrdinal("Remark")) ? "" : dr.GetString(dr.GetOrdinal("Remark")); model.AddressCountry = dr.GetInt32(dr.GetOrdinal("AddressCountry")); model.ContactName = dr.IsDBNull(dr.GetOrdinal("ContactName")) ? "" : dr.GetString(dr.GetOrdinal("ContactName")); model.ZpCode = dr.IsDBNull(dr.GetOrdinal("ZpCode")) ? "" : dr.GetString(dr.GetOrdinal("ZpCode")); model.MobileNum = dr.IsDBNull(dr.GetOrdinal("MobileNum")) ? "" : dr.GetString(dr.GetOrdinal("MobileNum")); model.TelNum = dr.IsDBNull(dr.GetOrdinal("TelNum")) ? "" : dr.GetString(dr.GetOrdinal("TelNum")); model.AddressProvinceName = dr.IsDBNull(dr.GetOrdinal("AddressProvinceName")) ? "" : dr.GetString(dr.GetOrdinal("AddressProvinceName")); model.AddressCityName = dr.IsDBNull(dr.GetOrdinal("AddressCityName")) ? "" : dr.GetString(dr.GetOrdinal("AddressCityName")); model.AddressCountryName = dr.IsDBNull(dr.GetOrdinal("AddressCountryName")) ? "" : dr.GetString(dr.GetOrdinal("AddressCountryName")); } } return model; }