Adding data into MS SQL Database from several classes using LINQ?

Question

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

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

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.

--------------------------------------------------------------------------
- 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.

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
• 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
• I then need to add the particular files to a particular build (BuildFiles table)
• 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?

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

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.

Then finally add the values to the file details table with the BuildFilesID by first querying buildFiles detail to get the 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.

Any ideas suggestions and examples would be great!

Answer 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.

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:

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:

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

Answer 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:

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.

Later today I will post some examples.