problem with creating billing system software C# win form

hadoop hadoop

11y1.9kWindows Forms

I am having problem inserting data from the medical bill to the database. Please suggest how can I do this?

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

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

-----------------------------------------------------------------------------------------------------------------

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()

);

values inserted from the bill are >>

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

Please suggest.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

hadoop hadoop11y ago
Thanks for the reply. Can you please explain the OPTION 2.
Manas Mohapatra11y ago
Hi,

Please find couple of options to resolve problem:

Option 1: (Through dataset)
You need to fetch all info of Transaction and create a datatable. And insert two records into that table.

Then go to sqlserver and create a type which will contain columns like your transaction columns.

Create a stored procedure and pass the table to it. Select from type and into your original TRANSACTION table.

http://www.aspsnippets.com/Articles/Send-Pass-DataTable-as-parameter-to-Stored-Procedure-in-C-and-VBNet.aspx

Option 2 :(Normal way)
You need to loop over how many rows in transaction info.

And you need to execute insert SQL command inside above loop.

But this is not good because if you have 20 rows then 20 times you need to call database.

Option 3 (Through XML)

You need to fetch all info of Transaction and create a xml data.

Create a stored procedure and pass the xml data to it. Then loop over xml data and insert records to table. It work only sqlserver 2008 on wards.

http://www.aspsnippets.com/Articles/Pass-XML-parameter-to-Stored-Procedure-in-C-and-VBNet.aspx

Choose which option is comfortable for you(most probably option 1).