Exchange Data between SQLCE and SQL database on laptop?

[rockdale]

Hi, All:

We have an Window CE application which stores data in SQLCE. We need to
transfer those data in SQLCE back to laptop which has a MSDE (MS SQL
Desktop Engine). The PDA will be sitted in the cradle when the
transfer happens. No WiFi available between the laptop and PDA.

We used webservice to handle the data exchange but now we find out that
our user does not want the IIS is running on the laptop because
security issues.

We checked Remote Data Access and Replication options but these two
options also need IIS installed on the laptop.

Is there any options that do not use IIS. All I read from Microsoft
website is using IIS.

I know we can export the data from SQLCE to text file and then send the
text file to the laptop and let the laptop read the text file and
import the data. But this may not be a good choice 'cause this will
require the user start the read text file and import function on the
laptop. Besides, the text file need to be encrypted.

I know there is System.Data.SQLClient in .NET CF, can this be used to
access the MSDE on laptop without the IIS server?

Thanks a lot

 

[Ginny Caughey [MVP]]

Yes you can use SqlClient to connect from the mobile device to MSDE on the
laptop. I haven't tested that particular configuration, but I have tested
using SQL Express, and the main issue is that you have to enable tcp/ip
which is disabled by default. You could also consider a sockets-based
approach if the client doesn't want IIS running.

 

[rockdale]

Thanks for your reply. So what you are saying is I can sit the PDA in a
cradle and connect to the USB port of laptop. Then use SQLClient to
connect to the MSDE on the laptop and execute sql to retrieve data and
save data into the MSDE database?
Another question, what and where to enable the TCP/IP? on the PDA or
somewhere else?

Thank you very much

 

[Ginny Caughey [MVP]]

You have to enable tcp/ip on the SQL Server using SQL Server Management
Console. (It may be enabled by default with MSDE - I don't remember - but is
is not enabled by default with SQL Expres.) You also may need to specify the
port# for the SQL server, and you should specify a static port. Then you can
connect to the server from the device using the IP address and port. If the
laptop uses DHCP, you'll need to get the IP address using ipconfig.

 

[rockdale]

Hi, Ginny

Thanks for your reply. I tried using ConnectionString =
"Server=10.10.10.15;Database=Database_On_LapTop;uid=userName;pwd=Password;",
It works and I can retrieve data from the SQL Database from LapTop. The
only PROBLEM is what if the LapTop does not connect to the network,
then the LapTop does have an ip address, in this case, the PDA can not
build the database connection anymore. The laptop is not always in the
network, actually the laptop is in a truck and connect back to
station's database through wireless network. So most time the laptop is
standalone. If there is no workaround for a standalone laptop can you
explain more on the sockets-based approach.

Thanks a lot

 

[Ginny Caughey [MVP]]

You might still have a usable IP address on the laptop when it is not
connected to the network, but it will be a different IP address than when it
is connected. Even using sockets, you still need to know the IP address, but
if you want to pursue that approach, this is one of my favorite samples
because it shows so many useful techniques:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/PPCSignatureApp.asp
In this particular case the data transferred is a signature, but it could be
modified to send any type of data.

 

[rockdale]

Thanks a lot.

I had refered to the signature example before. I also have a signature
capture function in my PDA application. It looks like the I need the
IPaddress no matter connect to database or write to socket.
Just another thought, can I export the data from SQLCE into text file,
copy over to a directory on the laptop. Is this copy over need
IPAddress also? then on laptop, running another windows application
read those text file and import into SQL database. Of course, this
approach does make any sense if copy over still need to know the
IPAddress.

It is kind hard to explain to customer that why we need the laptop on
the network 'cause there is a USB connection between the cradle and
laptop.

Again, thanks a lot
-Rockdale

 

[Ginny Caughey [MVP]]

Rockdale,

One way to do what you want using Activesync is to write a desktop app that
uses Rapi. (There's a .NET Rapi class on www.opennetcf.org in the
Desktop.Communication section.) You'd use the blocking function CeRapiInit,
and when that function returns you know you have a connection. At that point
you could call CeCreateProcessEx to launch an app running on the device side
that creates the text file, once the text file is created, you can then copy
it from the device to the laptop from the Rapi app - doable but a certain
amount of work.

I think you might still have an easier time of it using SqlClient or sockets
and trying to connect using a variety of IP addresses. When the laptop is
not connected to a network (when it's in the truck) it should have one IP
address that you can count on. If you need to also be able to run the app
when the laptop is connected, then you need some way of telling the app what
that IP address is unless it will be a static IP address. You could use code
like this to get the IP address of the Activesync-connected laptop:

System.Net.IPHostEntry ipHostEntry =
System.Net.Dns.GetHostEntry("PPP_PEER");
IPAddress[] ipAddress = ipHostEntry.AddressList;
string hostComputerIP = ipAddress[0].ToString();

Unfortunately this probably isn't the IP address that the SQL Server is
listening on for using in the connection string to SqlClient, but it would
probably work fine for a sockets-based approach. If you're targeting a WM
5.0 device, you could use the WindowsMobile State and Notification Broker to
tell your app when you have a network connection.

 

[rockdale]

Hi, Ginny

System.Net.IPHostEntry ipHostEntry =
System.Net.Dns.GetHostByName("LAPTOP_NAME");
System.Net.IPAddress[] ipAddress = ipHostEntry.AddressList;
String hostComputerIP = ipAddress[0].ToString();
ConnectString = "Server=" + hostComputerIP +
";Database=DB_NAME;uid=username;pwd=password;";
//ConnectString = "Server=localhost" + hostComputerIP +
";Database=DB_NAME;uid=username;pwd=password;";

you are right, I can get the IPAddress when laptop is disconnected but
the IPAddress is not the IPAddress that SQL server is listening on.

How can I find the IPAddress that a SQL server is listening on when the
computer is disconnected?

I will do more research

Thanks
-Rockdale

 

[Ginny Caughey [MVP]]

Rockdale,

I don't know how to get the IP address that SQL Server is listening on from
the device side. Maybe someone knows... I have even tried forcing SQL Server
to listen on the 169.254.2.1 address that Activesync is using, but that
doesn't work for me either.