Dynamic definition of information system fields

Abstract:
With the construction of the MIS system, it information users are constantly expanding their requirements for information processing, which will inevitably affect the Change Management of The mnis system construction. How to Implement custom databases andProgramThe design enables the customer to maintain the database content as needed. It is of great significance to reduce MIS System Construction Project changes and meet the customer's needs. This article proposes a solution under DOTNET and Microsoft sqlserver to implement custom database and program design solutions.
Keywords:
Custom, MIS, and stored procedures,
1. Introduction

Management Information System (MIS, A system composed of people, computers, and other peripheral devices that can collect, transfer, store, process, maintain, and use information. It is an emerging science. Its main task is to maximize the use of modern computer and network communication technology to strengthen enterprise information management, by investigating and understanding the human, material, financial, equipment, technology and other resources owned by the enterprise, establishing correct data, processing and compiling various information and materials, and providing them to the management personnel in a timely manner, in order to make correct decisions, and continuously improve the management level and economic efficiency of enterprises. At present, the enterprise's computer network has become an important means for enterprises to carry out technical transformation and improve the level of enterprise management. The MIS system has a long construction cycle after feasibility analysis, user research, demand analysis, system analysis, outline design, detailed design, coding, testing, trial run and online operation.

2. Question about custom design

Due to the long construction period of MIS, information users often lack the necessary understanding of the functions that the constructed information system can achieve. During version submission for trial run, it is discovered that the required functions have not been implemented or new functions have been required to be added, and a large number of system change orders have been added. This will inevitably lead to a great change in the system architecture, for system designers and developers, considering the development progress and workload, the system change form cannot be fully accepted. As a result, the system builder's expected objectives are not met, resulting in unlimited system construction delays and excessive costs, even system construction fails.
These changes are often due to the fact that the builder did not take into account more factors at the beginning, resulting in no corresponding fields in the database design. Designers usually consider the subsequent design issues. A common solution is to add several standby fields in the database design to meet the demand for system expansion, however, in a mode that requires Field Security Authorization and uncertainty about future system expansion, the corresponding data fields cannot be estimated. In addition, fields also need different types, which respectively store different types of data for future statistical analysis. Therefore, the best way is to customize the field at runtime.

3. Customer-defined design technical implementation

Three technologies are designed in the custom design. One is how to allow the user to customize database fields, and the other is how to perform field-level authorization on these data fields to ensure that when the MIS is running, ensure data security. Third, how do system users obtain the dataset they deserve. Take the SQL Server 2005 database and aspnet2.0 as examples to solve the User-Defined problem.
3.1 Data Tables, stored procedures, and triggers
3.1.1 data table knowledge
The number of records in a data table changes with the increase of data, but the number of fields is relatively fixed. Each column in the tables table has a unique field name; the sequence and column sequence of rows in the two identical rows in the sequence table cannot be exchanged randomly.
3.1.2 Stored Procedure
Stored Procedure encapsulates the database objects of the SQL statement set on the server. Similar to other Programming Language . Stored procedures are stored in databases, and business logic is usually stored in stored procedures.
3.1.3 field description
Because dynamic data fields need to be generated according to the stored procedure, the program needs to construct the header settings of the dataset Based on the field name of the dynamic field. We generally use the database description as the dataset description. Therefore, you need to set the description field of the field when modifying the database.
3.1.4sp _ addextendedproperty process and fn_listextendedproperty Function Description
Sp_addextendedproperty adds the new extension property to the database object. It contains eight parameters: extension field name, extension field content, category name, category, Object Name, object, attribute name, and attribute name. For example, if the ID field of User table test is set to serial number, the call method is as follows:
Sp_addextendedproperty 'Ms _ description', 'sequence number ', 'user', DBO, 'table', test, 'column', 'id '.
Fn_listextendedproperty returns the extended property value of the database object. Contains six parameters. The first parameter is the name of the extended attribute to be read, the second parameter is the user or user-defined type, the third parameter is the schema name, and the fourth parameter is the modified object, for example, if the fourth parameter is table, the fifth parameter is the table name, and the sixth parameter is the object to be read. We generally use column, and the seventh parameter is the column name. for example, the description attribute of column ID in table test in DBO is:
Fn_listextendedproperty ('Ms _ description', 'user', 'dbo', 'table', 'test', 'column', 'id ')
3.2 Implementation of custom database fields
To dynamically define the database, two tables are required to define the structure of the data table. The second table is used to record the structure and security allocation mode of the data table. Add records to the configuration table and modify the structure of the data table to expand the data table. At the same time, you can set the corresponding system user for the field () of the table in the modification setting table, and control the user in the program to implement the read and write permissions on the field.
Modifying a database table is actually done by dynamically calling the alter table statement. In the alter table statement, add, modify, and delete fields by calling add, modify, and drop.
To display Description fields when user datasets are dynamically generated, you must call sp_addextendedproperty and fn_listextendedproperty In the stored procedure and function to set and read description of data table fields respectively.
3.3 implement dynamic data filling Dataset
Based on the login user settings, we can obtain the data table fields that the login user has access to. Based on these fields, we can construct a SELECT statement to dynamically read the data table. Based on the descriptions of these fields, update the header attribute of the field in the dataset to ensure that the description field of the data column is displayed.
Updates are dynamically constructed based on the user's modified fields to ensure that they can only update their corresponding data fields. At the same time, set some public fields in the database fields to ensure that each user can access them.

4. Implementation of custom monthly device reports in the device management system

4.1 set table maintenance page implementation
4.1.1 Implementation of addcolumntosbyb Stored Procedure
Write the addcolumntosbyb stored procedure to modify the data table. Code As follows:
Create procedure [DBO]. [addcolumntosbyb]
@ Zdmc varchar (20), @ zdms varchar (50), @ sbybzdlx varchar (20 ),
@ Sbybzdcd int
As
Begin
-- Define the running parameters. SQL indicates the SQL statement to be run, and zdlx indicates the field type.
Declare @ SQL nvarchar (4000)
Declare @ zdlx varchar (20)
If @ sbybzdlx = 'char'
Set @ zdlx = 'varchar ('+ Cast (@ sbybzdcd as varchar) + ')'
If @ sbybzdlx = 'integers'
Set @ zdlx = 'int'
If @ sbybzdlx = 'date'
Set @ zdlx = 'datetime'
If @ sbybzdlx = 'decimal'
Set @ zdlx = 'decimal (18.2 )'
-- Construct an SQL statement to change the database
Set @ SQL = 'alter table sb_rcgl_sbyb add' + @ zdmc + @ zdlx;
-- Execute SQL statements to change the database
Execute sp_executesql @ SQL
-- Add description of Field Type
Execute sp_addextendedproperty 'Ms _ description', @ zdms, 'user', DBO, 'table', sb_rcgl_sbyb, 'column', @ zdmc
End
4.1.2 Implementation of sbybzd Stored Procedure
The system adds records to the sb_rcgl_sbyb_xmfp data record table based on the structure of the sb_rcgl_sbyb table to record the field type and safe access mode of the table.
Create procedure [DBO]. [sbybzd]
Begin
-- Define parameter fields
-- @ Sbybzdms device Monthly Report field description
-- @ Sbybzdmc field name of the device Monthly Report
-- @ Type_name field type of monthly device report
Declare @ sbybzdms varchar (50)
Declare @ sbybzdmc varchar (50)
Declare @ type_name varchar (50)
-- Determine whether a temporary table exists # temp. If yes, delete it.
If object_id ('tempdb .. # temp ') is not null
Drop table # temp
-- Create a temporary table to read the field settings of table sb_rcgl_sbyb
Create Table # temp
(
Table_qualifier varchar (20), table_owner varchar (20), table_name varchar (50), column_name varchar (20 ),
Data_type int, type_name varchar (20), precision int, length int, scale int, Radix int,
Nullable int, remarks varchar (20), column_def varchar (20), SQL _data_type int, SQL _datetime_sub varchar (20 ),
Char_octet_length int, ordinal_position int, is_nullable varchar (20), ss_data_type int
)
-- Save the field settings of the sb_rcgl_sbyb table to the temporary table # temp
Insert into # temp exec sp_columns "sb_rcgl_sbyb"
-- Set the record table items for each field
Declare sbybzdmc cursor for select column_name, type_name from # temp
Open sbybzdmc
Fetch sbybzdmc into @ sbybzdmc, @ type_name
While @ fetch_status = 0
Begin
If @ type_name = 'numeric'
Set @ type_name = 'decimal'
If @ type_name = 'bigint' or @ type_name = 'int'
Set @ type_name = 'integers'
If @ type_name = 'varchar 'or @ type_name = 'Char'
Set @ type_name = 'char'
-- Read the description of this field
Select @ sbybzdms = cast (value as varchar) from: fn_listextendedproperty ('Ms _ description', 'user', 'dbo', 'table', 'SB _ rcgl_sbyb ', 'column ', @ sbybzdmc)
Insert into sb_rcgl_sbyb_xmfp (xmmc, xmms, zdlx, zdcd, GY) Select column_name, @ sbybzdms, @ type_name, length, 'false' from # temp where column_name = @ sbybzdmc and column_name not in (select xmmc from sb_rcgl_sbyb_xmfp)
Fetch sbybzdmc into @ sbybzdmc, @ type_name
End
-- Update the data content in the data record table
Delete from sb_rcgl_sbyb_xmfp where xmmc not in (select column_name from # temp );
Close sbybzdmc
Deallocate sbybzdmc
End
4.1.3 logical process for adding data rows on the page
The following figure shows the page for adding and managing data rows:

When the customer enters the corresponding data field and clicks the new button, the event is triggered and the following code is executed:
-- Read the input content and save it to the database
Textbox tb_xmmc = gridview1.footerrow. findcontrol ("tb_xmmc") as textbox;
Dropdownlist ddl_fpr = gridview1.footerrow. findcontrol ("ddl_fpr") as dropdownlist;
Textbox tb_xmms = gridview1.footerrow. findcontrol ("tb_xmms") as textbox;
Dropdownlist ddl_zdlx = gridview1.footerrow. findcontrol ("ddl_zdlx") as dropdownlist;
Textbox tb_zdcd = gridview1.footerrow. findcontrol ("tb_zdcd") as textbox;
Checkbox cb_gy = gridview1.footerrow. findcontrol ("cb_gy") as checkbox;
If (tb_xmmc.text = NULL | tb_xmmc.text = "" | tb_xmms.text = NULL | tb_xmms.text = "" | bytes = NULL | tb_zdcd.text = NULL | bytes = "")
{
Label6.text = "error: required field name, field description, field type, and field length ";
}
Else
{
Sqldatasource1.insertparameters ["xmmc"]. defaultvalue = tb_xmmc.text;
Sqldatasource1.insertparameters ["xmms"]. defaultvalue = tb_xmms.text;
Sqldatasource1.insertparameters ["FPR"]. defaultvalue = ddl_fpr.selectedvalue;
Sqldatasource1.insertparameters ["zdlx"]. defaultvalue = ddl_zdlx.selectedvalue;
Sqldatasource1.insertparameters ["zdcd"]. defaultvalue = tb_zdcd.text;
Sqldatasource1.insertparameters ["gy"]. defaultvalue = cb_gy.checked.tostring ();
Sqldatasource1.insert ();
-- Call the Stored Procedure addcolumntosbyb to reflect the field changes to the table sb_rcgl_sbyb.
Darkblue. data. sqlserver. systemparameters. executescalarwithonevalue ("execute addcolumntosbyb" + tb_xmmc.text + "','" + tb_xmms.text + "','" + signature + "'," + tb_zdcd.text );
-- Call the Stored Procedure sbybzd to generate sb_rcgl_sbyb_xmfp data records
Darkblue. Data. sqlserver. systemparameters. executescalarwithonevalue ("execute sbybzd ");
Sqldatasource1.databind ();
Gridview1.databind ();
Label6.text = "data inserted successfully ";
}
4.2 User Data filling page implementation
When a customer logs on to the user filling page, the page constructs a permission field, fills in and saves it to the database.
4.2.1 construct a field set accessible to the current user
Put a dataset in the page to save all the fields that can be accessed by the customer in the sb_rcgl_sbyb data table. The SELECT statement is as follows:
Select [xmmc], [FPR], [xmms], [zdlx], [zdcd] from [sb_rcgl_sbyb_xmfp] Where ([FPR] = @ FPR and Gy = 'false ')
Where: @ FPR is the system logon customer.
4.2.2 The administrator sets the field user based on business needs.
For example, set the meter integrity rate, meter consumption rate, and meter control rate fields for the user Hu Lantian to fill in. The sequence number, year, month, current year period, and total number of periods are common fields, which are automatically maintained by the system. Is a public field. It can only be viewed and cannot be modified.

4.2.3 The customer entry control is dynamically generated based on the field set
For (INT I = 0; I <gridview2.rows. Count; I ++)
{
Textbox tb_1 = new Textbox ();
Label lb_1 = new label ();
Tb_1.id = "Tb _" + gridview2.rows [I]. cells [0]. text;
Lb_1.text = gridview2.rows [I]. cells [1]. text;
If (gridview2.rows [I]. cells [2]. Text = "character ")
{
Tb_1.width = system. Web. UI. webcontrols. Unit. pixel (600 );
Tb_1.height = system. Web. UI. webcontrols. Unit. pixel (300 );
Tb_1.textmode = textboxmode. multiline;
Tb_1.rows = 15;
}
If (gridview2.rows [I]. cells [2]. Text = "Number ")
{
Tb_1.width = system. Web. UI. webcontrols. Unit. pixel (15 );
Tb_1.height = system. Web. UI. webcontrols. Unit. pixel (15 );
}
Tablerow tb_row = new tablerow ();
Tablecell tb_cell = new tablecell ();
Tb_cell.controls.add (lb_1 );
Tb_cell.controls.add (tb_1 );
Tb_row.cells.add (tb_cell );
Table1.rows. Add (tb_row );
}
The page entry is as follows:

4.2.4 dynamically generate update statements as needed
After the customer enters the data, click Save to trigger the following Event code:
String updatesql;
Updatesql = "Update sb_rcgl_sbyb set ";
String XH;
XH = gridview1.rows [0]. cells [0]. text;
For (INT I = 0; I <gridview2.rows. Count; I ++)
{
Textbox tb_1 = table1.findcontrol ("Tb _" + gridview2.rows [I]. cells [0]. Text) as textbox;
If (gridview2.rows [I]. cells [2]. Text = "character ")
{
If (tb_1.text = "")
{
Updatesql = updatesql + gridview2.rows [I]. cells [0]. Text + "='' "+ ",";
}
Else
{
Updatesql = updatesql + gridview2.rows [I]. cells [0]. Text + "= '" + tb_1.text + "',";
}
}
Else
{
If (tb_1.text! = "")
{
Updatesql = updatesql + gridview2.rows [I]. cells [0]. Text + "=" + tb_1.text + ",";
}
}
}
Updatesql = updatesql. substring (0, updatesql. Length-1 );
Updatesql = updatesql + "where sbybxh =" + XH;
Darkblue. Data. sqlserver. systemparameters. executescalarwithonevalue (updatesql );
Gridview1.databind ();
Fill in the controlled fields.

5. Conclusion

By writing the stored procedures addcolumntosbyb and sbybzd, you can modify the data record table to modify the structure of the data table and authorize the fields of the data table. By programming on the page, the login user can access the authorized field to ensure that the user accesses the field. Implement custom modification of data table fields