Questions about statement execution

[GustavEikaas]

Are there any way today to convert a StatementExecution result to a C# class?

Are there any plans to add some sort of deserializer to this library?

I'm reluctant to write my own but the use-case for me is querying a single item from databricks and deserializing into a C# class. Of course I could store the response as CSV and parse it but it seems overly complicated for a single object.

Anyone feel free to comment and suggest ways of solving this.

[memoryz]

Are you referring to StatementExecutionResultChunk class? Or something else? Is the need to provide some sort of strong typing so you can get the result of an execution as IEnumerable<T> for some generic type T? If possible, could you please provide some mock code so I can better understand the need?

[dennisfriedrichsen]

I believe I have run into the same thing as @GustavEikaas

What I have: queryResult.Manifest.Schema

StatementExecutionSchemaColumn { Name = id, TypeText = STRING, TypeName = STRING, TypePrecision = 0, TypeScale = 0, TypeIntervalType = , Position = 0 } StatementExecutionSchemaColumn { Name = firstname, TypeText = STRING, TypeName = STRING, TypePrecision = 0, TypeScale = 0, TypeIntervalType = , Position = 1 } StatementExecutionSchemaColumn { Name = lastname, TypeText = STRING, TypeName = STRING, TypePrecision = 0, TypeScale = 0, TypeIntervalType = , Position = 2 }

queryResult.DataArray String[]

[00QRt000008qygoMAA, Loni, Moment] [00QRt000008qygoMAB, Les, Paul]

What I want:

public class Lead
{
                public string id { get; set; }
                public string firstname { get; set; }
                public string lastname { get; set; }
}

public async Task<IEnumerable<T>> RunSQL<T>(string sql)
{
                Console.WriteLine($"Querying warehouse id {id}");
                var s = SqlStatement.Create(sql, id);
                s.Format = StatementFormat.JSON_ARRAY;
                var results = await client.SQL.StatementExecution.Execute(s);
                var stronglyTyped = <Deserialize results into T>;
                return stronglyTyped;
}

IEnumerable<Lead> leads = RunSQL<Lead>(sql).Result;

I was hoping this StatementExecution would just return json so I could deserialize it into the object. But that's not what the JSON_ARRAY seems to be giving.

[GustavEikaas]

@memoryz sorry your reply must have passed me by. Yes, my issue is the same as @dennisfriedrichsen describes.

[memoryz]

@GustavEikaas, @dennisfriedrichsen, please take a look at PR #238 and let me know if this works for your cases.

[dennisfriedrichsen]

@memoryz - I'm far from an expert in this kind of stuff. But that still requires that constructor that takes a JsonArray and is dependent on position, right?

I've been using the following code to handle the current string[][] DataArray. It's far from perfect, but has worked for the simple cases I've thrown at it for the last couple weeks.

---

private static IEnumerable<T> CreateJson<T>(StatementExecutionSchema schema, string[][] dataArray) { IList<T> result = [];

var schemaColumns = schema.Columns.Select(c => c.Name).ToList();

JsonSerializerOptions options = new();
options.Converters.Add(new BooleanConverter());
options.Converters.Add(new DoubleConverter());
options.Converters.Add(new IntConverter());
options.Converters.Add(new NullableConverter<int>());
options.Converters.Add(new NullableConverter<double>());
// Add other converters as needed

foreach (var row in dataArray)
{
    JsonObject jsonObject = [];
    for (int i = 0; i < schemaColumns.Count; i++)
    {
        jsonObject[schemaColumns[i]] = row[i];
    }

    string jsonString = jsonObject.ToString();
    T? record = JsonSerializer.Deserialize<T>(jsonString, options);
    if (record != null)
    {
        result.Add(record);
    }
}
return result;

}

[memoryz]

I see what you're trying to accomplish here. My opinion is that this kind of deserialization should be handled by the caller instead of this library. From the JsonArray, you can pretty much use the same for loop to dynamically deserialize it into type T.