How to execute a generic SQL Command in C#?

Question

I am trying to write a Method that simply accepts a string which is an SQL Command and runs it against the pre-defined Database/Server ... This is what I have so far:

public class TSqlConnector : IDbConnector
{
    private readonly string _connectionString;

    public TSqlConnector(string conn)
    {
        _connectionString = conn;
    }
    public IEnumerable<object[]> ExecuteCommand(string query)
    {
        var res = new List<object[]>();
        try
        {
            using (SqlConnection sql = new SqlConnection(_connectionString))
            {
                sql.Open();
                SqlCommand cmd = new SqlCommand(query, sql);
                var reader = cmd.ExecuteReader();
                DataTable tbl = new DataTable();
                while (reader.Read())
                {
                    var dr = tbl.NewRow();
                    dr.ItemArray = new object[reader.FieldCount];
                    reader.GetValues(dr.ItemArray);
                    res.Add(dr.ItemArray);
                }

            }

            return res;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            throw;
        }
    }
}

This code, however, gives me an error saying that

Input array is longer than the number of columns in this table.

I googled the error message, apparently I first have to define the DataTable's columns, using tbl.Add("ColumnName", typeof(type));

This however, completely undermines what I was trying to do - writing a generic version. All I wanted was some kind of construct which contains the information I would get from the SqlServer if I typed the same command into SSMS, I don't really care what hoops I have to jump through to read the data in C#; Using an object-Array for each row, having to manually cast each object into a string, int or whatever is perfectly acceptable, even a CSV-like string would be just fine

The only thing I don't want to do is add a definition for the table or a fixed amount of row. Each method that uses ExecuteCommand() will have to know what type of object-array is returned and that's fine but adding some complex data structure containing types and column names in addition to the SQL Commands seems like overkill.

Is there some easier way to achieve this?

You could use Entity Framework for example. Btw. what output do you expect when `type` is something the database isn't supporting? E.g. an enum. `tbl.Add("ColumnName", typeof(MyColor));` — nilsK, Jul 24 '18 at 12:59
or even drop table or worse... offering such can be very dangerous depending on how / where you expose this "feature"... — DRapp, Jul 24 '18 at 13:00
If you ask me I would not do this. There are much better options like [dapper](https://github.com/StackExchange/Dapper) or [PetaPoco](https://github.com/CollaboratingPlatypus/PetaPoco) — Filip Cordas, Jul 24 '18 at 13:02
It is better practice to `yield return` when your method returns an Enumerable. — Crowcoder, Jul 24 '18 at 13:05
See the answer to this for automatically loading the datatable : https://stackoverflow.com/questions/18961938/populate-data-table-from-data-reader — PaulF, Jul 24 '18 at 13:06
@Crowcoder Using `yield return` is not really recommended for this scenario because of dangers leaving your connection open. In this scenario returning a list is probably better. — Filip Cordas, Jul 24 '18 at 13:21
@KennethK. I don't have a reference handy at the moment, but I think one place you can find it is in one of Bill Wagner's Effective C# editions. One benefit is that you allow the caller to end an iteration without having to materialize the entire collection. — Crowcoder, Jul 24 '18 at 13:58
@FilipCordas there is no danger of open connections if the reader is inside a `using` (instead of the connection) with CommandBehavior.CloseConnection. — Crowcoder, Jul 24 '18 at 14:08
@Crowcoder using will only work if you iterate all the items. If you are doing skip, take, first. I am not saying it shouldn't be done but there is nothing wrong with using lists in this case if you are afraid of problems with yield. — Filip Cordas, Jul 24 '18 at 20:54
@FilipCordas it is not true that `using` only works if you iterate all items. Good software is largely about good API etiquette and when I get an Enumerable I expect that it will yield return items. It is designed that way very intentionally so your code can be more efficient dealing with large collections or streams which is what a DataReader really exposes. — Crowcoder, Jul 25 '18 at 12:24

Gerald Chifanzwa · Accepted Answer · 2018-07-24T13:11:48.267

3

What you have is an IDataReader from your cmd.ExecuteReader();

To load the results into a DataTable, you can use the Load method as follows:

var reader = cmd.ExecuteReader();
DataTable tbl = new DataTable();
tbl.Load(reader);
// now tbl contains the corresponding columns and rows from your sql command.

// Then you can return the ItemArrays from each row;

return tbl.Rows.Cast<DataRow>().Select(row => row.ItemArray);

edited Jul 24 '18 at 13:11

answered Jul 24 '18 at 13:03

Gerald Chifanzwa

1,277
7
14

Sweet! I figured there had to be an easier way but could for the life of me not figure it out! Thank you! – Vaethin Jul 24 '18 at 13:19

score 0 · Answer 2 · answered Jul 24 '18 at 13:17

I've used code like this to input a generic SQL query and return the results as a datatable. Of course, you'll have to parse out the results how you need them.

private DataTable QueryToTable(string sql, string cs)
{

    var ds = new DataSet();

    using (var adapter = new SqlDataAdapter(sql, cs))
    {
        adapter.Fill(ds);
    }

    return ds.Tables(0);
}

How to execute a generic SQL Command in C#?

2 Answers2