Preliminary Exploration of ERP Database framework

In ERP software, database is its soul. Each ERP software has its own database, and the most critical of these databases is the database framework. So what is the database framework? What is his role? Why do we need to build a database framework during installation? This article will answer these questions.

When compiling ERP, MIS, S/B and other database applications, the first thing to do is to establish a database framework, which includes at least the tables in the database and database, of course, there are also views and stored procedures. This is the database framework (excluding specific data ). Then, the user interface is developed using programming languages such as vb, vc, vfp, and pb to accept user operations on the database. After you have successfully developed an ERP software, you need to package it and finally deliver it to the customer for installation and use. At this time, there is a problem. When you Package SQL server, you cannot package it into the installer. Therefore, you must first establish a database framework during use, the user does not know the framework structure of the database, and the ERP software must access the specific database framework to run successfully. At this time, we need a program that can automatically generate the database framework. For example, when developing a human resource management system, a database framework is required. At least one table is contained in the database, which contains information such as name, age, and salary, then access the table through the client. Without this table, the program cannot run successfully. Now you know what the database framework is and what its role is!

The current ERP software has the function of Automatically Generating the database framework. Different software implementation methods are different. To sum up, there are about three types:

1. appeared as a wizard;

2. The system is configured during installation;

3. integrated into the main program, the database framework is automatically generated when the main program runs for the first time.

They use the same method.

If you have the ERP of Guan jiapo, you can install it. It requires that SQL server be installed first. After the installation is complete, open SQL server and you will find that there are only a few default databases in the SQL server database, which is no different. Next, start to install "Guan jiapo". After installation, you can use its functions. Then, you can open SQL server and find that the database is different, added some databases (the added databases vary with the version of Guan jiapo because of their functions ). These added databases are automatically generated using the database framework.

Then, how can I use a program to automatically generate a database framework? Now, we will create such a program. In this program, five buttons are set up: Create a database, create a table, create constraints, create a stored procedure, and display data. The implementation code is as follows:

Public Class Form1 Inherits System. Windows. Forms. Form Private Sub button#click (ByVal sender As System. Object, ByVal e As System. EventArgs) Handles Button1.Click Dim con As New OleDb. OleDbConnection ("Provider = SQLOLEDB.1; Integrated Security = SSPI; Persist Security Info = False; Initial Catalog = Northwind; Data Source =.; Use Procedure for Prepare = 1; Auto Translate = True; Packet Size = 4096; Workstation ID = J; Use Encryption for Data = False; Tag with column collation When possible = False ") Con. Open () Dim cmd As New OleDb. OleDbCommand ("create database jk", con) Cmd. ExecuteNonQuery () Con. Close () ''Create a database End Sub Private Sub Button2_Click (ByVal sender As System. Object, ByVal e System. eventargs) handles button2.click Dim con2 as new oledb. oledbconnection ("provider = sqloledb.1; Integrated Security = sspi; persist Security info = false; initial catalog = JK; Data Source =.; Use Procedure for prepare = 1; Auto translate = true; packet size = 4096; workstation id = J; Use Encryption for Data = false; tag with column collation when possible = false ") Con2.open () Dim cmd as new oledb. oledbcommand ("create table kk (ID int identity (1, 1) Not Null constraint id Primary Key, name char (4) not null) ", con2) Cmd. executenonquery () Dim cmd2 as new oledb. oledbcommand ("create table pp (ID int not null, ads Char (20) null) ", con2) Limit 2.executenonquery () Con2.close () ''Create two tables End sub Private sub button3_click (byval sender as system. Object, byval e System. EventArgs) Handles Button3.Click Dim con2 As New OleDb. OleDbConnection ("Provider = SQLOLEDB.1; Integrated Security = SSPI; Persist Security Info = False; Initial Catalog = jk; Data Source =.; Use Procedure for Prepare = 1; Auto Translate = True; Packet Size = 4096; Workstation ID = J; Use Encryption for Data = False; Tag with column collation when possible = False ") Con2.Open () Dim com As New OleDb. OleDbCommand ("alter table pp add primary key (id )", Con2) Com. ExecuteNonQuery () Con2.Close () ''Create constraints End Sub Private Sub Button4_Click (ByVal sender As System. Object, ByVal e System. EventArgs) Handles Button4.Click Dim con2 As New OleDb. OleDbConnection ("Provider = SQLOLEDB.1; Integrated Security = sspi; persist Security info = false; initial catalog = JK; Data Source =.; Use Procedure for prepare = 1; Auto translate = true; packet size = 4096; workstation id = J; Use Encryption for Data = false; tag with column collation when possible = false ") Con2.open () Dim com as new oledb. oledbcommand ("create proc procname as select * from KK ", con2) Com. executenonquery () Con2.close () ''Create a stored procedure End sub Private sub button5_click (byval sender as system. Object, byval e System. eventargs) handles button5.click Dim con2 as new oledb. oledbconnection ("provider = sqloledb.1; Integrated Security = sspi; persist Security info = false; initial catalog = JK; Data Source =.; Use Procedure for prepare = 1; Auto translate = true; packet size = 4096; workstation id = J; Use Encryption for Data = False; Tag with column collation when possible = False ") Dim com As New OleDb. OleDbCommand ("procname", con2) Dim da As New OleDb. OleDbDataAdapter () Da. SelectCommand = com Dim ds As New DataSet () Da. Fill (ds) DataGrid1.DataSource = ds ''Display data End Sub End Class

In button1_click, the connection string of the connection object is different from the connection string in other button_click statements. Because we want to create a new database JK, but this database does not exist, we need to connect it to an existing database in SQL Server through this connection, obtain access control permissions for the entire SQL Server, and then create a JK database. Other button_click commands are directly connected to the JK database. Because we need to create tables, stored procedures, constraints, and display data in the JK database.

In the program, button2_click creates two tables, KK and PP. Kk includes a seed column and is set as the primary key. PP is a common table, and its primary key constraint is created in button3_click. Of course, we can also create other constraints, as long as we change the T-SQL statement.

The stored procedure of button_click is to query data in the KK table. Because no data is input to the KK table, the KK table is empty after running. But this is not important, because for ERP, all data can be input on the client, only the database framework is required.
Careful readers will surely find that I have not established a table relationship. In fact, you may think that although visual tools are provided in SQL Server to create a table relationship, however, we still use the T-SQL statement to create the database. If you don't believe it, you can open the script file. If you have training materials for Microsoft SQL Server, open the experiment and you will find that the table relationship is written using the T-SQL statement. You only need to convert the Statement of the stored procedure in this article into the corresponding relational statement. The same applies to views.

When testing this program, please click in the order of button1, button2, button3, button4, and button5 to complete the function of establishing a database framework, you can also write the first four parts to a module. Because the database servers of each computer have different names, you must specify the server in the connection string as the name of the current database server during code testing.

The instance described in this article is verified in win2000 sp2 and SQL server 2000.