用c检索父/子查询结果SQL Server#
我现在在这里做的是可能是满足我的要求的最糟糕的方式,但我还没有找到任何其他方式。用c检索父/子查询结果SQL Server#
这里是我的示例数据库结构;
下面是我为了获取一定的值,用脚本;
SELECT DISTINCT h.HotelID, h.HotelName, r.RoomCode, r.RoomName, r.RoomID
FROM RoomsInHotel rh
INNER JOIN Hotels h ON rh.HotelID = h.HotelID
INNER JOIN Rooms r ON rh.RoomID = r.RoomID
order by h.HotelName, r.RoomCode;
这是上面的脚本给我的结果;
一切都很好,直到在这里。
我需要从这里转到C#代码。我想达到的是以下结果;
这里是我担心的。我使用Linq来实现这个功能,下面的代码是我用于上述控制台结果的代码。
public class Hotel {
public int HotelID {get; set; }
public string HotelName {get; set; }
public IQueryable<Room> Rooms {get; set; }
}
public class HotelWithOneRoom {
public int HotelID { get; set; }
public string HotelName { get; set; }
public Room Room { get; set; }
}
public class Room {
public int RoomID {get; set; }
public string RoomCode {get; set; }
public string RoomName { get; set; }
}
class Program {
static void Main(string[] args) {
#region _assets
IList<HotelWithOneRoom> tempHotelWithOneRoom = new List<HotelWithOneRoom>();
IList<Hotel> tempDistinctHotels = new List<Hotel>();
IList<Room> tempRooms = new List<Room>();
#endregion
#region _connectionString
var connectionString = "Data Source=TOSHIBA-PC\\SQLEXPRESS;Initial Catalog=tbAccomm;Integrated Security=True";
#endregion
using (SqlConnection conn = new SqlConnection(connectionString)) {
using(SqlCommand cmd = conn.CreateCommand()) {
#region _connect to db, generate script and retrieve values
cmd.CommandText = "SELECT DISTINCT h.HotelID, h.HotelName, r.RoomCode, r.RoomName, r.RoomID FROM RoomsInHotel rh INNER JOIN Hotels h ON rh.HotelID = h.HotelID INNER JOIN Rooms r ON rh.RoomID = r.RoomID order by h.HotelName, r.RoomCode;";
cmd.CommandType = System.Data.CommandType.Text;
conn.Open();
SqlDataReader r = cmd.ExecuteReader();
#endregion
#region _assigning the values to tempHotelWithOneRoom
while (r.Read()) {
tempHotelWithOneRoom.Add(new HotelWithOneRoom {
HotelID = int.Parse(r["HotelID"].ToString()),
HotelName = r["HotelName"].ToString(),
Room = new Room {
RoomID = int.Parse(r["RoomID"].ToString()),
RoomCode = r["RoomCode"].ToString(),
RoomName = r["RoomName"].ToString()
}
});
}
#endregion
foreach (var item in tempHotelWithOneRoom) {
if (tempDistinctHotels.Where(x => x.HotelID == item.HotelID).Count() < 1) {
tempDistinctHotels.Add(new Hotel {
HotelID = item.HotelID,
HotelName = item.HotelName
});
var _tempHotel = tempDistinctHotels.Single(x => x.HotelID == item.HotelID);
var _tempRoomList = new List<Room>();
if (_tempHotel.Rooms != null) {
foreach (var _item in _tempHotel.Rooms) {
_tempRoomList.Add(_item);
}
}
_tempRoomList.Add(new Room {
RoomCode = item.Room.RoomCode,
RoomID = item.Room.RoomID,
RoomName = item.Room.RoomName
});
_tempHotel.Rooms = _tempRoomList.AsQueryable();
} else {
var _tempHotel = tempDistinctHotels.Single(x => x.HotelID == item.HotelID);
var _tempRoomList = new List<Room>();
if (_tempHotel.Rooms != null) {
foreach (var _item in _tempHotel.Rooms) {
_tempRoomList.Add(_item);
}
}
_tempRoomList.Add(new Room {
RoomCode = item.Room.RoomCode,
RoomID = item.Room.RoomID,
RoomName = item.Room.RoomName
});
_tempHotel.Rooms = _tempRoomList.AsQueryable();
}
}
#region _output the result
foreach (var item in tempDistinctHotels) {
Console.WriteLine(
"Hotel Name : " + item.HotelName + ", " + "Room Count : " + item.Rooms.Count()
);
foreach (var item2 in item.Rooms) {
Console.WriteLine("--" + item2.RoomCode + ", " + item2.RoomName);
}
}
#endregion
r.Close();
Console.Read();
}
}
}
}
国际海事组织,如果有最差的C#代码竞争,我会赢得这场比赛的代码。 (我会吗?)
那么,我做什么最优化的方式是?
C#是不是我的选择的语言,但在这里你去:
Dictionary<int, Hotel> Hotels = new Dictionary<int, Hotel>();
while (r.Read()) {
if (!Hotels.ContainsKey(r["HotelID"])) {
NewHotel Hotel= new Hotel();
NewHotel.HotelID = r["HotelID"];
Newhotel.HotelName = r["HotelName"];
NewHotel.Rooms = new Dictionary<int, Room>();
Hotels.Add(NewHotel);
}
Room NewRoom = new Room();
NewRoom.RoomID = r["RoomName"];
NewRoom.RoomCode = r["RoomCode"];
NewRoom.RoomName = r["RoomName"];
Hotels.Items("HotelID").Rooms.Add(NewRoom);
}
像jpmcclung指出的那样,你需要一些软件工程技术来创建成功的应用。您的项目越大,设计和规划就越需要。
避免编写这样的代码的最好方法是研究测试驱动设计的实践。此代码正在尖叫。为了在行动中看到它,我会在TekPub(http://shop.tekpub.com/products/ft_tdd_wilson)看看Brad Wilson的新TDD Full Throttle视频,它的价格是12美元,但它是值得的。否则,在这个问题上有很多资源。
具体来说,你为什么需要一个HotelWithOneRoom?只需在普通酒店的房间列表中添加一个房间即可。你为什么不重写HotelToString()并使用StringBuilder为酒店创建输出行?这些仅仅是我头顶的几件事情,但如果你使用TDD,它将有助于组织你的设计实践,并使你的一些代码不受影响。
输出对我来说不是问题。我创建了这个控制台应用程序,看看我是否可以解决这个问题(我做了,但我觉得这很健康)。你看到tempDistinctHotels变量了吗?我的目标是填补它,因为它在最后。 – tugberk
既然你在你的例子中包含了IQueryable,我们可以假设LINQ to SQL或EF是你的解决方案的一个选项吗?如果是这样,意识到他们支持直接投射到物体层面。
var query = from hotel in context.hotels
select new Hotel { HotelID = hotel.HotelID,
HotelName = hotel.HotelName,
Rooms = (from room in hotel.Rooms
select new Room {
RoomID = room.RoomID,
RoomCode = room.RoomCode,
RoomName = room.RoomName })
.Distinct()
};
我想你可以从这里开始启动,并通过重命名你的桌子避免一些困惑:
Asssuming你有你的表之间的关联集,它可能是那样简单。我认为你的表名应该是Hotel,Room和RoomType(我不是多元化表名的粉丝,但那不是重点)。
想想'域名',你有一个酒店。酒店有房间。每个房间被定义为一种类型的房间,双人房,单人房等...
无论如何,我把一些代码放在一起,这是你做同样的事情。我认为这有点清楚。 对于数据库访问,我使用Massive https://github.com/robconery/massive,因为它快速而有趣。
无论如何,这里是我想出的代码。
class Program {
static void Main(string[] args) {
const string sqlStmnt = @"SELECT h.HotelID, h.HotelName, r.HotelRoomID, rt.RoomTypeCode, rt.RoomTypeName FROM Hotel h INNER JOIN HotelRoom r ON r.HotelID = h.HotelID INNER JOIN RoomType rt ON r.RoomTypeID = rt.RoomTypeID order by h.HotelName, rt.RoomTypeCode";
var context = new HotelContext();
var hotelData = context.Query(sqlStmnt);
var hotelList = new List<Hotel>();
//Load our objects
foreach (dynamic data in hotelData) {
int hotelID = data.HotelID;
var hotel = hotelList.Where(h => h.HotelID == hotelID).FirstOrDefault()
?? new Hotel() {HotelName = data.HotelName};
hotel.AddRoom(new HotelRoom { HotelRoomID = data.HotelRoomID, RoomType = new RoomType{ TypeCode = data.RoomTypeCode, TypeDescription = data.RoomTypeName}});
if (hotel.HotelID != 0) {continue;}
hotel.HotelID = hotelID;
hotelList.Add(hotel);
}
//Display our output
foreach (var hotel in hotelList) {
Console.WriteLine("Hotel Name : " + hotel.HotelName + ", Room Count : " + hotel.HotelRooms.Count());
foreach (var room in hotel.HotelRooms) {
Console.WriteLine("--" + room.RoomType.TypeCode + ", " + room.RoomType.TypeDescription);
}
}
Console.ReadLine();
}
}
这是我的数据库的东西。
public class HotelContext : DynamicModel {
public HotelContext():base("test") {
PrimaryKeyField = "HotelID";
TableName = "Hotel";
}
}
这里是我使用的类。无法弄清楚你的HotelWithOneRoom是什么意思。
public class Hotel{
private readonly List<HotelRoom> _rooms = new List<HotelRoom>();
public int HotelID { get; set; }
public string HotelName { get; set; }
public void AddRoom(HotelRoom room) {_rooms.Add(room);}
public IQueryable<HotelRoom> HotelRooms {get {return _rooms.AsQueryable();}}
}
public class HotelRoom {
public int HotelRoomID { get; set; }
public RoomType RoomType { get; set; }
}
public class RoomType {
public string TypeCode { get; set; }
public string TypeDescription { get; set; }
}
对我来说看起来像一个简单的字典,你为什么只创建一个房间的酒店? –
hi stephan,如果你可以发布一个示例代码,对我来说会很棒。 – tugberk