How to apply SQL query to a C# DataTable/Dataset?

Question

I have an application, where users can enter an sql query as text, and I need to run it against a C# DataTable/Dataset inside my application. Is it possible to do?

EDIT: Based on the answers, and some more research it cannot be done - there is no way to apply an SQL query to a table that you already read into your application. Look at answers for possible workarounds.

EDIT 2: The final solution in my case was implementing a simple parser using ANTLR. It allowed users to enter simple queries using 'AND', and 'OR' keywords as well as parentheses. ANTLR generated class would convert that into a set of instructions which I could then use to query the C# Dataset.

Funds

Yes, but it's but that's probably a bad idea. What prevents sql injection? Have you looked at the SqlCommand and SqlDataAdapter classes? – Sinnard

As far as you are creating an application for tutorial/educational purpose this is fine. Other wise it will said to be a bad programming practice. You may use entityframework, you will have an experience of learning some ORM tool at the end. – Canadian

@Josh C. Quickly looking at SqlCommand and SqlDataAdapter classes gave me an impression I can only apply them to a database (because of connection string). How can I apply them to a C# DataTable residing in a C# Dataset (i.e. it's already read into memory). – Funds

@DarshanJoshi The Dataset/Datatable exist INSIDE the application, not actual database, and are read-only (there is no connection back to live database). Again, I'm not concerned about sql injection. I just want an easy and highly customizable way for users to enter their search criteria. – Funds

Do you want to run sql based query over dataset/datatable? – Canadian

Ah, I misunderstood. Are you only filtering? If so, DataTable has a .Select("filter clause"); method. – Sinnard

Answer 1

If your users will be entering anything but the simplest select statements, you will have a very difficult time doing this. I imagine it would be prohibitively costly for your project to write a complete parser for SQL, but that's essentially what you're talking about.

For a home-grown ORM that we have, I have a class which transforms essentially pre-defined SQL queries into something that can be used with DataTable.Select, but the where clause is generated from SqlParameters.

Possible Solution

Perhaps you can combine the following projects to get you close to what you're after:

Linqer (SQL to LINQ converter) then LINQ to DataSet

I have not used Linqer myself.

Some other thoughts

I'm sure you've been giving this some thought, but the difficulty of doing this possibly means there's a better way if you zoom out a little. Strictly speaking, querying a cache with an unknown query means that you'd have to fill the cache with all possible data, or be able to call that data upon submitting the query. By definition, this can't deliver better performance than querying the source directly, unless you're hitting the cache enough before it's out of date to make that worthwhile. For an ad-hoc reporting system (my assumption), I tend to doubt that's the case, and I would also worry that it will not outperform the database engine in anything but edge cases.

@JoshC also mentions a possibility with Sqlite, and there's also SQL Server 2012 LocalDB which may fit the bill, though these certainly aren't .net datasets.

Polymerism

Not having to write my own SQL parser was precisely the reason why I was trying to see if .NET already supports a method that can accept an SQL query. Based on answers so far, it doesn't look like it does. – Funds

Answer 2

if you want to run a search string against c# datatable/dataset inside your application

you can use filter expression in select method.

myDataTable.Select("columnName1 like '%" + value + "%'");

Oden

The problem is, that search string inside Select() function is not an sql query. I can't expect my end users to know some esoteric .NET syntax, but I can expect them to know SQL syntax. Is there any way I can convert sql query into something that can be applied to C# dataset? – Funds

@Funds If you are only filtering, you could trim the select down to the where clause. If you are hiding/showing columns based on the select, you could parse the column names form the select and modify the view accordingly. – Sinnard

is this work well when the column name have blanks in the middle ? – Jemison

enclose the column name within square brackets [ ] for explicit filters – Oden

Answer 3

Actually there is a simple solution.

1. Export your data in DataTable to a SQLite in-memory database.
2. Run SQL on the SQLite in-memory database.

Perianth

Answer 4

This is working for me for exporting DataSet to SQLite:

$Data = <your DataSet>

$SQLiteConnection = [System.Data.SQLite.SQLiteConnection]::new('DataSource=:MEMORY:')
$SQLiteConnection.Open()

[System.String[]]$tableNames = $Data.Tables.TableName

foreach ($table in $tableNames) {
 $columnDefinitions = @()
 foreach ($column in $Data.Tables[$table].Columns) {
#  Ought to create mapping between Types and DBTypes AnsiString, Binary, Byte, Boolean, Currency, Date, DateTime, Decimal, Double, Guid, Int16, Int32, Int64, Object, SByte, Single, String, Time, UInt16, UInt32, UInt64, VarNumeric, AnsiStringFixedLength, StringFixedLength, Xml, DateTime2, DateTimeOffset
  $columnDefinitions += ($column.ColumnName + ' ' + $column.DataType.Name)
 }

 $sqliteCommand = $SQLiteConnection.CreateCommand()
 $sqliteCommand.CommandText = 'CREATE TABLE IF NOT EXISTS ' + $table + ' (' + [System.String]::Join(', ', $columnDefinitions) + ')'
 $rowsAffected = $sqliteCommand.ExecuteNonQuery()

 $InsertCommand = 'INSERT INTO ' + $table + ' (' + [System.String]::Join(', ', $Data.Tables.Columns.ColumnName) + ') VALUES (:' + [System.String]::Join(', :', $Data.Tables.Columns.ColumnName) + ')'

 $sqliteDataAdapter = [System.Data.SQLite.SQLiteDataAdapter]::new($SQLiteConnection)
 $sqliteDataAdapter.MissingSchemaAction = [System.Data.MissingSchemaAction]::AddWithKey
 $sqliteDataAdapter.InsertCommand = [System.Data.SQLite.SQLiteCommand]::new($InsertCommand, $SQLiteConnection)

 foreach ($column in $Data.Tables[$table].Columns) {
  $null += $sqliteDataAdapter.InsertCommand.Parameters.Add($column.ColumnName, $column.DataType.Name, $column.MaxLength, $column.ColumnName)
 }

 $rowsAffected = $sqliteDataAdapter.Update($Data, $table)
}

Brandabrandais