saving result of SP

vjedlicka

How can I save the result of a stored procedure call as CSV and save just some columns in a particular order?

Thanks
Vaclav

nescafe

You can utilize Util.WriteCsv for this. It accepts DataTables as well as IEnumerables.

// Create a simple stored procedure (select and run):
//   ExecuteCommand("CREATE PROCEDURE sp_SelectTables AS SELECT * FROM sys.tables");

// After data context refresh, sp_SelectTables() returns a set of tables which can be exported to CSV:
Util.WriteCsv(sp_SelectTables().Tables[0], @"C:\Temp\tables-full.csv");

// To output a selection of fields, you can use ExecuteStoredProcedure() with dynamic types:
Util.WriteCsv(
  ExecuteStoredProcedure("sp_SelectTables")
    .AsDynamic()
    .Select(x => new { x.object_id, x.name, x.type_desc, x.modify_date }),
  @"C:\Temp\tables-selection.csv");

vjedlicka

Thanks for the quick response!

There is some issue with the parameter of the SP. I am getting error "SP does not exist". When I removed the parameter 'cs', getting error "SP expects parameter which was not supplied". My code:

Util.WriteCsv(
ExecuteStoredProcedure("dbo.p_selectItemsWithPathByLang 'cs'")
.AsDynamic()
.Select(x => new { x.ID, x.ITM_PATH, x.ITM_NAME }),
@C:\Temp\tables-selection.csv);

nescafe

You can provide parameters as additional arguments:

ExecuteStoredProcedure("dbo.p_selectItemsWithPathByLang", "cs")

vjedlicka

Fantastic! Works fine.
Thanks a lot!