Efficient ways to save C# nested objects to SQL server

Question

I have a table structure which is nested to 5 levels with a one to many relationship going downwards. I want to know what's an efficient way to save the this kind of data into the SQL Server. I now loop on each child object (C#) and run an insert which becomes slow if the data is large.

Is there a way to pass the C# directly to SQL in traditional ADO.NET? I have a custom framework which fires a SQL script for each insert, which picks up values from the object properties. I can't move to EF or NHirbernate as it's an existing project.

I have seen ways where C# objects can be inserted into DataTables and then passed to SQl, is that an efficient way?

Please advise.

Answer 1

I'm assuming that you have something like this from a database perspective

CREATE TABLE Items (ID INT -- primary key, 
                    Name VARCHAR(MAX),
                    ParentID INT) -- foreign key that loops on the same table

and an object like this in C#

public class Item
{
   public int ID {get; set;}
   public string Name {get; set;}
   public int ParentID {get; set;}
   public Item Parent {get; set;}
   public List<Item> Children {get; set;}
}

and you have some code that looks like:

var root = MakeMeATree();
databaseSaver.SaveToDatabase(root);

that generates an insert-per-item for every child. If you have lots of children, this can really slow up the application.

What I would use (and have used) in this case is a custom sql server type and a stored procedure to save the whole thing in a single call.

You will need to create a type that matches the table:

CREATE TYPE dbo.ItemType AS TABLE
(
    ID INT,
    Name VARCHAR(MAX),
    ParentID INT
);

and a simple procedure that uses the type:

CREATE PROCEDURE dbo.InsertItems
(
  @Items AS dbo.ItemType READONLY
)
AS
BEGIN
  INSERT INTO SampleTable(ID, Name, ParentID)
  SELECT ID, Name, ParentID From @Items
END

Now, that does it from the SQL Server side. Now on to the C# side. You need to do two things:

1. Flatten the hierarchy into a list
2. Sent that list as a datatable to the database

The first can be done using something like this (I use this, which is basically the same thing), with a simple

var items = root.Flatten(i => i.Children);

To do the second thing, first you need to declare the SQL Server type as a datatable:

DataTable dt = new DataTable("Items"); 
dt.Columns.Add("ID", typeof(int)); 
dt.Columns.Add("Name", typeof(string)); 
dt.Columns.Add("ParentID", typeof(int)); 

next, just fill the values:

foreach(var item in items)
{
   dt.Rows.Add(item.ID, item.Name, item.ParentID);
}

and attach them to a SqlParameter, that should be of the SqlDbType.Structured type, like this:

using (var cmd = new SqlCommand("InsertItems", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    var itemsParam = new SqlParameter("@Items", SqlDbType.Structured);
    itemsParam .Value = dt;

    cmd.Parameters.Add(itemsParam);
    cmd.ExecuteNonQuery();
}

And, that should be it.