VB.NET CF query remote SQL server.

[Dade]

Im new to the VB.NET stuff but I am looking for a way to query a remote SQL
database from a pocket PC application. I have done this in the past with VB6
in wondows using data environment disigners but I dont believe that this is
supported with the compact framework.

Any help or sample code would be great!

 

[Paul G. Tobey [eMVP]]

I don't think that I have anything in VB, but you can use SQL Server CE and
Remote Data Access (RDA), to get data from an SQL Server desktop database.
You basically need to do the following:

1. Create an SqlConnection with a connect string which specifies the machine
on which the SQL Server instance is running, the database name, and, if
necessary, the user name and password to use.

2. Use objects like SqlCommand, SqlDataAdapter, and DataSet to perform
queries or whatever. Here's the C# card to perform a query defined in an
EDIT control called QueryEdit, where sqlconnect is an SqlConnection
instance, and ResultGrid is a DataGrid:

SqlCommand cmd = new SqlCommand( QueryEdit.Text,
sqlconnect );

DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

ResultGrid.DataSource = ds.Tables[ 0 ];

Paul T.

 

[Saul]

Suggested reading : Larry Roof and "The definitive guide to the .NET Compact
Framework", by Apress. It's a complicated topic that you best learn out of a
book. Setting up Replication or RDA is a pig, it involves IIS on the server,
and the SSCESA20.DLL (I kid you not).

I have remote access to SQL server database working, using RDA. RDA Pull
gets the data via stored procedures. It creates the tables locally without a
fuss, it's cool. Just add your indexes afterwards.

I use the rda.submitsql to upload data, rather than the push method. The
reason I like this is that I can work out what needs to be uploaded on the
device, and then call stored procedures with parameters using this command.
One performance tip though, build a string with multiple calls, and execute
that.

eg
' Slower
rda.submitsql("exec myinsert 1", cn)
rda.submitsql("exec myinsert 2", cn)
....

'Faster
rda.submitsql("exec myinsert 1 ; exec myinsert 2 ; ...", cn)

Obviously there is some sort of string length limit here to watch out for (I
don't know what that is). At least, this is what I have found! Others may
disagree, but I hope what I have said helps.

Regards,
Saul

 

[Paul G. Tobey [eMVP]]

I'm not running IIS on my SQL Server machine and my RDA application still
works fine with it...

Paul T.