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

microsoft.public.dotnet.framework.adonet

13 Dec 2006 4:57 PM

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

13 Dec 2006 5:40 PM

William (Bill) Vaughn

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

<pRu***@procom-gmbh.com> wrote in message

Show quote

news:1166029033.768744.45140@l12g2000cwl.googlegroups.com...
> 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
>

13 Dec 2006 6:04 PM

pRumpf

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

William (Bill) Vaughn schrieb:

Show quote

> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> <pRu***@procom-gmbh.com> wrote in message
> news:1166029033.768744.45140@l12g2000cwl.googlegroups.com...
> > 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
> >

13 Dec 2006 10:30 PM

William (Bill) Vaughn

Okay, even Sybase SQL Server supports a bulk copy utility (at least). A
generic solution will be very (very) slow compared to bulk copy.

Show quote

news:1166033067.016460.8560@l12g2000cwl.googlegroups.com...
> 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
>
> William (Bill) Vaughn schrieb:
>
>> 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)
>> -----------------------------------------------------------------------------------------------------------------------
>>
>> <pRu***@procom-gmbh.com> wrote in message
>> news:1166029033.768744.45140@l12g2000cwl.googlegroups.com...
>> > 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
>> >
>

19 Dec 2006 9:38 AM

pRumpf

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

William (Bill) Vaughn schrieb:

Show quote

> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> <pRu***@procom-gmbh.com> wrote in message
> news:1166033067.016460.8560@l12g2000cwl.googlegroups.com...
> > 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
> >
> > William (Bill) Vaughn schrieb:
> >
> >> 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)
> >> -----------------------------------------------------------------------------------------------------------------------
> >>
> >> <pRu***@procom-gmbh.com> wrote in message
> >> news:1166029033.768744.45140@l12g2000cwl.googlegroups.com...
> >> > 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
> >> >
> >

20 Dec 2006 12:27 AM

William (Bill) Vaughn

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.

Show quote

news:1166521096.824993.292740@79g2000cws.googlegroups.com...
> 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
>
>
>
>
>
>
> William (Bill) Vaughn schrieb:
>
>> 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)
>> -----------------------------------------------------------------------------------------------------------------------
>>
>> <pRu***@procom-gmbh.com> wrote in message
>> news:1166033067.016460.8560@l12g2000cwl.googlegroups.com...
>> > 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
>> >
>> > William (Bill) Vaughn schrieb:
>> >
>> >> 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)
>> >> -----------------------------------------------------------------------------------------------------------------------
>> >>
>> >> <pRu***@procom-gmbh.com> wrote in message
>> >> news:1166029033.768744.45140@l12g2000cwl.googlegroups.com...
>> >> > 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
>> >> >
>> >
>