EDN Admin
Well-known member
<span style="font-size:9pt; line-height:115% Im developing my first SQL Server database app (for 30+ users) and could REALLY use some advice particularly because the app’s design is pretty complex so please bear with the long explanation.
<span> My app eventually will display an <span style="text-decoration:underline
always (hopefully) up to date list of files (and some related metadata) for a variety of purposes (open file, modify file’s metadata, etc).<span>
The users deal with (open files, modify metadata, etc) dozens if not hundreds of files everyday so developing an app that making it all super quick and easy would really save company time in our environment over the course of a year.
<span style="font-size:9pt; line-height:115% <span style="font-size:9pt; line-height:115%
<span style="font-size:9pt; line-height:115% The list is always enormous so my plan at the moment is to never close the app once it’s running.<span>
The app will have schemes (TBD) for updating the list (modified data, new files & deleted files).<span>
It takes approx. 45-60 seconds to load all the data which would make my app unusable if I were to allow the user to close the app.
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115% The design gets complicated because the list is a merging of two folder structures that share
<span style="text-decoration:underline some relative folder structures and files.<span>
The main list comes from a document management system (DMS) and the secondary list comes from a specific local folder structure (e.g. C drive).
<span> As with many DMSs, the user cannot modify documents directly in that system.<span>
Instead the user checks the document out (i.e. retains owership and gets a local copy), makes modifications with the files native sofware (editor) then checks the document back in to the DMS (i.e. uploading a new version and gives up ownership).<span>
My DMS is configured by the installer to map (relate) a local folder to the root folder in the DMS thereby creating a relationship between the two structures so the DMS client knows where to download the files to, compare the DMS version against the
local version of a file to display info (e.g. an icon depicting the DMS file is newer than the local) to the user in the DMS client app, etc.<span>
When a file is downloaded from DMS, its <span style="text-decoration:underline
relative folder structure (from the root folder in DMS) is replicated locally beneath mapped local root then the file is copied.<span>
To eliminate confusion in listing essentially the same file twice (the same file in different locations - DMS vs. local), i.e. which files does the user want?<span>
I will have some logic in my app to determine which duplicate to remove (DMS or local) and if the duplicate that
<span style="text-decoration:underline remains in the list happens to be a DMS file (i.e. newer than the local copy) then if for instance the user wants to open the file with the files native application then my app knows to first download the file
from the DMS locally before opening it with the app associated to the file extension.<span>
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115% <span>
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115% The idea in general is to have an always up to date list that can locate the desired file(s) extremely quickly either by data filtering controls or if the user places focus on the DataGridView
control then types the first few characters at the beginning of the desired file name to scroll the list to that file.<span>
The app offers a few commands currently to execute on the file (open, etc) and more will added I’m sure.<span>
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115% What I need from you is ideas on keeping the list up to date.<span>
I’ve read all the related threads to “Multithreading in Visual Basic .NET” on MSDN yesterday which seems to be exactly what I need (i.e. background processing).<span>
Multithreading appears to be a pretty complex subject and my app is as well so I’m figuring that the Multithreading needs to be set up in a very specific manner (plus I also can already see some gotchas based on the Multithreading documentation) so I
could really use some pointers.<span>
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115%
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115%
<span style="font-size:9pt; line-height:115% I’m using SqlDataAdapter, DataSet & BindingSource with a DataGridView control (is there a better choice?).<span>
In the Form Load event I start by populating the the DataSet with the latest version of every file in DMS then I iterate the list of local files (using GetPatternedFiles) comparing them to their DMS counterpart (if exists) to decide
which one to keep so the list contains only one instance of the file regardless of location (a main concept on my design).
<span> The query in DMS is from a View (i.e. dynamic Table) of the latest version of all files.<span>
I don’t believe the associated SQL Server database has triggers (events) that I could add to my app in order to somehow automatically update the data so instead I’m expecting to poll on a regular basis and/or when the user activates my already running
app. <span> It would be fantastic if it could be configured to somehow automatically update the DMS files (modified, new & deleted) based on some sort of dynamic link but I’m guessing polling is the only way (please tell me differently)
plus the DataSet is a mix of data populated via DataSet/SQL query and local file manually added (new rows) to the DataSet (i.e. local files that are not in DMS yet).<span>
I’m thinking of using a FileSystemWatcher to update the local files listed (does this need it own thread, I would think so?).
<span> If polling is the way for the DMS files then please give me some Multithreading configuration advice.<span>
I haven’t given the DMS polling much thought at this point but I’m thinking that I can programmatically compose query(ies) to only look for changes since the last polling (deleted files [there might still be a local file], new files, new version of an
existing file and its related data [i.e. new version was checked in since the last polling]).
<br/>
View the full article
<span> My app eventually will display an <span style="text-decoration:underline
always (hopefully) up to date list of files (and some related metadata) for a variety of purposes (open file, modify file’s metadata, etc).<span>
The users deal with (open files, modify metadata, etc) dozens if not hundreds of files everyday so developing an app that making it all super quick and easy would really save company time in our environment over the course of a year.
<span style="font-size:9pt; line-height:115% <span style="font-size:9pt; line-height:115%
<span style="font-size:9pt; line-height:115% The list is always enormous so my plan at the moment is to never close the app once it’s running.<span>
The app will have schemes (TBD) for updating the list (modified data, new files & deleted files).<span>
It takes approx. 45-60 seconds to load all the data which would make my app unusable if I were to allow the user to close the app.
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115% The design gets complicated because the list is a merging of two folder structures that share
<span style="text-decoration:underline some relative folder structures and files.<span>
The main list comes from a document management system (DMS) and the secondary list comes from a specific local folder structure (e.g. C drive).
<span> As with many DMSs, the user cannot modify documents directly in that system.<span>
Instead the user checks the document out (i.e. retains owership and gets a local copy), makes modifications with the files native sofware (editor) then checks the document back in to the DMS (i.e. uploading a new version and gives up ownership).<span>
My DMS is configured by the installer to map (relate) a local folder to the root folder in the DMS thereby creating a relationship between the two structures so the DMS client knows where to download the files to, compare the DMS version against the
local version of a file to display info (e.g. an icon depicting the DMS file is newer than the local) to the user in the DMS client app, etc.<span>
When a file is downloaded from DMS, its <span style="text-decoration:underline
relative folder structure (from the root folder in DMS) is replicated locally beneath mapped local root then the file is copied.<span>
To eliminate confusion in listing essentially the same file twice (the same file in different locations - DMS vs. local), i.e. which files does the user want?<span>
I will have some logic in my app to determine which duplicate to remove (DMS or local) and if the duplicate that
<span style="text-decoration:underline remains in the list happens to be a DMS file (i.e. newer than the local copy) then if for instance the user wants to open the file with the files native application then my app knows to first download the file
from the DMS locally before opening it with the app associated to the file extension.<span>
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115% <span>
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115% The idea in general is to have an always up to date list that can locate the desired file(s) extremely quickly either by data filtering controls or if the user places focus on the DataGridView
control then types the first few characters at the beginning of the desired file name to scroll the list to that file.<span>
The app offers a few commands currently to execute on the file (open, etc) and more will added I’m sure.<span>
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115% What I need from you is ideas on keeping the list up to date.<span>
I’ve read all the related threads to “Multithreading in Visual Basic .NET” on MSDN yesterday which seems to be exactly what I need (i.e. background processing).<span>
Multithreading appears to be a pretty complex subject and my app is as well so I’m figuring that the Multithreading needs to be set up in a very specific manner (plus I also can already see some gotchas based on the Multithreading documentation) so I
could really use some pointers.<span>
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115%
<p style="margin-bottom:6pt <span style="font-size:9pt; line-height:115%
<span style="font-size:9pt; line-height:115% I’m using SqlDataAdapter, DataSet & BindingSource with a DataGridView control (is there a better choice?).<span>
In the Form Load event I start by populating the the DataSet with the latest version of every file in DMS then I iterate the list of local files (using GetPatternedFiles) comparing them to their DMS counterpart (if exists) to decide
which one to keep so the list contains only one instance of the file regardless of location (a main concept on my design).
<span> The query in DMS is from a View (i.e. dynamic Table) of the latest version of all files.<span>
I don’t believe the associated SQL Server database has triggers (events) that I could add to my app in order to somehow automatically update the data so instead I’m expecting to poll on a regular basis and/or when the user activates my already running
app. <span> It would be fantastic if it could be configured to somehow automatically update the DMS files (modified, new & deleted) based on some sort of dynamic link but I’m guessing polling is the only way (please tell me differently)
plus the DataSet is a mix of data populated via DataSet/SQL query and local file manually added (new rows) to the DataSet (i.e. local files that are not in DMS yet).<span>
I’m thinking of using a FileSystemWatcher to update the local files listed (does this need it own thread, I would think so?).
<span> If polling is the way for the DMS files then please give me some Multithreading configuration advice.<span>
I haven’t given the DMS polling much thought at this point but I’m thinking that I can programmatically compose query(ies) to only look for changes since the last polling (deleted files [there might still be a local file], new files, new version of an
existing file and its related data [i.e. new version was checked in since the last polling]).
<br/>
View the full article