Using SQL Server's XML type to pass an "array" of values for multiple inserts, updates, etc.

Friends.cs

using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Gists.Data
{
  public class Friends
  {
    /// <summary>
    /// Inserts each friends' name into a table.
    /// </summary>
    /// <param name="friends">Names of friends.</param>
    /// <returns>Number of rows table rows inserted.</returns>
    public int Insert(params string[] friends)
    {
      var rowsAffected = 0;

      // Create an XML string from the array.
      // -------------------------------
      // This simple example will have the following structure:
      // <friends>
      //   <name>Joe</name>
      //   <name>Jill</name>
      //   <name>Jojo</name>
      // </friends>
      var sb = new StringBuilder();

      sb.Append("<friends>");
      foreach (var friend in friends)
      {
        sb.AppendFormat("<name>{0}</name>", friend);
      }
      sb.Append("</friends>");

      var xml = sb.ToString();

      // SQL Code
      // --------
      using (var conn = new SqlConnection("Data Source=BOSDEV8;Initial Catalog=Gists;Integrated Security=True"))
      {
        var cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection  = conn;
        cmd.CommandText = "Gists_Friends_Insert";

        // Add the XML to the sproc parameters
        var param = new SqlParameter("@friends", SqlDbType.Xml);
        param.Value = xml;
        cmd.Parameters.Add(param);

        conn.Open();

        rowsAffected = cmd.ExecuteNonQuery();
      }

      return rowsAffected;
    }
  }
}

gistfile1.cs

var friends = new Gists.Data.Friends();

var numInserts = friends.Insert("Clarence", "Rupert", "Horace", "Leopold", "Bubba");

// numInserts => 5

// Executing the above code would populate an empty Friends table like this:
// +-----+-----------+-------------------------+
// | id  | name      | date_created            |
// +-----+-----------+-------------------------+
// | 1   | Clarence  | 2012-02-23 17:42:26.027 |
// | 2   | Rupert    | 2012-02-23 17:42:26.027 |
// | 3   | Horace    | 2012-02-23 17:42:26.027 |
// | 4   | Leopold   | 2012-02-23 17:42:26.027 |
// | 5   | Bubba     | 2012-02-23 17:42:26.027 |
// +-----+-----------+-------------------------+

Gists_Friends_Insert.sql

USE Gists
GO

-- Assumes Sql Server 2008 feature set
--
-- Using XML as a SQL parameter allows you to pass an array of 
-- values or sets of values to be inserted, updated, etc.
--
-- Assume a DB table (Gists.dbo.Friends) with the following structure:
--   id             INT IDENTITY(1, 1)
--   name           VARCHAR(50)
--   date_created   DATETIME
-- 
-- Parameter @friends expects XML structured like:
--   <friends><name>Joe</name><name>Jill</name></friends>
-- ==============================================================


CREATE PROCEDURE dbo.Gists_Friends_Insert
(
  @friends  XML
)
AS
BEGIN
  
  --
  -- The following does a select against the @friends XML to 
  -- see if we have any new friends to insert.
  --
  -- Notice the XPath?
  --
  DECLARE @num_new_friends INT

  SELECT @num_new_friends = COUNT( T.names.value('.[1]', 'VARCHAR(50)') )
  FROM @friends.nodes('/friends/name') T(names);
  
  IF (@num_new_friends > 0)
  BEGIN
    --
    -- Insert new friends
    --
    INSERT INTO dbo.Friends (
      name,
      date_created
    )
      SELECT T.names.value('.[1]', 'VARCHAR(50)'), GETDATE()
      FROM @friends.nodes('/friends/name') T(names)
      
  END
  
  
END
GO