C# ASP.NET MVC – Efficient Methods to Store Data into SQL Server Database

csql server

I am using Microsoft Visual Studio and C# language to query and store data into my database.

I have two ways in which I can store data into my database in SQL server.

For example, I need to store the amount of fruits in my database:

Apples = 10, Orange = 3 and grape = 5 and watermelon = null.

So is it better to store it as:
(UPDATED!
1)

enter image description here

I use a for loop and store it one by one.

for(int i=0; i<  length ; i++)

{ sql.add(fruit[i],value[i],plotdate[i]);}


INSERT INTO fruit (fruit, value, plotdate)
VALUES ('Apple','10','11/01/2016 1:45:00PM');

Or the second method:

2)

enter image description here

I store it all at once,

sql.add(value,value,value,value,plotdate);

INSERT INTO fruit (Apple, Orange,grape, plotdate)
VALUES ('10','3', '5','11/01/2016 1:45:00PM');

Thank you

EDIT:

What I'm trying to achieve:

A device sends me data every 5 minutes so it will send it using the URL

www.example.com/get?apple=10,orange=2,grape=5,watermelon-1,apple=12,orange,13,grape=12 etc..

I then break down the URL I receive and which is why I use for-loop to store all the data for each fruit given by the url.
After doing so the updated version in the database will look like 1).

Best Answer

Compilation of comments

@Sole DBA Guy pointed out:

I'd say method 1 is what most DBAs will say is better of the two options because if you decide to add Banana, it's just another row for the first option but for the second option, it would be a new column. However, why does it need a loop to add the values? Is the loop to get the values for each fruit? Do the same fruit values not exist in the database already?

@MDCCL mentioned:

I agree with @Sole Dba Guy. From a modeling perspective, as usual, it depends on different considerations. For instance, if you want to store an entity type called, perhaps, Fruit which, say, has the attributes FruitNumber, Name and QuantityAvailable, then your first procedure is certainly more suitable and extensible. You have to determine with precision the kinds of things that exist in your context of interest so that you can define which option is more convenient.

Then, I replied to his comment with the following question:

Wouldn't the first method take up more processing time or CPU usage? Since it has to do {sql.add()} again and again due to being in a for loop rather than using {sql.add()} once like the second method.

To which @MDCCL responded:

Maybe, or maybe not, honestly, it is hard to tell accurately without knowing the whole context. That is, in part, an application program question, but it certainly has database implications. Since you would be executing multiple INSERTs, perhaps some kind of batch operation might be helpful in your case. Application programming and relational database implementation are different disciplines so, as a mere suggestion, you should read about relational theory, data modeling and Structured Query Language (particularly the Transact-SQL dialect that is used in Microsoft SQL Server, the platform that, according to your tag, you are currently employing).

Finally, @srutzky added:

The query string might have multiple instances of the same variable? The example shows "apple", "orange", and "grape" showing up twice, plus the mention of the for loop. How likely is it that other fruits will be added, over the next few years at least? If fruits show up multiple times in the query string, what's your plan for doing #2 for ones with multiple entries? Aggregate them into a single entry? Is the relationship between them in terms of when the data was submitted important? Option 1 doesn't have that, outside of the time. Option 1 also repeats fruit names instead of using FruitID.

Related Question