HOW to import/export data FROM/TO web server database

[Zvi]

I have a question.

I have an SQL database residing on a web server:

Server: sql2a.somesite.co.il
database: DBname,


I have an offline VB.NET program, residing on my computer, getting data from users and saving them in variables (no database), on computers with internet connection.

Scenario:
The database residing on the web server will have 2 tables:
1. users
2. user_data

Task:

I need to specify a connection from the offline program residing on my computer TO the Online database which resides on the web server, so that when the user starts the program, the program will initialize its variables from the Online server's database, as well as UPDATING the online database with the NEW values being input by user during the usage of the program.

I can't use ANY form of database on my offline VB.NET program because of security issues on the network of the users, so any ideas about saving data offline, other then plain variables in the offline program (which will of course be lost when program is closed... and that's why the program will periodicaly update the data inputs TO the online database) will not work. (maybe an XML file...?)

* I have administrative rights to the Online SQL database.

How do i go about doing it?

Notes:

1. The "users" table has the userID, username, userpassword
2. The "users_data" table has data fields.
3. When a user starts the Offline program, I will also have to think of a way that the Online database will know which user is logging on, so it will get and set only HIS data. (Either by pre-entering users myself and adding a checking code, or using windows authentication... which are also 2 things I don't know how to do but I will research it.)

Thank you.

 

[Gruntie776]

Coding the data access to a remote Internet hosted SQLServer is easy - it's more or less identical to any LAN server. It would be however a security nightmare. You will either have to expose the server IP ports directly (bad idea) or establish some sort of VPN (complex).

The best way to do this would be to implement the data access code on the server as a web service rather than in your application. Your application would pass the data to the web service which would take care of the data storage. The advantage of doing it this way is that web services operate over http on port 80 so there should be no connectivity issues and the application can be secured .

As for caching data locally when offline that is another can of worms - but implementing the above should make it unnecessary.

 

[Zvi]

re:

yes, I will go with a webservice. But I didn't understand the last line you wrote... "As for caching data locally when offline that is another can of worms "

Are you reffering to the fact that my program will be saving data with variables before transfering the data to the webservice (and then to the Online database)?

Because I don't want the data to be transfered to the webservice at real-time because it would be VERY time consuming, since the data updates itself every second! I thought that I would make the program update the webservice every 5 minutes or so.

What do you think?

 

[Gruntie776]

Well . . . in my opinion if the application is written properly there will be an insignificant difference in the speed between a remote server and a LAN server. Adding additional functionality makes the application much more complex and I don't believe it will give you much benefit - unless you have a specific need for disconnected use (for example sales people out on the road all day connecting their laptops to be updated periodically).

Of course this is my gut reaction and I am speaking in general terms.

The simple case is for for application architecture to be:

GUI -> Data Abstraction + Validation -> {internet} -> Webservice -> Database

to implement caching you will need two concurrent processes:

GUI -> Data Abstraction + Validation -> Temp Data
and
Temp Data -> Data Process -> {internet} -> Webservice -> Database

then you have the difficulty of what happens when the user queries the data and you need to provide results from the database and the temp data store?

I would urge you to try the connected version first and evaluate its performance before trying out local caching.

 

[Zvi]

You are correct

yes, I totaly agree with you. With my program though, it's so simple that I think it will be ok.

1. The only data is time and time calculations. No real data proccessing.
2. The only time the user would need to query the data is if he does a query on how much time he spent on the phones in the last couple of days. In this case, I can make that the program sends any temp data to the web service before giving him the results. Most of the program is just recording his work time, shift time and break time. This is what I ment that the program does proccessing every second... he just calculates the seconds every second. (yeh, I know... I can make a start time at begining of shift and end time at end of shift and just send time caculation once... but sometimes there are things that the program does after a certain amount of seconds like warning messages and alerts.... so every second the program is calculating the times.)

 

[Gruntie776]

Ok - in that case go for it. Let me know if you need help with the webservice.

 

[Zvi]

be carefull...

Be carefull what you wish for.... )))

