CN106649449A - Internal and external network data exchange method based on SQLite - Google Patents

Abstract

The invention provides an internal and external network data exchange method based on SQLite, and belongs to the field of network data exchange. The method comprises the steps of configuring tables needing to be exchanged to an intermediate table, based on a pre-made table, quickly exporting and generating an SQLite database file, importing the SQLite database file into the system, and cleaning the data. The internal and external network data exchange method based on SQLite has the advantages of being capable of supporting increment exchange, occupying low resources, processing in fast speed, and the like. The data exchange efficiency and safety between non-communicating networks are enhanced.

Description

A kind of intranet and extranet method for interchanging data based on SQLite

Technical field

The present invention relates to Data Interchange Technology, more particularly to a kind of intranet and extranet method for interchanging data based on SQLite.

Background technology

Method for interchanging data between traditional intranet and extranet, has dependence database software mostly, generates file speed slowly, The low problem of security, and because data exchange occurrence frequency is high, need input more time and manpower.SQLite is one Kind occupancy resource is very low, processing speed Lightweight Database quickly, while having zero configuration, being stored in single disk file One complete database feature.

The content of the invention

In order to solve the problem, the present invention proposes a kind of intranet and extranet method for interchanging data based on SQLite.Can be real Now be independent of database software, occupy little space, conveniently and efficiently carry out data exporting, improve software performance and can Maintainability.

A kind of intranet and extranet method for interchanging data based on SQLite, including

1）By visualization interface the table for exchanging and key message will be needed to be configured in middle table；

2）The database file of SQLite is quickly derived and generated to prefabricated table according to its switch type；

3）The database file of SQLite is imported in system；Carry out data dump.

1. configuration database table.The major key of the prefabricated table name of visualization interface and table, while selecting the switch type of table：All Exchange or increment is exchanged.Main method is as follows：

private void btnSave_Click(object sender, EventArgs e)

{

string sql = string.Empty;

OracleConnection con = new OracleConnection(this.dbStr);

con.Open();

OracleCommand cmd = new OracleCommand(string.Empty, con);

// first obtain exchanged form

string type = cbType.Text.Substring(0, cbType.Text.IndexOf(' |'));

// verify that the table whether there is

sql = "select count(1) from all_tables where table_name = ' {0}'";

sql = string.Format(sql, this.tbTable.Text.Trim());

cmd.CommandText = sql;

int val = Convert.ToInt32(cmd.ExecuteScalar());

if (val == 0)

{

MessageBox.Show (" there is no table in database "+this.tbTable.Text);

return;

}

// checking major key whether there is

string[] keys = this.tbKey.Text.Trim().Split(',');

foreach (string key in keys)

{

sql = "select count(1) from dba_tab_columns where table_ name='{0}' and column_name = '{1}'";

sql = string.Format(sql, this.tbTable.Text.Trim(), key);

cmd.CommandText = sql;

val = Convert.ToInt32(cmd.ExecuteScalar());

if (val == 0)

{

MessageBox.Show (" table does not have row name "+key);

return;

}

}

Preset table is derived in // insertion

sql = "insert into JGSJJHDC(JGSJJHDC_ID, JGSJJHDC_Table, JGSJJHDC_Name, JGSJJHDC_Module, JGSJJHDC_TYPE, JGSJJHDC_KEY) values('{0}', ' {1}', '{2}', '{3}', '{4}', '{5}')";

sql = string.Format(sql, tbID.Text, tbTable.Text.Trim(), tbName.Text.Trim(), tbModule.Text.Trim(), type, tbKey.Text.Trim());

cmd.CommandText = sql;

cmd.ExecuteNonQuery();

// if increment is exchanged, need to create trigger and timestamp. purpose is capture incremental data

if (type == "UP")

{

// create trigger

TriggerHelper helper = new TriggerHelper(this.dbStr);

cmd.CommandText = helper.GetCreateSql(tbTable.Text.Trim ());

cmd.ExecuteNonQuery();

}

con.Close();

con.Dispose();

this.btnSave.Enabled = false;

MessageBox.Show (" is preserved successfully！");

}

2. the database file of SQLite is derived and generated to prefabricated table, by the data Cun Chudao SQLite databases in table In DBF files.Main process includes：2.1st, the table for needing to exchange is obtained according to preset information；2.2nd, sentenced by timestamp field It is disconnected to meet derived record bar number；2.3rd, using the direct-connected databases of ADO.NET, it would be desirable to which derived data are derived one by one and are saved as one DBF tables are opened, the file for being saved as the entitled .db of suffix is encrypted；2.4th, updated time stamp record and system journal.

