How to execute SQL string from C#, and store the returned result in a DataTable, or some other appropriate data structure? [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 6 years ago.

Improve this question

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.

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
– BugFinder, Commented May 22, 2019 at 8:57
Have edited the question and added the code I wrote that didn't work — chicken momo
– chicken momo, Commented May 22, 2019 at 9: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
– Panagiotis Kanavos, Commented May 22, 2019 at 9:08
@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
– Marc Gravell, Commented May 22, 2019 at 9:19

Marc Gravell · Accepted Answer · 2019-05-22 09:23:19Z

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

answered May 22, 2019 at 9:23

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paulo Lins · Accepted Answer · 2019-05-24 14:40:19Z

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

edited May 24, 2019 at 14:40

answered May 22, 2019 at 9:38

Paulo Lins

12 bronze badges

1 Comment

Marc Gravell Over a year ago

You should probably be explicit about what tools are in play here; this looks like it might be EF or EF-Core?

Collectives™ on Stack Overflow

How to execute SQL string from C#, and store the returned result in a DataTable, or some other appropriate data structure? [closed]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related