A better way to achieve INSERT without hitting the database multiple times

Question

I have the following, I could make it work as I want to but I think i'm doing it the wrong way, could you please explain how this could be done in a more efficient way ? While also looping on Categories and doing the same as with Districts within the same Insert() Method.

Thanks in advance.

    #region Methods
    public int Insert(List<District> Districts, List<Category> Categories)
    {
        StringBuilder sqlString = new StringBuilder("INSERT INTO Stores (name, image) VALUES (@Name, @Image);");

        using (SqlConnection sqlConnection = new
           SqlConnection(ConfigurationManager.ConnectionStrings["OahuDB"].ConnectionString))
        {
            SqlCommand sqlCommand = new SqlCommand(sqlString.ToString(), sqlConnection);
            sqlCommand.Parameters.AddWithValue("@Name", this.Name);
            sqlCommand.Parameters.AddWithValue("@Image", this.Image);

            sqlConnection.Open();
            int x = (int)sqlCommand.ExecuteScalar();

            sqlString.Clear();
            sqlCommand.Parameters.Clear();

            foreach (District item in Districts)
            {
                sqlString.AppendLine("INSERT INTO districts_has_stores (district_id, store_id) VALUES (@DistrictID, @StoreID);");
                sqlCommand.CommandText = sqlString.ToString();
                sqlCommand.Parameters.AddWithValue("@DistrictID", item.ID);
                sqlCommand.ExecuteNonQuery();
            }

            return x;
        }
    }

EDIT

Is is wrong to achieve the above by doing the following ?

            sqlString.Clear();
            sqlCommand.Parameters.Clear();
            sqlString.AppendLine("INSERT INTO districts_has_stores (district_id, store_id) VALUES (@DistrictID, @StoreID);");
            sqlCommand.CommandText = sqlString.ToString();
            sqlCommand.Parameters.AddWithValue("@StoreID", x);
            foreach (District item in Districts)
            {
                sqlCommand.Parameters.AddWithValue("@DistrictID", item.ID);
                sqlCommand.ExecuteNonQuery();
            } 
            sqlString.Clear();
            sqlCommand.Parameters.Clear();
            sqlString.AppendLine("INSERT INTO categories_has_stores (category_id, store_id) VALUES (@CategoryID, @StoreID);");
            sqlCommand.CommandText = sqlString.ToString();
            sqlCommand.Parameters.AddWithValue("@StoreID", x);
            foreach (Category item in Categories)
            {
                sqlCommand.Parameters.AddWithValue("@CategoryID", item.ID);
                sqlCommand.ExecuteNonQuery();
            } 

Answer 1

The first obvious thing is to move the invariant part of the sqlCommand out of the loop

sqlCommand.Parameters.Clear(); 
sqlString.Clear();
sqlString.AppendLine("INSERT INTO districts_has_stores (district_id, store_id) VALUES (@DistrictID, @StoreID);"); 
sqlCommand.CommandText = sqlString.ToString(); 
sqlCommand.Parameters.AddWithValue("@DistrictID", 0);  // as dummy value
sqlCommand.Parameters.AddWithValue("@StoreID", x);  // invariant
foreach (District item in Districts) 
{ 
    sqlCommand.Parameters["@DistrictID"].Value = item.ID; 
    sqlCommand.ExecuteNonQuery(); 
} 

But this doesn't answer your fundamental problem. How to avoid hitting the database multiple times.
You could build a query with multiple inserts like this

sqlString.Clear();
sqlString.Append("INSERT INTO districts_has_stores (district_id, store_id) VALUES (");
foreach(District item in Districts)
{
    sqlString.Append(item.ID.ToString);
    sqlString.Append(", ")
    sqlString.Append(x.ToString()); 
    sqlString.Append("),"); 
}
sqlString.Length--;
sqlCommand.CommandText = sqlString.ToString()

But string concatenation is really a bad practice and I present this solution just as an example and I don't want to suggest this kind of approach.

The last possibility are Table-Valued Parameters (Only from SqlServer 2008).

First you need to create a Sql Type for the table you will pass in

CREATE TYPE dbo.DistrictsType AS TABLE
    ( DistrictID int, StoreID int )

and a StoredProcedure that will insert the data from the datatable passed in

CREATE PROCEDURE usp_InsertDistricts 
(@tvpNewDistricts dbo.DistrictsType READONLY)
AS
BEGIN
    INSERT INTO dbo.Districts (DistrictID, StoreID)
    SELECT dt.DistrictID, dt.StoreID FROM @tvpNewDistricts AS dt;
