[dba-VB] Access data across the internet

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Sat Aug 29 15:42:00 CDT 2009


Hi John,

I have got only a reference to MSDN:

"Web Services in Managed Code"
http://msdn.microsoft.com/en-us/library/xy59yt45.aspx

This MSDN entry and related ones have rather detailed and step by step
introduction into Web Services subject area.

Hamachi vs. Web Services: it depends - for me using Web Services look
quicker (no need to setup IIS and MS SQL for the sample I presented as it
runs on my ASP.NET hosting site - this sample deployed by XCOPY), more
flexible, scalable etc. To set wrapper activeX DLL (assuming all customers'
PC have target .NET Framework installed) is no more than run a .bat file. 

Of course learning how to use/develop Web Services takes some time. But this
learning will pay back very well - guaranteed.

Thank you.

--
Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, August 29, 2009 11:16 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] Access data across the internet

Thanks Shamil.

What is missing here is an explanation.

1) What IS a web service?
2) What are the pieces, and what do they do?  I assume that there is a
service and a client?
3) What is this web service doing?
4) Doesn't it seem that ...

getting IIS and SQL Server set up, writing web services, writing a DLL for
the client Access 
database, getting the existing system using the DLL and installing the DLL
on each machine...

doesn't all of that seem much more complicated than installing Hamachi and
mapping a shared 
directory to a drive letter?

Now, having said that, I have a future project which (if you would slow down
and explain just what 
the heck this stuff was) MIGHT be a perfect fit for this technology.  OTOH,
the future project is a 
system that I hope to sell to small drug companies.  Whatever this stuff is
has to be point and 
click installable as software at the client's site.

As I mentioned to Eric, I am listening, but so far I haven't understood a
word of what I am hearing.

Perhaps a reference to some book, or article that explains this stuff step
by step...?

