PostgreSQLCopyHelper批量插入Postgresql表C＃固定宽度文件 [英] PostgreSQLCopyHelper Bulk Insert Postgresql Table C# Fixed Width File
本文介绍了PostgreSQLCopyHelper批量插入Postgresql表C＃固定宽度文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试将固定宽度文件中的数据插入到Postgresql表中。我来到图书馆PostgreSQLCopyHelper
从调试屏幕可以看出，我的pd（ProductData）具有存储在表中所需的值。如何将其与SaveAll方法中所需的IEnumerable参数链接？
解决方案
我怀疑你想要的东西：
public ActionResult Update（q_product q_product，HttpPostedFileBase upload）
{
ProductData pd;
var entities = new List< ProductData>（）;
PostgreSQLCopyHelper< ProductData> insert = null;
尝试
{
if（ModelState.IsValid&& upload！= null）
{
//上传文件
流stream =上传。 InputStream的;
//此时需要使用BULK INSERT或MULTIPLE INSERT;
//使用（var fdr = new FileDataReader< ProductData>（stream））获取属性（列）
{
//获取文件中的每一行
while（（pd = fdr.ReadLine（））！= null）
{
//映射具有属性
insert = insert ??的表列new PostgreSQLCopyHelper< ProductData>（public，q_product）
.MapUUID（q_guid，x => Guid.NewGuid（））
.MapText（q_barcode，x => this.pd.barcode）
.MapText（q_description，x => this.pd.description）
.MapText（q_size，x => pd.size）
.MapInteger（q_stocklevel，x => this.pd.quantity）
.MapText（q_vatcode，x => pd.vatCode）
.MapMoney（q_casecost，x => ; this.pd.cost）
.MapMoney（q_sellprice，x => this.pd.price）;
entities.Add（pd）;
}
}
使用（var connection = new NpgsqlConnection（Host = 192.168.0.52; Database = tester; Username = test; Password = test））
{
尝试
{
connection.Open（）;
insert.SaveAll（connection，entities）;
TempData [SuccessMessage] =记录已插入！;
}
catch（例外）
{
TempData [ErrorMessage] = er.Message;
// TempData [ErrorMessage] =导入记录时出错！;
}
}
返回RedirectToAction（Index）;
}
}
catch（DataException错误）
{
TempData [ErrorMessage] =导入记录时出错！;
ModelState.AddModelError（，error.Message）;
}
返回RedirectToAction（Index）;
}
关键变化是填充 insert 只有一次，然后在迭代上传的文件时向实体添加条目。
I am attempting to Bukl Insert data from Fixed Width File into a Postgresql Table. I came accross the library PostgreSQLCopyHelper
https://github.com/bytefish/PostgreSQLCopyHelper
This is my update action in controller (updated 15/06/17)
ProductData pd = new ProductData();
public ActionResult Update(q_product q_product, HttpPostedFileBase upload)
{
ProductData pd;
var entities = new List<ProductData>();
PostgreSQLCopyHelper<ProductData> insert;
try
{
if(ModelState.IsValid && upload != null)
{
//uploaded file
Stream stream = upload.InputStream;
//need to use BULK INSERT or MULTIPLE INSERT at this point;
//get the properties (columns) as set in ProductData.cs
using (var fdr = new FileDataReader<ProductData>(stream))
{
//read and get each line on imported file
while ((pd = fdr.ReadLine()) != null)
{
//Lets populate insert
//map table columns with properties
insert = new PostgreSQLCopyHelper<ProductData>("public", "q_product")
.MapUUID("q_guid", x => Guid.NewGuid())
.MapText("q_barcode", x => pd.barcode)
.MapText("q_description", x => pd.description)
.MapText("q_import_size", x => pd.size)
.MapNumeric("q_stocklevel", x => pd.quantity)
.MapText("q_import_vatcode", x => pd.vatCode) //vatcode is numeric in DB, this is a String (new column for this test)
.MapNumeric("q_casecost", x => pd.cost)
.MapNumeric("q_sellprice", x => pd.price);
//add the populated entries as we iterate through the file
entities.Add(pd);
using (var connection = new NpgsqlConnection("Host=192.168.0.52;Database=bolo;Username=western;Password=western"))
{
try
{
connection.Open();
insert.SaveAll(connection, entities);
int lineCount = entities.Count();
TempData["SuccessMessage"] = lineCount+" Records Inserted!";
}
catch (Exception er)
{
TempData["ErrorMessage"] = er.Message;
//TempData["ErrorMessage"] = "Error: importing records!";
}
}
}
}
return RedirectToAction("Index");
}
}
catch(DataException error)
{
TempData["ErrorMessage"] = "Error importing records!";
ModelState.AddModelError("", error.Message);
}
return RedirectToAction("Index");
}
ProductData.cs file
public class ProductData
{
[Layout(22, 13)]
public string barcode;
[Layout(49, 25)]
public string description;
[Layout(74, 5)]
public string size;
[Layout(95, 4)]
public int quantity;
[Layout(99, 1)]
public string vatCode;
[Layout(108, 7)]
public decimal cost;
[Layout(115, 7)]
public decimal price;
public override string ToString()
{
return String.Format("string: {0}; string: {1}; string: {2}; int: {3}; string: {4}; decimal {5}; decimal {6}",
barcode, description, size, quantity, vatCode, cost, price
);
}
}
Upon debugging, the
entities
parameter on this line in the update action
insert.SaveAll(connection, entities);
happens to be null, thus no row is getting saved and its throwing an "Object not set reference" error. Now from the limited documentation about this CopyHelper library I cannot figure out which class or paramater I have to make IEnumerable, as SaveAll requries an IEnumerable second parameter
As you can see from the debug screen, my pd (ProductData) has the values it needs to store in the table. How do I link that with the IEnumerable paramater needed in the SaveAll method?
解决方案
I suspect you want something like:
public ActionResult Update(q_product q_product, HttpPostedFileBase upload)
{
ProductData pd;
var entities = new List<ProductData>();
PostgreSQLCopyHelper<ProductData> insert = null;
try
{
if(ModelState.IsValid && upload != null)
{
//uploaded file
Stream stream = upload.InputStream;
//need to use BULK INSERT or MULTIPLE INSERT at this point;
//get the properties (columns)
using (var fdr = new FileDataReader<ProductData>(stream))
{
//get each line on file
while ((pd = fdr.ReadLine()) != null)
{
//map table columns with properties
insert = insert ?? new PostgreSQLCopyHelper<ProductData>("public","q_product")
.MapUUID("q_guid", x => Guid.NewGuid())
.MapText("q_barcode", x => this.pd.barcode)
.MapText("q_description", x => this.pd.description)
.MapText("q_size", x => pd.size)
.MapInteger("q_stocklevel", x => this.pd.quantity)
.MapText("q_vatcode", x => pd.vatCode)
.MapMoney("q_casecost", x => this.pd.cost)
.MapMoney("q_sellprice", x => this.pd.price);
entities.Add(pd);
}
}
using (var connection = new NpgsqlConnection("Host=192.168.0.52;Database=tester;Username=test;Password=test"))
{
try
{
connection.Open();
insert.SaveAll(connection, entities);
TempData["SuccessMessage"] = "Records Inserted!";
}
catch (Exception er)
{
TempData["ErrorMessage"] = er.Message;
//TempData["ErrorMessage"] = "Error importing records!";
}
}
return RedirectToAction("Index");
}
}
catch(DataException error)
{
TempData["ErrorMessage"] = "Error importing records!";
ModelState.AddModelError("", error.Message);
}
return RedirectToAction("Index");
}
The key change is populating insert only once, and then adding entries to entities as you iterate through the uploaded files.
这篇关于PostgreSQLCopyHelper批量插入Postgresql表C＃固定宽度文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