END

then, back to your code you pass the district into the storedprocedure

(Probably you need to convert your List in a DataTable)

DataTable dtDistricts = ConvertListToDataTable(Districts);
SqlCommand insertCommand = new SqlCommand("usp_InsertDistricts", sqlConnection);
SqlParameter p1 = insertCommand.Parameters.AddWithValue("@tvpNewDistricts", dtDistricts);
p1.SqlDbType = SqlDbType.Structured;
p1.TypeName = "dbo.DistrictsType";
insertCommand.ExecuteNonQuery();

Well, if you look back at the link above, you will find other ways to pass your data in a single step to the database backend.... (Scroll to the end and you will find also a method that doesn't require a stored procedure on the database)

Answer 2

Assuming Stores has an identity column, in SQL Server, create a table type and a table-valued parameter to take advantage of it:

CREATE TYPE dbo.DistrictsTVP AS TABLE
(
  DistrictID INT -- PRIMARY KEY? I hope so.
);
GO

CREATE PROCEDURE dbo.InsertStoreAndDistricts
  @Name NVARCHAR(255),
  @Image <some data type???>,
  @Districts dbo.DistrictsTVP READONLY
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @StoreID INT;

  INSERT dbo.Stores(name, [image]) SELECT @Name, @Image;

  SET @StoreID = SCOPE_IDENTITY();

  INSERT dbo.district_has_stores(district_id, store_id)
    SELECT DistrictID, @StoreID
      FROM @Districts;
END
GO

Then in C#, you can pass your List in directly without any looping:

  using (...)
  {
    SqlCommand cmd       = new SqlCommand("dbo.InsertStoreAndDistricts", sqlConnection);
    cmd.CommandType      = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@Districts", Districts);
    tvparam.SqlDbType    = SqlDbType.Structured;

    // other params here - name and image

    cmd.ExecuteNonQuery();
  }

Answer 3

Recently in my project i used XML as a data type in my stored proc and did insert update and delete in just one shot instead of hitting the database multiple times .

Sample Stored proc

ALTER PROCEDURE [dbo].[insertStore]
@XMLDATA xml,
@name varchar(50),
@image datatype
 AS
 Begin
  INSERT INTO Store
  (name
   ,image
  )
Select XMLDATA.item.value('@name[1]', 'varchar(10)') AS Name,   
XMLDATA.item.value('@image[1]', 'yourData type') AS Image
FROM @XMLDATA.nodes('//Stores/InsertList/Store') AS XMLDATA(item)
END

Similarly you can write for update and delete .In C# u need to create the xml

public  string GenerateXML(List<District> Districts)
 var xml = new StringBuilder();
 var insertxml = new StringBuilder();
 xml.Append("<Stores>");
 for (var i = 0; i < Districts.Count; i++)
        { var obj = Districts[i];
          insertxml.Append("<Store");
          insertxml.Append(" Name=\"" + obj.Name  + "\" ");
          insertxml.Append(" Image=\"" + obj.Image + "\" ");
          insertxml.Append(" />");
        }
xml.Append("<InsertList>");
xml.Append(insertxml.ToString());
xml.Append("</InsertList>");

SqlCommand cmd= new SqlCommand("insertStore",connectionString);
cmd.CommandType=CommandType.StoredProcedure;
SqlParameter param = new SqlParameter ();
param.ParameterName ="@XMLData";
param.value=xml;
paramter.Add(param);
cmd.ExecuteNonQuery();

Answer 4

Personally, I would create a stored procedure for the insert and pass in a Table-Valued param, which would allow you to do

INSERT tbl (f1, f2, ... fN)
SELECT * FROM @TVP

http://msdn.microsoft.com/en-us/library/bb510489.aspx

Unless you're using SQL 2005, then I would use an XML param in my stored proc and Serialize a collection to be inserted.

Answer 5

Think about your system design. Where is the data that you need to insert coming from? If it's already in the database, or another database, or some other kind of data store, you should be able to achieve a more bulk kind of transfer, simply inserting from one database to the other in a loop in stored procedure.

If the data is coming from a user, or some incompatible data store, like say an export from some third party program, then you basically have to realize that to get it into the database will involve quite of few round-trips to the database. You can use some tables, or XML or such , but those are actually closer to doing a bulk insert using other methods.

The bottom line is that SQL databases are designed to do inserts one at a time. This is 99% of the time OK because you are never asking users using the UI to type in thousands of things at one time.