Thanks,

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Yes, web service would be a good solution here.
> 
> I have made a test web service, which is located here:
> 
> http://shamils-4.hosting.parking.ru/MSAccess/MSAccessWebService.asmx
> 
> 
> You can test it using this code (c#):
> 
> using System;
> 
> namespace WebServiceDirectTestConsole
> {
>     class Program
>     {
>         //
>         // Add ASP.NET 2.0 web service reference:
>         //
> http://shamils-4.hosting.parking.ru/MSAccess/MSAccessWebService.asmx
>         //
>         static void Main(string[] args)
>         {
>             try
>             {
>                 const int CALLS_QTY = 10;
> 
>                 DateTime startTime = DateTime.Now;
> 
>  
> WebServiceDirectTestConsole.FileServiceSample.WebServiceFacade
>                     service = new
> WebServiceDirectTestConsole.FileServiceSample.WebServiceFacade();
>                 
>  
> //http://shamils-4.hosting.parking.ru/MSAccess/LoremIpsum.txt (50KB)
>                 string p = @"c:\temp\LoremIpsum.txt";
>                 string fileName = "testFile";
>                 string fileText = System.IO.File.ReadAllText(p);
>                 string text = null;
> 
>                 for (int i = 1; i <= CALLS_QTY; i++)
>                 {
>                     service.StoreFile(fileName + i.ToString(), fileText);
>                     text = service.GetFile(fileName + i.ToString());
>                 }
> 
>                 text = service.GetWebServiceUsageStatistics();
> 
>                 Console.WriteLine(text);
> 
>                 DateTime endTime = DateTime.Now;
> 
>                 double elapsedTime = ((TimeSpan)(endTime -
> startTime)).TotalSeconds;
> 
>                 Console.WriteLine("Elapsed Time = {0:#0.00} seconds for
{1}
> calls ({2}s/call, fileSize = {3})",
>                     elapsedTime, CALLS_QTY, elapsedTime / CALLS_QTY,
> fileText.Length);
> 
>             }
>             catch (Exception ex)
>             {
>                 Console.WriteLine(ex.Message);
>             }
>         }
>     }
> }
> 
> One of the results of the above test runs is the following:
> 
> Total Web Calls: 20
> Total Web Calls Duration: 20 ticks
> Total Store File Web Calls: 10
> Total Store File Web Calls Duration: 10 ticks
> Total Get File Web Calls: 10
> Total Get File Web Calls Duration: 10 ticks
> Total Delete File Web Calls: 0
> Total Delete File Web Calls Duration: 0 ticks
> Currently Stored Files: 10
> Currently Stored Files  Length: 503440
> 
> FILES
> -----
> fileName = testFile1, length = 50344
> fileName = testFile2, length = 50344
> fileName = testFile3, length = 50344
> fileName = testFile4, length = 50344
> fileName = testFile5, length = 50344
> fileName = testFile6, length = 50344
> fileName = testFile7, length = 50344
> fileName = testFile8, length = 50344
> fileName = testFile9, length = 50344
> fileName = testFile10, length = 50344
> 
> Elapsed Time = 6,80 seconds for 10 calls (0,6799s/call, fileSize = 50467)
> 
> 
> If you are interested we can make intensive test of this sample web
service.
> 
> (I personally would be interested in such testing.)
> I will make all its source code (simple) available on
> northwind.codeplex.com.
> This sample web service has already MS Access/VBA callable wrapper ActiveX
> DLLs.
> 
> Thank you.
> 
> --
> Shamil
> 
> P.S. Here is how the same web service can be called from MS Access/VBA
using
> ActiveX DLL wrapper library:
> 
> Option Compare Database
> Option Explicit
> 
> Public Function TestWebServiceAdv()
> Dim service As New MSAccessWebService
> Const CALLS_QTY As Integer = 10
>     Dim startTime As Date
>     startTime = Now
>     
>     '//http://shamils-4.hosting.parking.ru/MSAccess/LoremIpsum.txt (50KB)
>     Dim p As String
>     p = "c:\temp\LoremIpsum.txt"
>     
>     Dim fileName As String
>     fileName = "testFile"
>     Dim fileText As String
>     fileText = ReadAllText(p)
>     Dim text As String
>     Dim i As Integer
> 
>     For i = 1 To CALLS_QTY Step 1
>         service.StoreFile fileName + CStr(i), fileText
>         text = service.GetFile(fileName + CStr(i))
>     Next i
>     
>     Dim endTime As Date
>     endTime = Now
>     
>     text = service.GetWebServiceUsageStatistics()
>     Debug.Print text
>     
>     Dim elapsedTime As Double
>     elapsedTime = DateDiff("s", startTime, endTime)
>     Debug.Print elapsedTime & " seconds per " & _
>            CStr(CALLS_QTY) & " calls"
> End Function
>                 
> Private Function ReadAllText(filePath As String) _
>    As String
> Dim text As String
> Dim fn As Integer
> Dim fileLen As Long
>     fn = FreeFile
>     Open filePath For Input As #fn
>     fileLen = LOF(fn)
>     text = Input(fileLen, fn)
>     Close fn
>     ReadAllText = text
> End Function
> 
> One of the test calls results is the following:
> 
> Total Web Calls: 20
> Total Web Calls Duration: 20 ticks
> Total Store File Web Calls: 10
> Total Store File Web Calls Duration: 10 ticks
> Total Get File Web Calls: 10
> Total Get File Web Calls Duration: 10 ticks
> Total Delete File Web Calls: 0
> Total Delete File Web Calls Duration: 0 ticks
> Currently Stored Files: 10
> Currently Stored Files  Length: 503460
> 
> FILES
> -----
> fileName = testFile1, length = 50346
> fileName = testFile2, length = 50346
> fileName = testFile3, length = 50346
> fileName = testFile4, length = 50346
> fileName = testFile5, length = 50346
> fileName = testFile6, length = 50346
> fileName = testFile7, length = 50346
> fileName = testFile8, length = 50346
> fileName = testFile9, length = 50346
> fileName = testFile10, length = 50346
> 
> 8 seconds per 10 calls
> 
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Eric Barro
> Sent: Friday, August 28, 2009 5:54 PM
> To: 'Discussion concerning Visual Basic and related programming issues.'
> Subject: Re: [dba-VB] Access data across the internet
> 
> John,
> 
> This is a good candidate for a web service. A web service allows you to
> expose certain methods to client machines outside the network.
> 
> 1. Basically in terms of servers you will need a web server and a database
> server (one physical server is possible but two are better due to security
> issues).
> 2. The web server exposes the web service application and takes care of
> authentication to the database server. The web server is the only one that
> is publicly accessible from the outside.
> 
> This saves you from having to set up VPN client software on the client
> machines and also saves you from having to purchase hardware to run the
VPN
> on the server side.
> 
> Eric
> 
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Friday, August 28, 2009 6:11 AM
> To: VBA
> Subject: [dba-VB] Access data across the internet
> 
> I am looking at doing an application in C# that needs to be able to run on
> user systems around the country, but manipulate data in a common location.
> IOW employees can be anywhere, running this program on their machine, but
> reading / writing data to a central server.
> 
> It is a fairly simple application in terms of the data, a hand full of
> fairly stable and short list tables that feed combos, and a couple of
"log"
> kind of tables that document processes.  It would be nice to have access
to
> a fairly complex directory structure containing data files that need to be
> imported by and exported from this program.  Again these files are small,
> less than a thousand lines of data, fixed width or CSV.  I am looking at
the
> files now and the largest appear to be 80K or so.
> 
> I am thinking that a VPN tunnel to allow access to the data directories,
> which are then mapped to a drive on the local workstation.  Some kind of
> data store on the server, perhaps SQL Server Express. 
>   A local data store to do the import into, manipulation / cleanup of
data,
> export back to files on the remote server directory structure.
> 
> I am wondering if you guys have experience in setting up this kind of a
> server and application to work on such a server.
> 
> --
> John W. Colby
> www.ColbyConsulting.com
> _______________________________________________
> 
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com


__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4379 (20090829) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru






More information about the dba-VB mailing list