/SQL/Mysql insert into select transaction c#

Mysql insert into select transaction c#

const string InsertStatement1 = @"INSERT INTO tbl_inv (cname, date, duedate, subtotal, vat, total) 
    VALUES (@cname, @date, @duedate, @subtotal, @vat, @total);";
const string InsertStatement2 = @"INSERT INTO tbl_line (invno, pname, qty, unitprice, amount) 
    VALUES (@invno, @pname, @qty, @unitprice, @amount);";

cn.Open();
using (MySqlTransaction sqlTrans = cn.BeginTransaction())
using (MySqlCommand sqlCommand = new MySqlCommand(InsertStatement1, cn, sqlTrans))
{
    sqlCommand.Parameters.Add("@cname", comboBox1.Text);
    sqlCommand.Parameters.Add("@date", dateTimePicker1.Value.ToString("yyyy-MM-dd"));
    sqlCommand.Parameters.Add("@duedate", dateTimePicker2.Value.ToString("yyyy-MM-dd"));
    sqlCommand.Parameters.Add("@subtotal", textBox6.Text);
    sqlCommand.Parameters.Add("@vat", textBox7.Text);
    sqlCommand.Parameters.Add("@total", textBox9.Text);
    sqlCommand.ExecuteNonQuery();

    sqlCommand.Parameters.Clear();
    sqlCommand.CommandText = "SELECT LAST_INSERT_ID();";
    var o = sqlCommand.ExecuteScalar();
    if (o is Int64)
    {
        var id = (Int64)o;
        foreach (DataGridViewRow row in mg2.Rows)
        {
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@invno", id);
            sqlCommand.Parameters.AddWithValue("@pname", row.Cells[1].Value);
            sqlCommand.Parameters.AddWithValue("@qty", row.Cells[2].Value);
            sqlCommand.Parameters.AddWithValue("@unitprice", row.Cells[3].Value);
            sqlCommand.Parameters.AddWithValue("@amount", row.Cells[4].Value);
            sqlCommand.CommandText = InsertStatement2;
            sqlCommand.ExecuteNonQuery();
        }
    }
    sqlTrans.Commit();
}

Source: stackoverflow.com

Similar codes

8 votes149 views3 answers
Find table for column name in sql
SQL
4 votes140 views4 answers
Mysql get date from datetime
SQL
0 vote150 views1 answer
Mysql command line top 10
SQL
0 vote131 views1 answer
Mysqli_real_connect(): (28000/1045): Access denied for user 'u0738423'@'www8.aname.net' (using password: YES)
SQL
1 vote110 views4 answers
Between vs in sql
SQL
1 vote125 views2 answers
Replace text in sql
SQL
0 vote209 views1 answer
Drop check command
SQL
0 vote76 views3 answers
Django.core.exceptions.improperlyconfigured: error loading mysqldb module. mac
SQL
1 vote129 views1 answer
Postgresql contains
SQL
1 vote139 views1 answer
How to see which columns are indexxed mysql
SQL