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
– nilsK, Commented Jul 24, 2018 at 12:59
or even drop table or worse... offering such can be very dangerous depending on how / where you expose this "feature"... — DRapp
– DRapp, Commented Jul 24, 2018 at 13:00
If you ask me I would not do this. There are much better options like dapper or PetaPoco — Filip Cordas
– Filip Cordas, Commented Jul 24, 2018 at 13:02

Gerald Chifanzwa · Accepted Answer · 2018-07-24 13:11:48Z

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, 2018 at 13:11

answered Jul 24, 2018 at 13:03

Gerald Chifanzwa

1,3677 silver badges14 bronze badges

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

1 Comment

Vaethin Over a year ago

Sweet! I figured there had to be an easier way but could for the life of me not figure it out! Thank you!

Slapout · Accepted Answer · 2018-07-24 13:17:46Z

0

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);
}

answered Jul 24, 2018 at 13:17

Slapout

3,8195 gold badges43 silver badges63 bronze badges

Collectives™ on Stack Overflow

How to execute a generic SQL Command in C#?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related