In ERP software, the database is its soul. Every ERP software has its own database, and the most critical is the database framework. So what is the database framework? What is his role? Why build a database framework when installing? This article will come to answer these questions.
When writing a database of ERP, MIS, S / B, the first thing to do is to establish a database framework, which includes at least: the database and database of the database, of course, there is also a view, stored procedure, etc., this is Database frame (excluding specific data). Then use the programming language such as VB, VC, VFP, PB, and accept the user's operation on the database. When you have successfully developed an ERP software, you need to pack it and finally give it to the customer installation and use. There is a problem at this time. When you pack it, you can't pack SQL Server to the installer, so the user must first establish the database framework, and the user does not know the framework structure of the database, the ERP software must Accessing a specific database framework can be run successfully, then we need to have a program that automatically generates a database framework. For example: When developing a human resource management system, a database framework is required, this minimum at the database contains a table, contains the name, age, salary, etc., and then accesses this table through the client. If there is no such table, the program is impossible to run successfully. Now everyone knows what is the database framework and his role!
Now there is a function of automatically generating a database framework in ERP software. Different software implementation methods are different, summarize, about 3:
1. The appearance in the form of a guide;
2. In the form of configuring the system in installation;
3. Integrated in the main program, automatically generates the database framework when the main program is running.
Whether it is the way, their use is the same.
If you have an ERP of "Guanjiao", you can install it. It requires to install SQL Server first. After installation, open SQL Server You will find that only a few databases in the SQL Server database are not different. Then I started to install "House", just use his function after installation, and then open SQL Server, you will find that the database has been different, add some databases (the function used by the database, "The" Manpo "version is different. Different). These additional databases are to automatically generate with the database framework.
So, how do you automatically generate a database framework with a program? Now let's create such a program. The establishment of 5 buttons in this program is: establish a database, establish a table, establish constraint, establish a stored procedure, display data. The code implemented is as follows:
Public Class Form1Inherits System.Windows.Forms.FormPrivate Sub Button1_Click (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.ClickDim 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 () 'establishment of a database End SubPrivate Sub Button2_Click (ByVal sender As System.Object , ByVal e As System.EventArgs) Handles Button2.ClickDim 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 Id Id Id Id Id Id Id Id Id Id Id Null Constraint ID Primary Key, Name Char (4) Not Null)", CON2) cmd.executenonury () DIM CMD2 As new oledb.oledbcommand ("CREATE TABLE PP (ID INT Not Null, ADS Char (20) NULL)", CON2) CMD2.EXECUTENONQUERY () Con2.close () Creating 2 Table End Subprivate Sub Button3_Click (Byval Sender AS System.Object, ByVal e As System.EventArgs) Handles Button3.ClickDim 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 () 'established constraint End SubPrivate Sub Button4_Click (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.ClickDim con2 As New OleDb. OleDbConnection ( "Provider = SQLOLEDB.1; Integrated Security = SSPI; Persist Security Info = False; Initial Catalog = jk; Data Source = .; UseProcedure 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 () 'establishment procedure stored End SubPrivate Sub Button5_Click (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.ClickDim con2 As New OleDb.OleDbConnection ( "Provider = SQLOLEDB.1; Integrated Securit y = 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 = comDim ds As New DataSet () da.Fill (ds) DataGrid1 .Datasource = DS 'display data END SUBEND CLASS
In Button1_Click, the connection string of the connection object and the connection string in other Button_Click are different. Because I want to create a new database JK, but this database does not exist, so I have to connect it to a database existing in a SQL Server to obtain access control permissions for the entire SQL Server, and then build JK. database. And other Button_Click connections are directly connected to the JK database. Because we have to create a table, store procedure, constraint, display data in the JK database. Button2_click in the program established two tables, KK, and PP. The KK includes a seed column and is set to a primary key. PP is a general table that establishes its primary key constraint in Button3_Click. Of course, we can also build other constraints, as long as the T-SQL statement is changed.
Button_Click stored procedures are data in Query KK tables, because there is no data in the KK table, so the KK table will be empty after running. But this is not important, because for ERP, all data can be entered on the client, just the database framework. The careful readers will definitely find that I have no establishment of the relationship. In fact, everyone wants to provide a visual tool in SQL Server to create a table, but it is still used by the T-SQL statement to build, if you can Open the script file to see if you have Microsoft's SQL Server training materials, open the experimental part to see if the table is written in the T-SQL statement, you only need to change the statement of the stored procedure of this article It is possible to make a corresponding relationship statement. The same is true for the view.
When testing this program, please click on Button1, Button2, Button3, Button4, button2, click, you can complete the function of establishing a database framework, you can also write the first 4 parts to a module. Since each computer's database server is different, you should specify the server in the connection string as the current database server name when testing the code.
The examples described herein are verified in Win2000 SP2, SQL Server 2000.
