0

I have a situation where I have a table of records that looks something like (detail removed for brevity):

CREATE TABLE Records (
    RecordsId int,
    Description varchar(255)
);

I want to add multiple comments for each record using another table such as:

CREATE TABLE Comments (
    CommentsId int,
    Comment varchar(255)
);

I am aware that I can link the tables using a join statement but how would I effectively retrieve the information if there was one Record with multiple comments attached to it, with no set number of comments?

Such as

RecordsId
Description
    - Comment 1
    - Comment 2
    - Comment 3
    - Comment .....

I am aware that I could run a query to get the value of the first table and then run a second query for the second table but this would be extremely slow when I have large numbers of records. I am trying to popullate a C# object with the values.

Christopher Vickers
  • 1,773
  • 1
  • 14
  • 18
  • 1
    This is a really broad question; first please pick a data access strategy/library/ORM and then we can offer more targeted advice. Your Comments table needs a RecordID column – Caius Jard Jun 05 '19 at 19:07
  • If this were a greenfield site using C# what would you recommend? – Christopher Vickers Jun 05 '19 at 19:17
  • If I were writing new code.I would use the Dapper ORM framework : https://stackoverflow.com/questions/6317937/dapper-net-and-stored-proc-with-multiple-result-sets – granadaCoder Jun 05 '19 at 19:27

1 Answers1

1

//add multiple comments for each record //

You need to add a FK from Comment back to Record.

CREATE TABLE dbo.Record (
    RecordKey int,
    Description varchar(255)
);

CREATE TABLE dbo.Comment (
    CommentsKey int,
    RecordKey int NOT NULL,
    Comment varchar(255)
);


ALTER TABLE [dbo].[Comment]
    ADD CONSTRAINT [FK_Comment_To_Record]
    FOREIGN KEY (RecordKey)
    REFERENCES [dbo].[Record] (RecordKey)

..

Other stuff:

Use a schema name to create tables/objects. "dbo" is the built-in default.

I prefer singular noun for the tablename. this is a holy-war. I think the table should be the name of the ENTITY. "Record" and "Comment" are the entitynames. Again, this is a holy war.

I prefer MyObjectKey vs "Id".

Think about this. What is an "EmployeeId"? Is that a db-surrogate-key? Or is that a value on a badge that an employee wears on their shirt? "Id" is ambiguous. "Key" is clearer IMHO.

......

As per your comment, "what would i use with new code?"

I would use Dapper ORM which is a micro orm, but performs really really well.

This article will get you there:

https://medium.com/dapper-net/handling-multiple-resultsets-4b108a8c5172

Pay attention to this comment:

To avoid doing two roundtrips to the database to get customer and orders data separately, the multiple resultset feature can be used:

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • Thank you but that will just prevent the links between the tables from being destoyed? How would I then retrieve a long list of the first table with a list for each row, created using the second table in a single query? On the subject of Key, yes that is an excellent point. – Christopher Vickers Jun 05 '19 at 19:15
  • You need to learn how to serialize db-data into c# objects. Thus the one comment form Caius about "what is your ORM?" . you can HAND-MAP using IDataREader. see my answer this post : https://stackoverflow.com/questions/16175972/how-can-i-extract-data-from-my-sql-server-dataset-into-a-class-that-i-can-use – granadaCoder Jun 05 '19 at 19:24
  • Here is a another post about using IDataReader. https://stackoverflow.com/questions/5758526/what-is-the-fastest-way-to-read-data-from-a-dbdatareader/5758573#5758573 – granadaCoder Jun 05 '19 at 19:25
  • FK is relatively optional, by the way.. The system would certainly work without an FK.. – Caius Jard Jun 05 '19 at 20:57