使用Npgsql连接pg数据库
c#通用helper
using System;
using System.Data;
using System.Xml;
using Npgsql;
using System.Collections.Generic;
using System.Reflection;
/// <summary>
/// Npgsql 2.2.6 数据库帮助类 PostgreSQL 专用
/// </summary>
public sealed class NpgsqlDbHelper
{
#region 【连接字符串】 数据库连接
/// <summary>
/// PostgreSQL 默认连接字符串
/// </summary>
public static string ConnectionString = @"Host=localhost;Port=5432;Database=testdb;Username=postgres;Password=123456;";
public static string GetConnSting()
{
if (!string.IsNullOrEmpty(ConnectionString)) return ConnectionString;
return ConnectionString;
}
public static NpgsqlConnection GetConnection()
{
NpgsqlConnection Connection = new NpgsqlConnection(GetConnSting());
return Connection;
}
#endregion
#region 私有构造函数和方法
private NpgsqlDbHelper() { }
/// <summary>
/// 将NpgsqlParameter参数数组(参数值)分配给NpgsqlCommand命令.
/// 这个方法将给任何一个参数分配DBNull.Value;
/// 该操作将阻止默认值的使用.
/// </summary>
/// <param name="command">命令名</param>
/// <param name="commandParameters">NpgsqlParameters数组</param>
private static void AttachParameters(NpgsqlCommand command, NpgsqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (NpgsqlParameter p in commandParameters)
{
if (p != null)
{
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
/// <summary>
/// 将DataRow类型的列值分配到NpgsqlParameter参数数组.
/// </summary>
private static void AssignParameterValues(NpgsqlParameter[] commandParameters, DataRow dataRow)
{
if ((commandParameters == null) || (dataRow == null))
{
return;
}
int i = 0;
foreach (NpgsqlParameter commandParameter in commandParameters)
{
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= 1)
throw new Exception(
string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName));
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
/// <summary>
/// 将一个对象数组分配给NpgsqlParameter参数数组.
/// </summary>
private static void AssignParameterValues(NpgsqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
}
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("参数值个数与参数不匹配.");
}
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
if (parameterValues[i] is IDbDataParameter)
{
IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
if (paramInstance.Value == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = paramInstance.Value;
}
}
else if (parameterValues[i] == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = parameterValues[i];
}
}
}
/// <summary>
/// 预处理命令:数据库连接/事务/命令类型/参数
/// </summary>
private static void PrepareCommand(NpgsqlCommand command, NpgsqlConnection connection, NpgsqlTransaction transaction, CommandType commandType, string commandText, NpgsqlParameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
#endregion
#region 【01】ExecuteNonQuery命令
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
return ExecuteNonQuery(connectionString, commandType, commandText, (NpgsqlParameter[])null);
}
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
public static int ExecuteNonQuery(NpgsqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteNonQuery(connection, commandType, commandText, (NpgsqlParameter[])null);
}
public static int ExecuteNonQuery(NpgsqlConnection connection, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
NpgsqlCommand cmd = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (NpgsqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
public static int ExecuteNonQuery(NpgsqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteNonQuery(transaction, commandType, commandText, (NpgsqlParameter[])null);
}
public static int ExecuteNonQuery(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
NpgsqlCommand cmd = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}
#endregion
#region 【02】ExecuteDataset方法
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
return ExecuteDataset(connectionString, commandType, commandText, (NpgsqlParameter[])null);
}
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}
public static DataSet ExecuteDataset(NpgsqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteDataset(connection, commandType, commandText, (NpgsqlParameter[])null);
}
public static DataSet ExecuteDataset(NpgsqlConnection connection, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
NpgsqlCommand cmd = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (NpgsqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return ds;
}
}
public static DataSet ExecuteDataset(NpgsqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteDataset(transaction, commandType, commandText, (NpgsqlParameter[])null);
}
public static DataSet ExecuteDataset(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
NpgsqlCommand cmd = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
#endregion
#region 【03】ExecuteReader 数据阅读器
private static NpgsqlDataReader ExecuteReader(NpgsqlConnection connection, NpgsqlTransaction transaction, CommandType commandType, string commandText, NpgsqlParameter[] commandParameters, bool connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
NpgsqlCommand cmd = new NpgsqlCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
NpgsqlDataReader dataReader;
if (connectionOwnership)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
bool canClear = true;
foreach (NpgsqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
public static NpgsqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
{
return ExecuteReader(connectionString, commandType, commandText, (NpgsqlParameter[])null);
}
public static NpgsqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
NpgsqlConnection connection = null;
try
{
connection = new NpgsqlConnection(connectionString);
connection.Open();
return ExecuteReader(connection, null, commandType, commandText, commandParameters, false);
}
catch
{
if (connection != null) connection.Close();
throw;
}
}
public static NpgsqlDataReader ExecuteReader(NpgsqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteReader(connection, commandType, commandText, (NpgsqlParameter[])null);
}
public static NpgsqlDataReader ExecuteReader(NpgsqlConnection connection, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
return ExecuteReader(connection, (NpgsqlTransaction)null, commandType, commandText, commandParameters, true);
}
public static List<T> ExecuteReader<T>(string connectionString, CommandType commandType, string commandText, Func<IDataReader, T> action,
params NpgsqlParameter[] commandParameters)
{
List<T> result = new List<T>();
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
NpgsqlConnection connection = null;
NpgsqlDataReader reader = null;
try
{
connection = new NpgsqlConnection(connectionString);
connection.Open();
reader = ExecuteReader(connection, null, commandType, commandText, commandParameters, false);
while (reader.Read())
{
var item = action(reader);
result.Add(item);
}
return result;
}
catch
{
if (connection != null) connection.Close();
throw;
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
public static NpgsqlDataReader ExecuteReader(NpgsqlConnection connection, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
return ExecuteReader(connection, CommandType.Text, commandText, commandParameters);
}
public static NpgsqlDataReader ExecuteReader(NpgsqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteReader(transaction, commandType, commandText, (NpgsqlParameter[])null);
}
public static NpgsqlDataReader ExecuteReader(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, true);
}
public static NpgsqlDataReader ExecuteReader(NpgsqlTransaction transaction, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
return ExecuteReader(transaction, CommandType.Text, commandText, commandParameters);
}
#endregion
#region 【03】ExecuteList 泛型自动映射（缓存反射，高性能）
/// <summary>
/// 泛型实体缓存：每个T只反射一次属性，永久复用
/// </summary>
private static class EntityPropertyCache<T> where T : new()
{
public static readonly PropertyInfo[] Properties =
typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
}
/// <summary>
/// 执行查询，返回泛型集合（自动映射，高性能缓存）
/// </summary>
public static List<T> ExecuteList<T>(string connectionString, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
where T : new()
{
if (string.IsNullOrEmpty(connectionString)) throw new ArgumentNullException(nameof(connectionString));
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return ExecuteList<T>(connection, commandType, commandText, commandParameters);
}
}
/// <summary>
/// 执行查询，返回泛型集合（连接重载）
/// </summary>
public static List<T> ExecuteList<T>(NpgsqlConnection connection, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
where T : new()
{
if (connection == null) throw new ArgumentNullException(nameof(connection));
List<T> list = new List<T>();
NpgsqlDataReader reader = null;
try
{
reader = ExecuteReader(connection, commandType, commandText, commandParameters);
// 🔥 从缓存读取属性，无重复反射
var properties = EntityPropertyCache<T>.Properties;
while (reader.Read())
{
T model = new T();
foreach (var prop in properties)
{
try
{
// 快速校验字段是否存在
int ordinal = reader.GetOrdinal(prop.Name);
if (reader.IsDBNull(ordinal)) continue;
// 直接取值赋值（无反射损耗）
object value = reader.GetValue(ordinal);
Type targetType = prop.PropertyType;
Type underType = Nullable.GetUnderlyingType(targetType) ?? targetType;
prop.SetValue(model, Convert.ChangeType(value, underType), null);
}
catch
{
// 字段不匹配直接跳过，不抛错
continue;
}
}
list.Add(model);
}
return list;
}
finally
{
if (reader != null) reader.Dispose();
}
}
/// <summary>
/// 执行查询，返回泛型集合（事务重载）
/// </summary>
public static List<T> ExecuteList<T>(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
where T : new()
{
if (transaction == null) throw new ArgumentNullException(nameof(transaction));
return ExecuteList<T>(transaction.Connection, commandType, commandText, commandParameters);
}
#endregion
#region【04】ExecuteScalar 返回结果集中的第一行第一列
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
{
return ExecuteScalar(connectionString, commandType, commandText, (NpgsqlParameter[])null);
}
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return ExecuteScalar(connection, commandType, commandText, commandParameters);
}
}
public static object ExecuteScalar(NpgsqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteScalar(connection, commandType, commandText, (NpgsqlParameter[])null);
}
public static object ExecuteScalar(NpgsqlConnection connection, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
NpgsqlCommand cmd = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (NpgsqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
public static object ExecuteScalar(NpgsqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteScalar(transaction, commandType, commandText, (NpgsqlParameter[])null);
}
public static object ExecuteScalar(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
NpgsqlCommand cmd = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return retval;
}
#endregion
#region 【05】ExecuteXmlReader XML阅读器
public static XmlReader ExecuteXmlReader(NpgsqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteXmlReader(connection, commandType, commandText, (NpgsqlParameter[])null);
}
public static XmlReader ExecuteXmlReader(NpgsqlConnection connection, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
NpgsqlCommand cmd = new NpgsqlCommand();
try
{
PrepareCommand(cmd, connection, (NpgsqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
DataTable dataTable = new DataTable();
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd))
{
adapter.Fill(dataTable);
}
string xml = DataTableToXml(dataTable);
XmlReader retval = XmlReader.Create(new System.IO.StringReader(xml));
cmd.Parameters.Clear();
return retval;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
private static string DataTableToXml(DataTable dataTable)
{
using (System.IO.StringWriter sw = new System.IO.StringWriter())
{
dataTable.WriteXml(sw);
return sw.ToString();
}
}
public static XmlReader ExecuteXmlReader(NpgsqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteXmlReader(transaction, commandType, commandText, (NpgsqlParameter[])null);
}
public static XmlReader ExecuteXmlReader(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
NpgsqlCommand cmd = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
DataTable dataTable = new DataTable();
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd))
{
adapter.Fill(dataTable);
}
string xml = DataTableToXml(dataTable);
XmlReader retval = XmlReader.Create(new System.IO.StringReader(xml));
cmd.Parameters.Clear();
return retval;
}
#endregion
#region 【拓展】ExecuteDataTable
public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText)
{
return ExecuteDataTable(connectionString, commandType, commandText, (NpgsqlParameter[])null);
}
public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return ExecuteDataTable(connection, commandType, commandText, commandParameters);
}
}
public static DataTable ExecuteDataTable(NpgsqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteDataTable(connection, commandType, commandText, (NpgsqlParameter[])null);
}
public static DataTable ExecuteDataTable(NpgsqlConnection connection, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
DataTable dt = new DataTable();
if (connection == null) throw new ArgumentNullException("connection");
NpgsqlCommand cmd = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (NpgsqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
using (NpgsqlDataAdapter sda = new NpgsqlDataAdapter(cmd))
{
sda.Fill(dt);
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return dt;
}
}
public static DataTable ExecuteDataTable(NpgsqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteDataTable(transaction, commandType, commandText, (NpgsqlParameter[])null);
}
public static DataTable ExecuteDataTable(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
NpgsqlCommand cmd = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
using (NpgsqlDataAdapter sda = new NpgsqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
}
#endregion
#region 【07】FillDataset 填充数据集
public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
FillDataset(connection, commandType, commandText, dataSet, tableNames);
}
}
public static void FillDataset(string connectionString, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
}
}
public static void FillDataset(NpgsqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames)
{
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
}
public static void FillDataset(NpgsqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params NpgsqlParameter[] commandParameters)
{
FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
}
public static void FillDataset(NpgsqlTransaction transaction, CommandType commandType,
string commandText,
DataSet dataSet, string[] tableNames)
{
FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
}
public static void FillDataset(NpgsqlTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params NpgsqlParameter[] commandParameters)
{
FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
}
private static void FillDataset(NpgsqlConnection connection, NpgsqlTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params NpgsqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (dataSet == null) throw new ArgumentNullException("dataSet");
NpgsqlCommand command = new NpgsqlCommand();
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
using (NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(command))
{
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index = 0; index < tableNames.Length; index++)
{
if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
dataAdapter.Fill(dataSet);
command.Parameters.Clear();
}
if (mustCloseConnection)
connection.Close();
}
#endregion
#region 【08】UpdateDataset 更新数据集
public static void UpdateDataset(NpgsqlCommand insertCommand, NpgsqlCommand deleteCommand, NpgsqlCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
using (NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter())
{
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
dataAdapter.Update(dataSet, tableName);
dataSet.AcceptChanges();
}
}
#endregion
#region 【09】CreateCommand 创建命令
public static NpgsqlCommand CreateCommand(NpgsqlConnection connection, string sqlText, params string[] sourceColumns)
{
if (connection == null) throw new ArgumentNullException("connection");
if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");
NpgsqlCommand cmd = new NpgsqlCommand(sqlText, connection);
cmd.CommandType = CommandType.Text;
if ((sourceColumns != null) && (sourceColumns.Length > 0))
{
for (int index = 0; index < sourceColumns.Length; index++)
{
NpgsqlParameter parameter = new NpgsqlParameter(":" + sourceColumns[index], DBNull.Value);
parameter.SourceColumn = sourceColumns[index];
cmd.Parameters.Add(parameter);
}
}
return cmd;
}
#endregion
#region [09]ExecuteNonQueryTypedParams 类型化参数(DataRow)
public static int ExecuteNonQueryTypedParams(String connectionString, String sqlText, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return ExecuteNonQueryTypedParams(connection, sqlText, dataRow);
}
}
public static int ExecuteNonQueryTypedParams(NpgsqlConnection connection, String sqlText, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");
NpgsqlCommand command = new NpgsqlCommand(sqlText, connection);
command.CommandType = CommandType.Text;
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
foreach (DataColumn column in dataRow.Table.Columns)
{
NpgsqlParameter parameter = new NpgsqlParameter(":" + column.ColumnName, dataRow[column]);
command.Parameters.Add(parameter);
}
}
return command.ExecuteNonQuery();
}
public static int ExecuteNonQueryTypedParams(NpgsqlTransaction transaction, String sqlText, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");
NpgsqlCommand command = new NpgsqlCommand(sqlText, transaction.Connection, transaction);
command.CommandType = CommandType.Text;
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
foreach (DataColumn column in dataRow.Table.Columns)
{
NpgsqlParameter parameter = new NpgsqlParameter(":" + column.ColumnName, dataRow[column]);
command.Parameters.Add(parameter);
}
}
return command.ExecuteNonQuery();
}
#endregion
#region [10]ExecuteDatasetTypedParams 类型化参数(DataRow)
public static DataSet ExecuteDatasetTypedParams(string connectionString, string sqlText, DataRow dataRow)
{
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException("connectionString");
}
if (string.IsNullOrEmpty(sqlText))
{
throw new ArgumentNullException("sqlText");
}
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return ExecuteDatasetTypedParams(connection, sqlText, dataRow);
}
}
public static DataSet ExecuteDatasetTypedParams(NpgsqlConnection connection, string sqlText, DataRow dataRow)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (string.IsNullOrEmpty(sqlText))
{
throw new ArgumentNullException("sqlText");
}
using (NpgsqlCommand command = new NpgsqlCommand(sqlText, connection))
{
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
foreach (DataColumn column in dataRow.Table.Columns)
{
command.Parameters.AddWithValue(":" + column.ColumnName, dataRow[column]);
}
}
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
return dataSet;
}
}
}
public static DataSet ExecuteDatasetTypedParams(NpgsqlTransaction transaction, string sqlText, DataRow dataRow)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
if (string.IsNullOrEmpty(sqlText))
{
throw new ArgumentNullException("sqlText");
}
using (NpgsqlCommand command = new NpgsqlCommand(sqlText, transaction.Connection, transaction))
{
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
foreach (DataColumn column in dataRow.Table.Columns)
{
command.Parameters.AddWithValue(":" + column.ColumnName, dataRow[column]);
}
}
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
return dataSet;
}
}
}
#endregion
#region [11]ExecuteReaderTypedParams 类型化参数(DataRow)
public static NpgsqlDataReader ExecuteReaderTypedParams(string connectionString, string sqlText, DataRow dataRow)
{
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException("connectionString");
}
if (string.IsNullOrEmpty(sqlText))
{
throw new ArgumentNullException("sqlText");
}
NpgsqlConnection connection = new NpgsqlConnection(connectionString);
try
{
connection.Open();
return ExecuteReaderTypedParams(connection, sqlText, dataRow);
}
catch
{
connection.Dispose();
throw;
}
}
public static NpgsqlDataReader ExecuteReaderTypedParams(NpgsqlConnection connection, string sqlText, DataRow dataRow)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (string.IsNullOrEmpty(sqlText))
{
throw new ArgumentNullException("sqlText");
}
NpgsqlCommand command = new NpgsqlCommand(sqlText, connection);
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
foreach (DataColumn column in dataRow.Table.Columns)
{
command.Parameters.AddWithValue(":" + column.ColumnName, dataRow[column]);
}
}
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
public static NpgsqlDataReader ExecuteReaderTypedParams(NpgsqlTransaction transaction, string sqlText, DataRow dataRow)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
if (string.IsNullOrEmpty(sqlText))
{
throw new ArgumentNullException("sqlText");
}
NpgsqlCommand command = new NpgsqlCommand(sqlText, transaction.Connection, transaction);
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
foreach (DataColumn column in dataRow.Table.Columns)
{
command.Parameters.AddWithValue(":" + column.ColumnName, dataRow[column]);
}
}
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion
#region [12]ExecuteScalarTypedParams 类型化参数(DataRow)
public static object ExecuteScalarTypedParams(string connectionString, string sqlText, DataRow dataRow)
{
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException("connectionString");
}
if (string.IsNullOrEmpty(sqlText))
{
throw new ArgumentNullException("sqlText");
}
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return ExecuteScalarTypedParams(connection, sqlText, dataRow);
}
}
public static object ExecuteScalarTypedParams(NpgsqlConnection connection, string sqlText, DataRow dataRow)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (string.IsNullOrEmpty(sqlText))
{
throw new ArgumentNullException("sqlText");
}
using (NpgsqlCommand command = new NpgsqlCommand(sqlText, connection))
{
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
foreach (DataColumn column in dataRow.Table.Columns)
{
command.Parameters.AddWithValue(":" + column.ColumnName, dataRow[column]);
}
}
return command.ExecuteScalar();
}
}
public static object ExecuteScalarTypedParams(NpgsqlTransaction transaction, string sqlText, DataRow dataRow)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
if (string.IsNullOrEmpty(sqlText))
{
throw new ArgumentNullException("sqlText");
}
using (NpgsqlCommand command = new NpgsqlCommand(sqlText, transaction.Connection, transaction))
{
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
foreach (DataColumn column in dataRow.Table.Columns)
{
command.Parameters.AddWithValue(":" + column.ColumnName, dataRow[column]);
}
}
return command.ExecuteScalar();
}
}
#endregion
}
public static class DataReaderExtensions
{
/// <summary>
/// 泛型取值：严格校验 字段存在 + 类型匹配
/// </summary>
/// <typeparam name="T">目标类型</typeparam>
/// <param name="reader">IDataReader</param>
/// <param name="fieldName">字段名</param>
/// <param name="fieldNotExistThrow">字段不存在是否抛错</param>
/// <param name="typeMismatchThrow">类型不匹配是否抛错</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
public static T Field<T>(
this IDataRecord reader,
string fieldName,
bool fieldNotExistThrow = false,
bool typeMismatchThrow = false,
T defaultValue = default(T))
{
// 1. 校验字段是否存在
int ordinal;
try
{
ordinal = reader.GetOrdinal(fieldName);
}
catch
{
if (fieldNotExistThrow)
throw new ArgumentException($"字段不存在：{fieldName}");
return defaultValue;
}
// 2. 空值直接返回默认值
if (reader.IsDBNull(ordinal))
return defaultValue;
// 3. 获取【数据库字段的真实类型】（你要的 GetFieldType）
Type dbFieldType = reader.GetFieldType(ordinal);
// 目标类型（处理可空类型：bool? → bool，DateTime? → DateTime）
Type targetType = Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T);
// 4. 核心：校验 数据库类型 和 目标类型 是否一致！！！
if (!dbFieldType.Equals(targetType))
{
if (typeMismatchThrow)
throw new InvalidCastException($"字段【{fieldName}】类型不匹配：数据库类型={dbFieldType.Name}，目标类型={targetType.Name}");
return defaultValue;
}
// 5. 类型一致，安全取值
return (T)reader.GetValue(ordinal);
}
}
使用，类似于ado.net
/// <summary>
/// 查询分库绑定规则
/// </summary>
private static List<SysDatabaseBind> QueryDatabaseBindRules(string connStr, string idleBusId)
{
string sql = @"
SELECT *
FROM public.""SysDatabaseBind"" WHERE ""IdleBusId"" = @IdleBusId";
NpgsqlParameter[] parameters = {
new NpgsqlParameter("@IdleBusId", NpgsqlDbType.Varchar) { Value = idleBusId }
};
return NpgsqlDbHelper.ExecuteList<SysDatabaseBind>(connStr, CommandType.Text, sql, parameters);
}
注意
但是最好使用高版本.net framework, 已测 .net 4.5 可联通， 低版本如.net 3.5报错 认证机制冲突
既让上了高版本，为什么不使用通用orm， 手写sql?
转载保留源出处即可，商业使用请自行鉴别，使用本博客中公开内容做任何违法犯罪于本作者无关
浙公网安备 33010602011771号