//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 btnCopyRows_Click(object sender, EventArgs e)
{
if (cmbFROM.Text.Length == 0 || cmbTO.Text.Length == 0 || DG.Rows.Count == 0)
{
return;
}
//if ((General.dbTypes)General.DB. == General.dbTypes.Access)
// if (General.Connections[cmbSourceServer.SelectedIndex].filename.ToLower().EndsWith("xlsx"))
// if (General.Mes("WARNING! XLSX import is broken, I couldnt find a solution, shows that rows imported but in the end there is no rows in sheet!\r\n\r\nContinue?",MessageBoxIcon.Exclamation,MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.No)
// return;
DataTable sourceDT;
if (cmbFROM.SelectedIndex == 0)
{
sourceDT = sourceDB.getDatatable(txtCustomSQL.Text);
}
else
{
//for xls
if (sourceDB is ADOnet)
{
sourceDT = sourceDB.getDatatable("select * from [" + cmbFROM.Text + "]");
}
else
{
sourceDT = sourceDB.getDatatable("select * from " + cmbFROM.Text);
}
}
if (sourceDT != null && sourceDT.Rows.Count == 0)
{
MessageBox.Show("Source has no rows!", General.apTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
sourceDT.Dispose();
return;
}
List <string> sourceFields = new List <string>();
List <string> destFields = new List <string>();
string INSsql = "";
string INSfields = "";
string INSvalues = "";
string mySQLtunnel_Q_VARS = "";
string mySQLtunnel_Q_S_VARS = "";
for (int i = DG.Rows.Count - 1; i >= 0; i--)
{
if (DG.Rows[i].Cells[1].Value == null || DG.Rows[i].Cells[1].Value.ToString().Length == 0)
{
DG.Rows.RemoveAt(i);
}
else
{
//INSfields += "[" + DG.Rows[i].Cells[1].Value.ToString() + "],";
if (General.DB is MySQLTunnel)
{
INSfields += DG.Rows[i].Cells[1].Value.ToString() + ",";
INSvalues += " ?,";
}
else
{
INSfields += DG.Rows[i].Cells[1].Value.ToString() + ",";
INSvalues += "@" + DG.Rows[i].Cells[1].Value.ToString() + ",";
}
sourceFields.Add(DG.Rows[i].Cells[0].Value.ToString());
destFields.Add(DG.Rows[i].Cells[1].Value.ToString());
}
}
if (INSfields.Length == 0)
{
MessageBox.Show("Nothing to do!", General.apTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
INSfields = INSfields.Substring(0, INSfields.Length - 1);
INSvalues = INSvalues.Substring(0, INSvalues.Length - 1);
INSsql = "INSERT INTO " + cmbTO.Text + "(" + INSfields + ") VALUES (" + INSvalues + ")";
General.dbTypes destDBtype = General.dbTypes.Access;
if (General.DB is SQLServer)
{
destDBtype = General.dbTypes.SQLSERVER;
}
else if (General.DB is MySQL)
{
destDBtype = General.dbTypes.MySQL;
}
else if (General.DB is MySQLTunnel)
{
destDBtype = General.dbTypes.MySQLtunnel;
}
else if (General.DB is SQLite)
{
destDBtype = General.dbTypes.SQLite;
}
else if (General.DB is ADOnet)
{
INSsql = "INSERT INTO [" + cmbTO.Text + "](" + INSfields + ") VALUES (" + INSvalues + ")";
destDBtype = General.dbTypes.Access;
}
object result = null;
if (destDBtype != General.dbTypes.MySQLtunnel)
{
result = WaitWindow.Show(CopyRows, "Init...", INSsql, sourceDT, sourceFields, destFields, cmbTO.Text, destDBtype);
}
else
{
if (chkMYSQLtunnelBATCH.Checked)
{
result = WaitWindow.Show(mySQLtunnel_CopyRowsBATCH, "Init...", INSsql, sourceDT, sourceFields, destFields, cmbTO.Text, destDBtype);
}
else
{
result = WaitWindow.Show(mySQLtunnel_CopyRows, "Init...", INSsql, sourceDT, sourceFields, destFields, cmbTO.Text, destDBtype);
}
}
if (sourceDT != null)
{
sourceDT.Dispose();
}
if (result == null)
{
MessageBox.Show("Aborted by user!", General.apTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (result.ToString() == "ok")
{
sourceDB.Disconnect();
this.DialogResult = System.Windows.Forms.DialogResult.Yes;
}
else
{
if (destDBtype != General.dbTypes.MySQLtunnel)
{
MessageBox.Show(result.ToString(), General.apTitle, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
frmInformation i = new frmInformation(result.ToString());
i.ShowDialog();
}
}
if (cmbFROM.Text.Length == 0 || cmbTO.Text.Length == 0 || DG.Rows.Count == 0)
{
return;
}
}
