How to Manipulates huge database fields in C# application?

Question

0.00/5 (No votes)

See more:

I create a Project in C# winform application on sales billing. So I create a table sales_details and fields are ->

SQL

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

JayantaChatterjee

Updated 5-Feb-13 5:55am

v2

Add a Solution

Comments

Kiran Susarla 5-Feb-13 11:59am

Any specific reason of why you cannot create another table or modify the existing schema?

JayantaChatterjee 5-Feb-13 12:06pm

I think it will be more complicated.....

PIEBALDconsult 5-Feb-13 12:42pm

Not in the long run.

2 solutions

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...

Posted 5-Feb-13 7:44am

Zoltán Zörgő

Comments

thatraja 5-Feb-13 14:45pm

5

JayantaChatterjee 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ő 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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mehdi Gholam · Accepted Answer · 2013-02-05T06:06:00

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

Posted 5-Feb-13 6:06am

Mehdi Gholam

Updated 5-Feb-13 6:16am

v2

Comments

JayantaChatterjee 5-Feb-13 12:12pm

Sir, Can You give me example of the query and table design structure???

Mehdi Gholam 5-Feb-13 12:16pm

I have updated the solution.

JayantaChatterjee 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 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.

JayantaChatterjee 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 5-Feb-13 12:51pm

Sorry my mistake the query should be "select * from detailstable where master_id=X"

JayantaChatterjee 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 5-Feb-13 14:45pm

5

Mehdi Gholam 6-Feb-13 0:57am

Cheers!