/// <summary> /// 将执行SQL后获得的信息映射为类型T的数据,如果数据不为空则返回数据,否则返回null,注意:字段名必须和数据库列名一致 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <returns></returns> public List <T> ExecuteReader <T>(String sql, Dictionary <string, object> dic = null, CommandType commandType = CommandType.Text) where T : class, new() { List <T> list = null; var fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance); List <NpgsqlParameter> parameters = new List <NpgsqlParameter>(); foreach (var item in dic) { object type = item.Value; type = type ?? DBNull.Value; NpgsqlParameter parm = new NpgsqlParameter(item.Key, type); parameters.Add(parm); } NpgsqlConnection connection = PostgreSQLConnection.GetNpgsqlConnection(); var commond = PostgreSQLConnection.GetNpgsqlCommand(connection, sql, parameters.ToArray(), commandType); var reader = commond.ExecuteReader(); if (reader.Read()) { //存储对应关系 Dictionary <FieldInfo, int> Maps = null; for (int i = 0; i < reader.FieldCount; i++) { foreach (var field in fields) { if (field.Name.Equals(reader.GetName(i))) { if (Maps == null) { Maps = new Dictionary <FieldInfo, int>(); } Maps.Add(field, i); } } } //判断是否有对应关系 if (Maps != null) { list = new List <T>(); do { T t = new T(); foreach (var map in Maps) { map.Key.SetValue(t, reader.GetValue(map.Value)); } list.Add(t); } while (reader.Read()); } } reader.CloseAsync(); commond.Dispose(); PostgreSQLConnection.ReturnConnection(connection); return(list); }
/// <summary> /// 返回执行SQL后,数据库返回的第一个值 /// </summary> /// <param name="sql">sql语句</param> /// <param name="parameters">sql所需参数</param> /// <param name="commandType">执行sql的类型</param> /// <returns></returns> public string ExecuteSingle(string sql, Dictionary <string, object> dic = null, CommandType commandType = CommandType.Text) { NpgsqlConnection connection = PostgreSQLConnection.GetNpgsqlConnection(); List <NpgsqlParameter> parameters = new List <NpgsqlParameter>(); foreach (var item in dic) { object type = item.Value; type = type ?? DBNull.Value; NpgsqlParameter parm = new NpgsqlParameter(item.Key, type); parameters.Add(parm); } var commond = PostgreSQLConnection.GetNpgsqlCommand(connection, sql, parameters.ToArray(), commandType); var value = commond.ExecuteScalar().ToString(); commond.Dispose(); PostgreSQLConnection.ReturnConnection(connection); return(value); }