Inserting Records Best Practice

[Guest]

Could anyone point me in the right direction for the best way to insert a
record into a SQL database using ADO.NET with 150+ columns?

1. DataReader + SqlCommand.CommandText + Insert statement
2. DataReader + SqlCommand.Paramerterized Query + Stored Procedure
3. DataSet + AddNew()
4. Other??

Thanks,

 

[Miha Markic [MVP C#]]

What do you do with DataReader?
5. DataSet + DataAdapter is the correct answer . Don't forget to use
batching on ado.net 2.

 

[Cor Ligthert [MVP]]

Mario,

SQLCommand.ExecuteNonQuery
with a CommandText or SP with parameters, a lot of work be aware that the
Microsoft generaters only go to 100 columns.

Maybe you use parts of this code, although it is completely the opposite
direction can you maybe use it to create your procedure in a loop.

http://www.vb-tips.com/dbPages.aspx?ID=49f2cff5-56ad-44fc-a4c6-fc0d5c470f53

Cor

 

[Cor Ligthert [MVP]]

Mario,

I know stored procedures execute quicker as they are already compiled

Are you using an IBM DB2 database. If you want to read about this, search
this newsgroup for Frans Bouma, who have had very long discussions about the
misunderstanding that Stored Procedures are compiled. They are compiled at
the same time as Text transaction strings.

Cor

 

[Miha Markic [MVP C#]]

Hi Mario,

Hi Miha,

I'm not sure what I was thinking when I added the DataReader in the first
2
options. What I meant was utilizing the SqlCommand with either a
CommandType.Text or CommandType.StoredProcedure. I know stored procedures
execute quicker as they are already compiled but my concern was having
150+
Parameters to pass to the stored procedure. Which would be faster.

Both approaches require parameters, so no big difference there. Furthermore,
there isn't a significant performance difference between the two approaches.
SP approach should be a bit faster because you are sending less data over
the wire I suppose.

As for the DataSet and DataAdapter method (my option #3), what would be
the
best method to accomplish this?

In light of the new details would you still recommend utilizing the
DataSet
+ DataAdapter over the SqlCommand to insert the record (with over 150
columns)?

Definitely the best approach. It does same job as you would done manually +
it supports batching. And batching improves performance dramatically.

 

[William \(Bill\) Vaughn]

1) Review your normalization. 150 columns is quite a few.
2) Where is the data coming from? Based on the other comments it sounds like
you're importing data, massaging it and sending it back out via an INSERT.
ADO.NET is not really designed for this type of operation. I suggest
investigating BCP or SqlBulkCopy.
3) Stored procedures are no longer pre-compiled. Their performance is gained
from a cached query plan and then only if the plan matches the operation.
They are also designed to help build systems in a team environment.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[William \(Bill\) Vaughn]

I would still investigate importing the data from XML to a SQL table via
bulk copy and doing the manipulation server-side.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------