I am a winner :-)

Hi Brian,

Here you have a code using the ADO.NET OleDb Provider and StreamWriter that
is 6 to 8 times faster than your ADODB method:

Sub Reward100(ByVal dbname As String)

'the first person to provide me with a ADO.net based function that
will insert the 30,000 records into the database
'as shown in the other functions of this class will receive a
cashier check for US $100. There is only one reward
'no other external references aloud

'reward code goes here
'Test inserting 30000 records into an Access database table using
StreamWriter and ADO.NET.
'This function is 6 to 9 times faster than the ADODB method

Dim x As Integer
Dim d As Date

Dim FormatProvider As IFormatProvider =
Globalization.CultureInfo.GetCultureInfo("en-US")
Dim writer As New StreamWriter("Table1.txt")

'insert records into a file text'
For x = 1 To 30000
d = DateAdd(DateInterval.Day, x, #1/1/1925#)

writer.Write("M1") : writer.Write(vbTab)
writer.Write(d.ToString("d", FormatProvider)) :
writer.Write(vbTab)
writer.Write(x) : writer.Write(vbTab)
writer.Write(x ^ 2) : writer.Write(vbNewLine)
Next
writer.Close()

'create Schema.ini
writer = New StreamWriter("Schema.ini")
writer.WriteLine("[Table1.txt]")
writer.WriteLine("Format = TabDelimited")
writer.WriteLine("ColNameHeader = False")
writer.WriteLine("Col1 = name Text Width 50")
writer.WriteLine("Col2 = recdate DateTime")
writer.WriteLine("Col3 = num Single")
writer.WriteLine("Col4 = num2 Single")
writer.Close()

'insert records into Access Database
Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & dbname & ";Persist Security Info=False"
Dim cn As New OleDbConnection(conString)

Dim query As String = "INSERT INTO Table1 " & _
"SELECT name, recdate, num, num2 FROM [Table1#txt] " & _
"IN '" & Application.StartupPath & "' 'Text;';"

Dim cmdInsert As New OleDbCommand(query, cn)
cn.Open()
cmdInsert.ExecuteNonQuery()
cn.Close()
End Sub

Regards from Madrid (Spain)

Jesús López
VB MVP

"bdwest" <bdwest@xxxxxxxxxxxxxxxxxxxxxxxxx> escribió en el mensaje
news:EADFAF2B-D5E9-4255-A5DF-7AA836066D7F@xxxxxxxxxxxxxxxx
>I am sponsoring a contest to see if someone can come up with ADO.net code
>to
> insert records into a MS Access 2003 database faster than ADODB methods
> (MS
> ActiveX Data Object 2.x library). My experiece has shown that I can write
> records to Access at least 4 times faster using ADODB methods in a Visual
> Studio 2003/2005 project as compared to any ADO.net methods. I want to
> see
> if this is due to my ignorance or a problem with ADO.net and Access 2003
> databases.
>
> From the link below, there is a test project available that has five
> methods
> of writing to an Access database, an ADODB method and 4 versions of
> ADO.net
> methods. The time to write 30,000 records is timed for each method. The
> code is written in VB.net.
>
> There is a $100 reward to the first person who can use ADO.net methods
> that
> beat the ADODB methods working within the confines of the rules. Note
> there
> is only one reward.
>
> Please see the following link for full details.
>
> http://www.kelbli.net/kb1/AdoNetContest.html
>
> Thanks
>
> Brian
>
>
>
>
>


.

Relevant Pages

  • RE: I am a winner :-)
    ... There is only one reward ... >> insert records into a MS Access 2003 database faster than ADODB methods ... >> records to Access at least 4 times faster using ADODB methods in a Visual ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: I am a winner :-)
    ... There is only one reward ... >> insert records into a MS Access 2003 database faster than ADODB methods ... >> records to Access at least 4 times faster using ADODB methods in a Visual ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO.net Code contest
    ... > code to insert records into a MS Access 2003 database faster than ... > ADODB methods. ... are a special case in data-access. ... but general purpose data-access code can't do ...
    (microsoft.public.dotnet.framework.adonet)