Ejemplo n.º 1
0
        //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();
                }
            }
        }
Ejemplo n.º 2
0
        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;
            }
        }