HOW TO: Delete data in SQL Using VB.NET And XPO

There are many ways to delete data in SQL, but the problem is that none of them are a silver bullet type answer. The bottom line is to delete a table you want to execute a truncate table against the table to get the fastest speed. Unfortunately if you have any foreign keys related to this table you cannot do that. The next best thing is to execute a delete from statement against the table, but that can be somewhat costly.

In XPO, there are two methods which you can use to delete objects from within a database. The first is calling a delete against an object, and the second is calling the Session.Delete method and pass in an object or collection of objects. Both of these statements use deferred deletion which can speed up your delete process if you are targeting specific records. If this it not a good option (which usually it isn’t) than the next best thing is to generate paged SQL calls. This is what a paged SQL delete might look like using XPO:

Dim strSql As String = "DELETE FROM [Order] WHERE Oid In ("
            Dim sshSession As New Session
            Dim xpcOrders As New XPCollection(Of Order)(sshSession)
            For intLoop As Integer = 0 To xpcOrders.Count - 1
                strSql &= String.Format("'{0}',", xpcOrders(intLoop).Oid.ToString)
                If intLoop Mod 100 = 0 OrElse intLoop = xpcOrders.Count - 1 Then
                    strSql = String.Format("{0})", _
                                         strSql.Substring(0, strSql.Length - 1))
                    sshSession.ExecuteNonQuery(strSql)
                    strSql = "DELETE FROM [Order] WHERE Oid In ("
                End If
            Next

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>