Oracl batch add and modify data in one SQL statement, oraclsql

Oracl batch add and modify data in one SQL statement, oraclsql

I have been using it for a long time, and I have been learning or1c. I have also used batch addition for projects (reading and uploading CSV file information and writing qualified information to the database ), when writing, I thought of how fast it could be because of a large amount of data.. NET has not been optimized in depth during development)

1 private string GUID; // GUID uniquely identifies 2 private string filename; // file name 3 private string lmportl_ID; // import ID4 private Int32? Lmportl_date; // import time 5 private string diflag; // International/domestic logo DIFlag

There are special methods for uploading CSV files, as long as you copy them in.

　　

# Region reads the CSV file data to the able /// <summary> /// read the CSV file data to the DataTable /// </summary> // <param name = "fileName"> CSV file path </param> // <returns> returns the DataTable that reads CSV data </returns> public static DataTable OpenCSV (string filePath) {DataTable dt = new DataTable (); FileStream fs = new FileStream (filePath, System. IO. fileMode. open, System. IO. fileAccess. read); StreamReader sr = new StreamReader (fs, Encoding. UT F8); // StreamReader sr = new StreamReader (fs, encoding); // string fileContent = sr. readToEnd (); // record the string strLine = "" of each row read; // record the content of each field in each row record string [] aryLine = null; string [] tableHead = null; // indicates the number of columns int columnCount = 0; // indicates whether the first row bool IsFirst = true is read; // read the CSV data row by row while (strLine = sr. readLine ())! = Null) {if (IsFirst = true) {tableHead = strLine. split (','); if (tableHead. length> 3) // extract the number of columns tableHead = tableHead. skip (0 ). take (3 ). toArray (); IsFirst = false; columnCount = tableHead. length; // create a column for (int I = 0; I <columnCount; I ++) {tableHead [I] = tableHead [I]. replace ("\" "," "); DataColumn dc = new DataColumn (tableHead [I]); dt. columns. add (dc) ;}} else {aryLine = strLine. split (','); if (RyLine. length> 3) aryLine = aryLine. skip (0 ). take (3 ). toArray (); DataRow dr = dt. newRow (); // determines whether the number of rows exceeds 1000 if (dt. rows. count <= 1000) {// whether the length of the Prefix and FormType strings exceeds 3 if (aryLine [0]. length <= 3 & aryLine [1]. length <= 3) {dr [0] = aryLine [0]. replace ("\" "," "); dr [1] = aryLine [1]. replace ("\" "," ");} else {dr [0] = aryLine [0]. substring (0, 3); dr [1] = aryLine [1]. substring (0, 3);} // whether the length of the TicketNo string exceeds 8 if (AryLine [2]. length <= 8) {dr [2] = aryLine [2]. replace ("\" "," ");} else {dr [2] = aryLine [2]. substring (0, 8) ;}} else {return dt; // jump out of the program. The number of rows exceeds 1000. Do not add it.} dt. rows. add (dr) ;}} if (aryLine! = Null & aryLine. length> 0) {dt. defaultView. sort = tableHead [2];} sr. close (); fs. close (); return dt ;}# endregion

The DataTable returned by the uploaded CSV file needs to be converted into a List. EF can be used for direct conversion.

  DataEnt = DataConvertor.GetEntityList<t>(dtb) as List<T>;

Passing List into Method

# Region insert ticket number to convert data to the table /// <summary> /// insert ticket number to convert data to the table /// </summary> /// <param name = ""> table entity </param> /// <returns> </returns> public BaseEntityJsonObject <int> Insert_TRANSFER (List <T> T) {BaseEntityJsonObject <int> result = new BaseEntityJsonObject <int> (); int count = 0; string strSql = ""; try {StringBuilder strBuilder = new StringBuilder (); strBuilder. append ("insert into Table Name (field, field, field)"); strBuilder. append ("select sequence_name.nextval, t. c1, t. c2, t. c3, t. c4, t. c5 FROM ("); foreach (var item in T) {strBuilder. append ("SELECT"); strBuilder. append ("'" + item. field name + "'c1, '" + item. field name + "'c2, '" + item. field name + "'c3,"); strBuilder. append ("'field name' C4, '" + field name + "'c5 from dual union all");} strBuilder. append (") T"); strSql = string. format (strBuilder. toString (); int I = 14; // After the SQL statement is completed, capture the SQL statement here. Otherwise, the error strSql = strSql will be reported. remove (strSql. length-I, 11); count = DbContext. database. executeSqlCommand (strSql);} catch (Exception ex) {ExceptionHandler. handleException (ex);} return result;} # endregion

This is add!

Write batch modification below

Before writing the changes, you must first query the data to be revised and convert the data into a LIst. I will not explain how to query the data, but how to transfer the data to a List.

The process I wrote here is to first write the List into the database view, then insert the data into the pseudo table, and then perform further conditions based on the data in the pseudo table.

# Region modify the converted data to the table /// <summary> /// modify the converted data to the table /// </summary> /// <param name = ""> entity </param> /// <returns> </returns> public BaseEntityJsonObject <int> Update_TRANSFER (List <T> T) {BaseEntityJsonObject <int> result = new BaseEntityJsonObject <int> (); int count = 0; string strSql = ""; try {StringBuilder strBuilder = new StringBuilder (); strBuilder. append ("merge into TRT_TICKETNUMBER_TRANSFER T1 USING ("); // Loop the data to be modified from here, and temporarily place the data in the pseudo table foreach (var item in t) {strBuilder. append ("SELECT" + item. field name + "'A, '" + item. field name to be modified + "'B FROM DUAL t"); strBuilder. append ("union all");} string str2 = strBuilder. toString (); if (str2! = "") {Str2 = str2.Remove (str2.Length-10);} // truncate the SQL statement. Otherwise, an error is reported. Then, concatenate the SQL statement StringBuilder strBuilder2 = new StringBuilder (); strBuilder2.Append (") T2 ON (T1. primary key field in the table = field in the temporary table T2.a)"); strBuilder2.Append ("when matched then "); strBuilder2.Append ("update set T1. field name to be modified = t2. B"); strSql = str2 + strBuilder2.ToString (); count = DbContext. database. executeSqlCommand (strSql); result. payload = count; result. errorCode = 0;} catch (Exception ex) {ExceptionHandler. handleException (ex); LogHelper. error (string. format ("failed to add table data"), ex);} return result ;}# endregion

This completes the data modification,

I was not prepared to write a blog on time,

If there is anything wrong with it, please point it out. You are welcome to point out the problem and then make common progress.