Funduc Software Home

FSDBMgr - SQLite Database Manager

SITE GUIDE                          
 Home Page

Utilities
 Search & Replace
 Replace Studio Pro
 Replace Studio BE
 Directory Toolkit
 Registry Toolkit
 Resource Translation Toolkit
 Folder Synch
 File Merge Express
 Application Mover
 Application Inventory
 Duplicate File Finder
 Shortcut Doctor
 Explorer Extensions

Free Stuff
 PO Auto-Translator
 FS Database Manager
 FS Config
 FS Touch
 Decode Shell Extension  
 Count Characters
 Fold Synch Editor
 FS Code Format
 FS ZIP Extractor
 FSHED - FS Hex Editor
 TZAdjust
 SR Wizard
 WordSR
 ExcelSR
 PowerPoint SR

Courtesy Links
 Search Engines
 Shareware Links
 Commercial Friends

FSDBMgr Icon FSDBMgr - Funduc Software Database Manager is a free Customer and Purchase Manager with data import, search, email, and PDF invoice generation capabilities.

Background: Our Access database needed a revamp. Since Microsoft decided to push SQL Server and its lite version instead of Access, we started looking at alternatives. Our requirements were simple: the data should be stored in few files (if possible, a single file like Access); no requirement that a database engine be running at all times; low cost; SQL queries should be available for extracting data as well as database maintenance. SQLite is one of the most widely used database out there now and it met our requirements. SQLite Administrator, a nice administrative tool, is available for laying out a database schema and for running free-form queries on the data.

SQLite can be obtained from http://www.sqlite.org/. If you use the .Net interface (see below) it's included in that product.

SQLite Administrator can be obtained from http://sqliteadmin.orbmu2k.de. It's great for visually laying out a SQLite database schema and for running free-form queries on the data.

So we decided to pursue this avenue and develop a utility for performing our most frequent tasks as easily as possible.

The ADO.NET 2.0/3.5 Provider for SQLite .Net interface was our choice because of the productivity and ease of use of the .Net environment. See http://sourceforge.net/projects/sqlite-dotnet2 for downloads and information.

For invoice generation in .pdf format we used another piece of excellent open source code - Gios PDF .NET library by Paolo Gios. The PDF .NET library is available at http://www.codeproject.com/KB/graphics/giospdfnetlibrary.aspx.

The license we release FSDBMgr under is the GNU Lesser General Public License (LGPL).

Since our solution may benefit other small businesses, we decided to open source it and provide it and the code for free on the Internet. 

We do accept donations and can provide support and custom enhancements for reasonable rates. To inquire, please write to sales@funduc.com.

If you wish to submit fixes or enhancements, please contact us directly for now at support@funduc.com. We will include them in new versions if they will benefit others and will start a project on SourceForge if the need becomes evident.

To build and work with the source code, the SharpDevelop environment can be used for free from http://www.icsharpcode.net/OpenSource/SD/. The Microsoft Visual Studio is another option and there are others.

These were our requirements for the program.

We should mention that although we chose to use SQLite for our database the program itself can be easily modified to work with any database for which an ADO .Net provider is available.

Some screenshots of the demo program are available...

FSDBMgr by Funduc Software - Main Screen Tab FSDBMgr by Funduc Software - Import Tab

