public bool DeleteDocument(string documentId)
        {
            var result = false;
            var sql    = "UPDATE " + CABINETID + "_IMAGES SET DELETED='Y' WHERE FILENAME='" + documentId + "'";

            result = FireBirdHelper.GenericCommand(sql, this.CabinetConnectionString);
            return(result);
        }
        public bool Add(FileItDocument doc)
        {
            ExceptionHelper.LogDebug("In Add Cabinet Document...");
            var           result = false;
            StringBuilder sql    = new StringBuilder();

            int fileSize = 501;

            //add the document to the database
            sql.Append("INSERT INTO " + CABINETID + "_IMAGES ");
            sql.Append("(");
            sql.Append("FILENAME, DELETED, PAGENUMBER, INDEXEDON, EXTENSION, ARCHIVED, FILESIZE, PUBLIC, VERSION_NO, VERSIONID, USERID, STATIONID, BATCHNO");
            doc.Indexes.ToList().ForEach(i =>
            {
                sql.Append(", INDEX" + i.Key.ToString());
            });
            sql.Append(") VALUES (");
            sql.Append("'" + doc.FILENAME.Replace("'", "''") + "',");
            sql.Append("'" + (doc.DELETED ? "Y" : "N") + "',");
            sql.Append("'" + "1" + "',");
            sql.Append("'" + doc.INDEXEDON.ToString("MM/dd/yyyy") + "',");
            sql.Append("'" + doc.EXTENSION.ToString().Replace("'", "''") + "',");
            sql.Append("'" + doc.ARCHIVED.ToString().Replace("'", "''") + "',");
            sql.Append("'" + fileSize.ToString() + "',");
            sql.Append("'" + (doc.PUBLIC ? "Y" : "N") + "',");
            sql.Append("'" + doc.VERSION_NO.ToString().Replace("'", "''") + "',");
            sql.Append("'" + doc.VERSIONID.ToString().Replace("'", "''") + "',");
            sql.Append("'" + doc.USERID.ToString().Replace("'", "''") + "',");
            sql.Append("'" + doc.STATIONID.ToString().Replace("'", "''") + "',");
            sql.Append("'" + doc.BATCHNO.ToString().Replace("'", "''") + "'");
            ExceptionHelper.LogDebug(sql.ToString());
            doc.Indexes.ToList().ForEach(i =>
            {
                //i.Value.ToString().Replace("'", "''")
                sql.Append(", " + FireBirdHelper.FormatValue(i.Value));
            });
            sql.Append(")");

            //var t = sql.ToString();
            ExceptionHelper.LogDebug("SQL Insert:" + sql.ToString());

            if (FireBirdHelper.GenericCommand(sql.ToString(), CabinetConnectionString))
            {
                //save the file
                var folderPath = doc.AddImageFileFolderPath(OSTOREPATH);
                //make sure there are no extra . in the name
                var filePath = System.IO.Path.Combine(folderPath, doc.FILENAME + (doc.EXTENSION.IndexOf('.') > -1 ? "": ".") + doc.EXTENSION);
                if (!Directory.Exists(folderPath))
                {
                    Directory.CreateDirectory(folderPath);
                }
                FileHelper.Base64ToFile(filePath, doc.ImageBase64);
                result = true;
            }
            return(result);
        }
        private void CreateTables()
        {
            var cabSQL = "CREATE TABLE " + CABINETID + "_IMAGES" + " (" +
                         " FILENAME VARCHAR(8) NOT NULL," +
                         " EXTENSION VARCHAR(4) NOT NULL," +
                         " ARCHIVED VARCHAR(3)," +
                         " INDEXEDON TIMESTAMP NOT NULL," +
                         " PAGENUMBER SMALLINT NOT NULL," +
                         " FILESIZE INTEGER," +
                         " PixelType VARCHAR(10)," +
                         " PixelDepth VARCHAR(10)," +
                         " Resolution VARCHAR(10)," +
                         " PixelSizeX VARCHAR(25)," +
                         " PixelSizeY VARCHAR(25)," +
                         " Orientation VARCHAR(10)," +
                         " Storageid VARCHAR(1) DEFAULT 'A', " +
                         " Side VARCHAR(1) DEFAULT 'A', " +
                         " PUBLIC VARCHAR(1) DEFAULT 'N', " +
                         " VERSION_NO INTEGER DEFAULT 0, " +
                         " CHECKEDOUT VARCHAR(1) DEFAULT 'N', " +
                         " VERSIONID VARCHAR(20) DEFAULT '', " +
                         " ALTSTORAGE VARCHAR(1) DEFAULT '', " +
                         " DELETED VARCHAR(1) DEFAULT 'N', " +
                         " USERID  VARCHAR(20) DEFAULT '', " +
                         " STATIONID	VARCHAR(20) DEFAULT '', "+
                         " BATCHNO	VARCHAR(20) DEFAULT '' ";

            //get template definition
            var idx = 1;

            Template.TemplateDefinitions.ToList().ForEach(td =>
            {
                switch (td.DATATYPE.ToLower())
                {
                case "date":
                    cabSQL += ", INDEX" + idx + " DATE";
                    break;

                case "number":
                    cabSQL += ", INDEX" + idx + " INTEGER";
                    break;

                default:
                    cabSQL += ", INDEX" + idx + " VARCHAR(" + td.MAXLENGTH + ") NOT NULL";
                    break;
                }
                idx++;
            });

            //now add primary key
            cabSQL += ",     Primary Key(FILENAME)     );";

            FireBirdHelper.GenericCommand(cabSQL, CabinetConnectionString);
        }