DBImport v3.3 Chinese Version released: database data interchange and document generation tools (required by IT staff)

Preface:

I haven't written any text for a long time. It was three months ago before the previous article. Although I was very busy at work, I still lacked the content and passion for writing, so I didn't do anything.

In the past, I tried to interview people from different levels for interviews over the past month. I was so eager to write a document that I had to endure.

It is estimated that it is also the kind of sentiment text that says ill people and hate iron. It has no nutrition, so I didn't want to write it after I 've got my emotions.

In addition to management, the company has also developed a rapid development framework for information systems. This gives us the opportunity to write and share it with others.

The following is the text.

Background:

The DBImport tool is released in a few versions, including V1.0, V2.0, V3.0, V3.2, and V3.3 released today.

Because you usually use this tool (data import, data export script, and database file generation) less frequently,

In addition, the previous version is very stable and basically meets individual needs. Therefore, the update frequency is not high. A version was released more than a year from V3.0.

This version is mainly driven by the needs of (paid or non-paid) users and the bottom-layer CYQ. Data framework.

Let's take a look at the historical text:

DBImport V3.3 The updated version is as follows:1: When MSSQL, MySql, and Oracle Data are added to each other, field descriptions are converted at the same time.

PS: When importing data, take the instructions in the past. If you want to copy a database of your company and do not have the permission to import or export backups, use this tool to export data.

2: Add a field description script to the database script export.

PS: with, it is also reasonable.

3: Add decimal places for processing, so there will be no loss of precision during data export.

PS: the previous version is on decimal scale. If you need to accurately export the previous version, you need to manually process the generated table structure, modify the decimal number, and then export it again. This time, we will do this directly.

4: add the decimal point field to display the exported database documents.

PS: it works out with 3, 4.

5: add N data types for processing, which may not be common.

PS: This is more extensive because all types of MSSQL are processed, and the underlying code is changed a lot. So there are too many things to be explained with more extensive words.

6. Add whether to use stored procedure paging settings in the configuration item.

PS: Because the bottom-layer CYQ. Data framework removes the paging Stored Procedure of Oracle, configuration items are added to the distributed Stored Procedure of MSSQL but not enabled, so this configuration item is also added to the software.

7. Add the following operation items to the configuration item: A: Batch insert [including primary Keys] B: Batch insert [ignore primary Keys] C: Batch update [automatically identified by primary Keys] D: insert or update [automatic identification based on primary key]

 

PS: this function is very powerful. With option D, data synchronization between two databases can be almost realized, and repeated import N times is not afraid.

:Http://www.cyqdata.com/download/article-detail-42517

 

New Version:

 

Share the issues with this version update: 1: MSSQL:

A: The timestamp type stores binary data, and other database types Use date, which requires special processing.

B: Identify the version numbers of each database. You can obtain them through the ServerVersion attribute of the Connection object. I didn't even care about this field before.

C: Use SqlBulkCopy for batch insertion. Pay attention to the Data Length issue. If the length is truncated, MSSQL will be suspended directly, which is abnormal.

D: The numeric type wood has the corresponding SqlDbType.

E: When the where in id (1, 2, 3, 4,...) expression goes directly to 10 thousand, MSSQL2000 directly times out, and other versions of the database are normal.

F: MSSQL2000 wood has Xml fields, and also has max related items, all need to be converted to other types for processing.

G: insert binary data into the MSSQL database, which can be so full:

UPDATE AllType SET
B5 = newid (),
-- B7 = '<aa> dddd </aa>'
A1 = (SELECT * from openrowset (bulk n 'C: \ Users \ cyq \ Pictures \ ico \ taobao. ico ', SINGLE_BLOB) AS aa)

WHERE id = 12: SQLite:

A: If the field is auto-increment, the field must be the primary key and only this primary key exists.

B: For auto-increment, the keyword of the database script must be prefixed and cannot be defined later. For example:

Positive Solution:
"ID" integer primary key autoincrement not null,
Incorrect:
"ID" integer autoincrement not null,
Primary key ("ID ")

C: For fields of the GUID type, because the storage structure is in hexadecimal format, it is difficult to query. You need to perform the following conversions before performing comparative queries:

String gv = BitConverter. ToString (new Guid ("509e4e37-43ed-4e3d-b3bc-1c0929f0d151"). ToByteArray (). Replace ("-","");

The final condition becomes where id = x 'gv'

3: Access:

A: For GUID, the normal condition is where id = '509e4e37-43ed-4e3d-b3bc-1c0929f0d151 '. However, this condition is only normal during query and deletion, and must be written as follows during update:

Where id = '{509e4e37-43ed-4e3d-b3bc-1c0929f0d151}', so you can add a bracket when you have nothing to do with it.

4: MySql:

A: Text or Blob fields cannot be set as primary keys.

B: For the TinyInt field, to use 0-255, it should be defined as: TinyInt (3) UNSIGNED

C: Only one auto-increment Column exists. If yes, it must be defined as a primary key.

D: (Chinese content or comments) for garbled characters, configure the encoding of my. ini in the MySql installation directory.

5: Oracle:

A: Null values cannot be inserted for fields set to the Not Null attribute. The solution is to insert an empty string.

B: ORA-08002: sequence SEQ1.CURRVAL is not defined in this session, this issue needs to be called first SEQ1.nextval.

C: For where id in (1, 2, 3, 4, 5 ...) the maximum expression is 1000. If this number is exceeded, it must be split into where id in ,.. 1000) or id in (1001,100 2... 2000 )...

D: When executing multiple statements in batches, You can generally use:

Begin
Statement 1;
Statement 2;
Statement 3;
End;

But it cannot be used, for example, the statement is comment on column table. The field is description. The solution is to split it into one execution.

Summary:

It's not too early. I have to go to work tomorrow. I have to leave a comment to 32 younger siblings who support thumb ups:

In the past two days, you only need to send the local information under the contact author (Tab) on the software to the e-mail address under the contact information, and the software registration code sent randomly will be obtained.

Thanks ~~~