Simple SQL query with DataContext

Question

I have a web-site connected to a SQL Server database, and I want to add a simple SQL query to it (for administrators). I was hoping to use the DataContext, and run a query, then return the results as a simple list. Is there any way to do this?

Using

                string full_query = "SELECT " + query;
            IEnumerable<string> results = DB.DB().ExecuteQuery<string>(full_query);

Doesn't work, throwing errors where ints come through. Changing the template parameter to "object" doesn't help much either.

So I need to run a select statement, and return the results as a list on a page.

Any ideas?

Nikita, the select statement is going to be generated at runtime, so it could be anything. The only limit is that it will start with "select" and have only one command (so no chaining a select followed by an insert) — Simon Parker
– Simon Parker, Commented Sep 27, 2015 at 23:39

Kien Chu · Accepted Answer · 2015-09-25 07:13:13Z

3

Normally you would want to use:

var results = DB.DB().SqlQuery(full_query);

If you want insert/update/delete, you can use:

DB.DB().ExecuteSqlCommand(full_query);

Hope it helps.

answered Sep 25, 2015 at 7:13

Kien Chu

4,8951 gold badge21 silver badges31 bronze badges

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

3 Comments

Simon Parker Over a year ago

I have a DataContext, not a DBContext, so I don't have access to SqlQuery

Kien Chu Over a year ago

Do you have any parameter in your select query?

Simon Parker Over a year ago

kienct89, the select query is created at run-time, so it could have anything in it.

Simon Parker · Accepted Answer · 2015-09-29 00:39:56Z

After a bit of messing around, I found something that works. I am using a class called DatabaseResults to hold the results:

public class DatabaseResults
{
    public List<string> ColumnNames { get; set; }
    public List<List<string>> Rows { get; set; }

    public DatabaseResults()
    {
        ColumnNames = new List<string>();
        Rows = new List<List<string>>();
    }
}

The method then goes and runs the query, grabbing the headings and putting them in the results objects. It then reads the rows, taking the strings of the column values. "query" is the string passed in. It is the "select" query, with the select bit missing.

            DatabaseResults results = new DatabaseResults();
            string full_query = "SELECT " + query;
            DbConnection connection = DB.DB().Connection;
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = full_query;

            try
            {
                using (var reader = command.ExecuteReader())
                {

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        results.ColumnNames.Add(reader.GetName(i));
                    }

                    while (reader.Read())
                    {
                        List<string> this_res = new List<string>();
                        for (int i = 0; i < reader.FieldCount; ++i)
                        {
                            this_res.Add(reader[i].ToString());
                        }
                        results.Rows.Add(this_res);
                    }
                }
            }
            catch (Exception ex)
            {
                results.ColumnNames.Add("Error");
                List<string> this_error = new List<string>();
                this_error.Add(ex.Message);
                results.Rows.Add(this_error);
            }
            finally
            {
                connection.Close();
            }

I can't destroy the connection, as it is used by the systems db object, so I need to open and close it. The try/catch/finally makes sure this happens.

Collectives™ on Stack Overflow

Simple SQL query with DataContext

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related