0

I was doing some work on a function that takes in a list of generic items, and converts them into a Sqlparameter that has been packed to interface with a SQL datatable parameter type. The challenge is that, the way the data will need to be packed will vary slightly if it is a simple list of what I will call atomic types, such as ints, floats, bools, and strings, versus how it will be processed if it is a collection of classes with public properties.

My function looks something like this:

public static SqlParameter ConvertObjectCollectionToParameter<T>
     (string parameterName, string sqlTypeName, IEnumerable<T> input)
{
    var dt = new DataTable();
    var genericType = typeof(T);

    // This bit is a hack
    var atomicTypes = new HashSet<string>() 
    { 
        "System.Int32","System.String","System.Double","System.Float","System.Boolean","System.DateTime",
        "System.DateTimeOffset","System.TimeSpan","System.Guid","System.Single","System.Decimal",
        "System.Byte","System.Byte[]","System.SByte","System.Char","System.UInt32","System.Int64", 
        "System.UInt64", "System.Object", "System.Int16" 
    };

    // this is the line that would be nice to replace with refelection
    if (atomicTypes.Contains(genericType.FullName))
    {
        // todo - convert list elements into datatable rows 
    }
    else
    {
        // this block works just fine right now with classes,
        // but note that there are any number of non-classes 
        // not in the hashset that could be passed in here that
        // will cause this code to fail.

        var objectProperties = genericType.GetProperties();

        foreach (var propertyInfo in objectProperties)
            dt.Columns.Add(propertyInfo.Name);

        foreach (var item in input)
        {
            var dr = dt.NewRow();

            foreach (var property in objectProperties)
                dr[property.Name] = genericType.GetProperty(property.Name).GetValue(item, null);

            dt.Rows.Add(dr);
        }
    }

    var sql_parameter = new SqlParameter()
    {
        ParameterName = parameterName,
        SqlDbType = SqlDbType.Structured,
        TypeName = sqlTypeName,
        Value = dt,
    };

    return sql_parameter;
}

I have tried to look at the properties of the generic type by looking at typeof(T), but unfortunately, I dont see anything that would distinguish these two types. Even the obvious looking IsClass property is true for both arbitrary classes and Strings when passed in, since the reflection is looking at the String object wrapper class that lurks under a C# string.

Any suggestions? I can deal with the specific cases easily enough, but just telling the two categories apart is pretty tricky. If I cannot do this with reflection, I am going to have to just make a list of every atomic type and check that each time. That seems pretty inelegant, though, so I'd like to avoid that approach if possible.

shingo
  • 18,436
  • 5
  • 23
  • 42
Roger Hill
  • 3,677
  • 1
  • 34
  • 38
  • 1
    Strings have to be handled ad-hoc, for all the other primitive types you have `Type.IsPrimitive` but the real question would be: what do you need it for? Maybe there is a better way to do it without knowing about specific types (after handling classes you might, for example, check for `IEnumerable` or similar). Note that "knowing" the type isn't the end of it. – Adriano Repetti Apr 24 '23 at 08:13
  • @AdrianoRepetti, `decimal` isn't primitive either: https://stackoverflow.com/questions/13471941/why-is-decimal-not-a-primitive-type – ASh Apr 24 '23 at 08:17
  • 1
    @Ash absolutely! Many commonly used `ValueType`s aren't _primitive_ types (also `DateTimeOffset` for example) – Adriano Repetti Apr 24 '23 at 08:21
  • You seem to be talking about C# data types, i.e. types that have their own keyword in C#, as opposed to .NET types. There is no actual distinction. C# doesn't provide any implementation for those data types. They are simply aliases for .NET types. You talk about a "String object wrapper class". But there is no such thing. There is no C# `string` object that is being wrapped by a .NET `String` object. VB has native data types too, e.g. `String` and `Integer` that are equivalent to C#'s `string` and `int`. VB has `Date` too, where C# has no alias for .NET's `DateTime`. – jmcilhinney Apr 24 '23 at 08:28
  • As suggested above, the question that needs answering here is, why do you want this? What do you think it will achieve for you? If we understand that then we can probably determine the best way to achieve it. – jmcilhinney Apr 24 '23 at 08:29
  • @Adriano, you have hit upon the central problem, as I could expect to see any of these types passed in - DateTimes, strings, decimals, bytes, etc. The problem is that I need to be able to pack any of this data into a data table object to parameterize as a SQL table parameter. – Roger Hill Apr 24 '23 at 08:33
  • Updated with full code sample – Roger Hill Apr 24 '23 at 08:42
  • A small note about names: those type are not _atomic_ (which has a different meaning). It seems that `SqlParameter` already handles (most of) those types for you. What you _could_ do (see also https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types) is to rely on the default implementation and handle the few remaining special cases (like big `byte[]` or other unknown reference types without a known conversion). Side note: try to avoid those strings (it's error prone, for example is there `System.Float`?) and use `typeof(...)` instead – Adriano Repetti Apr 24 '23 at 08:56
  • @adriano I don't think you understand the problem. I am not trying to pass basic parameters through an ADO connection. That is rather trivial to do. I am trying to pass collections of parameters into a table parameter, which requires you to structure the collection of data in a very specific fashion using data tables so that it can be correctly serialized in the ADO layer. Your suggestion doesn't really work. – Roger Hill Apr 24 '23 at 16:51
  • I know that atomic has meanings in CS. What better term applies? 'Class like' vs 'Non-class like'? 'User defined, non-nested class or struct POCOs with public properties that can be reflected across' doesn't really roll off the tongue... – Roger Hill Apr 24 '23 at 16:52

0 Answers0