Bug 26547 - Issue when using UDT/table with SqlParameter
Summary: Issue when using UDT/table with SqlParameter
Status: NEW
Alias: None
Product: Class Libraries
Classification: Mono
Component: System.Data (show other bugs)
Version: 3.12.0
Hardware: Macintosh Mac OS
: --- normal
Target Milestone: Untriaged
Assignee: Bugzilla
URL:
Depends on:
Blocks:
 
Reported: 2015-01-29 12:01 UTC by Robert Rascalon
Modified: 2017-09-01 09:29 UTC (History)
1 user (show)

See Also:
Tags:
Is this bug a regression?: ---
Last known good build:


Attachments
Source code for IntListType (note working on pc but not mac os) (25.30 KB, application/octet-stream)
2015-01-29 12:01 UTC, Robert Rascalon
Details

Description Robert Rascalon 2015-01-29 12:01:11 UTC
Created attachment 9552 [details]
Source code for IntListType (note working on pc but not mac os)

Hi

Exception when using UDT/Table with SQL Parameter (not working on PC platform but not mac os)

Code example:

                var ids = photoIds.ToDataTableIntList();

                var query = session.GetNamedQuery("PhotoThumbnailGetByIds");

                query.SetStructured("PhotoIds", ids);  

                var dbResult = query.List<Photo>();

Exception detail;

InnerException	{System.ArgumentOutOfRangeException: No mapping exists from SqlDbType Structured to a known DbType. Parameter name: SqlDbType   at System.Data.SqlClient.SqlParameter.SetSqlDbType (SqlDbType type) [0x003c3] in /private/tmp/source-mono-mac-3.12.0-branch-32/bockbuild-mono-3.12.0-branch/profiles/mono-mac-xamarin/build-root/mono-3.12.0/mcs/class/System.Data/System.Data.SqlClient/SqlParameter.cs:837    at System.Data.SqlClient.SqlParameter.set_SqlDbType (SqlDbType value) [0x00000] in /private/tmp/source-mono-mac-3.12.0-branch-32/bockbuild-mono-3.12.0-branch/profiles/mono-mac-xamarin/build-root/mono-3.12.0/mcs/class/System.Data/System.Data.SqlClient/SqlParameter.cs:382    at (wrapper remoting-invoke-with-check) System.Data.SqlClient.SqlParameter:set_SqlDbType (System.Data.SqlDbType)   at Abilis.Web.Inmate.Library.Database.IntListType.NullSafeSet (IDbCommand st, System.Object value, Int32 index, ISessionImplementor session) [0x00023] in /Users/rrascalon/Projects/AspNetTemplatesForMono-master/Mvc4CSharpRazorFx45Intranet/Abilis.Web.Inmate.Library/Database/IntListType.cs:233    at NHibernate.Param.NamedParameterSpecification.Bind (IDbCommand command, IList`1 multiSqlQueryParametersList, Int32 singleSqlParametersOffset, IList`1 sqlQueryParametersList, NHibernate.Engine.QueryParameters queryParameters, ISessionImplementor session) [0x00000] in <filename unknown>:0    at NHibernate.Param.NamedParameterSpecification.Bind (IDbCommand command, IList`1 sqlQueryParametersList, NHibernate.Engine.QueryParameters queryParameters, ISessionImplementor session) [0x00000] in <filename unknown>:0    at NHibernate.SqlCommand.SqlCommandImpl.Bind (IDbCommand command, ISessionImplementor session) [0x00000] in <filename unknown>:0    at NHibernate.Loader.Loader.PrepareQueryCommand (NHibernate.Engine.QueryParameters queryParameters, Boolean scroll, ISessionImplementor session) [0x00000] in <filename unknown>:0 }	System.ArgumentOutOfRangeException


where ToDataTableIntList implemented as follow:

       
        public static DataTable ToDataTableIntList(this IList<int> idsList)
        {
            return SqlTableTypeHelper.CreateTableType(idsList);
        }

using:

public sealed class SqlTableTypeHelper
    {
        #region Constants

        private const string PRIMARY_COLUMN_NAME = "Id";

        #endregion

        #region Public Methods

        /// <summary>
        /// Creates the type of the table.
        /// </summary>
        /// <param name="idList">The identifier list.</param>
        /// <returns></returns>
        public static DataTable CreateTableType(IEnumerable<String> idList)
        {
            return CreateTableType(idList, typeof(String), PRIMARY_COLUMN_NAME);
        }

        /// <summary>
        /// Creates the type of the table.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="idList">The identifier list.</param>
        /// <returns></returns>
        public static DataTable CreateTableType<T>(IEnumerable<T> idList) where T : new()
        {
            return CreateTableType(idList, (new T()).GetType(), PRIMARY_COLUMN_NAME);
        }

        /// <summary>
        /// Creates the type of the table.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="idList">The identifier list.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns></returns>
        public static DataTable CreateTableType<T>(IEnumerable<T> idList, string columnName) where T : new()
        {
            return CreateTableType(idList, (new T()).GetType(), columnName);
        }

        /// <summary>
        /// Creates the type of the table.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="idList">The identifier list.</param>
        /// <param name="type">The type.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns></returns>
        public static DataTable CreateTableType<T>(IEnumerable<T> idList, Type type, string columnName)
        {
            var table = new DataTable();

            table.Columns.Add(new DataColumn(columnName, type));

            if (idList != null)
            {
                IEnumerator enumerator = idList.GetEnumerator();

                while (enumerator.MoveNext())
                {
                    var row = table.NewRow();

                    row[0] = enumerator.Current;

                    table.Rows.Add(row);
                }
            }

            return table;
        }

        #endregion
    }


where SetStructured implemented as follow: (for IntListType see attachment)

    public static class TypeExtensions
    {
        #region Properties

        private static readonly IntListType _structured = new IntListType();

        #endregion

        #region Public Methods

        /// <summary>
        /// Sets the structured.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="name">The name.</param>
        /// <param name="dt">The dt.</param>
        /// <returns></returns>
        public static IQuery SetStructured(this IQuery query, string name, DataTable dt)
        {
            return query.SetParameter(name, dt, _structured);
        }

        #endregion
    }

Note You need to log in before you can comment on or make changes to this bug.