Main method is as follows：

private void btnExport_Click(object sender, EventArgs e)

{

float count = 0.0f;

int total = 0;

if (this.tbFile.Text == string.Empty)

{

MessageBox.Show (" asking select file ");

return;

}

if (File.Exists(this.tbFile.Text))

{

MessageBox.Show (" this file is existing, please reselect ");

return;

}

SqlLiteHelper sqllite = new SqlLiteHelper(this.tbFile.Text);

this.gridView1.CloseEditor();

DataTable dt = (DataTable)this.gridControl1.DataSource;

total = dt.Select("selected=True").Length;

foreach (DataRow row in dt.Rows)

{

if (Convert.ToBoolean(row["selected"]) == false)

continue;

DataSet ds = exportor.GetTableData(row["JGSJJHDC_Table"] .ToString());

// copy data

sqllite.CopyDataTable(ds.Tables[0], row["JGSJJHDC_ Table"].ToString());

// record maximum time stamp

if (row["JGSJJHDC_Type"].ToString().ToUpper() == "UP")

exportor.RecordLastStamp(row["JGSJJHDC_Table"] .ToString());

// derive and delete data, also to empty after having led

DataTable dtDelete = exportor.GetDeleteData(row[" JGSJJHDC_Table"].ToString());

if (dtDelete.Rows.Count > 0)

{

sqllite.CopyDataTable(dtDelete, "JGSJJHSC");

exportor.RemoveDeleteData(row["JGSJJHDC_Table"] .ToString());

}

count++;

this.pbTotal.Show();

this.pbTotal.Value = Convert.ToInt32((float)count / total * 100);

}

// // derive concordance list

sqllite.CopyDataTable(dt, "JGSJJHDC");

this.pbTotal.Value = 0;

MessageBox.Show (" is derived successfully！");

}

3. the database file of SQLite is imported in system.Mainly it is processed as：Concordance list, number in decryption db compressed files According to preset information table information；Data are imported using BulkCopy.Main code is as follows：

public void ImportDataToTable(DataSet ds, string tableName, string keys)

{

DataTable dt = ds.Tables[0];

StringBuilder insertCol = new StringBuilder();

string cmdSql = string.Empty;

OracleConnection con = new OracleConnection(this.dbStr);

con.Open();

OracleCommand sqlCommand = new OracleCommand(string.Empty, con);

// obtain row name character string

foreach (DataColumn col in dt.Columns)

{

if (col.ColumnName.ToUpper().IndexOf("_DATATIME") >= 0)

continue;

insertCol.Append(col.ColumnName + ",");

}

// set up interim table and copy data bulkcopy in interim table to

cmdSql = string.Format("create table {0}_COPY as select * from {0} where 0=1",tableName);

sqlCommand.CommandText = cmdSql;

sqlCommand.ExecuteNonQuery();

OracleConnection destinationConnection = new OracleConnection (this.dbStr);

destinationConnection.Open();

// be only mounted with just to be used on the machine of oracle 11G clients, to use ODP.NET groups OracleDataAccess.DLL in part;

OracleBulkCopy bulkCopy = new OracleBulkCopy(this.dbStr);

bulkCopy.BatchSize = 1000;

bulkCopy.NotifyAfter = 1000;

The title of object table on // server

bulkCopy.DestinationTableName = string.Format("{0}_COPY", tableName);

try

{

if (dt != null && dt.Rows.Count != 0)

bulkCopy.WriteToServer(dt);

}

catch (Exception ex)

{

bulkCopy.Close();

}

finally

{

if (bulkCopy != null)

destinationConnection.Close();

}

// ID identical data are deleted, insert incremental data

OracleTransaction tran = con.BeginTransaction();

try

{

cmdSql = string.Format("delete from {0} where exists (select 1 from {0}_COPY where {1})", tableName, this.GetDeleteWhere3 (tableName, keys));

sqlCommand.CommandText = cmdSql;

sqlCommand.Transaction = tran;

sqlCommand.ExecuteNonQuery();

cmdSql = string.Format("insert into {0}({1}) select {1} from {0}_COPY", tableName, insertCol.ToString().TrimEnd(','));

sqlCommand.Transaction = tran;

sqlCommand.CommandText = cmdSql;

sqlCommand.ExecuteNonQuery();

tran.Commit();

}

catch (Exception ex)

{

tran.Rollback();

con.Close();

Throw new Exception (" synchrodata failure "+ex.Message);

}

// delete interim table

cmdSql = string.Format("drop table {0}_COPY", tableName);

sqlCommand.CommandText = cmdSql;

sqlCommand.Transaction = null;

sqlCommand.ExecuteNonQuery();

con.Close();

}

