DataPort Web

Sometimes transferring data quickly and safely can be a major task. In the following example I’ll show you how you can do a parameterized insert and how to use the sql transaction. This will speed up your insert tremendously.

More detailed info on the sql transaction class:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

In the following example I loop through a dataset to insert the data back to my sql db. Its only three columns, but I’ve inserted 1.2 million records in less than 15 seconds

Dim cnn As New system.Data.SqlClient.SqlConnection("Connstr")
Dim da As New System.Data.Sqlclient.sqlDataAdapter

cnn.Open()
Using mytransaction As System.Data.Sqlclient.sqlTransaction = cnn.BeginTransaction
Using mycommand As System.Data.Sqlclient.sqlcommand = New System.Data.Sqlclient.sqlcommand(cnn)

                da.InsertCommand = mycommand
                Dim myparam As New System.Data.Sqlclient.sqlParameter
                Dim myparam1 As New System.Data.Sqlclient.sqlParameter
                Dim myparam2 As New System.Data.Sqlclient.sqlParameter

                Dim i As Integer
                mycommand.CommandText = "INSERT INTO [YourTable] (Col1,Col2,Col3) VALUES (?,?,?)"

                mycommand.Parameters.Add(myparam)
                mycommand.Parameters.Add(myparam1)
                mycommand.Parameters.Add(myparam2)

                For i = 0 To ds1.Tables(0).Rows.Count - 1
                                myparam.Value = Replace(ds1.Tables(0).Rows(i).Item(0).ToString, "'", "''")
                                myparam1.Value = Replace(ds1.Tables(0).Rows(i).Item(1).ToString, "'", "''")
                                myparam2.Value = Replace(ds1.Tables(0).Rows(i).Item(2).ToString, "'", "''")
                                mycommand.ExecuteNonQuery()
                Next 

                mytransaction.Commit()

            End Using
        End Using

        cnn.Close()

I've also tried this with sqLite and it works great! 

Please let me know what you think!

Other questions? Post it here on our Forum

I found the following method very useful in filtering large amounts of data with a datagridview.
In the following example I have a datagridview that populates +- 300 000 records and a textbox. And when I type something in the textbox I want the datagridview to filter out as I type.

Here's how

Private Sub txtSearch_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged

Dim dv1 As New Data.DataView
dv1 = New DataView(yourdataset.Tables("yourTable"))
dv1.RowFilter = "FieldName" & " LIKE '" & txtSearch.Text & "*'"
datagridview1.DataSource = dv1

End Sub

Other questions? Post it here on our Forum

After months of searching.. No luck for getting the colouring and intellisense to work for classic asp in visual studio 2008
Some users say it works perfect.. MS says they dropped support for that in visual studio 2008

So far the only solution that worked for me is to install visual studio 2005 for classic asp projects

Hope I'm missing something here..