Some tasks you can accomplish:

  • Find a customer by Last Name: Enter the last name in the corresponding field of the Find Customer dialog. If a reseller or customer has no purchases yet, uncheck Show Purchases.
  • Find a customer given last name, email or street address, order number: Enter any portion of the information in the appropriate field (one item at a time) surrounded by %% characters (wildcards in SQL). If you enter multiple items, change the ANDs in the query to ORs. If a reseller or customer has no purchases yet, uncheck Show Purchases.
  • Find a purchase by date, product and/or expiration date: Use the Add Purchase button or menu and input the information.
  • Find a purchase by customer info or purchase note: Follow the Find Customer use case and check the Show Purchases box (on by default).
  • Add a new customer: Use the Add Customer menu, button and input the information.
  • Add a purchase: Find the customer in the database (may need to add them first), right-click the row in the query display and select Add Purchase.
  • Make a receipt or invoice: Follow the Find Purchase use cases above, right-click one of the rows in the query display and select Make Receipt/Invoice.
  • Add a frequently used custom query to the Queries menu: Save the query in a text file in the Queries subdirectory with a .sql extension and restart the program. The new query will show up on the menu.
  • Keep track of Sales Tax information: Tax Information is stored in the Purchases table. Very simple queries can be run and stored in the Queries directory for this purpose. Please contact us at support@funduc.com if you need more info on this.
  • Import purchase records from a file: Switch to the Import tab. Select the columns in the file in the correct order by dragging and dropping or using the buttons next to the column list. Enter the correct delimiter. Then respond to the prompts for Adding Users or adding Purchases to existing users.

Some of the useful functions and functionalities included in the source code and sample compile (C# but can be adapted to any .Net compatible language) include:

SplitQuoted();       /// Splits any string using separators string.
                          /// This is different from the string.Split method
                          /// as we ignore delimiters inside double quotes

CapitalizeWords(); /// Capitalizes the words in the string passed in

Drag and Drop within a column list as well as from another list of available columns.

Use of the IAutoComplete COM interface from .Net to allow text controls to remember a history of text entered during the program's execution. See FSControlsLib.

Use of OUTER JOIN to show customers whether they made purchases or not (for example resellers are not end customers and therefore will not get update notifications).

Building a menu dynamically from the list of .sql files in the Query subdirectory. This allows building a commonly used query library.

Creating a PDF document from database purchase records.

Reading ASCII files and properly translating them to Unicode using the user's code page.

Using MAPI to send email messages using Interop calls.

Using SMTP classes SMTPClient and MailMessage to send email messages.

Performing Mail Merge on a block of text and substituting data from the database for predefined (and configurable) tags. The tags can be optional, so if not present the entire block can be omitted. For example
"[$(Company)\r\n]" will only add the company name on a separate line if one is found in the customer record.

Localizing the user interface using satellite assemblies.

These can come in handy for other projects as well. We hope they save you some time!

Mike Funduc
President, Funduc Software Inc.
www.funduc.com www.searchandreplace.com

 

Recent Changes:

- Handling added to show defaults if config files in C:\Documents and Settings\<user>\Local Settings\Application Data\Funduc_Software_Inc._www dir do not exist.
- pdf receipt written to the Documents folder.
- A 'Manage Products' function was added (Queries Menu).
- Additional error reporting during data import added.
- New toolbar functionality & layout.
- Added handling for an 'Order Number' field.
- Added a configurable 'export' function to export data from one or more records to a plain text file.
- Added functionality to send email messages with a mail merge syntax via MAPI or SMTP.
- Minor tweak to automatically load the first few sales records if the database and query files.
- Find Purchases allows entering date or a date range for Purchase Date and/or Expiration Date.
- Add Help Menu with Home Page link and About Box.
- Localized user interface now available, including prompts and error messages. Italian and French translations included with the demo
version of the program. See program source for compiling other languages

 

Downloads

 

FSDBMgr.zip (812 K)

Compiled version with a sample database and all necessary files to see a sample in action. Unzip all files to the path of your choice and run 'FSDBMgr.exe'. See 'UsageNotes.txt' for additional information, including a description of the files in FSDBMgr.zip. The zip also contains example .NET localization for French and Italian. If your system is set for French or Italian, FSDBMrg will use the respective strings in the interface.

 

FsDbMgrSrc-052610.zip (187 K)    

Source code.

 

setupdbmgr.exe (1.2 MB)

Self-installer that installs the sample compiled version, sample database, and source code.


[Back] [Home]

© 1995-2014 Funduc Software. All rights reserved.
http://www.funduc.com
http://www.searchandreplace.com
Send comments to webmaster@funduc.com