将数据从发票插入数据库表C＃桌面应用程序 [英] inserting data from invoice to database table C# desktop application

问题描述

我正在尝试将数据从发票插入数据库。问题是，发票中有多行应该插入到表中。这是问题所在。请建议。



----我的桌子，只显示必填字段-----

I am trying to insert data from invoice to database. The problem is, there are multiple rows in the invoice which should be inserted to the table. Here is problem. Please suggest.

---- my tables, only required fields are shown -----

CREAT TABLE PATIENT
(
	PATIENT_ID int IDENTITY(1,1) PRIMARY KEY,
	PATIENT_NAME NVARCHAR(200) NOT NULL
);
values are >> 1, JOE || 2, MARK || 3, SAM

CREAT TABLE RX
(
	RX_ID int IDENTITY(1,1) PRIMARY KEY,
	RX_NAME NVARCHAR(200) NOT NULL
);
values are >> 1, RX-1 || 2, RX-3, || 3, RX-10 || 4, RX-05

CREAT TABLE RX_RATE
(
	RX_RATE_ID int IDENTITY(1,1) PRIMARY KEY,
	RX_RATE DECIMAL (19,10) NOT NULL,
	RX_ID INT FOREIGN KEY REFERENCES RX(RX_ID)
);
values are >> 1, 100, 1 || 2, 150, 2 || 3, 100, 3 || 4, 200, 4

我有一个结算用户界面，其中有PATIENT NAME的文本框，RX NAME的下拉列表，TEXTBOX对于QUANTITY。然后从RX_RATE表中获取RATE。然后计算金额。



账单中的记录数是动态的。我的意思是有时用户购买的药品数量可以是5或10或20等等。这就是我的UI形式的样子。

I've a billing UI, where there is text box for PATIENT NAME, dropdowns for RX NAME, TEXTBOX for QUANTITY. Then the RATE is fetched from RX_RATE table. then the amount is calculated.

The number of records in the bill is dynamic. I mean some times number of mediciines purchased by the user can be 5 or 10 or 20 etc. etc. This is how my UI form looks like.

---------------------------------------------------------------------------------- 
Bill No.  >> 100
Patient Name >> Joe 
 
 rx name	 quantity	    rate	 amount
 RX-1	          2	          100	 200
 RX-05	          1	          200	 200
                              total      400
SUBMIT_BTN 
---------------------------------------------------------------------------------------  

现在我要在TRANSACTION表中插入此信息。 我的问题是如何在提交按钮单击时将账单中的整个数据插入TRANSACTION表。

Now I've to insert this information in TRANSACTION table. my problem is how can I insert the whole data from the bill to TRANSACTION table on submit button click.

CREAT TABLE TRANSACTION
(
	TRANSACTION_ID int IDENTITY(1,1) PRIMARY KEY,
	BILL_ID INT IDENTITY(100,1),
	P_ID INT FOREIGN KEY REFERENCES PATIENT(PATIENT_ID),
	RX_ID INT FOREIGN KEY REFERENCES RX(RX_ID),
	RX_QUANTITY INT NOT NULL,
	AMOUNT DECIMAL (19,10) NOT NULL, 
	DATE_TXN DATE DEFAULT GETDATE()
);

这是这个表中的数据将是>>

This is how the data in this table will be>>

1, 100, 1, 1, 2, 200, 2015-07-22  
2, 100, 1, 4, 1, 200, 2015-07-22

请建议。

Please suggest.

推荐答案

您没有发布用于插入任何数据的实际代码，但插入多行基本相同插入一个。您只需根据需要重复插入步骤。所以简而言之伪代码：

You didn't post the actual code you use to insert any data, but inserting multiple rows is basically the same thing as inserting one. You just repeat the insert step as many times as you need to. So in short pseudo code:

open SqlConnection
create SqlCommand // using parameters
start transaction
loop while rows to insert exist
   try
       set parameter values for the row
       execute the insert command
   catch error
      rollback
end loop
commit transaction if operation was succesful
close connection

我认为您的问题是您需要一个详细级别的事务表存储与事务关联的rx。

I think your problem is that you need a detail level transaction table to store the rx associated with the transaction.

CREAT TABLE TRANSACTION
(
	TRANSACTION_ID int IDENTITY(1,1) PRIMARY KEY,
	BILL_ID INT IDENTITY(100,1),
	P_ID INT FOREIGN KEY REFERENCES PATIENT(PATIENT_ID),
	DATE_TXN DATE DEFAULT GETDATE()
);

CREAT TABLE TRANSACTION_DETAIL
(
	TRANSACTION_DETAILID int IDENTITY(1,1) PRIMARY KEY,
	TRANSACTION_ID INT FOREIGN KEY REFERENCES TRANSACTION(TRANSACTION_ID),
	RX_ID INT FOREIGN KEY REFERENCES RX(RX_ID),
	RX_QUANTITY INT NOT NULL,
	AMOUNT DECIMAL (19,10) NOT NULL, 
);

使用此结构，您的交易可以包含任意数量的RX项目。

Using this structure your transaction can contain as many RX items as you like.

这篇关于将数据从发票插入数据库表C＃桌面应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！