List of columns and types from a raw sql query

ServiceStack.OrmLite
1

Is there a nice way to get the list of column names and types from a raw query? I understand that there must be a row of data to get types which is what I have in the sample code below. The biggest issue with what I have so far is that joined colums with the same name aren’t preserved. ie select c.id, d.id from table c inner join tabled d on c.id=d.id (the dictionary will only have one “id” entry). Is there a better way in general to accomplish this? Sample code, no error checking:

  var test2 = db.Select<Dictionary<string, object>>($$"{sql} {orderBy} {skiptake} ");
 Dictionary<string, string> ColumnTypes = new Dictionary<string, string>();
	
	foreach(var k in test2.FirstOrDefault().Keys) {
		var o = (test2.FirstOrDefault()[k]);
		if (o==null ) {
			ColumnTypes.Add(k, "null" );	
		} else {
			ColumnTypes.Add(k, o.GetType().ToString());	
		}
		
	}

I actually noticed this service stack template demo suffers the same issue: http://rockwind-sqlite.web-app.io/northwind/sql/

Try with this sql: select o.,c. from “Order” o inner join Customer c on o.CustomerId = c.Id order by Id desc limit 10

2

OrmLite generally never parses raw SQL columns, but in the latest v5.4.1 you may be able to use the .ParseCommands() extension method to parse a SELECT expression column list.

Here’s an example usage of retrieving the field or alias names of a SQL select statement, e.g:

var sql = "SELECT a as \"A\", fn() as B, C FROM Table";

var selectList = sql.RightPart("SELECT ").LeftPart(" FROM"); //a as \"A\", fn() as B, C

var columns = selectList.ParseCommands();
var aliasesOrNames = columns
    .Map(x => x.Original.ToString().LastRightPart("as").Trim().StripQuotes());

aliasesOrNames.PrintDump(); //= A, B, C
1 Like
3

I just upgraded (using linqpad) to 5.4.1 and getting runtime error on app.Init().

MissingMethodException: Method not found: ‘ServiceStack.Text.TextCase ServiceStack.Text.JsConfig.get_TextCase()’.

4

This is always due to dirty package dependencies, please clear out your existing packages cache with:

$ nuget locals all -clear
5

If I have so manually specify them with aliases (as A, as B) then there isn’t much need for my question. I was working off the case where I have something like select a., b. or even a.Id, b.Id. The Parsecommands does return the select part but that’s not really what I’m after as I need the full column list and all the types for the resulting set.

6

The OriginalString gets you the full column definition if that’s what you want, either way there’s nothing else that parses select expressions.

7

I think this is useful but not for my particular case.

I need every column returned and their data types. I need to execute the query to get one row of data in order to get the data types for each returned column. One problem is that the returned object (Dictionary<string, object>) overwrites columns with the same names (a.id, b.id, c.id) essentially only returning the last one c.id in the dictionary with key=id. Not sure if this is related to ormlite or something else. You can actually see this on the rockwind sql lite demo (see the edit for my first post with the link) as well, I assume it uses the same dictionary<string, object>.

8

You’re not going to be able to fetch duplicate column names from any key/value data structure like a dictionary which can only have unique keys, either your select expression needs aliases to make the names unique or you’re going to need to fetch them positionally using db.SqlList<List<object>>(sql).

If you knew the Type and names you could prefix the column name with the table name to be able to map the same column name from different tables to different properties, but with dynamic SQL you wont have that option.