Tuesday, March 13, 2012

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();
}

No comments: