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;
}
}
