ORMLite SQL Server Update

I have a table called PODetail with a primary Key of POno and ItemCode and I have the following:

[Route("/podetail/{POno}/{ItemCode}")]
    public class UpdatePODetail : IReturn<PODetail> {
        public string POno { get; set; }
        public string ItemCode  { get; set; }

        public int ?     QtyPend { get; set; }
        public decimal ? NewPrice { get; set; }
        public bool ? BackOrder { get; set; }
        public string  ActionCode { get; set; }
        public bool ? OpenOrder { get; set; }
    }

 public class PODetailService : Service {
    public object Any(UpdatePODetail request) {
        var podetail =  Db.SingleFmt<PODetail>("ItemCode = {0} AND POno = {1}", request.ItemCode, request.POno);
        // var cap = new CaptureSqlFilter();
        try {
            Db.Update(podetail);
        } catch {
            // var sql = string.Join(";nn", cap.SqlStatements.ToArray());
        }
        :
        :

        try {
            Db.Update(podetail);
        } catch (Exception ex) {
            string error = ex.Message;
        }
        return podetail;
    }
}

I added the Db.Update call at the top just to check to see if there was some issue changing a column, but I get

Violation of PRIMARY KEY constraint 'aaaaaPoDetail_PK'. Cannot insert duplicate key in object 'dbo.PODetail'.

So then I added the cap = line to see the SQL code which returns

UPDATE "PODetail" SET "NewItemCode"=@NewItemCode, "POno"=@POno, "Vendor"=@Vendor, "ActionCode"=@ActionCode, "Price"=@Price, "NewPrice"=@NewPrice, "CostPrice"=@CostPrice, "QtyOrd"=@QtyOrd, "QtyRcv"=@QtyRcv, "QtySPO"=@QtySPO, "QtyPend"=@QtyPend, "BackOrder"=@BackOrder, "OpenOrder"=@OpenOrder, "OrderDate"=@OrderDate, "InvoiceNo"=@InvoiceNo, "InvoiceVendor"=@InvoiceVendor, "InvoiceDate"=@InvoiceDate, "InvoiceDiscount"=@InvoiceDiscount, "QtyCancel"=@QtyCancel, "Qtylabels"=@Qtylabels, "REOVendor"=@REOVendor, "CurrentRcvQty"=@CurrentRcvQty, "SOPickQty"=@SOPickQty, "SOItem"=@SOItem, "QtyOther"=@QtyOther, "BackOrderCode"=@BackOrderCode WHERE "ItemCode"=@ItemCode

And then it runs fine uncommented -- no exceptions .. if I remove it it gets the Primary Key error

What is the deal -- why do I need that CaptureSqlFilter call -- or what I do I need to change so that it knows both PoNo and ItemCode are primary Keys or the update needs to say WHERE "ItemCode"=@ItemCode AND "POno"=@PONo? It almost seems as if it is trying to do an INSERT vs an UPDATE without the CaptureSqlFilter

Update 1

The documentation said :

Limitations For simplicity, and to be able to have the same POCO class persisted in db4o, memcached, redis or on the filesystem (ie providers included in ServiceStack), each model must have a single primary key, by convention OrmLite expects it to be Id although you use [Alias("DbFieldName")] attribute it map it to a column with a different name or use the [PrimaryKey] attribute to tell OrmLite to use a different property for the primary key.

You can still SELECT from these tables, you will just be unable to make use of APIs that rely on it, eg Update or Delete where the filter is implied (ie not specified), all the APIs that end with ById, etc.

Workaround single Primary Key limitation

A potential workaround to support tables with multiple primary keys is to create an auto generated Id property that returns a unique value based on all the primary key fields,

So I tried to add this

public class PODetail {
    public string Id { get { return this.ItemCode + "/" + this.POno; } }
    public string ItemCode { get; set; }
    public string NewItemCode { get; set; }
    public string POno { get; set; }
    :
}

But when it went to execute :

Db.SingleFmt<PODetail>

It error out with ID not a valid column or column not found or something like that

So I then tried

public class PODetail {
    //public string Id { get { return this.ItemCode + "/" + this.POno; } }
    [PrimaryKey]
    public string ItemCode { get; set; }
    public string NewItemCode { get; set; }
    [PrimaryKey]
    public string POno { get; set; }
    :
}

and it worked on the Db.SingleFmt ... and the Db.Update

So then I added back in the CaptureSqlFilter to see what the query looked like and I got

UPDATE "PODetail" SET "NewItemCode"=@NewItemCode, "Vendor"=@Vendor, "ActionCode"=@ActionCode, "Price"=@Price, "NewPrice"=@NewPrice, "CostPrice"=@CostPrice, "QtyOrd"=@QtyOrd, "QtyRcv"=@QtyRcv, "QtySPO"=@QtySPO, "QtyPend"=@QtyPend, "BackOrder"=@BackOrder, "OpenOrder"=@OpenOrder, "OrderDate"=@OrderDate, "InvoiceNo"=@InvoiceNo, "InvoiceVendor"=@InvoiceVendor, "InvoiceDate"=@InvoiceDate, "InvoiceDiscount"=@InvoiceDiscount, "QtyCancel"=@QtyCancel, "Qtylabels"=@Qtylabels, "REOVendor"=@REOVendor, "CurrentRcvQty"=@CurrentRcvQty, "SOPickQty"=@SOPickQty, "SOItem"=@SOItem, "QtyOther"=@QtyOther, "BackOrderCode"=@BackOrderCode WHERE "ItemCode"=@ItemCode AND "POno"=@POno

Which is what I wanted in the first place.

It works but what is the deal can you have the [PrimaryKey] attribute multiple times (it appears so) and also then why didn't the autogenerated Id work? Just wondering if I am missing something or not understanding the documentation correctly.

Oh and sorry for posting in the comments!

---

what I do I need to change so that it knows both PoNo and ItemCode are primary Keys

OrmLite's primary limitation is that each Table has a single primary Key .

Also you can use the built-in Profiling or debug logging to view the generated SQL without needing to change code to use CaptureSqlFilter .

I'd also recommend that you don't use the Request DTO for anything other than defining your Service with. You can use the built-in AutoMapping to easily use it to populate your data model.

链接地址: http://www.djcxy.com/p/65974.html

上一篇: OrmLite中的大括号选择查询会引发错误

下一篇: ORMLite SQL Server更新