예제 #1
0
        /// <summary>
        /// Do the work of converting a source data object to SqlDataRecords
        /// using the parameter attributes to create the table valued parameter definition
        /// </summary>
        /// <returns></returns>
        internal static IEnumerable <SqlDataRecord> TableValuedParameter(IList table)
        {
            // get the object type underlying our table
            Type t = StoredProcedureHelpers.GetUnderlyingType(table.GetType());

            // list of converted values to be returned to the caller
            List <SqlDataRecord> recordlist = new List <SqlDataRecord>();

            // get all mapped properties
            PropertyInfo[] props = StoredProcedureHelpers.GetMappedProperties(t);

            // get the column definitions, into an array
            List <SqlMetaData> columnlist = new List <SqlMetaData>();

            // get the propery column name to property name mapping
            // and generate the SqlMetaData for each property/column
            Dictionary <String, String> mapping = new Dictionary <string, string>();

            foreach (PropertyInfo p in props)
            {
                // default name is property name, override of parameter name by attribute
                var    attr = p.GetAttribute <StoredProcedureAttributes.Name>();
                String name = (null == attr) ? p.Name : attr.Value;
                mapping.Add(name, p.Name);

                // get column type
                var       ct      = p.GetAttribute <StoredProcedureAttributes.ParameterType>();
                SqlDbType coltype = (null == ct) ? SqlDbType.Int : ct.Value;

                // create metadata column definition
                SqlMetaData column;
                switch (coltype)
                {
                case SqlDbType.Binary:
                case SqlDbType.Char:
                case SqlDbType.NChar:
                case SqlDbType.Image:
                case SqlDbType.VarChar:
                case SqlDbType.NVarChar:
                case SqlDbType.Text:
                case SqlDbType.NText:
                case SqlDbType.VarBinary:
                    // get column size
                    var sa   = p.GetAttribute <StoredProcedureAttributes.Size>();
                    int size = (null == sa) ? 50 : sa.Value;
                    column = new SqlMetaData(name, coltype, size);
                    break;

                case SqlDbType.Decimal:
                    // get column precision and scale
                    var  pa        = p.GetAttribute <StoredProcedureAttributes.Precision>();
                    Byte precision = (null == pa) ? (byte)10 : pa.Value;
                    var  sca       = p.GetAttribute <StoredProcedureAttributes.Scale>();
                    Byte scale     = (null == sca) ? (byte)2 : sca.Value;
                    column = new SqlMetaData(name, coltype, precision, scale);
                    break;

                default:
                    column = new SqlMetaData(name, coltype);
                    break;
                }

                // Add metadata to column list
                columnlist.Add(column);
            }

            // load each object in the input data table into sql data records
            foreach (object s in table)
            {
                // create the sql data record using the column definition
                SqlDataRecord record = new SqlDataRecord(columnlist.ToArray());
                for (int i = 0; i < columnlist.Count(); i++)
                {
                    // locate the value of the matching property
                    var value = props.Where(p => p.Name == mapping[columnlist[i].Name])
                                .First()
                                .GetValue(s, null);

                    // set the value
                    record.SetValue(i, value);
                }

                // add the sql data record to our output list
                recordlist.Add(record);
            }

            // return our list of data records
            return(recordlist);
        }
예제 #2
0
        /// <summary>
        ///     Convert parameters from type T properties to SqlParameters
        /// </summary>
        /// <param name="data">Source data object</param>
        /// <returns></returns>
        internal IEnumerable <SqlParameter> Parameters(T data)
        {
            // clear the parameter to property mapping since we'll be recreating this
            MappedParams.Clear();

            // list of parameters we'll be returning
            List <SqlParameter> parms = new List <SqlParameter>();

            // properties that we're converting to parameters are everything without
            // a NotMapped attribute
            foreach (PropertyInfo p in typeof(T).GetMappedProperties())
            {
                //---------------------------------------------------------------------------------
                // process attributes
                //---------------------------------------------------------------------------------

                // create parameter and store default name - property name
                SqlParameter holder = new SqlParameter
                {
                    ParameterName = p.Name
                };

                // override of parameter name by attribute
                var name = p.GetAttribute <StoredProcedureAttributes.Name>();
                if (null != name)
                {
                    holder.ParameterName = name.Value;
                }

                // save direction (default is input)
                var dir = p.GetAttribute <StoredProcedureAttributes.Direction>();
                if (null != dir)
                {
                    holder.Direction = dir.Value;
                }

                // save size
                var size = p.GetAttribute <StoredProcedureAttributes.Size>();
                if (null != size)
                {
                    holder.Size = size.Value;
                }

                // save database type of parameter
                var parmtype = p.GetAttribute <StoredProcedureAttributes.ParameterType>();
                if (null != parmtype)
                {
                    holder.SqlDbType = parmtype.Value;
                }

                // save user-defined type name
                var typename = p.GetAttribute <StoredProcedureAttributes.TypeName>();
                if (null != typename)
                {
                    holder.TypeName = typename.Value;
                }

                // save precision
                var precision = p.GetAttribute <StoredProcedureAttributes.Precision>();
                if (null != precision)
                {
                    holder.Precision = precision.Value;
                }

                // save scale
                var scale = p.GetAttribute <StoredProcedureAttributes.Scale>();
                if (null != scale)
                {
                    holder.Scale = scale.Value;
                }

                //---------------------------------------------------------------------------------
                // Save parameter value
                //---------------------------------------------------------------------------------

                // store table values, scalar value or null
                var value = p.GetValue(data, null);
                if (value == null)
                {
                    // set database null marker for null value
                    holder.Value = DBNull.Value;
                }
                else if (SqlDbType.Structured == holder.SqlDbType)
                {
                    // catcher - tvp must be ienumerable type
                    if (!(value is IEnumerable))
                    {
                        throw new InvalidCastException(String.Format("{0} must be an IEnumerable Type", p.Name));
                    }

                    // ge the type underlying the IEnumerable
                    Type basetype = StoredProcedureHelpers.GetUnderlyingType(value.GetType());

                    // get the table valued parameter table type name
                    var schema = p.GetAttribute <StoredProcedureAttributes.Schema>();
                    if (null == schema && null != basetype)
                    {
                        schema = basetype.GetAttribute <StoredProcedureAttributes.Schema>();
                    }

                    var tvpname = p.GetAttribute <StoredProcedureAttributes.TableName>();
                    if (null == tvpname && null != basetype)
                    {
                        tvpname = basetype.GetAttribute <StoredProcedureAttributes.TableName>();
                    }

                    holder.TypeName  = (null != schema) ? schema.Value : "dbo";
                    holder.TypeName += ".";
                    holder.TypeName += (null != tvpname) ? tvpname.Value : p.Name;

                    // generate table valued parameter
                    holder.Value = StoredProcedureHelpers.TableValuedParameter((IList)value);
                }
                else
                {
                    // process normal scalar value
                    holder.Value = value;
                }

                // save the mapping between the parameter name and property name, since the parameter
                // name can be overridden
                MappedParams.Add(holder.ParameterName, p.Name);

                // add parameter to list
                parms.Add(holder);
            }

            return(parms);
        }