Dear Chuck:
I am about to work on a new project
that is developing a desktop Loan Origination application that uses .Net
technology because of strong XML support in .Net.
This application has a large users, between 250 to 1000, so deployment and upgrade is one of main concern. This application is installed on each user machine, most users will use a laptop, there is no internet connection required when the user uses the application. After a loan application is complete, then the user will connect to our web site to submit the loan application
There are two designs for this application.
- Because the complexity of home
loan application, some developers suggest me to use MSDE as local storage, when
the loan application is complete, then convert the data into XML and submit to
our web site.
The plus of this design is to use some existing code that already uses SQL 2000 database, also the programming against MSDE is very similar to SQL Server.
The disadvantage of this design is to deploy MSDE to so many remote users, I have to manage application upgrade/update over the time, also have to update any database changes
- Another option is not to use any
local database, instead to store loan application locally as XML and application
UI is built on top of the XML file. The difficulty of this design is when the
user starts an loan application, there is no much information stored in XML
file, as the user provides more information, this XML file
gro until all information
is collected.
The disadvantage of this approach is development time and to code class to work with XML file, instead of database. As I mentioned, because this loan origination application has its internal logic, so querying against XML is not as easy as querying SQL.
I would like to get some opinion from you and show me some direction.
Chuck:
I wish I heard this type of
question more often as it indicates developers are writing smart client
applications that are capable of working when an internet connection is
unavailable and offer a richer user experience than what HTML is capable of
supporting. In either case you listed your client-side data options as being one
of two choices:
- A Local MSDE installation; with the issue being installation and maintenance of a local store
- A Local XML Data Store; with the perceived issue being the difficulty and time involved of writing this code.
I don't have enough facts and context to suggest what is the best solution for your application but I can look at what you think the challenges are and perhaps offer some suggestions.
With the local MSDE solution you mentioned database schema changes as one of your primary concerns; here at Microsoft we run a CRM in which the setup program gives the user a choice of using a remote data store or a combination of a remote and local data store based on MSDE. I have yet to meet anybody that uses this CRM with just the remote data store (particularly in Australia where we access the servers located in California). In the last 3 years we have gone through numerous smaller schema changes and 2 larger changes. The smaller changes where shielded from the client side application and larger changes where used as an excuse to upgrade the clients - something that needed to be done regardless of a schema change. All of our 1000+ sales and marketing folks rely on this application and my friends on the development team indicate neither schema changes nor application upgrades were the largest challenge but rather MDAC versions of the SQL drivers. This application was pre .NET Framework which has resolved this issue with its managed SQL Client data provider that is version tied to the Framework the application was written to.
This brings us to your second option of using a local XML data store. You mention this option as less than ideal due to the additional development time to code to a local XML file. What a lot of people don't realise is that CODING TO ADO.NET IS CODING TO XML.
This means whether you get your datasets from a SQL Server or a local XML file doesn't change your application design. This is very easy to illustrate with a Windowsapplication and a Datagrid. The code below loads a dataset from a SQL Server, saves the contents of the dataset to a file then prompts the user if they would like to load the file version of the data or SQL Server version.
Good luck with your loan application, I hope my input and sample code will assist with your development efforts.
Happy Coding -Chuck
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim sqlConnection As New System.Data.SqlClient.SqlConnection("user id=sa;data source=WDNS;initial catalog=Northwind;password=xxx")
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("SELECT [Customers].* FROM [Customers]", sqlConnection)
Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)
Dim ds As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(ds)
Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter("C:\TestFile.txt")
sw.Write(ds.GetXml) '//place the xml into the text file
sw.Close()
If MsgBox("Run in Offline Mode?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
ds.Clear()'//simulate no connection to server by clearing the dataset
ds.ReadXml("C:\TESTFILE.TXT")
End If
DataGrid1.DataSource = ds.Tables(0) '//load datagrid to display
End Sub
