saving result of SP

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

Thanks
Vaclav

Comments

  • 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");
    
  • 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);
  • You can provide parameters as additional arguments:
    ExecuteStoredProcedure("dbo.p_selectItemsWithPathByLang", "cs")
  • Fantastic! Works fine.
    Thanks a lot!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!