I am new to Servicestack Ormlite.
I want to execute the SQL query on database using Servicestack Ormlite and get the results in datatable.
SQL query will be generated randomly, containing different tables, columns each time. So I can't use poco class for the same.
We are using SQL Server as the database.
OrmLite doesn't support or have any dependencies on DataTables or DataSets which as a code-first POCO ORM is strictly opposed against the use of.
See the Dynamic Result Set docs for examples of querying untyped structures, either in a object List:
db.Select<List<object>>(db.From<Poco>()
.Select("COUNT(*), MIN(Id), MAX(Id)"));
Or Dictionary:
db.Select<Dictionary<string,object>>(db.From<Poco>()
.Select("COUNT(*) Total, MIN(Id) MinId, MAX(Id) MaxId"));
you can use CreateCommand method to get a datareader, like this:
var dt = new DataTable();
using (var db = dbFactory.Open())
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "select * from [table]";
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var row = dt.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
if (dt.Columns == null || dt.Columns.Count == 0)
{
for (int j = 0; j < reader.FieldCount; j++)
{
dt.Columns.Add(reader.GetName(j), reader.GetFieldType(j));
}
}
var cell = reader.GetValue(i);
row[i] = cell;
}
dt.Rows.Add(row);
}
}
}