I will be working and researching on this webservice for the next 2 days!

I will have questions.... but feel free to tell me ENOUGH! )

Thanks for the help!!

 

[Zvi]

How to publish my Web Service?

ok, I made a simple Web Service just to try out my skills.

All it does is adds two integers:

<WebMethod()> _
PublicFunction add(ByVal x AsInteger, ByVal y AsInteger) AsString
Return x + y
EndFunction

When I add this web service to a Windows application, it works fine. (via http://localhost/....)

Now, I want to try to publish it to my ISP and try to add the web service with a full http://... but it's not working and I'm sure I'm doing something wrong.

What I did was upload the folder of my Web Service via FTP to my site.
Now when I try to add this ONLINE web service to my windows app, I get the following error:

Parser Error Message: Could not create type 'Service'.
Source Error:
Line 1: <%@ WebService Language="vb" CodeBehind="/App_Code/Service.vb" Class="Service" %>
I tried all different kinds of CodeBehind="/App_Code like:
CodeBehind=".App_Code
CodeBehind="http://somsite.com/App_Code
etc..

How do I use it Online?

Thanks!

 

[Zvi]

Resolved!

This is what I found, did, and it works!

***************
(http://pluralsight.com/blogs/keith/archive/2005/06/01/9698.aspx)

Thanx i've been trying to sort this for an age.
Something so simple is always the answer.

Don't make the app_code or bin the subdir of the directory that the asmx is in, make it the subdir of the virtual root.

Hard to beleive that MS makes it work like that, but who can trust the fevered mind of a Microserf .
****************

If this doesn't work for someone... go to that web page... there are other solutions there as well.

 

[Zvi]

Connection String not Connecting...

I'm trying to connect to my Online server via a webservice. This is my code:

Dim sqlConn As New SqlConnection( "Server=sql2a.adcd.co.il; Database=****; UId=******; Pwd=*****;")
sqlConn.Open()

...... (more code)

*********************
(Of course the name and password are correct in my code)

(Of course, on the top I added: Imports System.Data Imports System.Data.SqlClient)

I am getting the following error when executing this code:

System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)


I KNOW that it's POSSIBLE to connect to my server since the FTP programs and the MySql-Front program connect to my server.

What am I doing wrong?

 

[cjard]

Don't make the app_code or bin the subdir of the directory that the asmx is in, make it the subdir of the virtual root.

Hard to beleive that MS makes it work like that, but who can trust the fevered mind of a Microserf .

I'd assume there is some security advatnage to insisting that application code can only appear at one point in a server file system.. one place to secure and that's it. Whereas if you had possibility to run app_code in other places then by clever use of uploading you could run all kinds of naughty software..

 

[cjard]

I KNOW that it's POSSIBLE to connect to my server since the FTP programs and the MySql-Front program connect to my server.

What am I doing wrong?

huh?Since when has FTP been used to connect to sql server?

This error message is telling you that SQL Server itself, by default, does not allow connections from anywhere other then the local machine.

It may not be the source of your problem, as you can get this message from other situations too, but:
Ensure that your SQL Server is running
Ensure that it accepts TCP and Named pipe connections from local and remote hosts
Ensure that security is set appropriately
Ensure that no firewalls are in place

Starting the sql server browser service on the server will make it easier for your client to find a running sql server

 

[Zvi]

Fixed

I just switched the code to MySql instead of Sql. All works. My server was using MySql.

 

[cjard]

You mean you've been using the database all this time and didnt know it was a MySQL server? (Or if you did.. you need to say in one of your posts!)

SqlClient is only for Microsoft SQL Server
OracleClient is for Oracle
OleDbClient is for any of the usual OLE databases, like access, or ODBC stuff

If you use a database not in this list, you should install the relevant driver dlls etc for that database

 

[Zvi]

yes...

that's what I did. relevant dll's and switch connection strings.

Well, I never accessed the database directly through code, so I never needed to know what kind of database it was. I only hosted a Forum on the database, which was a free php coded form that self intstalled itself (phpBB).