I have a string that returns a number of records when executed via SQL Server Management Studio
:
EXEC stored_proc1 '2019-05-20';// Returns certain record on executing
EXEC stored_proc2 '2019-05-20','ABC'; // Returns certain record on executing
EXEC stored_proc3 '2019-05-20', true, 'DEF';// Returns certain record on executing
Need to write the results of each of the above statements to different CSV files. I need a generic solution that is independent of what parameters are passed to the stored proc and store the result of executing the statement in an appropriate data structure (maybe DataTable?) which I can then parse and write the data to different CSV files.
Tried using SQLDataAdapter
and storing the result in a DataTable
but couldn't make it work.
var connectionString = "my connection string";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("EXEC sp_return_records '2019-05-20','ABC';", conn);
cmd.CommandType = CommandType.StoredProcedure;
DataTable t1 = new DataTable();
using (SqlDataAdapter a = new SqlDataAdapter(cmd))
{
a.Fill(t1);
}
Not sure if this is the correct way of executing the string. I am getting System.Data.SqlClient.SqlException, Execution Timeout Expired.