How to create a store chain database

Chain software, the database structure of two kinds, one is all the data are placed in the same database, including headquarters, the other is a headquarters independent database, each branch of a database, and I often used is a separate database.

Each of these two methods have advantages, data placed in the same database, the data between the branches will be very convenient, there are statistics and the like, but also directly in the database operation, not so much trouble, the bad place is, data a lot, inconvenience to maintenance, but also to consider paging and other issues. And the second method is just on and the first good or bad, but I think the separation of the data will be very clear, maintenance convenience, of course, this is only for our software database interaction between the situation, there may be more frequent interaction between the database, is the first method, there is no maintenance problems, will also choose the first, Each has good or bad, to see the use of everyone.

The standalone database has to deal with the problem of how to create a database when the store is created, because the software is always updated, that is, the database is always updated, so creating a store database must also be flexible.

(Mssql) The earliest method: will put a database backup file (BAK) at Headquarters, each update is to overwrite the Bak file, and the idea of creating a database, 1. Back up the model database to model.bak,2. Restore the Bak file to the model, 3. Directly with the CREATE DATABASE statement can be built a branch databases, 4. Restore back to model database with Model.bak, "Note: When you create a database, will be created with the model database template, which is what this "

This method can be implemented to create a branch, but rather stupid, each update to add a database bak file in the update file, and the database has been increasing, although the empty database, only the table structure, and so on these things, but also have to 8m+, which in the update brought a lot of trouble, and then think of a method, On the basis of the original improvement.

(Mssql) Improved method: 1. When the software is installed, a template database is created on the Mssql server, and the template database can be regarded as a branch database, the database will be updated when the software is updated, and the template database will be backed up after the update, overwriting the Bak file in the headquarters. How do I create a database when I create a store later or not, as above. The main improvement here is the database template problem, so that you do not have to update the database files every time.

The original software has been using this method until now.

Recently a set of new software, in the design of the database is also encountered this problem, I always feel that the improved method is not very convenient, and then think of the following improvement methods.

1. Keep using the template database, template database with the update of the branch, the Bak file is not used, void. 2. When creating a store, create an empty database, and then read all the structure of the template database through the program to execute on the new database, simply by using the program to replicate all the structure of the template database.

This will not have to use the heavy files such as Bak, and the database can be independent of a server, the old method is the database and software must be the same server, because to rely on the Bak file in the headquarters, and now the database and software can be detached, template database protection of the latest, Branch to create a new database replication template The structure of the database, the problem is resolved.

With a talk about MySQL, because our software is compatible with a variety of databases, MySQL is among them, the previous MySQL and MSSQL idea is the same, all by updating the database files, with the database backup files to create a new database, Later MySQL also changed to this optimal method: 1. The software is installed, automatically create a template database, 2. When you create a new store, you back up the database to a SQL file, and then you modify the database name in the SQL file in the program, 3. Execute the SQL file. Here MySQL and MSSQL is not the same place, MSSQL copy table structure, must be a template to read out the data and then execute on the new database, and MySQL backup is to export all the structure of the database, we just need to modify a database name for the new database execution.

Here's how to copy a database in C #:

public void Initialdata (string modeldatabasename, String DataBaseName) {#region Processing table and table data stri ng strsql_getusertable = string. Format ("select * FROM {0}.dbo.            Getusertable ORDER by Id,columnsort ", modeldatabasename); DataTable dt_getusertable = Dbhelper.executequery (CommandType.Text, strsql_getusertable, null).            Tables[0]; if (Dt_GetUserTable.Rows.Count > 0) {//id,tablename,columnsort,columnname,tablekey,columnda Tatype,columndatalen1//columndatalen2,columndatafloat,allowisnull,defaultdata String TableN                ame = "";                StringBuilder strcreatetablesql = new StringBuilder ();                StringBuilder strkeysql = new StringBuilder ();                StringBuilder strinsertdata = new StringBuilder (); foreach (DataRow dr in Dt_getusertable.rows) {if (dr["tablename"].                ToString ()! = TableName) {        if (!string.                                                       IsNullOrEmpty (Strcreatetablesql.tostring ())) { if (Strkeysql.length > 0) {strkeysql.remove (Strkeysql.leng                                Th-1, 1);                                Strcreatetablesql.appendformat ("PRIMARY KEY CLUSTERED ({0})" on [PRIMARY] ", strkeysql.tostring ());                            Strkeysql.remove (strkeysql.length-1, 1); } else {if (Strcreatetablesql.tostri Ng ().                                EndsWith (",")) Strcreatetablesql.remove (strcreatetablesql.length-1, 1);                            Strcreatetablesql.append (")");                            } dbhelper.executenonquery (DataBaseName, strcreatetablesql.tostring (), NULL); Dbhelper.executenonquery (CommandtypE.text, String. Format ("insert INTO {0}.dbo.{ 1} select * FROM {2}.dbo.                        {1} ", DataBaseName, TableName, modeldatabasename), NULL); } tableName = dr["TableName"].                        ToString (); Strcreatetablesql.clear ();                        Strcreatetablesql = new StringBuilder (); Strkeysql.clear ();                        Strkeysql = new StringBuilder ();                    Strcreatetablesql.appendformat ("CREATE table {0} (", tableName); } strcreatetablesql.append (Dbhelper.getdatatype (dr["ColumnName"). ToString (), dr["Columndatatype"]. ToString (), Dbhelper.getint (dr["Columndatalen2"]. ToString ()), Dbhelper.getint (dr["Columndatafloat"]. ToString ()), dr["Allowisnull"]. ToString (), dr["Tablekey"].                ToString (), ref strkeysql)); } if (!string.                    IsNullOrEmpty (Strcreatetablesql.tostring ())) {if (Strkeysql.length > 0)          {              Strkeysql.remove (strkeysql.length-1, 1);                        Strcreatetablesql.appendformat ("PRIMARY KEY CLUSTERED ({0})" on [PRIMARY] ", strkeysql.tostring ());                    Strkeysql.remove (strkeysql.length-1, 1); } else {if (strcreatetablesql.tostring ().                        EndsWith (",")) Strcreatetablesql.remove (strcreatetablesql.length-1, 1);                    Strcreatetablesql.append (")");                    } dbhelper.executenonquery (DataBaseName, strcreatetablesql.tostring (), NULL); Dbhelper.executenonquery (CommandType.Text, String. Format ("insert INTO {0}.dbo.{ 1} select * FROM {2}.dbo.                {1} ", DataBaseName, TableName, modeldatabasename), NULL); }} #endregion #region stored procedures, views, functions, triggers StringBuilder strSQL = new StringBuilder            (); Strsql.appendformat (" Select Name,type,definition from {0}.dbo.            GETUSERPV ", modeldatabasename); DataTable DT_GETUSERPV = Dbhelper.executequery (CommandType.Text, strsql.tostring (), null).            Tables[0];                if (Dt_GetUserPV.Rows.Count > 0) {StringBuilder strtest = new StringBuilder (); foreach (DataRow dr in Dt_getuserpv.rows) {dbhelper.executenonquery (DataBaseName, St Ring. Format ("{0}", dr["definition"].                ToString ()), NULL); }} #endregion}

How to create a store chain database