/// <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);
}
/// <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);
}