4. data dump.Data that are qualified and being derived are removed, main code is as follows：

public void ClearTableData(string tableName, string delSql, string begindate, string enddate)

{

string sql = delSql;

DataSet ds = new DataSet();

if (begindate == "00010101")

begindate = "00000000";

if (enddate == "00010101")

enddate = "99999999";

sql = sql.Replace("{BEGINDATE}", begindate).Replace(" {begindate}", begindate);

sql = sql.Replace("{ENDDATE}", enddate).Replace("{enddate}", enddate);

TriggerHelper trigger = new TriggerHelper(this.dbStr);

string createTriggerSql = trigger.GetCreateSql(tableName);

string dropTriggerSql = trigger.GetRemoveSql(tableName);

OracleConnection dbcon = new OracleConnection(this.dbStr);

dbcon.Open();

OracleCommand command = new OracleCommand(string.Empty, dbcon);

OracleTransaction tran = dbcon.BeginTransaction();

try

{

command.CommandText = dropTriggerSql;

command.Transaction = tran;

command.ExecuteNonQuery();

command.CommandText = sql;

command.Transaction = tran;

command.ExecuteNonQuery();

// only when increment is exchanged, just rebuild trigger

command.CommandText = "select JGSJJHDC_TYPE from JGSJJHDC where JGSJJHDC_Table = '" + tableName + "'";

command.Transaction = tran;

string type = command.ExecuteScalar().ToString();

if (type == "UP")

{

command.CommandText = createTriggerSql;

command.Transaction = tran;

command.ExecuteNonQuery();

}

tran.Commit();

}

catch (Exception ex)

{

tran.Rollback();

Throw new Exception (" remove table "+tableName+" when malfunction：" + ex.Message);

}

finally

{

dbcon.Close();

dbcon.Dispose();

}

}

Based on the intranet and extranet method for interchanging data of SQLite, by good interface, shirtsleeve operation realize it is a kind of independently of Platform, quick and safety, comprising the method for interchanging data for all exchanging and increment is exchanged, greatly reduces manpower and uses into This, improves the speed of data exchange.

Specific embodiment

Below more detailed elaboration is carried out to present disclosure：

The present invention includes：Configuration database table, derives and generates the database file of SQLite, by SQLite's by prefabricated table Database file is imported in system, data dump.After the completion of four steps, it is possible to realize the data exchange of intranet and extranet.

To be easier to understand advantages of the present invention, purpose, it is understandable, below an explanation is done with regard to specific embodiment.

1., by table derived from the prefabricated needs of visualization interface, the essential information of addition table is saved in middle table.

2. call the methods such as the CopyDataTable of SQLite that the table selected in list is exported to one by one the number of SQLite According to storehouse table, i.e. then DBF tables generate the encryption file of the entitled .db of suffix together with concordance list.

3. the BulkCopy methods for calling database decompress the file of .db, then get each DBF table, will Data therein are imported to again in corresponding database table.

4., in order to improve the utilization ratio of database, the data dump that part meets deletion condition is fallen.

The method can be efficiently solved between heterogeneous networks, especially the data exchange demand between intranet and extranet.The party Method is independent of database software due to it, make use of SQLite databases so that data exchange becomes simple, quick, safe, together When take up room minimum, convenient operation.

At present the method finds application in many government-sponsored projects, and the mode that the increment that it is provided is exchanged is caused Data exchange is more targeted, more validity, greatly reduces manpower use cost, improves software performance and maintainability etc. Feature, has good value for applications.

Claims (5)

1. a kind of intranet and extranet method for interchanging data based on SQLite, it is characterised in that include

1）By visualization interface the table for exchanging and key message will be needed to be configured in middle table；

2）The database file of SQLite is quickly derived and generated to prefabricated table according to its switch type；

3）The database file of SQLite is imported in system；Carry out data dump.

2. method according to claim 1, it is characterised in that

1）In, by the prefabricated table name of the visualization interface of What You See Is What You Get and the major key of table, while selecting the switch type of table： All exchange or increment is exchanged, in being stored in database；Code is as follows

private void btnSave_Click(object sender, EventArgs e)

