OleDbParameter vs. manually SQL Statement to Insert mass date from host

  • Thread starter Thread starter pRumpf
  • Start date Start date
P

pRumpf

Hi

I translate an prior application written in Clipper to C#....

The Application reads Cobol Files (EBCDIC, BCD numbers....) and inserts
the data into a rdbms. This part is no problm to realize in C#.

The original App. create SQL Statements as string/text and execute them
with the Database command-Line-Interactiv-SQL Tool (run iSql.exe
"...sql....") because there is no ODBC or OleDB Support in Clipper

e.g.
sql = "insert into t01.data (field1, field2, field3) values (200,
123.90, 'Bread' );
"insert into ......;"

....

Now my Question has onyone experience using OleDBParameter /
performance.

What is the better (speediest) way:

1) Creating the complete sql command as text string and execute (e.g.)

conn = new OleDbConnection( myConnectionString );
conn.Open();

// here is the loop overall Cobol records....
// the values (200, 123.90, Bread) comes out of the Cobol-Record
// to simplify the example:

string sql = "insert into t01.data (field1, field2, field3) values
(200, 123.90, 'Bread' );"
cmd= new OleDbCommand(sql, conn);
cmd.ExecuteNonQuery();

....

2) Using OleDbParameters

conn = new OleDbConnection( myConnectionString );
conn.Open();

string sql = insert into t01.data (field1, field2, field3) values
(?, ? , ?);"
cmd= new OleDbCommand(sql, conn);

// here is the loop overall Cobol records
//...
cmd.Parameters.Clear();
cmd.Parameters.Add("@p1", ...).Value = 200;
cmd.Parameters.Add("@p2", ...).Value = 123,90;
cmd.Parameters.Add("@p3", ...).Value = "Bread";

OleDbDataReader dr = cmd.ExecuteNonQuery();


3) maybe another way.... ?

Which way is the speediest. Has anyone experince ?

Thank you
 
It's not clear that you're using SQL Server. If you are, I suggest (again)
SqlBulkCopy.

--
____________________________________
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)
 
We do not use Microsoft SQL Server.
We use Sybase SQL Anywere and/or IBM DB2
I will not use special DB Functions but universal OleDB
 
Okay, even Sybase SQL Server supports a bulk copy utility (at least). A
generic solution will be very (very) slow compared to bulk copy.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------
 
Hallo
Yes your are right a bulk copy is the fastest way to bring data in the
database.
But I think a bulkcopy is a "external" Database tool, or a special
"load table" statement
(that is the way in Sybase, I think)

But this was not my course of action

My prg reads cobol files an do a lot of things, the db update is less
than 5% of the job....
The customer requirement is: doing "db things" direct with OleDb.... !

The qestion is symply: What is better / faster

1) create each time a SQL Statement as string, executing the statement
(OleDbCommand without OleDbParameter, just plain text).

or

2) create a OleDbCommand with placeholder in the statement , execute
the Command with OleDbParameters, "reuse" the OleDbCommand with "new"
OleDbParameters.
--> Is it a problem to "keep" a OleDbCommand for a longer time (hours)
? Stay the connection "open" while the Command is alive ?

thank you
Peter
 
1) BCP (bulk copy) can be an external utility, but it's built into ADO.NET
2.0 now when working with the SqlClient provider.
2) As to opening and closing connections, if you're working with a console
or Windows Forms application, there's no reason to close the connection
between operations.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top