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