public static SqlParameter ToSqlPrimaryIDParam(this object obj, SqlParameter[] additionalParams = null)
{
Type type = obj.GetType();
var sqlProp = type.GetProperties().FirstOrDefault(x => Attribute.IsDefined(x, typeof(PrimaryID)));
if (sqlProp != null)
{
}
else
{
throw new Exception("No Primary ID Field Defined");
}
var v = new QueryParamInfo();
v.Name = sqlProp.Name;
v.Value = sqlProp.GetValue(obj);
var sqlParam = new SqlParameter(v.Name, TypeConvertor.ToSqlDbType(sqlProp.PropertyType))
{
Value = v.Value
};
return(sqlParam);
}
public void ToObject(object obj, List <SqlParameter> sqlParameters = null)
{
if (sqlParameters.Where(s => s.Direction == ParameterDirection.Output || s.Direction == ParameterDirection.InputOutput).Any())
{
var props = (
from p in obj.GetType().GetProperties()
let nameAttr = p.GetCustomAttributes(typeof(QueryParamAttribute), true)
let ignoreAttr = p.GetCustomAttributes(typeof(QueryParamIgnoreAttribute), true)
select new { Property = p, Names = nameAttr, Ignores = ignoreAttr }).ToList();
List <KeyValuePair <string, string> > flds = new List <KeyValuePair <string, string> >();
props.ForEach(p =>
{
if (p.Ignores != null && p.Ignores.Length > 0)
{
return;
}
QueryParamAttribute name = p.Names.FirstOrDefault() as QueryParamAttribute;
if (name == null)
{
return;
}
QueryParamInfo pinfo = new QueryParamInfo();
if (name != null && !string.IsNullOrWhiteSpace(name._name))
{
pinfo.Name = name._name.Replace("@", "");
}
else
{
pinfo.Name = p.Property.Name.Replace("@", "");
}
flds.Add(new KeyValuePair <string, string>(p.Property.Name.Replace("@", ""), pinfo.Name));
});
foreach (SqlParameter item in sqlParameters.Where(s => s.Direction == ParameterDirection.Output || s.Direction == ParameterDirection.InputOutput))
{
PropertyInfo prop = obj.GetType().GetProperty(flds.Where(f => f.Value == item.ParameterName).FirstOrDefault().Key, BindingFlags.Public | BindingFlags.Instance);
if (prop != null && prop.CanWrite)
{
if (item.Value == DBNull.Value)
{
prop.SetValue(obj, null);
}
else
{
prop.SetValue(obj, item.Value);
}
}
}
}
}
QueryParamInfo GetParamInfo(string name, string sqlTypeAndLength)
{
var qp = new QueryParamInfo();
var m = Regex.Match(sqlTypeAndLength, @"(?'type'^\w*)\(?(?'firstNum'\d*),?(?'secondNum'\d*)");
var typeOnly = m.Groups["type"].Value;
int.TryParse(m.Groups["firstNum"].Value, out int firstNum);
int.TryParse(m.Groups["secondNum"].Value, out int secondNum);
if (secondNum != 0)
{
qp.Precision = firstNum;
qp.Scale = secondNum;
}
else if (typeOnly.ToLower() == "datetime2")
{
qp.Precision = firstNum;
}
else if (firstNum > 0)
{
qp.Length = firstNum;
}
//string normalizedType;
// we have no info for the nullability of query params, so we'll make them all nullable
//var csType = System2Alias.Map( TypeMapDB2CS(typeOnly, out normalizedType), true );
try
{
// hack
if (typeOnly == "sql_variant")
{
typeOnly = "Variant";
}
var sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), typeOnly, true);
var csType = TypeConvertor.ToNetType(sqlDbType);
var dbType = TypeConvertor.ToDbType(sqlDbType);
qp.CSType = System2Alias.Map(csType.FullName, true); // will look up aliases of system types, and append the question mark.
qp.FullyQualifiedCSType = csType.FullName;
qp.DbType = dbType.ToString();
qp.CSNameCamel = char.ToLower(name.First()) + name.Substring(1);
qp.CSNamePascal = char.ToUpper(name.First()) + name.Substring(1);
qp.CSNamePrivate = "_" + qp.CSNameCamel;
qp.DbName = '@' + name;
return(qp);
}
catch (Exception ex)
{
throw new TypeNotMatchedException(ex.Message);
}
}
public static List <SqlParameter> ToSqlParamsList(this object obj, SqlParameter[] additionalParams = null)
{
var props = (
from p in obj.GetType().GetProperties(System.Reflection.BindingFlags.Public
| System.Reflection.BindingFlags.Instance
| System.Reflection.BindingFlags.DeclaredOnly)
let nameAttr = p.GetCustomAttributes(typeof(QueryParamNameAttribute), true)
let ignoreAttr = p.GetCustomAttributes(typeof(QueryParamIgnoreAttribute), true)
select new { Property = p, Names = nameAttr, Ignores = ignoreAttr }).ToList();
var result = new List <SqlParameter>();
foreach (var p in props)
{
if (p.Ignores != null && p.Ignores.Length > 0)
{
continue;
}
var name = p.Names.FirstOrDefault() as QueryParamNameAttribute;
var pinfo = new QueryParamInfo();
if (name != null && !String.IsNullOrWhiteSpace(name.Name))
{
pinfo.Name = name.Name.Replace("@", "");
}
else
{
pinfo.Name = p.Property.Name.Replace("@", "");
}
pinfo.Value = p.Property.GetValue(obj) ?? DBNull.Value;
var sqlParam = new SqlParameter(pinfo.Name, TypeConvertor.ToSqlDbType(p.Property.PropertyType))
{
Value = pinfo.Value
};
result.Add(sqlParam);
}
if (additionalParams != null && additionalParams.Length > 0)
{
result.AddRange(additionalParams);
}
return(result);
}
/// <summary>
/// First attempt matches system types using local type map. Here we're going to deal with user defined table types as input parameters.
/// </summary>
/// <param name="queryText"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
private QueryParamInfo GetParamInfoSecondAttempt(string paramName, string connectionString)
{
// ToDo. I'm mixing Smo calls with string manip. This will not do.
// Table Valued Parameters...
#if VS16
SqlConnection conn = new SqlConnection(connectionString);
#else
Microsoft.Data.SqlClient.SqlConnection conn = new Microsoft.Data.SqlClient.SqlConnection(connectionString);
#endif
Server s = new Server(new ServerConnection(conn));
var myDb = s.Databases.Cast <Database>().Where(db => db.ActiveConnections > 0).FirstOrDefault();
var type = myDb.UserDefinedTableTypes.Cast <UserDefinedTableType>().Where(t => t.Name == paramName);
if (type.Count() > 0)
{
var returnVal = new QueryParamInfo
{
CSNameCamel = paramName.First().ToString().ToLower() + paramName.Substring(1),
DbName = '@' + paramName,
DbType = type.First().Urn.Type,
CSType = $"IEnumerable<{paramName.First().ToString().ToUpper() + paramName.Substring(1)}>",
InnerCSType = paramName.First().ToString().ToUpper() + paramName.Substring(1),
IsTableType = true,
ParamSchema = new List <QueryParamInfo>()
};
foreach (Column col in type.First().Columns)
{
string normalizedType;
var csType = TypeMapDB2CS(col.DataType.Name, out normalizedType);
returnVal.ParamSchema.Add(new QueryParamInfo
{
CSNameCamel = col.Name,
DbType = normalizedType,
CSType = csType,
DbName = '@' + col.Name
});
}
return(returnVal);
}
else
{
throw new TypeNotMatchedException("No user defined type to match " + paramName);
}
}
public List <SqlParameter> ToSqlParamsList(object obj, SqlParameter[] additionalParams = null)
{
var props = (
from p in obj.GetType().GetProperties()
let nameAttr = p.GetCustomAttributes(typeof(QueryParamAttribute), true)
let ignoreAttr = p.GetCustomAttributes(typeof(QueryParamIgnoreAttribute), true)
select new { Property = p, Names = nameAttr, Ignores = ignoreAttr }).ToList();
List <SqlParameter> result = new List <SqlParameter>();
props.ForEach(p =>
{
if (p.Ignores != null && p.Ignores.Length > 0)
{
return;
}
QueryParamAttribute name = p.Names.FirstOrDefault() as QueryParamAttribute;
if (name == null)
{
return;
}
QueryParamInfo pinfo = new QueryParamInfo();
if (name != null && !string.IsNullOrWhiteSpace(name._name))
{
pinfo.Name = name._name.Replace("@", "");
}
else
{
pinfo.Name = p.Property.Name.Replace("@", "");
}
pinfo.Value = p.Property.GetValue(obj) ?? DBNull.Value;
if (name._isStructured)
{
object data = p.Property.GetValue(obj) ?? DBNull.Value;
List <dynamic> dlist = new List <dynamic>();
string json = JsonConvert.SerializeObject(data);
pinfo.Value = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));
SqlParameter sqlParam = new SqlParameter(pinfo.Name, SqlDbType.Structured)
{
TypeName = name._dataType,
Value = pinfo.Value,
Direction = (ParameterDirection)name._direction
};
if ((name._direction == Direction.InputOutput || name._direction == Direction.Output) || pinfo.Value != DBNull.Value)
{
result.Add(sqlParam);
}
}
else
{
SqlParameter sqlParam = new SqlParameter(pinfo.Name, (name._dbType != null ? (SqlDbType)name._dbType : TypeConvertor.ToSqlDbType(p.Property.PropertyType)))
{
Value = pinfo.Value,
Direction = (ParameterDirection)name._direction
};
if (name._size != 0)
{
sqlParam.Size = name._size;
}
if (name._precision > 0)
{
sqlParam.Precision = name._precision;
}
if (name._scale > 0)
{
sqlParam.Scale = name._scale;
}
if ((name._direction == Direction.InputOutput || name._direction == Direction.Output) || pinfo.Value != DBNull.Value)
{
result.Add(sqlParam);
}
}
});
if (additionalParams != null && additionalParams.Length > 0)
{
result.AddRange(additionalParams);
}
return(result);
}