{

string sql = string.Empty;

OracleConnection con = new OracleConnection(this.dbStr);

con.Open();

OracleCommand cmd = new OracleCommand(string.Empty, con);

// first obtain exchanged form

string type = cbType.Text.Substring(0, cbType.Text.IndexOf(' |'));

// verify that the table whether there is

sql = "select count(1) from all_tables where table_name = ' {0}'";

sql = string.Format(sql, this.tbTable.Text.Trim());

cmd.CommandText = sql;

int val = Convert.ToInt32(cmd.ExecuteScalar());

if (val == 0)

{

MessageBox.Show (" there is no table in database "+this.tbTable.Text);

return;

}

// checking major key whether there is

string[] keys = this.tbKey.Text.Trim().Split(',');

foreach (string key in keys)

{

sql = "select count(1) from dba_tab_columns where table_ name='{0}' and column_name = '{1}'";

sql = string.Format(sql, this.tbTable.Text.Trim(), key);

cmd.CommandText = sql;

val = Convert.ToInt32(cmd.ExecuteScalar());

if (val == 0)

{

MessageBox.Show (" table does not have row name "+key);

return;

}

}

Preset table is derived in // insertion

sql = "insert into JGSJJHDC(JGSJJHDC_ID, JGSJJHDC_Table, JGSJJHDC_Name, JGSJJHDC_Module, JGSJJHDC_TYPE, JGSJJHDC_KEY) values('{0}', ' {1}', '{2}', '{3}', '{4}', '{5}')";

sql = string.Format(sql, tbID.Text, tbTable.Text.Trim(), tbName.Text.Trim(), tbModule.Text.Trim(), type, tbKey.Text.Trim());

cmd.CommandText = sql;

cmd.ExecuteNonQuery();

// if increment is exchanged, need to create trigger and timestamp. purpose is capture incremental data

if (type == "UP")

{

// create trigger

TriggerHelper helper = new TriggerHelper(this.dbStr);

cmd.CommandText = helper.GetCreateSql(tbTable.Text.Trim ());

cmd.ExecuteNonQuery();

}

con.Close();

con.Dispose();

this.btnSave.Enabled = false;

MessageBox.Show (" is preserved successfully！");

}。

3. method according to claim 1, it is characterised in that

2）In, the database file for needing to import is selected, using ADO.NET immediate operands according to storehouse, the data in table are deposited In storing up the DBF tables of middle database-SQLite databases, while being encrypted, and index and daily record are created；Wherein increment is handed over Change is to need derived data by arranging timestamp field automatic decision；Main process includes：2.1）, obtained according to preset information Take the table for needing to exchange；2.2）, by timestamp field judge meet derived record bar number；2.3）, using the direct-connected numbers of ADO.NET According to storehouse, it would be desirable to which derived data derive one by one and be saved as a sheet by a sheet DBF tables, are encrypted the file for being saved as the entitled .db of suffix； 2.4）, updated time stamp record and system journal；Code is as follows：

private void btnExport_Click(object sender, EventArgs e)

{

float count = 0.0f;

int total = 0;

if (this.tbFile.Text == string.Empty)

{

MessageBox.Show (" asking select file ");

return;

}

if (File.Exists(this.tbFile.Text))

{

MessageBox.Show (" this file is existing, please reselect ");

return;

}

SqlLiteHelper sqllite = new SqlLiteHelper(this.tbFile.Text);

this.gridView1.CloseEditor();

DataTable dt = (DataTable)this.gridControl1.DataSource;

total = dt.Select("selected=True").Length;

foreach (DataRow row in dt.Rows)

{

if (Convert.ToBoolean(row["selected"]) == false)

continue;

DataSet ds = exportor.GetTableData(row["JGSJJHDC_Table"] .ToString());

// copy data

sqllite.CopyDataTable(ds.Tables[0], row["JGSJJHDC_ Table"].ToString());

// record maximum time stamp

if (row["JGSJJHDC_Type"].ToString().ToUpper() == "UP")

exportor.RecordLastStamp(row["JGSJJHDC_Table"] .ToString());

// derive and delete data, also to empty after having led

DataTable dtDelete = exportor.GetDeleteData(row[" JGSJJHDC_Table"].ToString());

if (dtDelete.Rows.Count > 0)

{

sqllite.CopyDataTable(dtDelete, "JGSJJHSC");

exportor.RemoveDeleteData(row["JGSJJHDC_Table"] .ToString());

}

count++;

this.pbTotal.Show();

this.pbTotal.Value = Convert.ToInt32((float)count / total * 100);

}

// // derive concordance list

sqllite.CopyDataTable(dt, "JGSJJHDC");

this.pbTotal.Value = 0;

MessageBox.Show (" is derived successfully！");

}。

4. method according to claim 1, it is characterised in that

3）In, in the network for needing to import data, the file of the entitled .db of derived suffix before selection, by it after decryption In DBF tables read out and be put in classes of data entities, it is by db transaction that data are disposable using BulkCopy methods All import；Code is as follows：

