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