public static List<object> LookupIds(SqlConnection cnn, IEnumerable<object> items, string lookupSql)
{
var rval = new List<object>();
if (items.Count() == 0) return rval;
// safe due to the long cast (not that it matters, it runs read only)
string list = String.Join(" , ",
items.Where(i => i != null && i is int).Select(i => ((int) i).ToString()).ToArray());
if (list == "")
{
return items.ToList();
}
StringBuilder query = new StringBuilder()
.Append(lookupSql)
.Append("( ")
.Append(list)
.Append(" ) ");
var linkMap = new Dictionary<long, object>();
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query.ToString();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var info = new MagicResult
{
Id = reader.GetInt32(0),
Title = reader.IsDBNull(1) ? "unknown" : reader.GetString(1)
};
linkMap[info.Id] = info;
}
}
}
foreach (object item in items)
{
if (item == null || !(item is int))
{
rval.Add(item);
}
else
{
try
{
rval.Add(linkMap[(int) item]);
}
catch
{
// this is exceptional
rval.Add(item);
}
}
}
return rval;
}
public static List <object> LookupIds(SqlConnection cnn, IEnumerable <object> items, string lookupSql)
{
var rval = new List <object>();
if (items.Count() == 0)
{
return(rval);
}
// safe due to the long cast (not that it matters, it runs read only)
string list = String.Join(" , ",
items.Where(i => i != null && i is int).Select(i => ((int)i).ToString()).ToArray());
if (list == "")
{
return(items.ToList());
}
StringBuilder query = new StringBuilder()
.Append(lookupSql)
.Append("( ")
.Append(list)
.Append(" ) ");
var linkMap = new Dictionary <long, object>();
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query.ToString();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var info = new MagicResult
{
Id = reader.GetInt32(0),
Title = reader.IsDBNull(1) ? "unknown" : reader.GetString(1)
};
linkMap[info.Id] = info;
}
}
}
foreach (object item in items)
{
if (item == null || !(item is int))
{
rval.Add(item);
}
else
{
try
{
rval.Add(linkMap[(int)item]);
}
catch
{
// this is exceptional
rval.Add(item);
}
}
}
return(rval);
}
