public static object Convert(string value, DataType dataType) { switch (dataType) { case DataType.Varchar: case DataType.Text: case DataType.Timestamp: case DataType.Date: case DataType.Time: return(value); case DataType.Int4: case DataType.Int8: return(ConvertUtil.ConToInt(value)); case DataType.Numeric: return(ConvertUtil.ConToDecimal(value)); case DataType.Jsonb: case DataType.Json: return(value); case DataType.Boolean: return(ConvertUtil.ConToBoolean(value)); default: throw new NotSupportedException("类型不支持"); } }
public void BulkCopy(IDbConnection conn, DataTable dataTable, string tableName) { var columnNameList = (from DataColumn column in dataTable.Columns select column.ColumnName.ToLower()) .ToList(); var columnNames = columnNameList.Aggregate((l, n) => l + "," + n); var cn = conn as NpgsqlConnection; if (conn == null) { return; } using (var writer = cn.BeginBinaryImport($"COPY {tableName}({columnNames}) FROM STDIN (FORMAT BINARY)")) { foreach (DataRow dr in dataTable.Rows) { writer.StartRow(); foreach (var columnName in columnNameList) { if (string.IsNullOrWhiteSpace(dr[columnName].ToString())) { writer.WriteNull(); } else { if (dataTable.Columns[columnName].DataType == typeof(int) || dataTable.Columns[columnName].DataType == typeof(long)) { writer.Write(ConvertUtil.ConToInt(dr[columnName]), NpgsqlTypes.NpgsqlDbType.Integer); } else if (dataTable.Columns[columnName].DataType == typeof(decimal)) { writer.Write(ConvertUtil.ConToDecimal(dr[columnName]), NpgsqlTypes.NpgsqlDbType.Numeric); } else if (dataTable.Columns[columnName].DataType == typeof(DateTime)) { writer.Write(ConvertUtil.ConToDateTime(dr[columnName]), NpgsqlTypes.NpgsqlDbType.Timestamp); } else if (dataTable.Columns[columnName].DataType == typeof(JToken)) { writer.Write(dr[columnName].ToString(), NpgsqlTypes.NpgsqlDbType.Jsonb); } else { writer.Write(dr[columnName].ToString()); } } } } writer.Complete(); } }