[dba-VB] Access data across the internet

jwcolby jwcolby at colbyconsulting.com
Sat Aug 29 20:21:21 CDT 2009


http://www.hanselman.com/blog/BreakingAllTheRulesWithWCF.aspx

Read all the comments at the bottom.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> 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
> 
> 
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 



More information about the dba-VB mailing list