Share a small program (oralce database structure Comparison Program)

In the system, a business database usually has multiple physical databases, such as developing databases, testing databases, and production databases. In addition, there are other databases for other purposes, it is not easy to maintain the consistency of the structures between these databases. Therefore, we have created a simple database table structure comparison program to analyze the differences between different databases. here is just a simple comparison. In fact, on this basis, you can also perform automatic repair of differences. the following code is used:

/Create a comparison result dataset with two columns. The result indicates: // 1st columns, 2nd Column meaning // empty non-null table fields shown in Column 2nd do not exist in 1st databases // non-empty table fields shown in Column 1st do not exist in 2nd databases // non-empty table fields the two empty database tables and fields are the same, however, the data type is different. // null indicates that the tables and fields in the two databases can correspond, but such results are not saved. datatable thetable = new datatable (); thetable. columns. add (New datacolumn ("database1", typeof (string); thetable. columns. add (New datacolumn ("database2", typeof (string); // obtain the names of tables and fields owned by all current users in Oracle, their data types and data lengths, the accuracy is not considered here. Be sure to sort it; otherwise, it will be difficult for the algorithm. string t Hesql = @ "select t2.tname, t1.column _ name, t1.data _ type, t1.data _ length from user_tab_columns T1, Tab T2 where t1.table _ name = t2.tname and t2.tabtype = 'table' order by t2.tname, t1.column _ name "; // datahelper. querydatafromdb () is a simple query statement execution function. datatable thetabs1 = datahelper. querydatafromdb (thesql, "database connection string"); datatable thetabs2 = datahelper. querydatafromdb (thesql, "database connection string"); int thecount1 = 0; int theco Unt2 = 0; // compare. while (thecount1 <thetabs1.rows. count & thecount2 <thetabs2.rows. count) {datarow therow1 = thetabs1.rows [thecount1]; datarow therow2 = thetabs2.rows [thecount2]; string thetabname1 = therow1 ["tname"]. tostring (); string thecol1 = therow1 ["column_name"]. tostring (); string thetype1 = therow1 ["data_type"]. tostring (); int thelen1 = int. parse (therow2 ["data_length"]. tostring (); Str Ing thetabname2 = therow2 ["tname"]. tostring (); string thecol2 = therow2 ["column_name"]. tostring (); string thetype2 = therow2 ["data_type"]. tostring (); int thelen2 = int. parse (therow2 ["data_length"]. tostring (); int theret1 = string. compare (thetabname1, thetabname2); // compared to a table, if the table name is not equal because it has been sorted, it indicates that the "small" data does not exist in the other data, A small index is added. // compare fields if they are equal. if (theret1> 0) {datarow theretrow = thetable. newrow (); thetable. row S. add (theretrow); theretrow [1] = "\ r \ n" + thetabname2 + "" + thecol2; thecount2 ++; continue;} If (theret1 <0) {datarow theretrow = thetable. newrow (); thetable. rows. add (theretrow); theretrow [0] = "\ r \ n" + thetabname1 + "" + thecol1; thecount1 ++; continue;} // The table name is the same and the comparison field is used, because the data has been sorted, it indicates that the field "small" does not exist in the data of the other side, and the index of the small side is increased. // if they are equal, the data type and length will be compared. int theret2 = string. compare (thecol1, thecol2); If (theret2> 0) {datarow ther Etrow = thetable. newrow (); thetable. rows. add (theretrow); theretrow [1] = "\ r \ n" + thetabname2 + "" + thecol2; thecount2 ++; continue;} If (theret2 <0) {datarow theretrow = thetable. newrow (); thetable. rows. add (theretrow); theretrow [0] = "\ r \ n" + thetabname1 + "" + thecol1; thecount1 ++; continue;} // if the type or length is inconsistent, all are output. If (thetype1! = Thetype2 | thelen1! = Thelen2) {datarow theretrow = thetable. newrow (); thetable. rows. add (theretrow); theretrow [0] = "\ r \ n" + thetabname1 + "" + thecol1 + "" + thetype1 + "" + thelen1.tostring (); theretrow [1] = "\ r \ n" + thetabname2 + "" + thecol2 + "+ thetype2 +" "+ thelen2.tostring ();} thecount1 ++; thecount2 ++;} // In this comparison mode, either party has reached the header, and the other party has not compared the data, you just need to output it directly. While (thecount1 <thetabs1.rows. count) {datarow theretrow = thetable. newrow (); thetable. rows. add (theretrow); datarow therow1 = thetabs1.rows [thecount1]; theretrow [0] = "\ r \ n" + therow1 ["tname"]. tostring () + "" + therow1 ["column_name"]. tostring (); thecount1 ++;} while (thecount2 <thetabs1.rows. count) {datarow theretrow = thetable. newrow (); thetable. rows. add (theretrow); datarow therow2 = thetabs1.rows [thecount2]; theretrow [1] = "\ r \ n" + therow2 ["tname"]. tostring () + "" + therow2 ["column_name"]. tostring (); thecount2 ++;} // deal with the database. Here I use odp.net. public class datahelper {public static datatable querydatafromdb (string SQL, string connstr) {oracleconnection theconn = new oracleconnection (connstr); oracledataadapter Theda = new oracledataadapter (SQL, theconn ); dataset theds = new dataset (); Theda. fill (theds); Theda. dispose (); theconn. close (); Return theds. tables [0] ;}}

PS: in fact, we can see from this small program the importance of sorting, because once the data is sorted, a lot of processing is very convenient. If the results on both sides are not sorted, the time complexity will be very high. The time complexity of the preceding algorithm is O (m + n). If there is no sorting, it may reach O (M * n ).