Serialize a. ADO.NET DataTable to a CSV String

Here is an example of querying a stored procedure and serializing the DataTable to a comma delimited string. The bonus with this with the reuse of the connection string in the associated Entity Data Model (EDM). I originally tried to use the connection object from the EDM, but I received errors sparatically when I opened the "StoreConnection" SQLConnection object.

Code notes:
"_baseContext" is an object of type "ObjectContext" is the result from the Entity Framework context's "GetBaseContext()" function.

The BuildXmlString function is the same from used in my SQL Multiple Item Update Using XML blog post. 

public string GetDemoExport(int[] DemoItemIDs)
{
    string connectionString = ((System.Data.EntityClient.EntityConnection)_baseContext.Connection).StoreConnection.ConnectionString;
    DataTable data = new DataTable();
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        //IDbConnection conn = ((System.Data.EntityClient.EntityConnection)_baseContext.Connection).StoreConnection;
        SqlCommand cmd = new SqlCommand("GetDemoExport", (SqlConnection)conn);
        cmd.CommandType = CommandType.StoredProcedure;
        string paramRoot = "DemoItemIDs";
        cmd.Parameters.AddWithValue("@" + paramRoot, BuildXmlString<int>(paramRoot, DemoItemIDs));

        conn.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        adapter.Fill(data);
        conn.Close();
    }

    StringBuilder buffer = new StringBuilder();

    int colCount = data.Columns.Count;
    for (int idx = 0; idx < colCount; idx++)
    {
        if (buffer.Length > 0)
        {
            buffer.Append(",");
        }
        buffer.Append(data.Columns[idx].ColumnName);
    }
    buffer.AppendLine("");
    int rowCount = data.Rows.Count;
    for (int idx = 0; idx < rowCount; idx++)
    {
        DataRow row = data.Rows[idx];
        buffer.AppendLine(string.Join(",", row.ItemArray));
    }

    return buffer.ToString();
}

Ensure a SQL Table Has a Certain Number of Rows

Here is a useful simple script that can be modified to ensure a table has a certain number of rows. The example uses an insert statement, however I have used it to call a stored procedure that inserts rows (among other things). It is easier than copy/pasting insert/stored procedure statements.

DECLARE @Count as int
SELECT @Count = COUNT(DemoItemID) FROM DemoItems
WHILE @Count < 50
BEGIN
 INSERT INTO DemoItems ([DemoItemID])
  VALUES (@Count+1)
 SELECT @Count = COUNT(DemoItemID) FROM DemoItems
END
PRINT 'Done.'

SQL Multiple Item Update Using XML

From time to time, I run into a situation where I need to update multiple rows on a table. I could make multiple requests to the database, but that is rather chatty. Alternatively, a comma delimited string of values could be parsed and used to get the update. Being a big fan of XQuery, I was able to pass in an xml document and load a table that could be used to join with the table to be updated. This should scale beautifully since it plays to SQL's strengths. I have looked for a way to get some metrics on the efficiency of this verses other options and could not find a way.

This is the function I used to generate XML string. I think there are ways that I can make the function a little tighter, but it does the job and is too inefficient.

public static string BuildXmlString<t>(string xmlRootName, T[] values)
{
    StringBuilder xmlString = new StringBuilder();

    xmlString.AppendFormat("<{0}>", xmlRootName);
    int count = values.Length;
    for (int idx = 0; idx < count; idx++)
    {
        xmlString.AppendFormat("<value>{0}</value>", values[idx]);
    }
    xmlString.AppendFormat("</{0}>", xmlRootName);

    return xmlString.ToString();
}

Here is the SQL that uses the XML and updates a table with a resolved parameter value.

DECLARE @status varchar(50), @demoItemIDs xml
 SET @status = ''
 
 -- Load up the table variable with the parsed XML data
 DECLARE @DemoItems TABLE (ID int) 
 INSERT INTO @DemoItems (ID) SELECT ParamValues.ID.value('.','int')
 FROM @demoItemIDs.nodes('/demoItemIDs/value') as ParamValues(ID) 

 -- Resolve status name to ID
 DECLARE @statusID int 
 SELECT TOP 1 @statusID = DemoStatusID FROM DemoStatuses ds WHERE ds.Name = @status

 IF (@statusID > 0)
 BEGIN 
  -- Update the payments with the new status
  UPDATE [Demo].[dbo].[DemoItemApprovals]
     SET DemoStatusID = @statusID
   WHERE DemoItemID IN (
    SELECT ID FROM @DemoItems d
      INNER JOIN DemoItemApprovals da
    ON    da.PaymentID = d.ID
   )
 END
 ELSE
 BEGIN
  RAISERROR (
   N'The status ''%s'' does not exist.' -- Message text.
   , 10 -- Severity,
   , 1 -- State,
   , @status -- First argument.
   );
 END