使用LINQ从几个类向MS SQL数据库添加数据？

Ok, so I am not even sure where to begin here. SQL programming is fairly new to myself.

好的,所以我甚至不确定从哪里开始。 SQL编程对我自己来说相当新鲜。

Also before I go on about my issue I would like to be able to complete this in LINQ and not using stored procedures.

在继续讨论我的问题之前,我希望能够在LINQ中完成此操作,而不是使用存储过程。

Problem: I have multiple nested classes and I need to be able to add the classes to several tables in a sql database. I need to also not add data if it already exists.

问题:我有多个嵌套类,我需要能够将类添加到sql数据库中的多个表中。如果数据已存在,我还需要添加数据。

--------------------------------------------------------------------------
- Products      - Build        - File      - FileDetail   - BuildFiles   -
--------------------------------------------------------------------------
- ProductID     - BuildID      - FileID    - FileDetailID - BuildFilesID -
- Product_Name  - Build_Number - File_Name - File_Size    - BuildID      -
-               - Build_Date   - File_Path - File_Version - FileID       -
-               - ProductID    - ProductID - BuildFilesID -              -
--------------------------------------------------------------------------

My Class is similar but not eactly the same. So for instance:

我的班级相似,但不完全相同。例如:

ProductProperties productProperties = new ProductProperties("Notepad","090706");
Product myProduct = new Product(productProperties);
FileDetails fileDetails = new fileDetails("Notepad.exe","20kb","1.0.0.0");
myProduct.AddFile(fileDetails);
FileDetails fileDetails2 = new fileDetails("Notepad.config","5kb","1.1.2.5");
myProduct.AddFile(fileDetails2);

Now I need to add the details to the SQL database.

现在我需要将详细信息添加到SQL数据库中。

Some notes about the tool:

关于该工具的一些说明:

I already make sure the product exists earlier in my code. (the above is a rough example)

我已经确保我的代码中的产品早先存在。 (以上是一个粗略的例子)

I also verify that there is not build already if there is I then remove all the details related to that particular build before adding my new information

我还验证,如果有,那么我已经没有构建,然后在添加新信息之前删除与该特定构建相关的所有细节

This is where I am stuck:

这是我被困的地方:

So I need to use an existing Product ID

所以我需要使用现有的产品ID

I need to create the build details

我需要创建构建细节

I need to add a file if the file does not exist, or get an existing FileID if the file already exists

如果文件不存在,我需要添加文件,如果文件已经存在,我需要获取现有的FileID

I then need to add the particular files to a particular build (BuildFiles table)

然后我需要将特定文件添加到特定的构建(BuildFiles表)

Then I need to add the file details

然后我需要添加文件详细信息

So pretty much everything is needed, lol.

所以几乎所有东西都需要,哈哈。

The problem is, I don't know the best method for this. For instance, do I create a query to find the product ID for a particular product, then use that product ID when I add the row to the build table?

问题是,我不知道最好的方法。例如,我是否创建查询以查找特定产品的产品ID,然后在将该行添加到构建表时使用该产品ID?

Do I have a sperate method to see if a file exists for a particular product and if not then add the file?

我是否有sperate方法来查看特定产品是否存在文件,如果没有,则添加该文件?

Then when I do all that, do I query the file and the build to to get the buildID and the FileID and add the build and file to the BuildFiles table.

然后当我完成所有这些操作时,我是否查询文件和构建以获取buildID和FileID,并将构建和文件添加到BuildFiles表中。

Then finally add the values to the file details table with the BuildFilesID by first querying buildFiles detail to get the ID?

然后最后使用BuildFilesID将值添加到文件详细信息表中,首先查询buildFiles详细信息以获取ID?

Or am I totally wrong here?

或者我在这里完全错了?

If I have 20'000 files which could be the case, there are going to be hundreds of thousands of queries just to do this, so I was worried that it would be a little slow or crazy.

如果我有20'000个文件可能就是这种情况,那么就会有成千上万的查询,所以我担心它会有点慢或者疯狂。

Any ideas suggestions and examples would be great!

任何想法建议和例子都会很棒!

2 个解决方案

#1

Consider using a simple Object-Relational Mapper like Linq to SQL. It will create a bunch of this scaffolding code for you, which you can then use to implement whatever logic you want. It will greatly simplify your life.

考虑使用像Linq to SQL这样的简单对象关系映射器。它将为您创建一堆这样的脚手架代码,然后您可以使用它来实现您想要的任何逻辑。它将大大简化您的生活。

The best example of this I have seen is Scott Hanselman's talk on NerdDinner. His discussion about creating Linq to SQL Classes starts at 6 minutes and 30 seconds into the talk. Watch that for about a minute, and then go to this blog entry:

我见过的最好的例子是Scott Hanselman关于NerdDinner的演讲。他关于创建Linq to SQL Classes的讨论从6分30秒开始。观看大约一分钟,然后转到此博客条目:

http://www.fryan0911.com/2009/05/what-is-linq-to-sql-introduction.html

Finally, Scott Guthrie has an excellent blog series on Linq to Sql at:

最后,Scott Guthrie在Linq to Sql上有一个很棒的博客系列:

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

#2

Code Monkey,

Watch Hanselman's video from 11:00 to 15:30 where he describes how to create a Rob Conery style repository. Don't worry too much about the surrounding ASP.NET MVC stuff; the point is that the repository object will contain your methods for adding, deleting, and determining if a record exists. Once your repository has been created, you will be able to write code like this:

从11:00到15:30观看Hanselman的视频,在那里他描述了如何创建Rob Conery风格的存储库。不要太担心周围的ASP.NET MVC的东西;关键是存储库对象将包含添加,删除和确定记录是否存在的方法。创建存储库后,您将能够编写如下代码:

Repository repository = new Repository();
if (!repository.exists(myFileID))
{
   myFile = new File
   {
      File_Name = myFileName;
      FilePath = myFilePath;
      ProductID = productID;
   }
   myNewFileID = repository.AddFile(myFile);
}

Note that it is assumed that you have already created Linq to SQL classes for your tables. The repository you write will be based on these Linq to SQL classes.

请注意,假设您已经为表创建了Linq to SQL类。您编写的存储库将基于这些Linq to SQL类。

Later today I will post some examples.

今天晚些时候我会发布一些例子。

#1