Interpretation of ASP.net Portal Starter Kit (1)--database article

Source: Internet
Author: User
Tags date datetime md5 md5 encryption sort sql injection valid contact form
asp.net| Data | database
The ASP.net Portal Starter kit database structure is generally composed of the core tables of the Web engine (user table, Role table, and role relationship table) and the tables related to each user module. The core table stores the configuration information for the entire site's user rights (detailed usage and instructions will be placed in the role identity authentication article). Each user module stores information for each function module. Each user Function module table is independent. This is beneficial to the new module extension function. At the same time, each function module can be deployed to different databases to improve the program's high scalability and scalability. The way data is accessed is all through the stored procedure. The advantages of doing so are: 1, improve the performance of the database, 2, eliminate the SQL injection attacks (in my understanding); 3, you can configure the permissions that connect to SQL Server database users only to perform operations on the specified stored procedures, which further improves the security of the database.



Tables in the database:



Portal_announcements: Bulletin Information table (used in Bulletin information module)



Field name



Type



Meaning



Note



ItemID



Int



Link ID



Primary KEY (AutoNumber)



ModuleID



Int



Owning module ID



Decide that the bulletin is displayed in that module (module ID: the module table ID in the user profile portalcfg.xml, all of the following)



Createdbyuser



Nvarchar (100)



Created by



Record the users who created and modified the bulletin



CreatedDate



Datetime



Creation time



Record when this post was created and modified



Title



Nvarchar (100)



Name







Morelink



Nvarchar (150)



More details of the link







Mobilemorelink



Nvarchar (150)



Move Detail Link Address



More details link addresses on the mobile device browser



ExpireDate



Datetime



Valid date







Description



Nvarchar (2000)



Describe











Portal_contacts: Contact information table (used in the contact mode module)



Field name



Type



Meaning



Note



ItemID



Int



Link ID



Primary KEY (AutoNumber)



ModuleID



Int



Owning module ID



Determine how this contact is displayed in that module



Createdbyuser



Nvarchar (100)



Created by



Record the users who created and modified the contact



CreatedDate



Datetime



Creation time



Record when the contact was created and modified



Name



Nvarchar (50)



Contact person Name







Role



Nvarchar (100)



Contact role



Unlike the roles in the system framework, it is more appropriate to be understood as a position



Email



Nvarchar (100)



Contact person Email







Contact1



Nvarchar (250)



Contact Way 1







Contact2



Nvarchar (250)



Contact Way 2











Portal_discussion: User Discussion message table (for user discussion module)



Field name



Type



Meaning



Note



ItemID



Int



Link ID



Primary KEY (AutoNumber)



ModuleID



Int



Owning module ID



Decide that the discussion is displayed in that module



Title



Nvarchar (50)



Discussion title







CreatedDate



Datetime



Creation time



Record when the discussion was created



Body



Nvarchar (100)



Message content







Createdbyuser



Nvarchar (100)



Created by



Record the users who created and modified the discussion



Displayorder



Nvarchar (100)



Display order



Record the time of the discussion of the reply and the time of submission itself (can be used as a sort and display reply relationship for the discussion message)







Portal_documents: User Upload Document Information table (used in the Display Document Information module)



Field name



Type



Meaning



Note



ItemID



Int



Link ID



Primary KEY (AutoNumber)



ModuleID



Int



Owning module ID



Determine how this contact is displayed in that module



Createdbyuser



Nvarchar (100)



Created by



Record the users who created and modified the contact



CreatedDate



Datetime



Creation time



Record when the contact was created and modified



Filenameurl



Nvarchar (250)







File path to upload to server



Filefriendlyname



Nvarchar (150)



Document name



Unlike the roles in the system framework, it is more appropriate to be understood as a position



Category



Nvarchar (50)



Category







Content



Image



Content (binary)



Content uploaded to the database



ContentType



Nvarchar (50)



Type



Type of file to upload



Contentsize



Int



Size



Size of uploaded file







Portal_events: Event information table (used in a module that displays event information)



Field name



Type



Meaning



Note



ItemID



Int



Link ID



Primary KEY (AutoNumber)



ModuleID



Int



Owning module ID



Decide that the event is displayed in that module



Createdbyuser



Nvarchar (100)



Created by



Record the user who created and modified the event



CreatedDate



Datetime



Creation time



Record when the event was created and modified



Title



Nvarchar (100)



Event name







Wherewhen



Nvarchar (150)



where it happened







Description



Nvarchar (2000)



Describe







ExpireDate



Datetime



Valid date











Portal_htmltext: Static HTML information table (used in modules that display static static HTML, which can be used to display text such as news)



Field name



Type



Meaning



Note



ModuleID



Int



Owning module ID



Determines that the static HTML is displayed in that module (the primary key, which qualifies a module to correspond to only one static HTML file)



Desktophtml



Ntext



Desktop HTML content







Mobilesummary



Ntext



Move Summary



Summary displayed on the mobile device browser



Mobiledetails



Ntext



Move details



Details displayed on the mobile device browser







Portal_links: Link Information table (For quick link module and connection module)



Field name



Type



