美好的一天,我正在尝试从我的网站(
PHP)导入XML文件.我做了一些关于它的研究,我发现这个
Passing datatable to a stored procedure但是对于C#.那么,是否可以发送它?
这是我的存储过程
USE [sales_web] GO /****** Object: StoredProcedure [dbo].[InsertSALESPOS] Script Date: 11/30/2016 09:19:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[InsertSALESPOS] @HeaderTemp HeaderTemp readonly,@DetailTemp DetailTemp readonly,@PaymentTemp PaymentTemp readonly,@VoucherTemp VoucherTemp readonly,@LedgerTemp LedgerTemp readonly AS BEGIN set nocount on; begin try begin transaction select ID,TransactionNo,TransactionDate,TotalTransaction,LocationID,UserID,CustomerCode,SendStatus into #HeaderTemp from @HeaderTemp where TransactionNo not in(SELECT TransactionNo from TransactionHeader with(nolock)) INSERT INTO dbo.SalesOrderHeader (TransactionNo,DocumentNo,TransDate,ETADate,ExternalDocument,Reference,RefTransNo,Category,Remark,Remark2,TermOfPayment,Currency,Rate,UseVAT,SubTotal,DiscPercent,DiscAmount,GrandTotal,TotalReceivable,FromCustomerCode,PostingStatus,PostingBy,PostingDate,CreatedDate,CreatedBy,ModifiedDate,ModifiedBy,CustomerReceivable,LocationCode,VatPercent,VatAmount,FinalReleasedDate_Outlet) select h.TransactionNo,h.TransactionNo as DocumentNo,cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as TransDate,len(h.TransactionDate)-6) as datetime) as ETADate,'' as ExternalDocument,'' as Reference,'' as RefTransNo,'POS' as Category,h.CustomerCode,'' as Remark,'' as Remark2,Cast('' as Datetime) as TermOfPayment,'IDR' as Currency,Cast(1 as Float) as Rate,Cast(0 as Bit) as UseVAT,Cast(SUM(d.TotalAmountAfterDisc) as Float) as SubTotal,Cast(0 as Float) as DiscPercent,Cast(0 as Float) as DiscAmount,Cast(SUM(d.TotalAmountAfterDisc) as Float) as GrandTotal,Cast(SUM(d.TotalAmountAfterDisc) as Float) as TotalReceivable,h.CustomerCode as FromCustomerCode,Cast(0 as Bit) as PostingStatus,'' as PostingBy,Cast('' as Datetime) as PostingDate,len(h.TransactionDate)-6) as datetime) as CreatedDate,h.UserID as CreatedBy,len(h.TransactionDate)-6) as datetime) as ModifiedDate,h.UserID as ModifiedBy,h.CustomerCode as CustomerReceivable,h.CustomerCode as LocationCode,Cast(0 as Float) as VatPercent,Cast(0 as Float) as VatAmount,len(h.TransactionDate)-6) as datetime) as FinalReleasedDate_Outlet from @HeaderTemp h Inner Join @DetailTemp d on h.id = d.TransactionID and h.CustomerCode = d.CustomerCode Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock)) Group By h.TransactionNo,h.UserID Insert Into dbo.SalesOrderDetail SELECT h.TransactionNo + '-' + Cast(ROW_NUMBER() Over(Order By h.TransactionNo) as Varchar) as TransactionNo,h.TransactionNo as SalesOrderTransNo,h.TransactionNo as RefTransNo,(ROW_NUMBER() Over(Order By h.TransactionNo))*1000 as [LineNo],isnull(d.ItemCode,''),d.Quantity,isnull(d.MainUOMCode,'') as UOM,d.UnitPrice,0 as DiscountPercent,0 as VatPercent,(d.Quantity*d.UnitPrice) as AmountPrice,((d.Quantity*d.UnitPrice)-d.TotalAmountAfterDisc) as AmountDiscount,0 as AmountVat,d.TotalAmountAfterDisc as Total,d.TotalAmountAfterDisc as Receivable,d.UnitPrice as UnitPriceAfterReceivable,'' as ExtraRemark,'' as ExtraRemark2,h.UserID as ModifiedBy from @HeaderTemp h Inner Join @DetailTemp d on h.id = d.TransactionID and h.CustomerCode = d.CustomerCode Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock)) Insert Into dbo.SalesOrderPayment Select h.TransactionNo + '-' + Cast(ROW_NUMBER() Over(Order By h.TransactionNo) as Varchar) as TransactionNo,p.PaymentMethodCode,p.TotalDue,p.TotalTendered From @HeaderTemp h Left Join @PaymentTemp p on h.id = p.TransactionID and h.CustomerCode = p.CustomerCode Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock)) --cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) Insert Into dbo.SalesOrderVoucher Select h.TransactionNo + '-' + Cast(ROW_NUMBER() Over(Order By h.TransactionNo) as Varchar) as TransactionNo,v.VoucherVendor as Voucher,Case When v.VoucherType = '1' Then 'NOMINAL' Else 'PERCENT' End as VoucherType,v.VoucherValue,v.VoucherAmount From @HeaderTemp h Left Join @VoucherTemp v On h.CustomerCode = v.CustomerCode And h.ID = v.TransactionID Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock)) Insert Into dbo.ItemLedgerEntry(TransactionNo,SequenceNo,EntryType,ItemCode,Quantity,UomCode,CreatedBy) Select TransactionNo,cast(left(PostingDate,len(PostingDate)-6) as datetime) as PostingDate,Cast(Quantity as Float) as Quantity,cast(left(CreatedDate,len(CreatedDate)-6) as datetime) as CreatedDate,CreatedBy From @LedgerTemp Where DocumentNo in (select Transactionno from #HeaderTemp with(nolock)) drop table #HeaderTemp Select 'Succes' as [Status] commit transaction end try begin catch rollback insert into XMLLOGERROR values(getdate(),'SALESPOS',ERROR_MESSAGE()) end catch end
我试过这种方式
$xml=simplexml_load_file($file); $HeaderTemp = array(); $DetailTemp = array(); $PaymentTemp = array(); $LedgerTemp = array(); $VoucherTemp = array(); $tambahan = array('session_id' => date('d:h:i:s')); $login = str_replace(' ','',$this->input->cookie('cookie_webstore_user')); $session = $login.$tambahan['session_id']; foreach ($xml->HeaderTemp as $HeaderTempnya) { $HeaderTemp[] = $HeaderTempnya; } foreach ($xml->DetailTemp as $DetailTempnya) { $DetailTemp[] = $DetailTempnya; } foreach($xml->PaymentTemp as $PaymentTempnya) { $PaymentTemp[] = $PaymentTempnya; } foreach($xml->LedgerTemp as $LedgerTempnya) { $LedgerTemp[] = $LedgerTempnya; } foreach($xml->VoucherTemp as $VoucherTempnya) { $VoucherTemp[] = $VoucherTempnya; } if($VoucherTempnya != ""){ $this->modelmodel->showsingle("[InsertSALESPOS] '{$HeaderTemp}','{$DetailTemp}','{$PaymentTemp}','{$VoucherTemp}','{$LedgerTemp}'"); }else{ $this->modelmodel->showsingle("[InsertSALESPOS] '{$HeaderTemp}','{$LedgerTemp}'"); }
使用上面的脚本我得到了这个错误
Error Number: 22018/206
[Microsoft][sql Server Native Client
10.0][sql Server]Operand type clash: varchar is incompatible with HeaderTemp[InsertSALESPOS]
‘Array’,’Array’,”,’Array’Filename:
D:/xampp/htdocs/new_store/system/database/DB_driver.PHPLine
Number: 691
对不起,我的英语不好.
好.我用不同的方式解决了上面的问题.如果不可能,我只是好奇,请解释原因.