public void ImportDataToTable(DataSet ds, string tableName, string keys)

{

DataTable dt = ds.Tables[0];

StringBuilder insertCol = new StringBuilder();

string cmdSql = string.Empty;

OracleConnection con = new OracleConnection(this.dbStr);

con.Open();

OracleCommand sqlCommand = new OracleCommand(string.Empty, con);

// obtain row name character string

foreach (DataColumn col in dt.Columns)

{

if (col.ColumnName.ToUpper().IndexOf("_DATATIME") >= 0)

continue;

insertCol.Append(col.ColumnName + ",");

}

// set up interim table and copy data bulkcopy in interim table to

cmdSql = string.Format("create table {0}_COPY as select * from {0} where 0=1",tableName);

sqlCommand.CommandText = cmdSql;

sqlCommand.ExecuteNonQuery();

OracleConnection destinationConnection = new OracleConnection (this.dbStr);

destinationConnection.Open();

// be only mounted with just to be used on the machine of oracle 11G clients, to use ODP.NET groups OracleDataAccess.DLL in part;

OracleBulkCopy bulkCopy = new OracleBulkCopy(this.dbStr);

bulkCopy.BatchSize = 1000;

bulkCopy.NotifyAfter = 1000;

The title of object table on // server

bulkCopy.DestinationTableName = string.Format("{0}_COPY", tableName);

try

{

if (dt != null && dt.Rows.Count != 0)

bulkCopy.WriteToServer(dt);

}

catch (Exception ex)

{

bulkCopy.Close();

}

finally

{

if (bulkCopy != null)

destinationConnection.Close();

}

// ID identical data are deleted, insert incremental data

OracleTransaction tran = con.BeginTransaction();

try

{

cmdSql = string.Format("delete from {0} where exists (select 1 from {0}_COPY where {1})", tableName, this.GetDeleteWhere3 (tableName, keys));

sqlCommand.CommandText = cmdSql;

sqlCommand.Transaction = tran;

sqlCommand.ExecuteNonQuery();

cmdSql = string.Format("insert into {0}({1}) select {1} from {0}_COPY", tableName, insertCol.ToString().TrimEnd(','));

sqlCommand.Transaction = tran;

sqlCommand.CommandText = cmdSql;

sqlCommand.ExecuteNonQuery();

tran.Commit();

}

catch (Exception ex)

{

tran.Rollback();

con.Close();

Throw new Exception (" synchrodata failure "+ex.Message);

}

// delete interim table

cmdSql = string.Format("drop table {0}_COPY", tableName);

sqlCommand.CommandText = cmdSql;

sqlCommand.Transaction = null;

sqlCommand.ExecuteNonQuery();

con.Close();

}。

5. method according to claim 1, it is characterised in that

According to the condition of deletion, qualified data in middle table are removed, further improve the utilization ratio of database；Main generation Code is as follows：

public void ClearTableData(string tableName, string delSql, string begindate, string enddate)

{

string sql = delSql;

DataSet ds = new DataSet();

if (begindate == "00010101")

begindate = "00000000";

if (enddate == "00010101")

enddate = "99999999";

sql = sql.Replace("{BEGINDATE}", begindate).Replace(" {begindate}", begindate);

sql = sql.Replace("{ENDDATE}", enddate).Replace("{enddate}", enddate);

TriggerHelper trigger = new TriggerHelper(this.dbStr);

string createTriggerSql = trigger.GetCreateSql(tableName);

string dropTriggerSql = trigger.GetRemoveSql(tableName);

OracleConnection dbcon = new OracleConnection(this.dbStr);

dbcon.Open();

OracleCommand command = new OracleCommand(string.Empty, dbcon);

OracleTransaction tran = dbcon.BeginTransaction();

try

{

command.CommandText = dropTriggerSql;

command.Transaction = tran;

command.ExecuteNonQuery();

command.CommandText = sql;

command.Transaction = tran;

command.ExecuteNonQuery();

// only when increment is exchanged, just rebuild trigger

command.CommandText = "select JGSJJHDC_TYPE from JGSJJHDC where JGSJJHDC_Table = '" + tableName + "'";

command.Transaction = tran;

string type = command.ExecuteScalar().ToString();

if (type == "UP")

{

command.CommandText = createTriggerSql;

command.Transaction = tran;

command.ExecuteNonQuery();

}

tran.Commit();

}

catch (Exception ex)

{

tran.Rollback();

Throw new Exception (" remove table "+tableName+" when malfunction：" + ex.Message);

}

finally

{

dbcon.Close();

dbcon.Dispose();

}

}。