예제 #1
0
        /// <summary>
        /// This helper converts the input list of native pararmeters to the
        /// equivalent SqlParameter.
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] ConvertToSqlParams(TVPProcInfo aProcInfo, List <object> aParameters, int aTVPCount, out SqlParameter aResultParam)
        {
            // Iterate the input parameters and convert to SqlParameters.
            List <SqlParameter> result = new List <SqlParameter>();

            for (int i = 0; i < aParameters.Count; i++)
            {
                // When i < aTypes.Length, the parameter will be a 'Structured' type.
                // If not a table, then make special allowance for a null value being passed
                // and set the parameter type to VarChar, since this can accept a DbNull value.
                SqlParameter p = new SqlParameter(String.Format("@P{0}", i), (i < aTVPCount) ? SqlDbType.Structured : (aParameters[i] != null) ? _SqlDataTypes[aParameters[i].GetType()] : SqlDbType.VarChar)
                {
                    Direction = ParameterDirection.Input
                };

                // For TableType parameters, the SQL TypeName is required.
                if (i < aTVPCount)
                {
                    p.TypeName = aProcInfo.Details[i].SqlTypeName;
                }

                // If value is present then set it, else set DbNull and the length (mini optimization).
                if (aParameters[i] != null)
                {
                    p.Value = aParameters[i];
                }
                else
                {
                    p.Value = DBNull.Value;
                    p.Size  = 1;
                }

                result.Add(p);
            }

            // Add a final parameter representing the ReturnValue.
            aResultParam = new SqlParameter("@Result", SqlDbType.Int)
            {
                Value     = result,
                Direction = ParameterDirection.Output
            };
            result.Add(aResultParam);

            // Return result as an array, as this is required by ExecuteSqlCommand().
            return(result.ToArray());
        }
예제 #2
0
        private static TVPProcInfo _RegisterProcedure(string aProcName = null, string[] aTVPTypeNames = null, params Type[] aTypes)
        {
            // Auto-registration is possible, but only for single-type invocations.
            Validation.AssertArgument(!String.IsNullOrWhiteSpace(aProcName) || (aTypes.Length == 1), "The 'aProcName' parameter cannot be null or empty when more than a single Type is being Registered!");

            if (String.IsNullOrWhiteSpace(aProcName))
            {
                // For auto-registration, determine the sproc name based on the pluralization of the type
                // and using the default schema.
                aProcName = String.Format("dbo.Save{0}", aTypes[0].Name.Pluralize());
            }

            var KeyName      = DetermineKeyName(aProcName, aTypes);
            var EmptyKeyName = DetermineKeyName(null, aTypes);

            // Ensure only a single registration per ordered TVP-type combination and sproc name.
            if (_Registrations.TryGetValue(KeyName, out TVPProcInfo found))
            {
                throw new AlreadyRegisteredException(String.Format("A stored procedure named '{0}' has already been registered for the Type set [{1}].", found.ProcedureName, KeyName));
            }

            // If an EmptyKeyName registration already exists, reuse the TVPProcInfo. This
            // will happen with multiple RegisterProcedure() calls for the same TVP-type set.
            if (_Registrations.TryGetValue(EmptyKeyName, out TVPProcInfo TPI))
            {
                // Register it under the additional sproc name and return it.
                _Registrations[KeyName] = TPI;
                return(TPI);
            }

            // Create and populate a TVPProcInfo instance.
            TPI = new TVPProcInfo()
            {
                ProcedureName = aProcName
            };

            // Iterate the Types and create a TVPProcInfo per instance.
            for (int i = 0; i < aTypes.Length; i++)
            {
                // See if we have a user-defined name or should use the convention, including any custom schema or prefix.
                var STN = ((aTVPTypeNames != null) && (aTVPTypeNames.Length > i) && (!String.IsNullOrWhiteSpace(aTVPTypeNames[i]))) ?
                          aTVPTypeNames[i] :
                          String.Format("{0}{1}{2}", (C_CustomTVPSchema ?? "dbo."), C_CustomTVPPrefix, aTypes[i].Name);

                var TD = new TVPDetails()
                {
                    SqlTypeName = STN, Properties = GetProperties(aTypes[i]).ToList()
                };

                // Add it to the sproc tracker.
                TPI.Details.Add(TD);
            }

            // Register with the sproc name as part of the key.
            _Registrations[KeyName] = TPI;

            // Attempt to register a reference for the TVP-type set with an empty sproc
            // name as the default for when executing without passing sproc name.
            // It is okay if this does not succeed when registering a second sproc
            // using the same types.
            _Registrations.TryAdd(EmptyKeyName, TPI);

            // Return the created ProcInfo instance.
            return(TPI);
        }