Page 1
Page 2
Page 3
.NET Compact Frameworks & the
ADO.NET Data Adapter
So you've been toying with jumping on
the .NET cf bandwagon for months now right? Get with the
program! .NET is definitely the way to go. For me it has
been a tremendous learning curve to embrace and it is pretty
the process is pretty much ongoing. In many ways one of
the biggest challenges to .NET is coming to terms with the
myriad of ways that you can architect solutions. eVB is
pretty limiting and this makes things pretty easy from the
perspective that there is often only one way to do it. I
also got very tired of the amount of work around code required
to build menus or tabs, work with XML etc., so welcome to
the future - .NET Compact Framework - sure it's not the
universal panacea to all mobile development but it is a
huge improvement.
One of the biggest challenges in adapting
to .NET is getting to grips with the new data access methodology
- ADO.NET. OLE DB providers are no longer used, instead
they have been usurped by .NET managed providers
which are used to manage data access between the data source
and your application. This may seem like an extension of
the OLE dB provider approach but that is where the similarity
ends. Under the hood the implementation is very different
and COM has been removed from the equation. Server-side
cursor support has been dropped in ADO.NET owing to the
server performance and locking penalties; consideration
being cast rather in favor of forward-only, read-only resultsets.
So we have established that ADO.NET
is definitely the subject of whole tomes of information;
before you continue too far down this path you need to have
a look at Larry Roof's article called Smart
Device Extensions, SQL Server CE, and Me - this will
give you a great overview of the SDE environment and the
basics of SQL Server CE.
Now
we get to the fun part. I was putting together a tutorial
on how to use combo-boxes in .NET CF and was generating
some XML to use as test data. Actually the original idea
here was to do some perforce testing on datasets when I
lost focus and got onto the combo-box idea (more about this
next week!). So my plan was that I would provide an XML
file in the tutorial and then instantiate the SQL Server
CE database from the XML. This is where the flexibility
of the .NET data adapters and data sets really
becomes apparent. So what is a dataset you ask. Think
of it as your private in-memory data storage object. You
can utilize one dataset populating it from different
date sources, setting up relationships between
the data tables it contains and then write the changes
back to their respective sources. Another thing you can
do is create a dataset from one source and then use
a data adapter to write it to another source essentially
using the data adapter as an update bridge. All this pretty
easily too.
Remember to delete the devbuzztest.xml
file from the Windows directory on your device after
you have run the attached app - it will save you just under
a 100k.
Form Load
On the form load event we create an
instance of the dataset object as well as instantiating
a a new SQLHelper object.
data = New DataSet()
sql = New SQLHelper()
The SQLHelper class has the usual
suspects providing connectivity and SQL CE error checking,
as well as some additional functionality like the TableExists
function:
Function TableExists(ByVal
pTableName As String) As Boolean
Dim SQL As String = "SELECT TABLE_NAME FROM "
& _
"INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '"
& _
pTableName & "'"
If ceConnect() Then
Dim sqlReader As SqlCeDataReader
Dim sqlCmd As New SqlCeCommand(SQL, connCE)
Try
sqlReader = sqlCmd.ExecuteReader
Catch err As SqlCeException
ErrorDisplay(err)
Catch err As Exception
MsgBox("There was an error: " & err.ToString())
End Try
If sqlReader.Read Then
'found the name?
TableExists = True
Else
TableExists = False
End If
sqlReader = Nothing
sqlCmd = Nothing
Disconnect()
End If
End Function
Next Page