The SQL query is faster because it does not enforce security (nor does it get audited since the LF Server has no idea what you're doing). The Laserfiche Server caches data, so querying SQL directly is not guaranteed to give the same results as you would get through the SDK.

I don't know if this is possible at this point in your project, but if you use RepositoryAccess instead of LFSO, you can specify fields as columns to be returned in the search results entry listing. See the SearchListingSettings class in your SDK .Net documentation.

Edit: LFSO's LFSearchListingParams.AddTemplateFieldColumn allows you to specify fields to retrieve with your search results.

Ah, I didn't think about LF caching any recent metadata activity but I would also assume that there is not much latency.  What would be a ballpark on how long LF will cache metadata changes before writing them to SQL?

As far as enforcing security and recording activity in Audit Trail; I assumed that the framework for the search was Workflow, not an external application so those would be valid points in certain deployments.

The Laserfiche server caches data to avoid reads, but always writes through changes to SQL Server immediately. While this could potentially change in a future version, the above behavior has been the case for all versions of Laserfiche. As long as you don't modify the database directly, you shouldn't have to worry about caching effects.

Ok, that makes sense.  The points Miruna brings up about SQL queries bypassing audit trail and access rights are appropriate when developing an external application though.

Thanks so much for your help.  I'll first try RepositoryAccess. Where is this located?

Cliff, Can you help with some sql syntax? I have a ton of fields but something to get me started would be great... here are my fields:

            Name, 
            EntryType, 
            ID, 
            ParentID, 
            TemplateName, 
            DocType, 
            Sub_DocType, 
            Entity Name,
            Tags, 
            Extension, 
            Mime Type, 
            OCR'ed Pages, 
            Page Count, 
            Indexed, 
            Create Date LF, 
            Created By LF, 
            LastModified, 
            Volume, 
            Linked, 
            Version, 
            //GSCO Template Fields
            All Fields, 
            Date Range Type, 
            Doc - Date Range - Start, 
            Doc - Date Range - End,     
            Comments 1, 
            Entity Type, 
            Doc Category, 
            Doc Source, 
            Doc - Date Rec'd by GSCO, 
            Bates # - Start, 
            Bates # - End, 
            Department, 
            Client Name, 
            File Description, 
            Created by, 
            Date Printed, 
            Time Printed, 
            Barcode, 
            GSID, 
            //Correspondence Fields
            Subject, 
            Sender Name, 
            Sender Email, 
            To, 
            CC, 
            Comments1, 
            Received Date, 
            Received Time, 
            Dept

Jean,

As to your first question on Repository Access (RA); it is a available via the SDK by including the appropriate RA references in Visual Studio.  (Depending on what you want to do within Laserfiche it could be as simple as adding a reference to the 'Laserfiche.RepositoryAccess'  and/or the 'Laserfiche.DocumentServices' assemblies.)

As to the syntax for the SQL query.  Hopefully your search is limited to just a couple of metadata fields and not the entire list?   

I guess a good place to start would be what are you going to do with the resulting recordset from the search?  Do you want to end up with a list of entryID's that can be used to reference the actual Laserfiche entries that meet the search criteria? Or do you want to end up with a list of column data (like the native search function) that you can use to populate something like a listbox?

If i did the sql query, I would need that whole list.. 

In the end I need all of those columns with the entryIDs. I am basically creating a client that works like an excel sheet allowing more customized filtering.

Hmmm, trying to wrap my head around that one.  How would you display a list of entries in a table like that if the entries had different fields and/or templates assigned? 

Would it be something like each row would be an entry that matched the search criteria and then there would be a column for each metadata field that exists in the system.  If a particular field was assigned to the entry and contained a value then that value would be displayed in the appropriate cell?

Yes, that is correct! 

I can give it some thought if you are not in too much of a hurry.  My first thought would be to look at a SQL query that could return the appropriate data but it wouldn't be very efficient as you would have to step through the returned recordset to populate the grid.

The second thought (if you are using Visual Studio) would be a bit cleaner.  I would try to try to tie a Visual Studio datagrid to an appropriate datasource that would autopopulate the grid based on the appropriate search criteria.  That would probably be more efficient as the datagrid only pulls back displayed data (plus some overhead)  instead of the entire recordset.

Unfortunately in either case I don't think it is going to be simple.  Hopefully I haven't taken you down a rabbit hole with the SQL query idea!
 

haha.. thanks.. Before I take up your time with the Sql route.. Let me try to use the repository access. I will get back to you.

I am afraid that code is similar to what I was doing in the first place using getdatum which is what is taking so much time.

Here is a short SQL query to get some entry data that is somewhat based on your earlier query (This query is just returning the entryID, name, dateModified, and dateCreated.  Many other fields are available in the 'toc' table);

SELECT toc.tocid, toc.name, toc.modified, toc.created FROM propval
LEFT JOIN toc ON propval.tocid = toc.tocid  
LEFT JOIN propdef ON propval.prop_id = propdef.prop_id
WHERE propdef.prop_name = 'Client Name' AND propval.str_val = 'Drama, Johnny' ORDER BY toc.created

Without going into a lot of detail the 'toc' table is where most of the 'entry' data resides, the 'propdef' table contains the metadata field definition data (Field name, type, length, etc), and the 'propval' table contains the actual metadata values.

Speaking of the 'propval' table; the actual metadata value is only in the 'str_val' column for text type fields.  If the metadata field is a numeric then the field value is in the 'num_val' column and if it is a date then those values are in the 'date_val' column.  (Then there is the 'bin_val' column for large blob data like images!)

So even using the direct SQL query route you are going to have to go through a lot of gyrations to end up with an Excel type spreadsheet that you mentioned earlier.

That and all of the earlier warning about access rights, audit trail, and schema changes.

You sure you don't want to rethink your user interface?

Thanks Cliff!.. This works so fast and I think we can use it.. I will update you later.