将附件添加到T_SQL中的任务记录 [英] Adding attachments to task records in T_SQL
问题描述
我有一个存储任务的应用程序，我想为这些任务添加附件.
I have an application which stores tasks and I want to add attachments to those tasks.
我尝试了三种不同的方法，不知道其中任何一种是否正确，并且正在寻找有关去往何处的建议:
I have tried three different ways of doing this and don't know if any of them are correct and am looking for advice on where to go:
例如，为简化起见，我使用了一个表:
For example, simplified I have used a table:
+----------------------------------------------------------------------------+
| TaskID Description attachmentString |
+----------------------------------------------------------------------------+
| 1 Task1 "FileName1:::fileLocation;FileName2:::fileLocation" |
| 2 Task2 "FileName3:::fileLocation;FileName4:::fileLocation" |
+----------------------------------------------------------------------------+
这类似于在ASP.NET成员身份中存储配置文件数据的方式.
This is similar to how profile data is stored in ASP.NET membership.
我也尝试过:
+---------------------------+
| TaskID Description |
+---------------------------+
| 1 Task1 |
| 2 Task2 |
+---------------------------+
+------------------------------------------------------+
| AttachmentId Description Location TaskId |
+------------------------------------------------------+
| 1 FileName1 FileLocation 1 |
| 2 FileName2 FileLocation 1 |
+------------------------------------------------------+
如果使用第一个选项，则可以选择任务并在一个SQL调用中获取所有附件数据.但是对我来说似乎很笨拙，然后必须解析该字符串.它也不是很关系"
If I use the first option, I can just select tasks and get all the attachment data in one SQL call; but it seems cluncky to me to have to then parse the string. Its also not very "relational"
但是，使用附件ID时，如果我想获取附件，则可以将两个表都加入AttachmentId上，然后具有附件数x返回的任务数.我最多可以有5个附件，因此对于50个任务，它可能返回250行，其中第一列(从JOIN的任务表一侧)重复了，这似乎很浪费.显然，我桌上的内容不仅仅只是描述而已！！
However using an attachment Id, if I want to get the attachments, I either JOIN both tables on attachmentId and then have number of attachments x number of tasks returned. I can have up to 5 attachments so for 50 tasks, it could return 250 rows of which the first columns (from the task table side of the JOIN) are repeated and this seems like a waste. Obviously I have a little more than just description in my table!!!
我还考虑过仅获取任务数据，然后仅获取附件数据，然后将它们加入我的应用程序中.返回的数据少于第二个选项，但需要两次调用数据库，这似乎也是错误的.
I have also considered just getting the task data and then just getting the attachment data separately and then joining them in my application. This returns less data than the second option, but requires two calls to the database and that seems wrong too.
我做错了吗?有没有更好的办法?是否有人对实现此目标的最佳方法有任何想法.
I am doing this wrong? Is there a better way? Does anyone have any thoughts on the best way to do this.
我对SQL不太自信，也许我错过了很多东西，所以将不胜感激地收到任何指针.
I'm not very confident with SQL and maybe I have missed something huge so any pointers would be gratefully received.
推荐答案
正确的设计显然是两个表.只有一个表违反了第一个范式.
The right design is obviously two tables. Having only one table violates the first normal form.
关于负载问题，两种方法都是正确的.
Relating to the load problem, both approaches are correct.
大多数ORM急于加载相关对象的工作就是在sql语句中加入表.显然有一些网络流量开销，但是我认为这是可以接受的.
Joining the tables in the sql statement is what most ORM's do to eagerly load related objects. Obviously there is some network traffic overhead, but I think it is acceptable.
执行两个单独的sql语句也是正确的.您可以将它们一起批量发送到SQL Server，以节省往返时间.尽管它有一个缺点，但是您需要在客户端执行连接.
Executing two separate sql statements is also correct. You can send them together in one batch to SQL Server to save roundtrips. It has a disadvantage although, you need to perform the join at the client side.
那么，您愿意编写更多代码以节省一些网络流量吗?
So, are you willing to write more code to save some network traffic?
给出下表和数据:
CREATE TABLE Tasks
(
TaskId int IDENTITY(1,1) PRIMARY KEY,
TaskDescription nvarchar(500) NOT NULL
)
CREATE TABLE TaskAttachments
(
AttachmentId int IDENTITY(1,1) PRIMARY KEY,
TaskId int NOT NULL REFERENCES Tasks(TaskId),
[FileName] nvarchar(500) NOT NULL,
[FileLocation] nvarchar(500) NOT NULL
)
GO
INSERT INTO Tasks VALUES
('Task1'), ('Task2')
INSERT INTO TaskAttachments VALUES
(1, 'FileName1', 'File location 1'),
(1, 'Filename2', 'File location 2'),
(2, 'FileName3', 'File location 3'),
(2, 'Filename4', 'File location 4')
以下类别:
public class TaskAttachment
{
public int AttachmentId { get; set; }
public string FileName { get; set; }
public string FileLocation { get; set; }
}
public class AppTask
{
public int TaskId { get; set; }
public string TaskDescription { get; set; }
public List<TaskAttachment> Attachments { get; set; }
public AppTask()
{
this.Attachments = new List<TaskAttachment>();
}
}
以下类通过在一个批处理中执行两个select语句来加载带有附件的任务:
The following class loads the tasks with its attachments by executing two select statements in one single batch:
public class DataLayer
{
private readonly SqlConnection connection;
public DataLayer(SqlConnection connection)
{
this.connection = connection;
}
public List<AppTask> GetTasks()
{
var commandText = @"
SELECT TaskId, TaskDescription FROM Tasks;
SELECT AttachmentId, TaskId, [FileName], FileLocation FROM TaskAttachments;
";
using (var cmd = new SqlCommand(commandText, connection))
using (var reader = cmd.ExecuteReader())
{
var tasks = new List<AppTask>();
while (reader.Read())
{
var task = new AppTask
{
TaskId = reader.GetInt32(0),
TaskDescription = reader.GetString(1)
};
tasks.Add(task);
}
var taskDic = tasks.ToDictionary(x => x.TaskId);
reader.NextResult();
while (reader.Read())
{
var attachment = new TaskAttachment
{
AttachmentId = reader.GetInt32(0),
TaskId = reader.GetInt32(1),
FileName = reader.GetString(2),
FileLocation = reader.GetString(3)
};
var task = taskDic[attachment.TaskId];
task.Attachments.Add(attachment);
}
return tasks;
}
}
}
您可以像这样使用上面的类:
You can use the above class like this:
using (var cn = new SqlConnection("Data Source=(local);Initial Catalog=Tests;Integrated Security=SSPI"))
{
var dataLayer = new DataLayer(cn);
cn.Open();
var tasks = dataLayer.GetTasks();
}
这篇关于将附件添加到T_SQL中的任务记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
