private async Task <PersonalDetails> GetPersonalDetails(string employeeId) { var parameters = new { CrewId = employeeId }; string commandText = "SELECT top 100 Name,rank as Contact,CrewType as Address FROM HMS.VW_PERSONALDETAILS where personal_id= @CrewId"; //string sql = @"select top 1 Name,rank as Contact from HMS.VW_PERSONALDETAILS where personal_id=@PersonalId"; PersonalDetails result = await adapter.Get <PersonalDetails>(commandText, parameters); return(result); }
/// <summary> /// Returns a single entity by a single id from table "T". /// If T is an interface a proxy is generated. /// Id must be marked with [Key] attribute. /// Entity is tracked/intercepted for changes and used by the Update() extension. /// </summary> /// <typeparam name="T">Entity Type</typeparam> /// <param name="connection">Open SqlConnection</param> /// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param> /// <returns>Entity of T</returns> public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { // Argument Validation if (connection == null) { throw new ArgumentNullException("connection", "connection cannot be null"); } if (id == null) { throw new ArgumentNullException("id", "id cannot be null"); } InitializeCacheSweeper(); var type = typeof(T); StringCacheData cacheData; // Retrieve cached query if (!_cachedQueries.TryGetValue(type.TypeHandle, out cacheData)) { var keys = KeyPropertiesCache(type); if (keys.Count() > 1) { throw new DataException("Get<T> only supports an entity with a single [Key] property"); } if (keys.Count() == 0) { throw new DataException("Get<T> only supports an entity with a [Key] property"); } var onlyKey = keys.First(); var name = GetTableName(type); // TODO: query information schema and only select fields that are both in information schema and underlying class / interface var sql = "select * from " + name + " where " + onlyKey.Name + " = @id"; cacheData = new StringCacheData(sql); _cachedQueries.TryAdd(type.TypeHandle, cacheData); } // Build parameters var dynParms = new Dapper.DynamicParameters(); dynParms.Add("@id", id); T obj = null; // Processing //if (type.IsInterface) // Create proxy if interface //{ // // Execute // var res = connection.Query(cacheData.Data, dynParms).SingleOrDefault() as IDictionary<string, object>; // if (res == null) return (T)((object)null); // // Create proxy // obj = ProxyGenerator.GetInterfaceProxy<T>(); // // Map properties // foreach (var property in TypePropertiesCache(type)) // { // var val = res[property.Name]; // property.SetValue(obj, val, null); // } // // Reset change tracking // ((IProxy)obj).IsDirty = false; //} //else //{ //obj = connection.Query<T>(cacheData.Data, dynParms, transaction: transaction, commandTimeout: commandTimeout).SingleOrDefault(); //} ISqlAdapter adapter = GetFormatter(connection); obj = adapter.Get <T>(connection, transaction, commandTimeout, cacheData.Data, dynParms); return(obj); }