-3

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 SQLDataAdapterand 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.

chicken momo
  • 79
  • 7
  • 1
    Hi, you will need to be more specific than "couldnt make it work" .. what code did you use, what results did you get, what problem did you have – BugFinder May 22 '19 at 08:57
  • Can you provide example? – Tim Maes May 22 '19 at 09:00
  • Have edited the question and added the code I wrote that didn't work – chicken momo May 22 '19 at 09:08
  • You pass a SQL query to SqlCommand and yet you specify that `CommandType` is a stored procedure *name*. Pass only the name to SqlCommand. The parameters should be added to SqlCommand's Parameters collection. – Panagiotis Kanavos May 22 '19 at 09:08
  • Yes it didn't feel right to me either so I knew it wouldn't work. What's the correct way of executing a query and storing the result. Removed the – chicken momo May 22 '19 at 09:10
  • See I removed the CommandType stored procedure, now getting a different error, Execution Timeout Expired. I don't wish to add the parameters, because I need to execute different stored procedures this way which all have different number of parameters. I want the code to return records and store them, irrespective of which SP name is mentioned in the command string. – chicken momo May 22 '19 at 09:13
  • 1
    @chickenmomo the approach of embedding parameters in a string is usually a **terrible idea** - it might be OK if these are hard-coded or configuration-supplied trusted strings, but if that data comes from your app executing, **seriously, don't do that** - it will hurt you *a lot*. Parameters are your friend, and tools like "Dapper" exist to make working with parameters a painless experience. Additional notes: the `sp_` prefix is reserved by Microsoft - you're not meant to prefix your own procs with that; and you should *almost never* use `DataTable` these days. – Marc Gravell May 22 '19 at 09:19
  • I have multiple stored procedures that return records. The different SPs accept different parameters, that's why I am looking for a generic solution that executes a string with hard coded parameters and returns the result in some data structure, that can be easily parsed and written to a CSV – chicken momo May 22 '19 at 10:36

2 Answers2

1
List<MyKnownType> rows;
using (var conn = new SqlConnection(connectionString))
{
    DateTime when = ... /// 2019-05-20 - typed **as a DateTime**, not a string
    rows = conn.Query<MyKnownType>("return_records",
        new { ParamNameHere = when, AnotherParamName = "ABC" }, // this is the parameters
        commandType: CommandType.StoredProcedure
    ).AsList();
}

Important points here:

  • DataTable is almost never a preferred choice, unless you're writing a dynamic reporting app, or something similar to SSMS
  • in this case, I'm using a class MyKnownType { ... } which would have properties that match your expected columns in both name and type
  • parameters; always parameters
  • ADO.NET has an unfriendly API; tools like "Dapper", shown above, make it easy
  • I removed the sp_ prefix; you aren't meant to do that
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
0
using System.Collections.Generic;
using System.Text;
using System.Web.Mvc;
using System.Data.Entity;

public class Car
{
    public string Model{ get; set; }
    public string Color{ get; set; }
    public int Year { get; set; }
}

var sql = new StringBuilder();
sql.Append(" SELECT Model, Color, Year ");
sql.Append(" FROM   TableCar ");

var list = someDBCONTEXT.Database.SqlQuery<Car>(sql.ToString()).ToList();

Important points here:

I'm using Entity Framework 6

Paulo Lins
  • 1
  • 2