William (Bill) Vaughn's Musings

A recent question on the newsgroups asked about the life-expectancy of DAO and if it makes sense to transition to ADO to access their database.

The problem here is not DAO or even ADO classic (COM-based ADO). The problem here is the JET engine.
When you write an application using JET whether you you Access or VB6 or VB.NET or any other language, you are limited by the capacity and architecture of the shared-file architecture of JET. Your security is limited by JET's inability to be encrypted and since by its very nature it's designed to be passed around as a file and accessed by whomever receives it, it should not contain private data. Sure, there are lots of situations where a DBMS as simple as JET is okay--as in the home or for some non-secure office applications. But IMHO, JET is really not suitable for businesses that need to pass manage secure data. We've talked about and debated the merits and foibles of JET for many years here on this newsgroup and in other forums.

The next question that invariably gets asked is what are the migration paths? Should one transition to SQL Server or SQL Server Everywhere or some other DBMS? What about the applications? Will they run faster on SQL Server or other engines? There is no simple answer here. Applications, if they are written well, take advantage of the engine's strengths and work around its limitations. Most folks have considerable time and talent invested in JET and the applications written to access the data. Sadly, much of this logic has to be redesigned to take advantage of the strengths and limitations of the target DBMS. Can SQL Server be faster than JET? Only perhaps. For small databases, SQL Server can be cumbersome. SQL Server Everywhere (SSEv) is better suited for lightweight applications and yes, I expect it is faster than JET for these applications but again, you'll have to reengineer your application and the skills you use to get to it in order to transition to its use. On the other hand, for applications that have to serve more than one user (SSEv is not designed for that) and have to scale to dozens or hundreds of users, SQL Server is ideal. It's well supported by the tools, documentation and third-party books. But again, you'll have to re-write your application to interface with SQL Server. DAO is not ADO is not ADO.NET and that's just the tip of the sword. The features and benefits of SQL Server are very different than those exposed by the now obsolete JET engine.
hth

Posted by William Vaughn at 08:39 AM | Permalink | Comments (0)

I'll be working with the folks at SetFocus to present an 8-hour seminar on ADO.NET 2.0 Architecture and Best Practices. It's one of the "The Guy Who Wrote the Book" series. Basically, the attendees can be anywhere on the planet (except parts of Cleveland). They run Polycom's PVX (or somesuch) software on their systems--using a web-cam to show their face to the instructor (me). On my end I see all of the students (limited to about a dozen) so I can see if they have questions or have fallen asleep.
This talk is similar to the session I give at major conferences--but updated with the latest on SQL Server Everywhere and other emerging technologies. Given the small class size, there will be plenty of time for individual Q&A. I’m sure you’ll enjoy it. Know someone who needs this? Get signed up soon they have just opened registration and there aren’t many seats available.

Posted by William Vaughn at 01:41 PM | Permalink | Comments (0)

Another question came up in the newsgroups today that's been asked (and answered) before so to short-circuit having to repeat myself, here is a blocl of code that can be used to enumerate the providers on a system and the services that the provider can see on the network. In other words, it lists the SQL Server instances on the network and puts the list in a DataGridView.

This code is extracted from an example in my new book Hitchhiker's Guide to Visual Studio and SQL Server due to be on the streets in early November. See www.hitchhikerguides.net for more information.

Private Sub ShowServerInstances()
' List Providers
Dim tblProviders As Data.DataTable = DbProviderFactories.GetFactoryClasses
DataGridView2.DataSource = tblProviders
lblStatus.Text = "Located providers... searching for servers."
lblStatus.Refresh()
' List Server Instances for a selected provider
If Not My.Computer.Network.IsAvailable Then _
MessageBox.Show("Local area network not enabled... only local instances visible")
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim dsE As DbDataSourceEnumerator = factory.CreateDataSourceEnumerator
Dim dtInstances As DataTable = dsE.GetDataSources
DataGridView1.DataSource = AddServiceName(dtInstances)
End Sub
Shared Function AddServiceName(ByVal dtList As DataTable) As DataTable
If dtList Is Nothing Then Return Nothing
Dim colService As New DataColumn("ServiceName", GetType(String))
dtList.Columns.Add(colService)
Dim colState As New DataColumn("Status", GetType(String))
dtList.Columns.Add(colState)
For Each dr As DataRow In dtList.Rows
dr("ServiceName") = DBNull.Value
If Not IsDBNull(dr("Version")) Then
dr("ServiceName") = "MSSQL$" & dr("InstanceName")
Else
dr("ServiceName") = "MSSQLSERVER"
End If
dr("Status") = GetStatusForService(dr("ServiceName"), dr("ServerName"))
Next
Return dtList
End Function

Posted by William Vaughn at 04:50 PM | Permalink | Comments (2)

The Question:

I am writing a database application for use in disconnected situations. The application will be installed on laptops. The intention is for the users to connect to the central SQL Server 2000 database, get the data, then go into the field and use it. They may be disconnected for several days. They data needs to persist through application closings, power offs, etc. When they are done, they will reconnect and their changes will be sent back to the database. I am new to .NET, so am looking for some advice on how to best approach this. Thanks <name withheld>

My Answer:

This is a fairly common question for those new to .NET. Yes, ADO.NET is designed around a “disconnected” approach, but it’s designed for use with ASP (networked) applications that run under IIS. The DataTable and the DataSet objects that hold them disappear when the application is shut down—they are simply an in-memory data store. No, that does not mean that ADO.NET can’t work with an independent database architecture where data needs to be persisted between queries to the host—which is what you’re describing. It does, however suggest that you might need a local DBMS of some kind to persist and manage the data while the laptops (the client systems) are not connected to the LAN. A number of alternatives come to mind that you might consider. Fortunately, many of them are free—they can be implemented with royalty-free software. You might not need a DBMS at all if the data is not that complex and security is not an issue. Using a DBMS means you can write applications that can execute SQL queries and use the power of the DBMS to do what it’s designed to do: manage your data without you having to replicate its functionality in your own code. Here are a couple of ideas that might help:

·         Design around SQL Server Express edition for your laptops. This approach leverages the full-featured SQL Server engine that has all the flexibility you’ll ever need—except for one feature: security. Deployed SQL Server databases can be easily opened and accessed once the physical system is compromised (stolen from a car or a hotel room). This means sensitive data must be encrypted and decrypted on a column-by-column basis. SQL Server Express is easier than ever to install and it’s managed just like SQL Server—using the same tools. This architecture is described in my new book Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)  which should be available in early November.

·         Design around SQL Server Everywhere edition. This approach uses the DBMS engine formerly known as “SQL Mobile” (and SQLCE). SQL Server Everywhere (SQL Ev) implements a subset of ANSI SQL and (IMHO) is very different than SQL Server in many respects. However, it is fully securable and the entire database can be encrypted by simply setting a password. I think it’s a great solution for data you have to take to the field. It’s limited in capacity (4GB) but can outperform other DBMSs because its functionality is so limited. Microsoft is gearing up to fully integrate SQL Ev with the Visual Studio tools to make creating and managing the databases far easier.

·         Consider using replication to keep your client databases in sync with the host server. Both SQL Server Express and SQL Ev can be a Subscriber to a SQL Server (Workgroup or better) Publication. The code to set this up could not be called “easy” but it’s manageable and probably worth the effort. I would shy away from having to create my own home-grown replication software and infrastructure. I’m writing a new EBook on SQL Server Everywhere and it includes a long section on how to setup and implement replication. It should be available sometime in October.