在T_SQL将附件添加到TAKS记录 [英] Adding attachments to taks 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调用的所有附件数据;但似乎cluncky我不得不然后解析字符串。它也并不是很关系
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两个表，然后有返回任务的附件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.
SQL语句连接表是大多数ORM的做翘首负载相关的对象。显然，有一些网络流量开销，但我认为这是可以接受的。
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.
那么，你愿意写更多code以节省一些网络流量？
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将附件添加到TAKS记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