Meaning



Note



ItemID



Int



Link ID



Primary KEY (AutoNumber)



ModuleID



Int



Owning module ID



Decide that the link is displayed in that module



Createdbyuser



Nvarchar (100)



Created by



Record the users who created and modified the link



CreatedDate



Datetime



Creation time



Record when the link was created and modified



Title



Nvarchar (100)



Name







Url



Nvarchar (250)



Link Address







Mobileurl



Nvarchar (250)



Move a link address







Vieworder



Int



Sort number







Description



Nvarchar (2000)



Describe











Portal_roles: Role Information table (Portal engine core table)



Field name



Type



Meaning



Note



Roleid



Int



Role ID



Primary KEY (AutoNumber)



Portalid



Int



Portal URL ID



Can set up multiple portal sites and share a database, through the portalid distinction



RoleName



Nvarchar (50)



Role name



Using MD5 encryption to store







Portal_userroles: User Role Relationship table (Portal engine core table)



Field name



Type



Meaning



Note



Userid



Int



User ID



Associated User Information table (portal_roles)



Roleid



Int



Role ID



Management Role Information table (portal_users)







Portal_users: User Information table (Portal engine core table)



Field name



Type



Meaning



Note



Userid



Int



User ID



Primary KEY (AutoNumber)



Name



Nvarchar (50)



User name







Password



Nvarchar (50)



Password



Using MD5 encryption to store



Email



Nvarchar (100)



User Email



Used to log in, and set to a unique index (to prevent the registration of the same email, so that when there is the same email insert when the program will throw an exception, catch this exception can determine whether the email is repeated, so you can dispense with the code to determine whether the email is repeated)







Stored procedures in the database:



Stored Procedure Name



Description



Portal_addannouncement



Add a new announcement



Portal_addcontact



Add New Contact method



Portal_addevent



Add new Event



Portal_addlink



Add New link



Portal_addmessage



Create a new discussion message, where @parentid parameters for the reply to the message ID, through the ID to find the message displayorder, add the time is the new message displayorder.



Portal_addrole



Add role Information



Portal_adduser



Add a new user, return the user's ID



Portal_adduserrole



Adding User Role relationships



Portal_deleteannouncement



Delete Post information (note: The following deletions are deleted if no special instructions are for deleting the specified itemid information)



Portal_deletecontact



Delete Contact Form



Portal_deletedocument



Delete User Upload Document



Portal_deleteevent



Delete Event Information



Portal_deletelink



Delete link Information



Portal_deletemodule



When a module is deleted, the linkage deletes all information related to the module



Portal_deleterole



Remove role Information



Portal_deleteuser



Delete User



Portal_deleteuserrole



Delete role User relationship



Portal_getannouncements



Returns the announcement information within the validity period based on the ModuleID (module ID)



Portal_getauthroles



As if the stored procedure was not used, and the stored procedure was not involved in the table database (there are similar tables in the user profile), it needs to be studied in depth



Portal_getcontacts



Return contact form based on ModuleID (module ID)



Portal_getdocumentcontent



Get the document information stored in the database based on the document (ItemID)



Portal_getdocuments



Return user uploaded document information based on ModuleID (module ID)



Portal_getevents



Returns the event information within the validity period based on the ModuleID (module ID)



Portal_gethtmltext



Returns static HTML text information based on ModuleID (module ID)



Portal_getlinks



Returns the connection information based on the ModuleID (module ID) and sorts by (Vieworder) ordinal



Portal_getnextmessageid



Get the next comment on the discussion



Portal_getportalroles



Gets the full role information for the specified portal site (specified Portalid)



Portal_getprevmessageid



Get the last message from the discussion



Portal_getrolemembership



Get the user member information for the role based on the role ID



Portal_getrolesbyuser



Get user role information based on user email



Portal_getsingleannouncement



According to the Bulletin (ItemID), get the information of a single bulletin



Portal_getsinglecontact



Get information about a single contact by contact (ItemID)



Portal_getsingledocument



Get information for a single document based on the document (ItemID)



Portal_getsingleevent



Gets the information for a single event, based on the event (ItemID)



Portal_getsinglelink



Get information for a single link based on the link (ItemID)



Portal_getsinglemessage



According to the message (ItemID), get the information of a single message



Portal_getsinglerole



Get information for a single role based on the role ID



Portal_getsingleuser



Get information for a single user based on the user's email



Portal_getthreadmessages



Returns the message in chronological order and reply relationship according to the displayorder of the parent message information



Portal_gettoplevelmessages



Return to top message information based on ModuleID (module ID)



Portal_getusers



Get all the user information and sort by email



Portal_updateannouncement



Update Bulletin Information



Portal_updatecontact



Update contact information



Portal_updatedocument



Update document information and add new document information when the specified document ID is not found



Portal_updateevent



Update event Information



Portal_updatehtmltext



Update static HTML text information



Portal_updatelink



Update the specified connection information



Portal_updaterole



Update role Information



Portal_updateuser



Update user Information



Portal_userlogin



Returns the name of the logged-on user based on email and password (used to determine whether the user logged in through)








Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.