Npgsql实现Postgresql数据库表的批量导入及导出(Bulk Copy)
我们知道在ADO.NET中有“System.Data.SqlClient.SqlBulkCopy类”专门针对SQL Server的批导入表数据操作(Bulk Copy)，而针对Postgresql的Npgsql并无相应的类，Npgsql实现Bulk Copy是通过Copy指令实现的，详细可以参考Npgsql官网对Bulk Copy的说明https://www.npgsql.org/doc/copy.html （如果想更多了解关于Copy指令的语法可以阅读https://www.postgresql.org/docs/current/sql-copy.html）。
以下是C#中实现批量导入表格数据的通用方法（导出方法请阅读官网相关说明，此处略过）：
public void BulkCopy(string tableName, DataTable dt)
{
List<string> lsColNames = new List<string>();
for (int i = 0; i < dt.Columns.Count; i++)
{
lsColNames.Add($"\"{dt.Columns[i].ColumnName}\"");
}
string copyString = $"COPY \"{tableName}\" ( {string.Join(",", lsColNames) } ) FROM STDIN (FORMAT BINARY)";
using (NpgsqlConnection conn = (NpgsqlConnection)_DB.CreateConnection())
{
if (conn.State == ConnectionState.Closed)
conn.Open();
var writer = conn.BeginBinaryImport(copyString);
foreach (DataRow row in dt.Rows)
{
writer.StartRow();
IEnumerable<KeyValuePair<string, JToken>> JRowData = DataConvert.ToJObject(row);
foreach (var kvp in JRowData)
{
NpgsqlParameter colParam = GetParameter(tableName, kvp);
writer.Write(colParam.Value, colParam.NpgsqlDbType);
}
}
writer.Complete();
conn.Close();
}
}
以下是上例中涉及的有关获取各字段作参数的类型与值的方法：
public NpgsqlParameter GetParameter(string tableName, KeyValuePair<string,JToken> columnValuePair)
{
string columnDBypeName = _DBTableDefProvider.GetTableColumn(tableName, columnValuePair.Key).data_type.ToLower();NpgsqlParameter p = new NpgsqlParameter("@" + columnValuePair.Key,
columnDBypeName == "timestamp" || columnDBypeName == "timestamp without time zone" ? NpgsqlDbType.Timestamp
: columnDBypeName == "timestamp with time zone" ? NpgsqlDbType.TimestampTz
: columnDBypeName == "date" ? NpgsqlDbType.Date
: columnDBypeName=="time" || columnDBypeName == "time without time zone" ? NpgsqlDbType.Time
: columnDBypeName == "time with time zone" ? NpgsqlDbType.TimeTz
: columnDBypeName == "smallint" ? NpgsqlDbType.Smallint
: columnDBypeName == "integer" || columnDBypeName == "serial" ? NpgsqlDbType.Integer
: columnDBypeName == "bigint" || columnDBypeName == "bigserial" ? NpgsqlDbType.Bigint
: columnDBypeName == "double precision" ? NpgsqlDbType.Double
: columnDBypeName == "real" ? NpgsqlDbType.Real
: columnDBypeName == "boolean" ? NpgsqlDbType.Boolean
: columnDBypeName == "uuid" ? NpgsqlDbType.Uuid
: columnDBypeName == "bit" ? NpgsqlDbType.Bit //eg:0|1
: columnDBypeName == "json" ? NpgsqlDbType.Json
: columnDBypeName == "money" ? NpgsqlDbType.Money
: columnDBypeName == "numeric" ? NpgsqlDbType.Numeric
: columnDBypeName == "bit varying" ? NpgsqlDbType.Varbit //eg:01010101
: columnDBypeName == "text" ? NpgsqlDbType.Text
: columnDBypeName == "character varying" ? NpgsqlDbType.Varchar //NpgsqlDbType.Varchar可以直接用NpgsqlDbType.Text
: columnDBypeName == "\"char\"" || columnDBypeName == "character" ? NpgsqlDbType.Char //NpgsqlDbType.Char可以直接用NpgsqlDbType.Text
//: columnDBypeName == "array" ? NpgsqlDbType.Array|NpgsqlDbType.Json //ARRAY需要匹配各个基础类型的Array，且不能直接以string传值，不常用不做处理
: columnDBypeName == "interval" ? NpgsqlDbType.Interval
//: NpgsqlDbType.Text);
: NpgsqlDbType.Unknown);
p.Value = columnValuePair.Value.Type == JTokenType.Null ? DBNull.Value
: columnDBypeName.StartsWith("timestamp") || columnDBypeName == "date" || columnDBypeName.StartsWith("time") ? Convert.ToDateTime(((JValue)columnValuePair.Value).Value)
: columnDBypeName == "smallint" ? Convert.ToInt16(columnValuePair.Value)
: columnDBypeName == "integer" || columnDBypeName == "serial" ? Convert.ToInt32(((JValue)columnValuePair.Value).Value)
: columnDBypeName == "bigint" || columnDBypeName == "bigserial" ? Convert.ToInt64(((JValue)columnValuePair.Value).Value)
: columnDBypeName == "double precision" ? Convert.ToDouble(((JValue)columnValuePair.Value).Value)
: columnDBypeName == "real" ? Convert.ToSingle(((JValue)columnValuePair.Value).Value)
: columnDBypeName == "boolean" ? Convert.ToBoolean(((JValue)columnValuePair.Value).Value)
: columnDBypeName == "uuid" ? Guid.Parse((string)columnValuePair.Value)
: columnDBypeName == "bit" ? Convert.ToString(Convert.ToInt32(columnValuePair.Value), 2).Last().ToString()
: columnDBypeName == "json" ? JObject.Parse((string)columnValuePair.Value).ToString()
: columnDBypeName == "money" || columnDBypeName == "numeric" ? Convert.ToDecimal(((JValue)columnValuePair.Value).Value)
: columnDBypeName == "text"|| columnDBypeName == "character varying" || columnDBypeName == "character" ? (string)columnValuePair.Value
: columnDBypeName == "interval" ? TimeSpan.Parse(Regex.Replace((string)columnValuePair.Value, "days?",".",RegexOptions.IgnoreCase).Replace(" ",""))
: (object)(string)((JValue)columnValuePair.Value).Value;
return p;
}
以下是查询Postgresql表格与字段信息的SQL：
select
--col.table_schema,
col.table_name,
col.column_name,
(case when col.column_default like 'nextval(%' then true else false end) as is_serial,
--col.is_generated,
(case when col.is_nullable='NO' then false else true end) as is_nullable,
(case when con.contype is not null then true else false end) as is_primarykey,
--con.contype ,
col.data_type,
col.character_maximum_length
from information_schema.columns col
inner join pg_class cls on col.table_name=cls.relname
left join pg_constraint con on cls.oid=con.conrelid and con.contype='p' and array_position(con.conkey::integer[],col.ordinal_position::integer)>0
where col.table_schema = 'public'
order by col.table_name,col.ordinal_position;