Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL WinForm
I create a Project in C# winform application on sales billing. So I create a table sales_details and fields are ->
Bill_no,B_date,Pa_name,P_addr,ph_no, pr_nam1,batch1,exp_dt1,drg_mfg1,qty1,mrp1,g_amt1,disc1,net_amt1, pr_nam2,batch2,exp_dt2,drg_mfg2,qty2,mrp2,g_amt2,disc2,net_amt2 (so on...up to 5 product details)
I don't want to create another table and also can't delete this fields from database cause it will need in future..
So,in my application i used each and every fields name(9*5) times for save and retrieve the fields values..
Please tell me if there is another way to do this.....
I'm using access database with OLEDB.......
Posted 5-Feb-13 5:54am
Edited 5-Feb-13 5:55am
v2
Comments
Kiran Susarla at 5-Feb-13 11:59am
   
Any specific reason of why you cannot create another table or modify the existing schema?
Jayanta Chatterjee at 5-Feb-13 12:06pm
   
I think it will be more complicated.....
PIEBALDconsult at 5-Feb-13 12:42pm
   
Not in the long run.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

1) Forget OLEDB, switch to EF or an other object persistence engine, but at least add a business logic layer and a model above database layer.
2) If you think in objects with properties, instead of tables with fields - it is not so hard to imagine complex structure (like property of collection of an other class) instead of flat tuples (rows in a table).
3) Once you have that, you might consider using structured datatypes as storage primitives. Like XML[^]. You might than find out that you have a powerful tool in your hand, that allows you to bring a high dynamism in your model - by not being forced to predefine fields even before using them.
4) You might even go further, and figure out that there is a world beyond relational model known as NoSQL[^], whose basic concept is the document: a structured, but absolutely dynamic entity, very close to the real world...
  Permalink  
Comments
thatraja at 5-Feb-13 14:45pm
   
5
Jayanta Chatterjee at 5-Feb-13 21:49pm
   
Sir can you please give me some code or example??
because I'm new in C# application.
Zoltán Zörgő at 6-Feb-13 6:39am
   
Well, that's not so easy. Especially for a beginner. For the start you should follow Mehdi Gholam's suggestion.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Bad design.
 
Just break your table into two tables a master and a detail table, and the (pr_nam1,batch1,exp_dt1,drg_mfg1,qty1,mrp1,g_amt1,disc1,net_amt1) values can have a foreign key to the master (you can add a LINE_NO instead of the 1,2... etc in the names).
 
This way you are not limited to 9 items and the design is much simpler and more performant.
 
EDIT:
Master table
-------------
ID,
Bill_no,
B_date,
Pa_name,
P_addr,
ph_no
 
Details table
-------------
ID,
LINE_NO,
Master_ID,
pr_nam,
batch,
exp_dt,
drg_mfg,
qty
...
 
Foreign KEY reference : detailstable.Master_ID -> mastertable.ID
  Permalink  
v2
Comments
Jayanta Chatterjee at 5-Feb-13 12:12pm
   
Sir, Can You give me example of the query and table design structure???
Mehdi Gholam at 5-Feb-13 12:16pm
   
I have updated the solution.
Jayanta Chatterjee at 5-Feb-13 12:24pm
   
But Sir when i insert and retrieve the product details from Details table then i need to define each and every fields name in my applications...
like if i show all the product details in a listView then i do the same thing....
Mehdi Gholam at 5-Feb-13 12:38pm
   
I have no idea what you are saying! You can get all the products for the master by doing "select * from mastertable where master_id=X" where X is the ID you want.
Jayanta Chatterjee at 5-Feb-13 12:48pm
   
Sir, suppose i need a print of all the fields in printDocument then My query is "select * from mastertable where master_id=X" after that i need the fields values then My code will be
dataset.table["mastertable"].Rows[0][0]
dataset.table["mastertable"].Rows[0][1]
dataset.table["mastertable"].Rows[0][2]
so on..
 
and also when i insert the value into master table then my query is :
"insert into mastertable values(9*5 times)"..
that is my problem, i don't want this long statement... :-(
Please help me....
Mehdi Gholam at 5-Feb-13 12:51pm
   
Sorry my mistake the query should be "select * from detailstable where master_id=X"
Jayanta Chatterjee at 5-Feb-13 13:03pm
   
Sir Your query and suggestion is too good, but my problem is
too long insert statements and if i update that product fields then it will be too long..
is there a way to concise the insert and update statement with loosing any info???
thatraja at 5-Feb-13 14:45pm
   
5
Mehdi Gholam at 6-Feb-13 0:57am
   
Cheers!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 404
1 Nirav Prabtani 389
2 Abhinav S 210
3 PIEBALDconsult 160
4 Dave Kreskowiak 155
0 OriginalGriff 7,580
1 Sergey Alexandrovich Kryukov 6,806
2 Maciej Los 3,919
3 Peter Leow 3,693
4 CHill60 2,742


Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 5 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100