SQLClient dropping connection on sending large data across GPRS

[Paul Bell]

Hello

I am developing an app that uses SQLClient to connect to a desktop SQL
Server 2000 database over a GPRS/VPN link.

I can transfer records containing text successfully, but when I try to
send an image (40k to 60k), sometimes it is successful, and sometimes
it fails, for no apparent reason with the same images.

My code uses a sqlcommand and fills parameters:


Dim sclTrsMasterUpdate As System.Data.SqlClient.SqlTransaction
Dim a_bytBLOBData() As Byte
Dim sclComUpdate As System.Data.SqlClient.SqlCommand

sclComUpdate = MasterDB.sclConMasterData.CreateCommand
' SET THE TIMEOUT TO SOMETHING BIG
sclComUpdate.CommandTimeout = 1200

sclComUpdate.CommandText = "INSERT INTO Attachment_TBL " _
& "( attAlias_TXT " _
& ", attDescription_MEM " _
& ", attFile_BLOB " _
& ", attOriginalFileName_TXT " _
& ", attClientAdded_BLN ) " _
& "VALUES " _
& "( @attAlias_TXT " _
& ", @attDescription_MEM " _
& ", @attFile_BLOB " _
& ", @attOriginalFileName_TXT " _
& ", @attClientAdded_BLN ) "

sclComUpdate.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@attAlias_TXT",
System.Data.SqlDbType.NVarChar, 50))
sclComUpdate.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@attDescription_MEM",
System.Data.SqlDbType.NText, 16))
sclComUpdate.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@attOriginalFileName_TXT",
System.Data.SqlDbType.NVarChar, 50))
sclComUpdate.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@attClientAdded_BLN",
System.Data.SqlDbType.Bit, 1))

sclComUpdate.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@attFile_BLOB",
System.Data.SqlDbType.VarBinary))

' THIS CALL FILLS a_bytBLOBData WITH THE IMAGE FILE PATH GIVEN IN
uldUmaArgs.drwActiveAttachment("ialOriginalFileName_TXT")
If MUCO.Image.blnGetImageData(uldUmaArgs.drwActiveAttachment("ialOriginalFileName_TXT"),
a_bytBLOBData) Then
sclComUpdate.Parameters("@attFile_BLOB").Value = a_bytBLOBData

sclComUpdate.Parameters("@attAlias_TXT").Value =
uldUmaArgs.drwActiveAttachment("ialAlias_TXT")
sclComUpdate.Parameters("@attDescription_MEM").Value =
uldUmaArgs.drwActiveAttachment("ialDescription_MEM")
sclComUpdate.Parameters("@attOriginalFileName_TXT").Value =
uldUmaArgs.drwActiveAttachment("ialOriginalFileName_TXT")
' TODO:
sclComUpdate.Parameters("@attClientAdded_BLN").Value = False

Try
sclTrsMasterUpdate =
MasterDB.sclConMasterData.BeginTransaction
sclComUpdate.Transaction = sclTrsMasterUpdate

'MUCO.MyMessages.dlrInfobox("Sending attachment")

sclComUpdate.ExecuteNonQuery()

sclTrsMasterUpdate.Commit()

'MUCO.MyMessages.dlrInfobox("Sending attachment ok")
Catch excSqlClient As System.Data.SqlClient.SqlException
'MUCO.MyMessages.dlrInfobox("Sending attachment error")

' CHECK THE MASTER CONNECTION HAS DROPPED
Try
sclTrsMasterUpdate.Rollback()
Catch excGeneric As Exception
End Try
End Try

sclTrsMasterUpdate.Dispose()
Else
'MUCO.MyMessages.dlrInfobox("Cannot find an attachment file for
transfer." & vbNewLine _
'& "The attachment may be on a removed storage card or has been
deleted.", Windows.Forms.MessageBoxIcon.Exclamation)
End If

I need to use transactions as I am doing other stuff aswell, but this
snippet gives an idea of what I am doing. What happens is the
connection never drops, but I often get a SqlException of general
network error after a few minutes after calling ExecuteNonQuery(),
with no other information as to why it failed.

Does anybody know what might be happening? Can SQLClient be used
effectively like this, or should I use RDA or maybe a web service? I
would prefer not to go down the IIS route as it means a lot more work
and deployment issues.

Thanks

Paul

 

[Darren Shaffer]

Paul,

Using SQLClient from a device connected only by GPRS is a high-risk idea.
SQLClient
depends on a highly reliable connection to SQL Server, and even with 802.11x
you need to perform connection checking before using it to ensure smooth
operation.

In my experience with large deployments of devices connected only via GPRS,
a mixture
of RDA and custom web services replication works best given bandwidth and
stability
of the connection. I have also tried using Merge Replication over GPRS and
this is also
problematic if you have any significant amount of data requiring
replication.

FYI, I am giving an MSDN webcast on March 2nd to cover this in much more
detail.

Darren Shaffer
Principal Architect
Connected Innovation