Quickly build the database frame of ERP

(This article takes SQL Server as the database server, and T-SQL is an extension of the standard SQL language.) In the ERP software, the 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 a database framework? His

.

What is the role? Why build a database framework at installation time? This article is to answer these questions.

One of the first things to do when writing an application for a database such as erp,mis,s/b is to create a database framework that includes at least the tables in the database and database, and of course views, stored procedures, and so on, which is the database framework (without specific data). The user interface is then developed using programming languages such as VB,VC,VFP,PB to accept user action on the database. When you have successfully developed an ERP software, you need to package it, and finally to the customer to install and use. Then there is a problem, when you pack, you can not package SQL Server into the installer, so users must first establish a database framework, and users do not know the framework of the database, ERP software must access a specific database framework to 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 needed, at least in the database contains a table, the table contains the name, age, salary and other information, and then access the table through the client. Without this table, the program will not be able to run successfully. Now we know what is the database framework and his role!

Today's ERP software with the automatic generation of database framework functions, different software implementation methods are different, summed up, about 3 kinds:

1. Appearing in the form of a guide;

2. In the form of configuration system in the installation;

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

In either way, their use is the same.

If everyone has "housekeeper" ERP, you can install to see. It requires you to install SQL Server first, and then open SQL Server after installation you will find that there are only a few databases in the SQL Server database that are default, and nothing different. Then began to install the "housekeeper", after installation casually use his function, and then open SQL Server you will find that the database has been different, adding a number of databases (the increase in the database due to the use of the function and "housekeeper" version of different and different). These additional databases are created to use the database framework automatically.

So, how to implement automatic generation of database framework with program? Now, let's create a program like this. 5 buttons are established in this procedure: establishing a database, establishing a table, establishing a constraint, establishing a stored procedure, and displaying data. The implementation code is as follows:

Public Class Form1

Inherits System.Windows.Forms.Form

Private Sub button1_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 ()

' Build a Database

End Sub

Private Sub button2_click (ByVal sender as System.Object, ByVal e As

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 () null) ", Con2)

Cmd2. ExecuteNonQuery ()

Con2. Close ()

' Create 2 tables

End Sub

Private Sub button3_click (ByVal sender as System.Object, ByVal e As

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 ()

' Establish a constraint

End Sub

Private Sub button4_click (ByVal sender as System.Object, ByVal e As

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 *"

KK ", Con2)

Com. ExecuteNonQuery ()

Con2. Close ()

' Establish a stored procedure

End Sub

Private Sub button5_click (ByVal sender as System.Object, ByVal e As

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

' Show data

End Sub

End Class