internal object DoWork() { // Invoke the worker method and return any results. WaitWindowEventArgs e = new WaitWindowEventArgs(this._Parent, this._Parent._Args); if ((this._Parent._WorkerMethod != null)) { this._Parent._WorkerMethod(this, e); } return(e.Result); }
//List<string> sourceFields4mySQLtunnel = new List<string>(); //List<string> destFieldsT4mySQLtunnel = new List<string>(); //DataTable sourceDT4mySQLtunnel=null; //int sourceDT4mySQLtunnel_counter = 0; //private void mySQLtunnel_CopyRows(object sender, WaitWindowEventArgs e) //{ // sourceDT4mySQLtunnel_counter = -1; // mySQLtunnel_CopyRows2(); //} //private void mySQLtunnel_CopyRows2() //{ // sourceDT4mySQLtunnel_counter += 1; // if (sourceDT4mySQLtunnel_counter < sourceDT4mySQLtunnel.Rows.Count) // { // //WebPostRequest myPost = new WebPostRequest("http://localhost/sensor.php"); // //myPost.Add("keyword", "void"); // //myPost.Add("data", "hello&+-[]"); // //Console.WriteLine(myPost.GetResponse()); // //Console.ReadLine(); // string result = null; // using (var wb = new WebClient()) // { // var parameters = new NameValueCollection(); // parameters["sql"] = "{\"q\": \"" + General.SafeJSON(sql) + "\"}"; // parameters["p"] = General.Connections[ConnIndex].password; // var response = wb.UploadValues(General.Connections[ConnIndex].serverName, "POST", parameters); // result=Encoding.UTF8.GetString(response); // } // } //} private void CopyRows(object sender, WaitWindowEventArgs e) { string INSsql = (string)e.Arguments[0]; DataTable sourceDT = (DataTable)e.Arguments[1]; List <string> sourceFields = (List <string>)e.Arguments[2]; List <string> destFields = (List <string>)e.Arguments[3]; string toTable = (string)e.Arguments[4]; General.dbTypes destDBtype = (General.dbTypes)e.Arguments[5]; if (chkIDENTITY.Checked) { INSsql = "SET IDENTITY_INSERT " + toTable + " ON; " + INSsql; } IDbCommand command = null; if (destDBtype == General.dbTypes.SQLSERVER) { command = new SqlCommand(INSsql); } else if (destDBtype == General.dbTypes.MySQL) { command = new MySqlCommand(INSsql); } else if (destDBtype == General.dbTypes.SQLite) { command = new SQLiteCommand(INSsql); } else if (destDBtype == General.dbTypes.Access) { command = new OleDbCommand(INSsql); } //transaction //http://msdn.microsoft.com/en-us/library/system.data.idbtransaction.aspx //http://msdn.microsoft.com/en-us/library/86773566.aspx IDbTransaction transaction = null; transaction = General.DB.getConnection().BeginTransaction(); //command command.Connection = General.DB.getConnection(); command.Transaction = transaction; //for all records int Count = sourceDT.Rows.Count; IDbDataParameter parameter = null; try { for (int i = 0; i < sourceDT.Rows.Count; i++) { e.Window.Message = "Copying " + i.ToString() + " of " + Count.ToString(); command.Parameters.Clear(); command.CommandText = INSsql; //for all columns for (int x = 0; x < sourceFields.Count; x++) { //create new parameter parameter = command.CreateParameter(); parameter.ParameterName = "@" + destFields[x]; parameter.Value = sourceDT.Rows[i][sourceFields[x]]; //sqlserver get from accdb works without this //if (sourceDT.Rows[i][sourceFields[x]].GetType() == typeof(System.Byte[])) // parameter.DbType = DbType.Binary; //add to command command.Parameters.Add(parameter); //command.Parameters.Add( AddWithValue("@" + destFields[x], sourceDT.Rows[i][sourceFields[x]]); } command.ExecuteNonQuery(); } e.Result = "ok"; transaction.Commit(); transaction.Dispose(); } catch (Exception ex) { // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. General.Mes("Rollback Exception Type: " + ex2.GetType() + "\r\nMessage: " + ex2.Message); } e.Result = ex.Message; } finally { if (command != null) { command.Dispose(); } if (sourceDT != null) { sourceDT.Dispose(); } } }
private void mySQLtunnel_CopyRows(object sender, WaitWindowEventArgs e) { string INSsql = (string)e.Arguments[0]; DataTable sourceDT = (DataTable)e.Arguments[1]; List <string> sourceFields = (List <string>)e.Arguments[2]; List <string> destFields = (List <string>)e.Arguments[3]; INSsql = General.SafeJSON(INSsql); try { //parse server name + add tablename.php string url = General.Connections[General.activeConnection].serverName; int pos = url.LastIndexOf("/"); url = url.Substring(0, pos) + "/trans.php"; string errors = ""; int errorsCount = 0; string result = null; //for all records int Count = sourceDT.Rows.Count; for (int i = 0; i < sourceDT.Rows.Count; i++) { result = null; e.Window.Message = "Copying " + i.ToString() + " of " + Count.ToString() + " - errors : " + errorsCount.ToString(); //JSON OBJECT var rec = new JObject(); //for all columns for (int x = 0; x < sourceFields.Count; x++) { rec[destFields[x]] = sourceDT.Rows[i][sourceFields[x]].ToString(); //create new parameter //parameter = command.CreateParameter(); //parameter.ParameterName = "@" + destFields[x]; //parameter.Value = sourceDT.Rows[i][sourceFields[x]]; } var serialized = JsonConvert.SerializeObject(rec); using (var wb = new WebClient()) { var parameters = new NameValueCollection(); parameters["record"] = serialized; parameters["statement"] = "{\"q\": \"" + INSsql + "\"}"; parameters["p"] = General.Connections[General.activeConnection].password; var response = wb.UploadValues(url, "POST", parameters); result = Encoding.UTF8.GetString(response); } if (result.Contains("error")) { errorsCount += 1; errors += "ERROR on record : \r\n" + rec.ToString() + "\r\n\r\n"; } } if (errors.Length > 0) { e.Result = errors; } else { e.Result = "ok"; } } catch (Exception ex) { e.Result = ex.Message; } }
private void mySQLtunnel_CopyRowsBATCH(object sender, WaitWindowEventArgs e) { string INSsql = (string)e.Arguments[0]; DataTable sourceDTpre = (DataTable)e.Arguments[1]; List <string> sourceFields = (List <string>)e.Arguments[2]; List <string> destFields = (List <string>)e.Arguments[3]; INSsql = General.SafeJSON(INSsql); //clone source table to new datatable to change columns datatype DataTable sourceDTpre2 = sourceDTpre.Clone(); for (int i = 0; i < sourceDTpre2.Columns.Count; i++) { sourceDTpre2.Columns[i].DataType = typeof(string); } foreach (DataRow row in sourceDTpre.Rows) { sourceDTpre2.ImportRow(row); } //clone source table to new datatable to change columns datatype DataTable sourceDT = null; try { //parse server name string url = General.Connections[General.activeConnection].serverName; int pos = url.LastIndexOf("/"); url = url.Substring(0, pos) + "/trans_batch.php"; string errors = ""; int errorsCount = 0; string result = null; //used for per 900records counter int copyRowsIndexPosition = 0; int copyRowsIndexEND_Position = 0; repeatSTEP: //slice source datatable per 900recs sourceDT = copyTo_FROM(sourceDTpre2, copyRowsIndexPosition, out copyRowsIndexEND_Position); //update status e.Window.Message = "Trans " + copyRowsIndexPosition.ToString() + " of " + ((copyRowsIndexPosition + copyRowsIndexEND_Position) - 1).ToString() + " - errors : " + errorsCount.ToString() + " - total : " + sourceDTpre2.Rows.Count.ToString(); ////////////////////JSON DataTable sourceDT2 = CleanDataTable(sourceDT); string h = GetJson4Datatable(sourceDT2); ////////////////////JSON ////POST //var keyValues = new Dictionary<string, string> //{ // { "records", h }, // { "statement", "{\"q\": \"" + INSsql + "\"}" }, // {"p",General.Connections[General.activeConnection].password} //}; //used because there is no timeout //result = HttpPostRequest(url, keyValues); using (var wb = new WebClient()) { var parameters = new NameValueCollection(); parameters["records"] = h; parameters["statement"] = "{\"q\": \"" + INSsql + "\"}"; parameters["p"] = General.Connections[General.activeConnection].password; var response = wb.UploadValues(url, "POST", parameters); result = Encoding.UTF8.GetString(response); } //report error if (result.Contains("error")) { errorsCount += 1; errors += "ERROR on record : \r\n" + copyRowsIndexPosition.ToString() + " - " + (copyRowsIndexPosition + copyRowsIndexEND_Position).ToString() + "\r\n" + result + "\r\n\r\n"; } //report error ////POST if (copyRowsIndexEND_Position == 900) { copyRowsIndexPosition = copyRowsIndexPosition + copyRowsIndexEND_Position;//+1; goto repeatSTEP; } if (errors.Length > 0) { e.Result = errors; } else { e.Result = "ok"; } } catch (Exception ex) { e.Result = ex.Message; } }