Resolving common attachment management functions for large. NET ERP Systems

Large systems have a common attachment management function, and it is best to use an attachment picture or an attachment document to represent a field that cannot be clearly expressed in a document. For example, the BOM attached a CAD drawing, the Sales order review function to add the customer's various forms, the general attachment function to the system to play a finishing touch. A schematic thousand words, first look at the interface design pattern, looks and the general data input function is same.

The first is the design of the attachment table, and its definition is referenced in the following code.

CREATE TABLE[dbo]. [Attachment] ([Index] [int] not NULL, [mastertable] [nvarchar] (50)COLLATESQL_Latin1_General_CP1_CI_AS not NULL CONSTRAINT[df__attachmen__maste__5165187f]DEFAULT("'), [Masterkey] [decimal] (10, 0) not NULL CONSTRAINT[DF__ATTACHMEN__MASTE__52593CB8]DEFAULT((0)), [FileType] [nvarchar] (10)COLLATESQL_Latin1_General_CP1_CI_AS not NULL CONSTRAINT[DF__ATTACHMEN__FILET__534D60F1]DEFAULT("'), [FilePath] [nvarchar] (250)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [CreatedDate] [datetime]NULL, [createdby] [nvarchar] (10)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [reviseddate] [datetime]NULL, [revisedby] [nvarchar] (10)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [Description] [nvarchar] (60)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [KeySegment1] [nvarchar] (30)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [KeySegment2] [nvarchar] (30)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [KeySegment3] [nvarchar] (30)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [KeySegment4] [nvarchar] (30)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [KeySegment5] [nvarchar] (30)COLLATESQL_Latin1_General_CP1_CI_ASNULL,[Size] [decimal] (18, 0)NULL,[File] [Image]NULL, [uploadedby] [nvarchar] (10)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [uploadeddate] [datetime]NULL, [Md5hash] [nvarchar] (32)COLLATESQL_Latin1_General_CP1_CI_ASNULL) on[PRIMARY] textimage_on [PRIMARY]GOALTER TABLE[dbo]. [Attachment]ADD CONSTRAINT[Pk_attachment]PRIMARY KEY CLUSTERED([Index], [mastertable], [Masterkey]) on[PRIMARY]GOEXECSp_addextendedproperty N' Ms_description 'N' Accessories ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',NULL,NULLGOEXECSp_addextendedproperty N' Ms_description 'N' index ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' Index 'GOEXECSp_addextendedproperty N' Ms_description 'N' attachments attached to the main table ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' mastertable 'GOEXECSp_addextendedproperty N' Ms_description 'N' primary key ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' Masterkey 'GOEXECSp_addextendedproperty N' Ms_description 'N' file type ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' FileType 'GOEXECSp_addextendedproperty N' Ms_description 'N' file path ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' FilePath 'GOEXECSp_addextendedproperty N' Ms_description 'N' Created Date ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' CreatedDate 'GOEXECSp_addextendedproperty N' Ms_description 'N' Building People ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' CreatedBy 'GOEXECSp_addextendedproperty N' Ms_description 'N' Date Modified ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' Reviseddate 'GOEXECSp_addextendedproperty N' Ms_description 'N' modified person ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' Revisedby 'GOEXECSp_addextendedproperty N' Ms_description 'N' name ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' Description 'GOEXECSp_addextendedproperty N' Ms_description 'N' keywords ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' KeySegment1 'GOEXECSp_addextendedproperty N' Ms_description 'N' keywords ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' KeySegment2 'GOEXECSp_addextendedproperty N' Ms_description 'N' keywords ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' KeySegment3 'GOEXECSp_addextendedproperty N' Ms_description 'N' keywords ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' KeySegment4 'GOEXECSp_addextendedproperty N' Ms_description 'N' keywords ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' KeySegment5 'GOEXECSp_addextendedproperty N' Ms_description 'N' Attachment size ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' Size 'GOEXECSp_addextendedproperty N' Ms_description 'N' attachment content ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' File 'GOEXECSp_addextendedproperty N' Ms_description 'N' on the Descendants ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' Uploadedby 'GOEXECSp_addextendedproperty N' Ms_description 'N' upload date ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' Uploadeddate 'GOEXECSp_addextendedproperty N' Ms_description 'N' hash value ',' SCHEMA 'N' dbo ',' TABLE 'N' Attachment ',' COLUMN 'N' Md5hash 'GO

As explained in a previous article, why do I need to add a recnum (record number) numeric value field to each database table, where we want to record the function of the attachment in the Business function table, that is, to write down the value of recnum in the table.

Mastertable is used for program grouping display attachments, when uploading more attachments and not easily to each individual function to find, you need to design an attachment browser function for viewing all the accessories in the system.

With the above two foundations, the attachment function is basically complete. Then add the attachment file store or download view function.

First look at the storage method of the attachment. You can store attachments directly in the database, or consider adding an FTP server to transfer attachments to the file server. When the attachment file is too large, the database performance is affected by the presence of the database, and the advantage is ease of migration. stored in the FTP server, the advantage is that performance will be good, the disadvantage is to consider the file system related matters. For example, user A uploads an attachment file doc20150718.pdf, User B also uploads a copy of the same file Doc20150718.pdf, and the uploaded file name cannot overwrite the previously uploaded filename. The workaround is to use a GUID to represent the file name, and the GUID of the files in the database, which resolves the file duplication problem, but does not resolve the file readability issue. Open the folder structure in the FTP directory, see the GUID as file name files, not conducive to search. You need to consider the readability of filenames, for example, by the combination of user and time. Or the previous file name Doc20150718.pdf as an example, the current login user name is a, I can rename this file to user + year + month + day + hour minutes + file name + serial number of the document encoding scheme to the file name, So the above file name becomes a-201507182110-doc20150718-0012.pdf, the time part of the file name is accurate to the minute, the likelihood of repetition is greatly reduced, and the readability is increased. I follow this rule and then write a file Rename tool, in case these files with the ERP system out of relation, with the software tools to simply rename, you can also revert to the original user upload the file name used.

Then look at the reader code. The PDF reader and the docx reader come from radcontrols_winforms_2013, which has built-in reader controls for both documents and is simple to call. The PDF Reader code is called as follows:

Private Telerik.WinControls.UI.RadPdfViewer readerpdf;  This true; if null && attachment. Length > 0) {       new MemoryStream (attachment);       this. readerpdf.loaddocument (stream);}

The docx document reader code invocation is referenced as follows:

Idocumentformatprovider Provider = getproviderbyextension (extension); if null) {    thrownew fileloadexception ("Unable to find format provider for extension" + extension) ;} using (Stream stream = File.openread (File)) {    Raddocument document = provider. Import (stream);    this. readerword.document = Document;    Document. LayoutMode = documentlayoutmode.paged;    Readerword.changesectionpageorientation (pageorientation.rotate270);    Readerword.focus ();}

. NET has the powerful file API Aspose software package, which contains almost all the files on the market conversion interface, which is. NET's file API. For files that cannot be read directly, such as Excel,powerpoint, consider calling the Aspose file interface to PDF and then invoking the PDF reader display. Aspose is widely used in file processing, and this interface is recommended for file conversion related functions.

Summarize the three main points of the generic Attachment management feature:

1 How to correlate the business function table, mainly save the Recnum record number.

2 How to store an attachment, FTP file server or database image binary field.

3 How to view documents, PDF and docx document readers, and other common documents into PDF.

Resolving common attachment management functions for large. NET ERP Systems