Upload table mysteriously deletes all records

maxxBit

I am using .NET client library and VB.NET as my language. I have multiple tables that expect uploaded file per row. I have created a separate table UploadedFiles with key EntryID.

I want to use this UploadedFiles table to be image metadata repository for multiple tables. But somehow very strangely the entire UploadedFiles table is getting deleted after adding 5-10 records. I can not find any option to debug SQL being executed.

This is my controller -

Public Class AdminResponsibilityController
    Inherits ApiController
    <Route("api/AdminResponsibility")>
    <Authorize>
    <HttpGet>
    <HttpPost>
    Public Function AdminResponsibility() As IHttpActionResult
        Dim request = HttpContext.Current.Request


        Dim ApplicationID As Integer = Library.LoginFunctions.GetUserdataFromAuthCookie().EntityKey

        If ApplicationID > 0 Then
            Using db = New Database(GlobalConfig.DataTablesEditorDBType, GlobalConfig.DBConnectionString)

                Dim response = New Editor(db, "AdminResponsibilities", "EntryID")
                response.Model(Of DataLayer.Models.AdminResponsibility)() _
                .TryCatch(True) _
                .Field(New Field("ApplicationID").Set(Field.SetType.Create).SetValue(ApplicationID)) _
                .Field(New Field("Activity").Validator(Validation.NotEmpty()).Validator(Validation.MinLen(3))) _
                .Field(New Field("UploadID") _
                            .SetFormatter(Format.NullEmpty()) _
                            .Upload(New Upload(GlobalConfig.UploadPath & "\AdminResponsibilities\__ID____EXTN__") _
                                .Db("UploadedFiles", "EntryID", New Dictionary(Of String, Object) From {
                                                                {"FileName", Upload.DbType.FileName},
                                                                {"FileSize", Upload.DbType.FileSize},
                                                                {"MimeType", Upload.DbType.MimeType},
                                                                {"SystemPath", Upload.DbType.SystemPath},
                                                                {"WebPath", Upload.DbType.WebPath},
                                                                {"ApplicationID", ApplicationID},
                                                                {"TableName", "AdminResponsibilities"}
                                }) _
                                .DbClean("UploadedFiles.EntryID", Function(data)
                                                                      'For Each row In data
                                                                      '    System.IO.File.Delete(row("SystemPath").ToString())
                                                                      'Next
                                                                      Return False
                                                                  End Function))
                 ) _
                .Where("ApplicationID", ApplicationID) _
                .IdPrefix("AdminResponsibilities_") _
                .Process(request) _
                .Data()

                Return Json(response)
            End Using

        Else
            Return Content(HttpStatusCode.BadRequest, "Invalid ApplicationID")
        End If

    End Function


End Class

Another controller will have the same code only with replacement of "AdminResponsibility" to "BoardResponsibility" like so

Public Class BoardResponsibilityController
    Inherits ApiController
    <Route("api/BoardResponsibility")>
    <Authorize>
    <HttpGet>
    <HttpPost>
    Public Function BoardResponsibility() As IHttpActionResult
        Dim request = HttpContext.Current.Request


        Dim ApplicationID As Integer = Library.LoginFunctions.GetUserdataFromAuthCookie().EntityKey

        If ApplicationID > 0 Then
            Using db = New Database(GlobalConfig.DataTablesEditorDBType, GlobalConfig.DBConnectionString)

                Dim response = New Editor(db, "BoardResponsibilities", "EntryID")
                response.Model(Of DataLayer.Models.BoardResponsibility)() _
                .TryCatch(True) _
                .Field(New Field("ApplicationID").Set(Field.SetType.Create).SetValue(ApplicationID)) _
                .Field(New Field("Activity").Validator(Validation.NotEmpty()).Validator(Validation.MinLen(3))) _
                .Field(New Field("UploadID") _
                            .SetFormatter(Format.NullEmpty()) _
                            .Upload(New Upload(GlobalConfig.UploadPath & "\BoardResponsibilities\__ID____EXTN__") _
                                .Db("UploadedFiles", "EntryID", New Dictionary(Of String, Object) From {
                                                                {"FileName", Upload.DbType.FileName},
                                                                {"FileSize", Upload.DbType.FileSize},
                                                                {"MimeType", Upload.DbType.MimeType},
                                                                {"SystemPath", Upload.DbType.SystemPath},
                                                                {"WebPath", Upload.DbType.WebPath},
                                                                {"ApplicationID", ApplicationID},
                                                                {"TableName", "BoardResponsibilities"}
                                }) _
                                .DbClean("UploadedFiles.EntryID", Function(data)
                                                                      'For Each row In data
                                                                      '    System.IO.File.Delete(row("SystemPath").ToString())
                                                                      'Next
                                                                      Return False
                                                                  End Function))
                 ) _
                .Where("ApplicationID", ApplicationID) _
                .IdPrefix("BoardResponsibilities_") _
                .Process(request) _
                .Data()

                Return Json(response)
            End Using

        Else
            Return Content(HttpStatusCode.BadRequest, "Invalid ApplicationID")
        End If

    End Function


End Class

What am I doing wrong? I think somewhere down the line the sql executed is "DELETE FROM UploadedFiles;" without any EntryID parameter. How can I trace the sql and/or log it?

allan

You can add .Debug(true) just before the .Process(request) in order to have the server-side return the SQL it is generating and executing.

My initial guess would be the .DbClean() call - if you disable that, does the problem stop happening?

Allan

maxxBit

Dear Allan

Yes, indeed. DBClean was incorrectly deleting all records when file was overwritten. I have removed the call and I am using SQL job to clean orphan records at a schedule.

Thanks!