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