C# MySql and Accounting Software

Hello,

You will have to to this at the time of insert. First add a column say record_id INTEGER in the table and have a primary key as record_id + account_id (column containing the vlaues Sales, Discount, Cash etc). 
Then define following table in your database <pre lang="SQL">CREATE TABLE sequence_data (
    sequence_name       varchar(100) NOT NULL,
    sequence_increment  int(11) unsigned NOT NULL DEFAULT 1,
    sequence_min_value  int(11) unsigned NOT NULL DEFAULT 1,
    sequence_max_value  bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
    sequence_cur_value  bigint(20) unsigned DEFAULT 1,
    sequence_cycle      boolean NOT NULL DEFAULT FALSE,
    PRIMARY KEY (sequence_name)
)

CREATE FUNCTION nextval (seq_name varchar(100))
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);
 
    SELECT
        sequence_cur_value INTO cur_val
    FROM
        sequence.sequence_data
    WHERE
        sequence_name = seq_name;
 
    IF cur_val IS NOT NULL THEN
        UPDATE
            sequence.sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_name = seq_name;
    END IF;
     RETURN cur_val;
END;

public void createAcTxn(List<AcEntry> entries) {
    string strCon = @"server=localhost;userid=userid;password=pasword;database=mydb";
    int retVal = -1;
    MySqlCommand seqCmd = null;
    MySqlCommand insCmd = null;
    MySqlConnection conn = null;

    try
    {
        conn = new MySqlConnection(cs);
        conn.Open();
        seqCmd = new MySqlCommand("CALL nextval(@seq_name);", conn);
        seqCmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add("@seq_name", System.Data.SqlDbType.VarChar, 40).Value = "ACTXN_SEQ";
        
        retVal = (int) seqCmd.ExecuteScalar();
        insCmd = new MySqlCommand("INSERT actxn(record_id,  account_id, txn_amount) VALUES(@recId, @acId, @amnt)", conn);
        insCmd.Prepare();
        insCmd.Parameters.Add("@recId", SqlDbType.Int);
        insCmd.Parameters.Add("@acId", SqlDbType.VarChar);
        insCmd.Parameters.Add("@amnt", SqlDbType.Decimal);

        foreach(AcEntry entry in entries) {
            incCmd.Parameters(0).Value = retVal;
            incCmd.Parameters(1).Value = entry.AccountId;
            incCmd.Parameters(2).Value = entry.Amount;
            insCmd.ExecuteNonQuery();
        }
    } catch (MySqlException ex) {
        Console.WriteLine("Error: {0}",  ex.ToString());
    } finally {
        doClose(conn);  // A Helper method to cleanup & release the connection
    }
}